I trying to create two DB users in azure postgresql, one is admin user and another one is ready only(Only have permission to execute select queries) user for the public schema. I tried with following commands, readonly users not able to execute the select commands over the newly create tables by admin users.
I'm getting the permission denied error
Create Admin user and assign all privileges to public schema
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE ROLE test_admin WITH LOGIN NOSUPERUSER INHERIT NOCREATEROLE NOREPLICATION PASSWORD 'mypassword';
GRANT CONNECT ON DATABASE my_db TO test_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC TO test_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA PUBLIC TO test_admin;
GRANT ALL ON SCHEMA public TO test_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT ALL PRIVILEGES ON TABLES TO test_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC GRANT ALL PRIVILEGES ON SEQUENCES TO test_admin;
Create readonly users and assign only select commands.
CREATE ROLE test_read_only_user WITH LOGIN PASSWORD 'mypassword' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
GRANT CONNECT ON DATABASE my_db TO test_read_only_user;
GRANT USAGE ON SCHEMA public TO test_read_only_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_read_only_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO test_read_only_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO test_read_only_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO test_read_only_user;
I'm not sure what I'm missing, anyone please advise me on this
Reference
Grant privileges on future tables in PostgreSQL?
https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html
