Ошибка permission denied for relation

asked Mar 20, 2013 at 10:00

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, 2013 at 11:41

Connect to the right database first, then run:

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

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, 2018 at 10:36

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 2, 2018 at 13:05

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

answered Oct 9, 2017 at 18:31

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>;

Brian McCallBrian McCall

1,7831 gold badge17 silver badges33 bronze badges

answered Mar 19, 2020 at 8:03

Jens TimmermanJens Timmerman

9,2461 gold badge41 silver badges47 bronze badges

If the backup was in the «custom» format (-Fc), you could use pg_restore instead so you can tell it to not apply ownership changes:

pg_restore -U username -W --no-owner --dbname=dbname migrations/schema.psql

All the objects will created with «username» as the owner.

Barring that, try to grep out the ALTER OWNER and SET SESSION AUTHORIZATION commands into new file (or through send grep output via pipe to psql). Those commands should always be on a single line in the plain-text output format.

answered Aug 18, 2010 at 17:54

alvherrealvherre

2,49920 silver badges22 bronze badges

As I work on my websites, I get that error all the time because I’ll be creating a table as me, table that needs to be accessed by the Apache/PHP user that connects later.

There is a table named pg_class that defines your tables. This includes a column named relowner. Changing that with the correct number will give you the correct ownership.

I have details on this page:

http://linux.m2osw.com/table_owner_in_postgresql

The ALTER OWNER … may be a better solution that does the same thing, although in older versions of PostgreSQL it did not exist!

answered Sep 13, 2012 at 17:07