Has anyone discussed previously 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? Dan Lynch (734) 657-4483