ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

The readonly user can connect, see the tables but when it tries to do a simple select it gets:

ERROR: permission denied for relation mytable
SQL state: 42501

This is happening on PostgreSQL 9.1

What I did wrong?

Here is the complete solution for PostgreSQL 9+, updated recently.

CREATE USER readonly  WITH ENCRYPTED PASSWORD 'readonly';
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- repeat code below for each database:

GRANT CONNECT ON DATABASE foo to readonly;
\c foo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; --- this grants privileges on new tables generated in new database "foo"
GRANT USAGE ON SCHEMA public to readonly; 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Thanks to https://jamie.curle.io/creating-a-read-only-user-in-postgres/ for several important aspects

If anyone find shorter code, and preferably one that is able to perform this for all existing databases, extra kudos.

Try to add

GRANT USAGE ON SCHEMA public to readonly;

You probably were not aware that one needs to have the requisite permissions to a schema, in order to use objects in the schema.

This worked for me:

Check the current role you are logged into by using: SELECT CURRENT_USER, SESSION_USER;

Note: It must match with Owner of the schema.

Schema | Name | Type | Owner
--------+--------+-------+----------

If the owner is different, then give all the grants to the current user role from the admin role by :

GRANT 'ROLE_OWNER' to 'CURRENT ROLENAME';

Then try to execute the query, it will give the output as it has access to all the relations now.

make sure your user has attributes on its role. for example:

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 flux      |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

after performing the following command:

postgres=# ALTER ROLE flux WITH Superuser;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 flux      | Superuser                                      | {}
postgres  | Superuser, Create role, Create DB, Replication | {}

it fixed the problem.

see tutorial for roles and stuff here: https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2

You should execute the next query:

GRANT ALL ON TABLE mytable TO myuser;

Or if your error is in a view then maybe the table does not have permission, so you should execute the next query:

GRANT ALL ON TABLE tbm_grupo TO myuser;