1

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.

0

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.

  • This was the issue. The intention was for the _rw role to also include SELECT. It now works ok. Thanks – MarkTeehan Aug 3 '16 at 8:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.