I am having a slight problem getting permissions to work the way I want them.
I have a role that should generally be allowed to SELECT everywhere, there are a bunch of members to this role. One of them should NOT be allowed to select from a certain table.
I thought this would be possible by granting role membership to the general reader role and revoking SELECT from the restricted table.
It seems the the permissions of the parent role apply and not the specific permissions. Is there a way around this without having to maintain the permissions of the more restricted role or am I applying the role concept in PostgreSQL in a wrong way?
Here's a sample script:
-- as superuser
CREATE DATABASE permission_test;
\c permission_test
CREATE ROLE r_general_select;
CREATE ROLE r_restricted_select IN ROLE r_general_select;
-- set the default permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO "r_general_select";
CREATE TABLE "open"(
id SERIAL,
payload TEXT
);
insert into "open"(payload) values ('test');
-- covered by default privileges
GRANT SELECT ON "open" TO PUBLIC;
-- Tests
-- this is good
SET ROLE r_general_select;
SELECT * FROM "open";
RESET ROLE;
-- this is good
SET ROLE r_restricted_select;
SELECT * FROM "open";
RESET ROLE;
CREATE TABLE "restricted" (
id SERIAL,
payload TEXT
);
insert into "restricted"(payload) values ('test');
-- the role and it's members should be able to read
GRANT SELECT ON "restricted" TO r_general_select;
-- except for this one!
REVOKE SELECT ON "restricted" FROM r_restricted_select;
-- Tests
-- this is good
SET ROLE r_general_select;
SELECT * FROM restricted;
RESET ROLE;
-- this should barf with a permission violation
SET ROLE r_restricted_select;
SELECT * FROM restricted;
RESET ROLE;
--- CLEANUP
DROP OWNED BY "r_restricted_select" CASCADE;
DROP ROLE r_restricted_select ;
DROP OWNED BY "r_general_select" CASCADE;
DROP ROLE r_general_select ;
