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
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 : ERROR: permission denied for table.
Any help would be appreciated.