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

Question

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

What I did wrong?

Ellsworthellwood

Can you provide some details about "relation mytable" ? Schema, is it a "real" table (or a view/function), triggers... – Rhine

Answer 1

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.

Ellsworthellwood

does this include views? – Pythagorean

Why are you giving the GRANT ALL permission by default to the readonly user? – Catastrophism

i gave exactly as it is defined, still the sam error – Polyandrist

can confirm rights granted using \ddp. Should show just =r/ like granting_user=r/readonly_user for read only access. – Storfer

This is verbatim SQL from the question. I do not see how it provides a solution. – Sect

Answer 2

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.

Spada

Something strange is happening, I run these commands on the server using psql as postgres user and I do get a proper response, GRANT. Still, when I look to the ACL on the tables I see only two other accounts, one being the database owner jirauser and another readonly account named qauser. But my readonly does not appear there. Postgres is version 9.1 and I even restarted the server, still nothing happens. – Ellsworthellwood

what is/was output of \du in psql console? Can you still give this output or it's fixed already like in your answer? – Spada

I don't really know what happened, as the output was correct (GRANT). YEsterday it did not work, but today it worked after running, again, all 3 commands. – Ellsworthellwood

Note: The expected respose to this is simply 'GRANT'. If you see 'WARNING: no privileges were granted for "public"' then it DID NOT work. User readonly cannot grant themself extra permissions. Only a user with 'GRANT' permissions can do that so you probably need to log in as a superuser. – Immensity

Hi, when i try to GRANT SELECT ON ALL TABLES IN SCHEMA public TO postgres; it responds: ERROR: permission denied for relation databasechangeloglock. Do you know what i'm doing wrong? Thanks (it happens on GAppEngine Posgres9.6 using the public address and accessing through the terminal) – P

@P do you have permission to grant permission for that table? – Spada

Thanks a lot @Spada – P

Answer 3

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.

Prior

Changing the owner to a user called readonly hardly sounds like the right fix. – Majestic

Depending on your case, wrong table owner can indeed be the cause (it was foe me). Proper way to change table ownershiip in PostgreSQL: see stackoverflow.com/a/13535184 – Becket

Answer 4

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

Templeton

No! Giving the Superuser role to a user called "readonly" in order to do a "select" is not the correct fix. – Majestic

@Majestic That's not what's happening here. This thread is not about giving read only access. This thread is about giving a user access to give another user read-only access. IMHO, this is correct. If your user is not a Superuser, you cannot create a read-only user. The error being run into is from being able to create a read-only user ERROR: permission denied for relation mytable – Sect

Answer 5

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;

Lettuce

The user is called "readonly". It's doubtful that giving the user all permissions is the goal. He just wants to do a select. – Majestic

This defeats the purpose of naming the user 'ReadOnly' if you are giving ALTER DROP DELETE Ect. To that user... – Hic