3

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 [42501]: ERROR: permission denied for table.

Any help would be appreciated.

3 Answers 3

5

The ALTER DEFAULT PRIVILEGES statement you ran will only affect tables created by postgres. If a different user creator creates the tables, you need

ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
2

For me, the problem was that I was using the free tier of ElephantSql.com (a great website BTW), and I used (way) more than the allocated 20 MB free quota.

I was able to make a select query, but an insert one failed with the error permission denied for table.

0
   Login : sudo -u postgres psql
    Select db : \c yourDbName
    View all table \dt;
    grant youUserName to postgres; 
    (permission related error then use this command)
    
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO {your_user_name};

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.