SQL Error [42501]: ERROR: permission denied for table

Question

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.

Answer 1

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;

Answer 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.

Answer 3

   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 {serverName};