debugsqlMinor
SQL Error [42501]: ERROR: permission denied for table
Viewed 0 times
errorsql42501permissiondeniedfortable
Problem
I am very new to postgres so please my apologies in advance if I sound naive. I am still trying to learn. I am trying to create a readonly role and then create a role and assign readonly role to the user. I logged in as postgres user
Now I can login as user readonlyuser but I can't read data from any tables. I get error SQL Error [42501]: ERROR: permission denied for table.
Any help would be appreciated.
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE test_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
CREATE USER readonlyuser WITH PASSWORD 'read123';
grant readonly to readonlyuser;Now I can login as user readonlyuser but I can't read data from any tables. I get error SQL Error [42501]: ERROR: permission denied for table.
Any help would be appreciated.
Solution
The
ALTER DEFAULT PRIVILEGES statement you ran will only affect tables created by postgres. If a different user creator creates the tables, you needALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public GRANT SELECT ON TABLES TO readonly;Code Snippets
ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public GRANT SELECT ON TABLES TO readonly;Context
StackExchange Database Administrators Q#283206, answer score: 5
Revisions (0)
No revisions yet.