TIL — Postgres RLS on VIEWs — part 2

This is a continuation of part 1

But wait, Postgres v15 introduces “security invoker” views, which make it possible to use row-level security effectively!

So if you did CREATE VIEW WITH (security_invoker). This option causes the underlying base relations to be checked against the privileges of the invoking user rather than the view owner.

Let’s observe SELECTs with these roles — postgres and apiuser

-- "users" table view with limited data
CREATE VIEW "users_view_part2" WITH (security_invoker) AS 
  SELECT "id", "username", "data" 
  FROM users;

-- Change role to postgres
SET role = 'postgres';
SELECT current_user; -- postgres (superadmin)

-- Execute SELECTs
SELECT * FROM "users";
SELECT * FROM "users_view";
SELECT * FROM "users_view_part2";

-- Change role to apiuser
SET role = 'apiuser';
SELECT current_user; -- apiuser

-- Execute SELECTs
SELECT * FROM "users";
SELECT * FROM "users_view";
SELECT * FROM "users_view_part2";

role is `postgres`

role is `apiuser`

References:

1. https://www.postgresql.org/docs/15/sql-createview.html
2. https://www.cybertec-postgresql.com/en/view-permissions-and-row-level-security-in-postgresql/
3. https://github.com/supabase/supabase/discussions/901