Permission denied for relation

Question

I tried to run simple SQL command:

select * from site_adzone;

and I got this error

ERROR: permission denied for relation site_adzone

What could be the problem here?

I tried also to do select for other tables and got same issue. I also tried to do this:

GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

but I got this response from console

WARNING: no privileges were granted for "jerry"

Does anyone have any idea what can be wrong?

I am not sure how to update permissions so I can read/write in DB — Bob, Mar 20 '13 at 10:23
You need to grant the necessary privileges: http://www.postgresql.org/docs/current/static/ddl-priv.html and http://www.postgresql.org/docs/current/static/sql-grant.html — , Mar 20 '13 at 10:32
Welcome to SO! For this question, you may get more help over at dba.stackexchange.com, though you may find just as many snide comments :) — Jared Beck, Mar 28 '13 at 04:09
Excuse me. This is the second very programming-related postgresql question I've seen closed as off-topic tonight! The last one had 67,000 views, this one 30,000 views. We should have a popularity clause: Any non-subjective question with > 15,000 views = on topic. — Theodore R. Smith, Dec 12 '14 at 05:51
This question is not off topic! It is, however, a duplication of http://stackoverflow.com/questions/13497352/error-permission-denied-for-relation-tablename-on-postgres-while-trying-a-selec — wheaties, Feb 17 '15 at 20:05

score 539 · Accepted Answer · answered Mar 20 '13 at 11:41

539

GRANT on the database is not what you need. Grant on the tables directly.

Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions.

You want instead:

 GRANT ALL PRIVILEGES ON TABLE side_adzone TO jerry;

This will take care of this issue.

answered Mar 20 '13 at 11:41

Chris Travers

25,424
6
65
182

I tried this grant all privileges on table site_adzone to jerry; and got this response ERROR: permission denied for relation site_adzone – Bob Mar 20 '13 at 12:19
12

run it as a superuser, like postgres. – Chris Travers Mar 20 '13 at 12:21
23

Can this be shortcut somehow? GRANT ALL PRIVILEGES ON ALL TABLES ? – Shadur Apr 08 '14 at 11:17
240

@Shadur GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry; – Ron E Jun 09 '14 at 02:29
12

@RonE is that restricted to the current database, though? – Shadur Jun 09 '14 at 07:04
Thank you, this really help. How about schema? – Zein Miftah Feb 27 '15 at 04:14
8

@zmiftah for schema you need > GRANT ALL PRIVILEGES ON SCHEMA nameSchema TO user; – Pierozi Sep 04 '15 at 09:17
@Ron E, add your comment as answer to this question please – Oleg Abrazhaev Feb 16 '16 at 08:57
1

Would like re-iterate the question asked by @Shadur "Is that restricted to the current database?" – jazaman May 31 '16 at 04:03
6

Okay got my answer, may be it obvious to everyone else but not to me. I executed the command and got response GRANT but it did not allow me run any query. After a lot of trial and error I realized I have to *connect* to the database as superuser first and then grant permission. So answer to @Shadur 's question is YES it is definitely restricted to the current database. – jazaman May 31 '16 at 06:25
4

You might also need this (replace `TABLES` with `SEQUENCES`): `GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO jerry;` – Nick Jun 16 '16 at 17:09
I am using inspectdb command to generate models. The aforementioned error has gone but a new error has popped up - Unable to inspect table 'table_name' # The error was: list index out of range – JDP Sep 17 '16 at 04:14
1

As written on an answer bellow you need to connecto the the right database inside the psql terminal first: `\connect databasename;` – Ole Henrik Skogstrøm Feb 14 '19 at 14:09
I got `Error in query: ERROR: schema "db_name" does not exist` after I ran `GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA db_name TO user`. – Paul Dec 10 '19 at 21:44
@jazaman on top of current database, it is restricted to current tables as well! i.e. if you are adding a new table you have to run this again. Found using some trial and error only :) – Mahesh Thumar Mar 15 '20 at 04:50

score 348 · Answer 2 · answered May 23 '16 at 11:22

348

Posting Ron E answer for grant privileges on all tables as it might be useful to others.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;

answered May 23 '16 at 11:22

sag

5,333
8
54
91

36

You may also need to execute a similar command for `ALL SEQUENCES` and `ALL FUNCTIONS`. – Pistos Jun 27 '17 at 05:37
16

Just for those who were wondering: `ALL TABLES` also includes views, so there is no separate `ALL VIEWS` command :-) – André Gasser Apr 03 '18 at 06:24
3

stupid question, but how come simply `GRANT ALL PRIVILEGES ON SCHEMA public TO jerry;` does not give select access on a table inside the schema? – lollerskates Apr 19 '21 at 16:21

score 132 · Answer 3 · edited Aug 19 '17 at 00:59

132

Connect to the right database first, then run:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;

edited Aug 19 '17 at 00:59

BrDaHa

5,138
5
32
47

answered Jan 01 '17 at 20:00

user2757813

1,329
1
8
3

51

connect to right database makes a HUGE dufference :) `\connect databasename;` – Denis Matafonov Jan 22 '18 at 10:54
7

THIS. I was connected to "postgres" all along. Thank You! – Andrew G. Feb 05 '18 at 04:28
3

Yes connecting to the DB made the difference – inostia Apr 15 '19 at 18:07

score 96 · Answer 4 · answered Jul 18 '18 at 10:36

96

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to jerry;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to jerry;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to jerry;

answered Jul 18 '18 at 10:36

Meirza

1,318
10
15

Did exactly this, but still get permission denied when trying to select from tables in schema `public` as user `jerry` ...WTF? – syr Nov 30 '22 at 11:48

score 51 · Answer 5 · answered May 02 '18 at 13:05

51

1st and important step is connect to your db:

psql -d yourDBName

2 step, grant privileges

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO userName;

answered May 02 '18 at 13:05

zond

1,473
1
21
34

isapir · Answer 6 · 2018-04-05T08:03:02.143

To grant permissions to all of the existing tables in the schema use:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO <role>

To specify default permissions that will be applied to future tables use:

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> 
  GRANT <privileges> ON TABLES TO <role>;

e.g.

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin;

If you use SERIAL or BIGSERIAL columns then you will probably want to do the same for SEQUENCES, or else your INSERT will fail (Postgres 10's IDENTITY doesn't suffer from that problem, and is recommended over the SERIAL types), i.e.

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON SEQUENCES TO <role>;

See also my answer to PostgreSQL Permissions for Web App for more details and a reusable script.

Ref:

GRANT

ALTER DEFAULT PRIVILEGES

This needs to be way higher up, this is what did the trick for me, the usual grant was being executed, but due to no tables being there yet it did not take any effect. Perhaps this is also something version specific. — BuzZin', May 02 '22 at 07:53

score 18 · Answer 7 · edited Feb 03 '20 at 15:30

18

This frequently happens when you create a table as user postgres and then try to access it as an ordinary user. In this case it is best to log in as the postgres user and change the ownership of the table with the command:

alter table <TABLE> owner to <USER>;

edited Feb 03 '20 at 15:30

salvador

107
4

answered May 22 '18 at 04:13

Bruce

449
4
6

Very nice! It's painful to know this, but this is what I had ended up doing. :D (facepalm) – ankush981 Dec 27 '21 at 13:53

score 8 · Answer 8 · answered May 15 '18 at 17:30

8

Make sure you log into psql as the owner of the tables. to find out who own the tables use \dt

psql -h CONNECTION_STRING DBNAME -U OWNER_OF_THE_TABLES

then you can run the GRANTS

answered May 15 '18 at 17:30

Brian McCall

1,831
1
18
33

score 7 · Answer 9 · edited May 17 '23 at 13:47

7

You should:

Connect to the database using DBeaver and user as postgres
On the left tab open your database
Open Roles tab/dropdown
Select your user
On the right tab press 'Permissions tab'
Press your schema tab
Press tables tab/dropdown
Select all tables
Select all required permissions checkboxes (or press Grant All)
Press Save

edited May 17 '23 at 13:47

aakaasaraamanna

61
10

answered Mar 19 '20 at 08:03

happydmitry

111
2
5

score 2 · Answer 10 · answered Jan 08 '20 at 15:33

2

As you are looking for select permissions, I would suggest you to grant only select rather than all privileges. You can do this by:

GRANT SELECT ON <table> TO <role>;

answered Jan 08 '20 at 15:33

Sergi Ramón

185
1
4

score 2 · Answer 11 · answered Nov 18 '22 at 11:00

For PostgreSQL. On bash terminal, run this:

psql db_name -c "GRANT ALL ON ALL TABLES IN SCHEMA public to db_user;"
psql db_name -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to db_user;"
psql db_name -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to db_user;"

score 1 · Answer 12 · answered Aug 20 '21 at 19:02

I ran into this after switching a user to another user that also needed to have the same rights, I kept getting the error: "must be owner of relation xx"

fix was to simply give all rights from old user to new user:

postgres-# Grant <old user> to <new user>;

Permission denied for relation

12 Answers12

Linked