Обсуждение: column-level security policies for application users
Has anyone discussed previously column-level security "policies" or how to best manage/implement them as they don't exist yet?
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'
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:
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');
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.
- uses triggers, cannot use BYPASSRLS and have to use replication role
- 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
(734) 657-4483