N
Neon•7mo ago
fascinating-indigo

Db & Schema owner unable to GRANT SELECT on 1 schema while another in same db works

Anyone know why GRANTing all table access to 1 schema would fail while succeeding for another schema in the same db? This failure happens when run as the database owner (either from Atlas or from the Neon SQL Editor). This is the code, run right after creating role 'datamart_pg_reader': 
GRANT USAGE ON SCHEMA dm TO datamart_pg_reader;
GRANT USAGE ON SCHEMA plus TO datamart_pg_reader;
REVOKE ALL ON SCHEMA public FROM datamart_pg_reader;  
-- does not work, even if changed to SELECT, MAINTAIN:
GRANT SELECT ON ALL TABLES IN SCHEMA dm TO datamart_pg_reader;
-- works fine:
GRANT SELECT, MAINTAIN ON ALL TABLES IN SCHEMA plus TO datamart_pg_reader;
GRANT USAGE ON SCHEMA dm TO datamart_pg_reader;
GRANT USAGE ON SCHEMA plus TO datamart_pg_reader;
REVOKE ALL ON SCHEMA public FROM datamart_pg_reader;  
-- does not work, even if changed to SELECT, MAINTAIN:
GRANT SELECT ON ALL TABLES IN SCHEMA dm TO datamart_pg_reader;
-- works fine:
GRANT SELECT, MAINTAIN ON ALL TABLES IN SCHEMA plus TO datamart_pg_reader;
When run from SQL Editor, or from psql, I get this error back for each table in the schema: WARNING: no privileges were granted for "premium_report" WARNING: no privileges were granted for "claim_version" and so on. Both schema exist (and contain objects) before this code is run. I confirmed the role executing the SQL has the same privileges on both schema (OWNER, USAGE, CREATE) and I don't see any revocation of any privileges for this role.
1 Reply
fascinating-indigo
fascinating-indigoOP•7mo ago
This has been partially answered, in that the granting role probably does not own the tables it is trying to grant privileges on -- however, the larger question is how to manage an 'admin' role within Neon's particular role configuration, which has been asked here: https://discord.com/channels/1176467419317940276/1346897069876576326/1346897069876576326

Did you find this page helpful?