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?
Dale K
25k15 gold badges42 silver badges71 bronze badges
asked Mar 20, 2013 at 10:00
5
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
Chris TraversChris Travers
25.2k6 gold badges63 silver badges181 bronze badges
15
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;
3
Connect to the right database first, then run:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;
BrDaHa
5,0685 gold badges32 silver badges47 bronze badges
answered Jan 1, 2017 at 20:00
user2757813user2757813
1,3191 gold badge8 silver badges3 bronze badges
3
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
MeirzaMeirza
1,29810 silver badges15 bronze badges
1
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
zondzond
1,4411 gold badge21 silver badges34 bronze badges
0
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
isapirisapir
20.8k13 gold badges114 silver badges115 bronze badges
2
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>;
answered May 22, 2018 at 4:13
BruceBruce
4393 silver badges6 bronze badges
1
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, 2018 at 17:30
Brian McCallBrian McCall
1,7831 gold badge17 silver badges33 bronze badges
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
answered Mar 19, 2020 at 8:03
happydmitryhappydmitry
1112 silver badges5 bronze badges
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;"
answered Nov 18, 2022 at 11:00
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 8, 2020 at 15:33
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>;
answered Aug 20, 2021 at 19:02
Jens TimmermanJens Timmerman
9,2461 gold badge41 silver badges47 bronze badges
DEFAULT PRIVILEGES
do not change permissions for existing objects. They are the default privileges for newly created objects and only for the particular role they belong to. If you do not specify the role when running ALTER DEFAULT PRIVILEGES
, it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES
statement.
Also, since you are using a serial
column, which creates a SEQUENCE
, you’ll want to set default privileges for sequences as well.
Run this on the user you create objects with, before you run the CREATE
command:
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON TABLES TO bspu;
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON SEQUENCES TO bspu;
A word of caution for pgAdmin users. There is a bug in all versions of pgAdmin III and pgAdmin4 (including v5.3). The reverse engineered SQL script for the database or schema nodes displays DEFAULT PRIVILEGES
ignoring the owning user and is therefore incorrect in certain situations. I reported the bug (repeatedly), but the project encountered difficulties fixing it. pgAdmin III was discontinued in favor of pgAdmin4. The bug is still there in pgAdmin4.
For existing objects you may also be interested in this «batch» form of the GRANT
command:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bspu;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO bspu;
More under this related question on SO:
- Grant all on a specific schema in the db to a group role in PostgreSQL
I’m trying to import a schema-dump into PostgreSQL with ‘psql -U username -W dbname < migrations/schema.psql’, the Schema is imported partly, but the console throws errors like «ERROR: permission denied for relation table_name» and «ERROR: relation table_name does not exist».
I’ve created the database like this: «createdb -O username dbname»
There are only 7 tables to import, and it breaks with just importing 3 of them.
Anybody a hint, what to do?
asked Aug 18, 2010 at 12:52
1
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
Matthew WoodMatthew Wood
15.9k5 gold badges46 silver badges35 bronze badges
2
Sometimes this kind of problem is caused by case-sensitivity issues. PostgreSQL folds to lowercase all unquoted identifiers; if the tables are created with quoted names containing uppercase letters, later commands that don’t quote the names may fail to find the table.
The permission errors may be related to the same thing, or it may be something else entirely. Hard to tell without seeing the failing commands.
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
Alexis WilkeAlexis Wilke
18.9k10 gold badges83 silver badges150 bronze badges
3
Ever needed to create a bunch of tables for a newly created user to access in PostgreSQL and see the following error message pop up, ‘permission denied for relation some_table_name’? You are in luck, in this tutorial, I am going to guide you guys through it to make the error message goes away.
Let me give you guys an example on how this error message might occur (at least this is how I found out),
Check out this following sql file,
CREATE DATABASE "database";
CREATE USER someuser WITH PASSWORD 'securepassword';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO someuser;
CREATE TABLE "some_table_name"(
"id" int NOT NULL,
"data" text NOT NULL
);
INSERT INTO "some_table_name" values(0, "some text");
source code hosted on GitHub
As one can imagine, when you login as ‘someuser’ and try to access the table with the SELECT query, you will likely see the error message pop up. This is because you granted all privileges to the someuser on all tables but no table has been created yet which means that the query has no effect at all. To fix this, you can simply move that GRANT ALL.. query all the way down to the bottom (the point where you created all the necessary table for your database). It should look something similar to the following.
Your someuser should now have access to the table,
CREATE DATABASE "database";
CREATE USER someuser WITH PASSWORD 'securepassword';
CREATE TABLE "some_table_name"(
"id" int NOT NULL,
"data" text NOT NULL
);
INSERT INTO "some_table_name" values(0, "some text");
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO someuser;
source code hosted on GitHub
As a result, the order is very important. Just remember to do that next time haha!
Tada. This is a fairly short tutorial. Hope that solve your problem. Let me know if it didn’t. I will be happy to help as always.
Wrapping Up
Hopefully you enjoyed this short tutorial. Let me know if this helps you. Thank you for reading!
Resources
I’ll try to keep this list current and up to date. If you know of a great resource you’d like to share or notice a broken link, please let us know.
Getting started
- permission denied for relation.
Author
PoAn (Baron) Chen
Software Engineer at Microsoft. Graduated from @uvic. Previously worked at @illumina, @ACDSee, @AEHelp and @AcePersonnel1. My words are my own.
Редактирую сайт на django после создания приложения и успешной миграции возникла ошибка, что у меня нет прав доступа. Не могу разобраться как дать права пользователю. Проделал всё как пишут в этой инструкции:
https://stackoverflow.com/questions/12233046/djang…
удалил базу, восстановил из дампа, дал права как в инструкции, дал права суперпользователя БД но ошибка не уходит. Не пойму в чём дело, создал другого пользователя БД, дал права ошибка та же, не пойму что я нет так делаю.
Сейчас зайдя на любую страницу сайта можно увидеть ошибку.
Ссылка на сайт: elec.market
Команды которые использовал:
psql freechips -c "GRANT ALL ON ALL TABLES IN SCHEMA public to postgres;"
psql freechips -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to postgres;"
psql freechips -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to postgres;"
GRANT ALL PRIVILEGES ON DATABASE freechips TO postgres;