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

This is happening on PostgreSQL 9.1

What I did wrong?

Can you provide some details about "relation mytable" ? Schema, is it a "real" table (or a view/function), triggers... — Ihor Romanchenko, Nov 21 '12 at 16:51

score 173 · Accepted Answer · edited Oct 30 '19 at 20:21

173

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.

edited Oct 30 '19 at 20:21

Greg Bray

14,929
12
80
104

answered Nov 22 '12 at 11:20

sorin

161,544
178
535
806

6

does this include views? – Frank Conry Jun 25 '14 at 05:04
9

Why are you giving the `GRANT ALL` permission by default to the readonly user? – Slava Fomin II Nov 06 '17 at 13:35
1

i gave exactly as it is defined, still the sam error – Anish Gopinath Oct 21 '19 at 06:32
can confirm rights granted using `\ddp`. Should show just `=r/` like `granting_user=r/readonly_user` for read only access. – Greg Bray Oct 30 '19 at 20:23
This is verbatim SQL from the question. I do not see how it provides a solution. – r351574nc3 Jan 07 '20 at 22:06

score 15 · Answer 2 · edited Jan 23 '18 at 12:55

15

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.

edited Jan 23 '18 at 12:55

Homunculus Reticulli

65,167
81
216
341

answered Nov 21 '12 at 18:36

sufleR

2,865
17
31

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. – sorin Nov 22 '12 at 10:55
2

what is/was output of \du in psql console? Can you still give this output or it's fixed already like in your answer? – sufleR Nov 22 '12 at 11:57
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. – sorin Nov 22 '12 at 12:54
3

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. – PeterVermont Jan 07 '17 at 17:44
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) – Mike Mar 07 '20 at 11:40
@Mike do you have permission to grant permission for that table? – sufleR Mar 09 '20 at 23:21
Thanks a lot @sufleR – Mike Mar 11 '20 at 18:56

score -5 · Answer 3 · edited Jun 20 '20 at 09:12

-5

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 24 '15 at 23:33

Dhwani Shah

11
2

5

Changing the owner to a user called readonly hardly sounds like the right fix. – Anna Apr 25 '16 at 18:16
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 http://stackoverflow.com/a/13535184 – tanius Dec 31 '16 at 22:40

score -7 · Answer 4 · answered Jul 16 '15 at 06:28

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

No! Giving the Superuser role to a user called "readonly" in order to do a "select" is not the correct fix. — Anna, Apr 25 '16 at 18:02
@Anna 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` — r351574nc3, Jan 07 '20 at 22:02

score -8 · Answer 5 · edited Oct 27 '15 at 17:27

-8

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;

edited Oct 27 '15 at 17:27

Bruce P

19,995
8
63
73

answered Oct 27 '15 at 17:11

yesy

7
1

6

The user is called "readonly". It's doubtful that giving the user all permissions is the goal. He just wants to do a select. – Anna Apr 25 '16 at 18:01
1

This defeats the purpose of naming the user 'ReadOnly' if you are giving `ALTER DROP DELETE` Ect. To that user... – JayRizzo Oct 30 '19 at 20:30

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

5 Answers5

Linked