CODEWITHSUNDEEP
postgresqlpsqlrow-level-security
Hari
Hari

Reputation: 333

postgres how to apply RLS only for one user

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) ?

Upvotes: 0

Views: 833

Answers (1)

Bergi
Bergi

Reputation: 664375

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);
--                                        ^^^^^^^^^^^^^         ^^^^^^^^^

Upvotes: 5

Related Questions