0

Has anyone dealt with column-level security "policies" or how to best manage/implement them as they don't exist yet?

In my mind we have great tools for database administrator users to have column level security with grants, but not application users in a manner akin to RLS.

My current solution is to leverage a trigger with a whenClause that checks the permissions. Imagine creating a publishing flow with authors and publishers on the same object

CREATE TABLE posts (
    id serial primary key,
    title text,
    content text,
    published boolean DEFAULT FALSE,
    author_id uuid NOT NULL DEFAULT get_curent_user_id(),
    publisher_id uuid NOT NULL DEFAULT '85d770e6-7c18-4e98-bbd5-160b512e6c23'
);

CREATE TRIGGER ensure_only_publisher_can_publish
    AFTER UPDATE ON posts
    FOR EACH ROW
    WHEN (
        NEW.publisher_id <> get_curent_user_id ()
            AND
        OLD.published IS DISTINCT FROM NEW.published
    )
EXECUTE PROCEDURE throw_error ('OWNED_COLUMNS', 'published');

CREATE TRIGGER ensure_only_publisher_can_publish_insert
    AFTER INSERT ON posts
    FOR EACH ROW
    WHEN (
        NEW.publisher_id <> get_curent_user_id ()
            AND
        NEW.published IS TRUE
    )
EXECUTE PROCEDURE throw_error ('OWNED_COLUMNS', 'published');

If you want to run the example I've included a gist here that wraps all deps in a tx: https://gist.github.com/pyramation/2a7b836ab47a2450b951a256dfe7cbde

It works! The author can create posts, and only the publisher can "publish" them. However it has some disadvantages.

  1. uses triggers, cannot use BYPASSRLS and have to use replication role
  2. Behavior for INSERT to my knowledge requires an understanding of valid or default values

#1 I could manage, I can imagine using the replication role if needed in some places. #2 however, feels clunky and closely coupled to the data model given it requires default or whitelisted values.

Thoughts? Any other solutions out there I should be aware of?

pyramation
  • 1,631
  • 4
  • 22
  • 35
  • caveat: I know that the publisher_id can be set to the current user... let's ignore that for sake of example ;) – pyramation Apr 20 '21 at 00:03
  • 1
    You might want to use a view that shows only the data the user is allowed to see. – Laurenz Albe Apr 20 '21 at 05:17
  • This is def worth considering. The underlying table has RLS to handle both author/publisher, and if a view's owner is the app user then the table's RLS would remain in tact. However, this means that we'd need a way to prevent author from accessing the publisher's view, and as RLS is for tables and not views, there is still no way to give the publisher access that the author wouldn't also have access to (if they're both application users whose privileges are managed via RLS and use the same database user to connect) – pyramation Apr 20 '21 at 20:12
  • Upon further research, your view suggestion could work if the view contains a WHERE clause that contains an additional security policy to limit to publisher only! https://stackoverflow.com/a/64283814/600515 that would allow views to limit certain fields for certain users while keep RLS in tact – pyramation Apr 20 '21 at 20:15

0 Answers0