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"

Do you have some idea what can be wrong?

Answer 1

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;

Answer 2

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.

Answer 3

Connect to the right database first, then run:

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

Answer 4

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

Answer 5

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;

Answer 6

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;