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?

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

Greg Bray · Accepted Answer · 2019-10-30 20:21:46Z

174

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, 2019 at 20:21

Greg Bray

15.9k13 gold badges86 silver badges107 bronze badges

answered Nov 22, 2012 at 11:20

sorin

173k194 gold badges582 silver badges861 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Frank Conry Over a year ago

does this include views?

Slava Fomin II Over a year ago

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

Anish Gopinath Over a year ago

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

Greg Bray Over a year ago

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

r351574nc3 Over a year ago

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

Homunculus Reticulli · Accepted Answer · 2018-01-23 12:55:10Z

17

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, 2018 at 12:55

Homunculus Reticulli

69k86 gold badges234 silver badges365 bronze badges

answered Nov 21, 2012 at 18:36

sufleR

2,99320 silver badges31 bronze badges

6 Comments

sorin Over a year ago

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.

sufleR Over a year ago

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

sorin Over a year ago

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.

PeterVermont Over a year ago

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.

Mike Over a year ago

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)

|

Community · Accepted Answer · 2020-06-20 09:12:55Z

-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, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 24, 2015 at 23:33

Dhwani Shah

112 bronze badges

2 Comments

Anna Over a year ago

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

tanius Over a year ago

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

PuN1sh3r · Accepted Answer · 2015-07-16 06:28:24Z

-7

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

answered Jul 16, 2015 at 6:28

PuN1sh3r

1012 silver badges8 bronze badges

2 Comments

Anna Over a year ago

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

r351574nc3 Over a year ago

@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

Bruce P · Accepted Answer · 2015-10-27 17:27:09Z

-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, 2015 at 17:27

Bruce P

20.9k8 gold badges68 silver badges75 bronze badges

answered Oct 27, 2015 at 17:11

yesy

71 bronze badge

2 Comments

Anna Over a year ago

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

JayRizzo Over a year ago

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

Collectives™ on Stack Overflow

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

5 Answers 5

5 Comments

6 Comments

2 Comments

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

6 Comments

2 Comments

2 Comments

2 Comments

Linked

Related