Skip to content

Instantly share code, notes, and snippets.

@daurnimator
Last active March 20, 2019 23:20
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save daurnimator/b1d2c16359e346a466b3093ae2757acf to your computer and use it in GitHub Desktop.
Save daurnimator/b1d2c16359e346a466b3093ae2757acf to your computer and use it in GitHub Desktop.
PostgreSQL RLS + views = weird
$ psql -f rls_trouble.sql
CREATE ROLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE ROLE
CREATE POLICY
GRANT
GRANT
CREATE VIEW
ALTER VIEW
GRANT
SET
psql:rls_trouble.sql:18: ERROR: permission denied for table qux
create role alice;
create table bar(a integer);
alter table bar enable row level security;
create table qux(b integer);
create role bob;
create policy blahblah on bar to bob
using(exists(select 1 from qux));
grant select on table bar to bob;
grant select on table qux to bob;
create view foo as select * from bar;
alter view foo owner to bob;
grant select on table foo to alice;
-- grant select on table qux to alice; -- shouldn't be required
set role alice;
select * from foo;
create role alice;
create table bar(a integer);
alter table bar enable row level security;
create table qux(b integer);
-- if we add a layer of indirection it works.... wat?
create view indirection as select * from bar;
create role bob;
create policy blahblah on bar to bob
using(exists(select 1 from qux));
grant select on table bar to bob;
grant select on table indirection to bob;
grant select on table qux to bob;
create view foo as select * from indirection;
alter view foo owner to bob;
grant select on table foo to alice;
set role alice;
select * from foo;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment