Permission denied for relation
PostgresqlPrivilegesPostgresql 9.2Sql GrantPostgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
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.
Solution 2 - Postgresql
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;
Solution 3 - Postgresql
Connect to the right database first, then run:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;
Solution 4 - Postgresql
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;
Solution 5 - Postgresql
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;
Solution 6 - Postgresql
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:
Solution 7 - Postgresql
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>;
Solution 8 - Postgresql
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
Solution 9 - Postgresql
You should:
- connect to the database by means of the DBeaver with postgres user
- 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
Solution 10 - Postgresql
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>;
Solution 11 - Postgresql
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>;