0

I have a table that has a userid field for owner(s) and a collaborator(s). Access to the table is done via one role (has SELECT, INSERT and UPDATE access to the table), and a userid available from a JWT-token (returned in the sample via the rls_current_user function).

To enforce some more security, I wanted to add an UPDATE-restriction, the userid doing the update must occur in either of the two userid fields. For inserts, the userid for the owner field must include the userid that does the insert. This insert policy is not very important, but is still a nice addition.

I created an UPDATE RLS policy:

CREATE POLICY upd_owner_collab ON test AS PERMISSIVE
FOR UPDATE USING ((COALESCE(user_id_owner,'[]'::jsonb)||COALESCE(user_id_collab,'[]'::jsonb)) @> rls_current_user())

To my surprise, when enabling this policy it prevents INSERTS and SELECTS (and probably DELETE as well, which I didn't try). I can't find anything in the documentation that suggests this behaviour.

My next step was to include a policy on INSERTs. The insert required the use of WITH CHECK instead.

CREATE POLICY ins_owner ON test AS PERMISSIVE
FOR INSERT WITH CHECK ((COALESCE(user_id_owner,'[]'::jsonb)) @> rls_current_user())

To "restore" the SELECT access, I added the policy:

CREATE POLICY sel_any ON test AS PERMISSIVE FOR SELECT USING ( true );

My question: does setting a single policy on one type of statement (eg. UPDATE) disallow the other types (SELECT, INSERT, DELETE), or are my policies incorrectly formulated?

I feel that it's a bit odd to explicitly have to add a policy to have SELECT access while the user already has the SELECT grant on the table, and I can't find this restriction in the docs.

This is on PostgreSQL 14.

1 Answer 1

5

The principle is: everything that is not explicitly allowed is forbidden. So as soon as you run

ALTER TABLE tab ENABLE ROW LEVEL SECURITY;

only the owner, superusers and roles with BYPASSRLS can access rows in the table.

If you then define a policy FOR UPDATE, you still won't see any rows when you SELECT from the table, and INSERT and DELETE will result in an error. This is documented:

When row security is enabled on a table ([...]), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. [...] If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.