CODEWITHSUNDEEP
sqlpostgresql
Evan Summers
Evan Summers

Reputation: 1202

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

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?

Upvotes: 2

Views: 612

Answers (2)

Evan Summers
Evan Summers

Reputation: 1202

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;
$$;

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246308

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

Upvotes: 0

Related Questions