postgres: "alter default privileges" issue

Question

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.

Answer 1

Either you've forgot to add a permission to SELECT in the GRANT below, quoted from the question:

ALTER DEFAULT PRIVILEGES FOR USER access1
 IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

... or, if you really meant that having the role access1_rw doesn't let read the tables even tough it lets write to them, then you must grant the role access1_ro to user1 (in addition to granting the role access1_rw).

Not having either of those has the consequence that user1 was never granted the right to SELECT from tables in the mentioned schema.

• MarkTeehan: This was the issue. The intention was for the _rw role to also include SELECT. It now works ok. Thanks