column-level security policies for application users

From: Dan Lynch <pyramation(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: column-level security policies for application users
Date: 2021-04-19 22:33:52
Message-ID: CA+_muLEshQ8LR49-ArOvZz=Qz3sUG13gD+wrCg=yfoMBjFPumg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-04-19 23:10:05 Re: "could not find pathkey item to sort" for TPC-DS queries 94-96
Previous Message Alexey Kondratov 2021-04-19 22:22:41 Free port choosing freezes when PostgresNode::use_tcp is used on BSD systems