-
-
Save daurnimator/b1d2c16359e346a466b3093ae2757acf to your computer and use it in GitHub Desktop.
PostgreSQL RLS + views = weird
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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