Permission denied for relation

postgresql privileges postgresql-9.2 grant
Boban picture Boban · Mar 20, 2013 · Viewed 448k times · Source

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"

Do you have some idea what can be wrong?

Answer

Chris Travers picture Chris Travers · Mar 20, 2013

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.

Related questions

Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More
Grant all on a specific schema in the db to a group role in PostgreSQL

Using PostgreSQL 9.0, I have a group role called "staff" and would like to grant all (or certain) privileges to this role on tables in a particular schema. None of the following work GRANT ALL ON SCHEMA foo TO staff; GRANT …

Read More
Check Postgres access for a user

I have looked into the documentation for GRANT Found here and I was trying to see if there is a built-in function that can let me look at what level of accessibility I have in databases. Of course there is: \…

Read More