I have 3 roles in Postgres as admin, tablecreator and reader. I like to restrict reader role to SELECT statements on mydb/myschema. I like to use tablecreator role only for creating and modifying tables in mydb/myschema. I like to use admin role as less as possible, and so it is used only for creating roles, granting permissions etc. For this, I executed below statements as each user.
/* as admin on mydb - success */
CREATE ROLE tablecreator;
CREATE ROLE reader;
CREATE SCHEMA myschema;
GRANT CONNECT ON DATABASE mydb TO tablecreator, reader;
GRANT USAGE ON SCHEMA myschema to tablecreator, reader;
GRANT CREATE ON SCHEMA myschema to tablecreator;
-- Applicable only on pre-existing tables
-- GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO reader;
-- Failed for not member of role.
-- ALTER DEFAULT PRIVILEGES FOR USER tablecreator IN SCHEMA myschema GRANT SELECT ON TABLES TO reader;
/* as table creator - success */
CREATE TABLE IF NOT EXISTS myschema.mytable (id integer, name TEXT);
-- I like to avoid below statement for per table permission
-- GRANT SELECT ON myschema.mytable TO reader;
/* as reader - failure */
SELECT * FROM myschema.mytable; -- permission denied error
The final SELECT query from reader failed. My question is
- What statement should I run once as admin, so the reader can access the table or any tables created in future.
- If that is not possible or not recommended, what statement should I run once as tablecreator in order to avoid assigning permissions on a per table basis.
This question may look like duplicate of previous questions 1 or 2. But I felt those were broader queries and hence did not get the desired clearcut answer. To get a clear copy/paste answer, I narrowed down the question to a specific situation. Of lesser importance is that, Postgres version is 14 and I don't have super user access. The admin role is member of azure_pg_admin as DB is Azure Postgres Flexible server.
