I am implementing a schema access model on postgres (I know oracle). Using guidance from http://bonesmoses.org/2016/02/26/pg-phriday-corralling-the-hordes/ I created a data owner account and two roles (_ro and _rw), and a second user account which is granted the _rw role. I created a table in the Owner account which I expect to be automatically accessible via the roles because I issued a "ALTER DEFAULT PRIVILEGES" command to automatically issue GRANTs in future-created tables. However, when I log in as the user account, a select on the table returns "ERROR: permission denied for relation checklist1new"
Create the Owner account
CREATE USER access1 WITH PASSWORD 'whatever'; CREATE SCHEMA access1 AUTHORIZATION access1; ALTER USER access1 SET search_path = access1; ALTER SCHEMA access1 OWNER TO access1; CREATE GROUP access1_ro; CREATE GROUP access1_rw; GRANT USAGE ON SCHEMA access1 TO access1_ro; GRANT USAGE ON SCHEMA access1 TO access1_rw; ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1 GRANT SELECT ON TABLES TO access1_ro; ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw; ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1 GRANT USAGE ON SEQUENCES TO access1_rw; ALTER DEFAULT PRIVILEGES FOR USER access1 IN SCHEMA access1 REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; CREATE TABLE TEST_TABLE_access1(col1 integer);
Create the user account
CREATE USER user1 WITH PASSWORD 'whatever'; GRANT CONNECT on DATABASE rwinkwavu to user1; GRANT access1_rw to user1;
Attempt to query the table, logged in as user1
select * from access1.TEST_TABLE_access1 ERROR: permission denied for relation TEST_TABLE_access1
I expected this query to return 0 rows. What am I doing wrong? Postgres 9.4 on macOS.