postgres how to apply RLS only for one user

Question

There are many users connecting to database. I have to apply Row Level Security(RLS) only for one user david, all other users should be able to access table as usual.

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY accounts_web ON accounts TO david USING (id = current_setting('rls.tenant_id' )::uuid);

I can create a policy like CREATE POLICY accounts_no_rls ON accounts TO Michel USING (true); but i will have to create a policy for each users.

Is it possible with just two policy (one policy for david and 2nd policy for all other users) ?

Bergi · Accepted Answer · 2021-09-14 19:20:46Z

5

You can create a permissive policy for the PUBLIC role (that is, all users) to grant them access to the table, then add a second restrictive policy for david only that will restrict access by row.

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY accounts_public ON accounts USING (true);
CREATE POLICY accounts_web ON accounts AS RESTRICTIVE TO david USING (id = current_setting('rls.tenant_id')::uuid);
--                                     ^^^^^^^^^^^^^^

The long form would be

CREATE POLICY accounts_public ON accounts AS PERMISSIVE FOR ALL TO PUBLIC USING (true);
--                                        ^^^^^^^^^^^^^         ^^^^^^^^^

answered Sep 14, 2021 at 19:20

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres how to apply RLS only for one user

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related