How to idempotently define row-level security policies in PostgreSQL?

Question

I recently learned about row-level security policies in SQL, and love the idea of being able to run important security logic inside the database. However, I'm not sure how to make the workflow for updating RLS policies as good as updating API code is.

Since RLS policies are essentially just stateless logic, it feels like I should be able to define them all in an sql schema file that is checked into git and run on each deploy to idempotently set the policies.

We can get some of the way there by using drop if exists, e.g.:

drop policy if exists "everyone can read" on patterns;
create policy "everyone can read" on patterns for select using (auth.role() = 'anon');

This seems pretty good, but it's not quite truly declarative, because it won't drop any policies that still exist in the database from a previous version of the schema. Can I drop all existing policies for a table and then recreate them? Or is there another way to go about this?

I don't understand the problem. If you want to get rid of a policy, the only thing you need to do is to include the drop policy if exists line, but don't add the create policy line. — user330315
– user330315, Commented Sep 2, 2021 at 6:55
Yep, but to be sure it syncs correctly, I'd have to keep a drop statement in the file for every policy that had ever existed in any version of the database. It works but a bit messy, I figured out how to query for existing policies instead. — Evan Summers
– Evan Summers, Commented Sep 4, 2021 at 7:45
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community
– Community Bot, Commented Sep 5, 2021 at 10:04

Evan Summers · Accepted Answer · 2021-09-04 07:44:30Z

1

I figured out how to drop all policies. There are two catalog tables that list policies; pg_policy has low-level info, whereas pg_policies is more useful for this because it includes the table names directly.

-- Drop all existing policies
do
$$
declare
   rec record;
begin
   for rec in (SELECT tablename, policyname FROM pg_policies)
   loop
     execute 'drop policy "'||rec.policyname||'" on '||rec.tablename;
   end loop;
end;
$$;

answered Sep 4, 2021 at 7:44

Evan Summers

1,2929 silver badges19 bronze badges

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

1 Comment

Bergi Over a year ago

You should use format for proper escaping though. Also don't ignore the schema name of the table. SELECT polname AS name, polrelid::regclass AS table FROM pg_policy and EXECUTE format('DROP POLICY %i ON %s, pol.name, pol.table);

Laurenz Albe · Accepted Answer · 2021-09-02 06:53:03Z

0

You have two options:

query pg_policy for all policies on a table and drop them
drop all policies that ever existed in any version of your schema

answered Sep 2, 2021 at 6:53

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

How to idempotently define row-level security policies in PostgreSQL?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related