NeonN
Neon•11mo ago•
1 reply
spontaneous-moccasin

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;

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.
Was this page helpful?