0

After learning about dynamic queries, I'm trying to build a couple of base functions C(reate)R(ead)U(pdate)d(elete) for my db saving me to repeat a lot of code for table/view. I want to take advantage of roles and apply security at database layer so to have a dumb UI that only connects and interact with available functions for that roles the user belongs.

I would really appreciate any advice, critic, correction that community of advanced developers could contribute.

This is a continuation of my learning path in Postgres 13.

A brief of my approach to accomplish this is:

ROLES

  • role owner

    • All database, schemas, tables, functions, seq, etc... belongs to it.
    • Everyone is revoked everything over these listed objects.
    • No user/role belong to this role.

  • role login

    • Only granted connection to database.

  • role manager

    • Only granted a subset of wrapper functions.

  • role seller

    • Only granted another subset of wrapper functions.

USERS

  • User1 - Has login and manager.
  • User2 - Has login and seller.

BASE FUNCTION

This is a base function for insert.

  • Owned by owner.
  • Everyone revoked anything on it.
  • Thought to only to be ran by wrapper function owned by owner.
  • Parameter 1 is the table where to work and always will be hardcoded at wrapper.
  • Parameter 2 is a list of allowed columns for operation and always hardcoded at wrapper.
  • Parameter 3 is a json payload with key and values, some of them could be valuable for function itself, others validated against parameter 2 used in the query building and the rest just ignored.
CREATE FUNCTION public.crud__insert (IN _tbl text, IN _cols text[], IN _opts json, OUT _id int)
    LANGUAGE plpgsql STRICT AS
$$
DECLARE
BEGIN
    EXECUTE (
        SELECT concat('INSERT INTO '
                , _tbl
                , '('
                , string_agg(e.key, ', ' ORDER BY ord)
                , ') VALUES ('
                , string_agg(format('%L', e.val), ', ' ORDER BY ord)
                , ') RETURNING id'
        )
    FROM json_each_text(_opts) WITH ORDINALITY e(key, val, ord)
        WHERE e.key = ANY(_cols)
    ) INTO _id;
END;
$$;

WRAPPER FUNCTION

This is a wrapper function for one specific table or view.

  • Owned by owner.
  • Everyone revoked anything on it.
  • manager granted select.
  • Parameter 1 is the json payload to use as parameter 3 in base function.
CREATE FUNCTION public.entity__insert (IN _opts json, OUT _id int)
    LANGUAGE sql STRICT SECURITY DEFINER AS
$$
    SELECT public.crud__insert(
        'public.entity',
        ARRAY['name', 'provider', 'customer'],
        _opts
    );
$$;

You run it by:

SELECT public.entity__insert('{"name": "Richard", "provider": true}');

And it gives you new id.

entity__insert 158

basilean
  • 23
  • 5
  • I don't see how calling functions like that could be simpler or more efficient than issuing the SQL statements directly. – Laurenz Albe May 16 '21 at 15:50
  • 1
    Hello @LaurenzAlbe, maybe insert was not best example, it is more useful for update and list. From UI side, I just need to fill a json instead of worry about specific queries for parameters and types. I want all modifications at db going through these base functions in order to apply auditing just there. – basilean May 16 '21 at 19:16
  • Before you go too far down the road of developing this yourself, you should look into PostgREST and Postgraphile to see if either of those will meet your needs. – Jeremy May 17 '21 at 06:53
  • Hello @Jeremy, thank you for your comment. I'm doing this small project for the love of learning PgPL and GTK. I intend to build a small business client-server gnu application without middleware. – basilean May 17 '21 at 16:04

0 Answers0