GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
The readonly user can connect, see the tables but when it tries to do a simple select it gets:
ERROR: permission denied for relation mytable
SQL state: 42501
This is happening on PostgreSQL 9.1
What I did wrong?
Barmar
718k53 gold badges481 silver badges598 bronze badges
asked Nov 21, 2012 at 16:10
1
Here is the complete solution for PostgreSQL 9+, updated recently.
CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonly';
GRANT USAGE ON SCHEMA public to readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
-- repeat code below for each database:
GRANT CONNECT ON DATABASE foo to readonly;
c foo
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; --- this grants privileges on new tables generated in new database "foo"
GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
Thanks to https://jamie.curle.io/creating-a-read-only-user-in-postgres/ for several important aspects
If anyone find shorter code, and preferably one that is able to perform this for all existing databases, extra kudos.
Greg Bray
14.5k10 gold badges80 silver badges104 bronze badges
answered Nov 22, 2012 at 11:20
sorinsorin
157k172 gold badges523 silver badges784 bronze badges
5
Try to add
GRANT USAGE ON SCHEMA public to readonly;
You probably were not aware that one needs to have the requisite permissions to a schema, in order to use objects in the schema.
answered Nov 21, 2012 at 18:36
sufleRsufleR
2,80517 silver badges31 bronze badges
7
This worked for me:
Check the current role you are logged into by using:
SELECT CURRENT_USER, SESSION_USER;
Note: It must match with Owner of the schema.
Schema | Name | Type | Owner
———+———+——-+———-
If the owner is different, then give all the grants to the current user role from the admin role by :
GRANT ‘ROLE_OWNER’ to ‘CURRENT ROLENAME’;
Then try to execute the query, it will give the output as it has access to all the relations now.
answered Jun 24, 2015 at 23:33
2
make sure your user has attributes on its role. for example:
postgres=# du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
flux | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
after performing the following command:
postgres=# ALTER ROLE flux WITH Superuser;
ALTER ROLE
postgres=# du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
flux | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication | {}
it fixed the problem.
see tutorial for roles and stuff here: https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps—2
answered Jul 16, 2015 at 6:28
PuN1sh3rPuN1sh3r
911 silver badge8 bronze badges
2
You should execute the next query:
GRANT ALL ON TABLE mytable TO myuser;
Or if your error is in a view then maybe the table does not have permission, so you should execute the next query:
GRANT ALL ON TABLE tbm_grupo TO myuser;
Bruce P
19.5k7 gold badges62 silver badges73 bronze badges
answered Oct 27, 2015 at 17:11
2
I am very new to postgres so please my apologies in advance if I sound naive. I am still trying to learn. I am trying to create a readonly role and then create a role and assign readonly role to the user. I logged in as postgres user
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE test_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
CREATE USER readonlyuser WITH PASSWORD 'read123';
grant readonly to readonlyuser;
Now I can login as user readonlyuser but I can’t read data from any tables. I get error SQL Error [42501]: ERROR: permission denied for table.
Any help would be appreciated.
asked Jan 14, 2021 at 6:39
The ALTER DEFAULT PRIVILEGES statement you ran will only affect tables created by postgres. If a different user creator creates the tables, you need
ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
answered Jan 14, 2021 at 7:37
Laurenz AlbeLaurenz Albe
38.9k4 gold badges34 silver badges58 bronze badges
Login : sudo -u postgres psql
Select db : c yourDbName
View all table dt;
grant youUserName to postgres;
(permission related error then use this command)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO {serverName};
answered Jul 6, 2022 at 12:37
For me, the problem was that I was using the free tier of ElephantSql.com (a great website BTW), and I used (way) more than the allocated 20 MB free quota.
I was able to make a select query, but an insert one failed with the error permission denied for table.
answered Jan 25 at 1:13
In Postgres I created the following table inside a db called testing:
CREATE TABLE category_google_taxonomy (
category_id integer references category ON UPDATE CASCADE ON DELETE CASCADE,
google_taxonomy_id integer references google_taxonomy ON UPDATE CASCADE ON DELETE CASCADE
);
When I try to populate the table:
INSERT INTO category_google_taxonomy (category_id, google_taxonomy_id) VALUES
(1,7),
(2,12);
I get the following error:
ERROR: permission denied for schema public
LINE 1: SELECT 1 FROM ONLY "public"."category" x WHERE "category_id"...
^
QUERY: SELECT 1 FROM ONLY "public"."category" x WHERE "category_id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x
I read up a bit and eventually granted ALL PRIVILEGES out of exasperation, but it still doesn’t work:
testing=# GRANT ALL PRIVILEGES ON public.category TO testing;
GRANT
testing=# dp category_google_taxonomy
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------------------+-------+-------------------------+--------------------------
public | category_google_taxonomy | table | testing=arwdDxt/testing |
: super=arwdDxt/testing
testing=# dp category
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+----------+-------+------------------------+--------------------------
public | category | table | testing=arwdDxt/super | category_id:
: testing=arwx/super
(1 row)
On @Daniel’s suggestion I tried GRANT USAGE ON schema public TO super;, now when I run the INSERT command I get:
ERROR: permission denied for relation category
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."category" x WHERE "category_id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"
Here is the relevant part of d:
public | category | table | super
public | category_google_taxonomy | table | testing
Я использую PostGres 9.5. У меня проблемы с вставкой данных в таблицу, которую я только что создал. Я получаю ошибки «Отказано в доступе», несмотря на предоставление всех прав пользователю базы данных. См. ниже …
localhost:myapp davea$ psql -Upostgres
Password for user postgres:
psql (9.5.0, server 9.5.1)
Type "help" for help.
postgres=# GRANT ALL ON schema public TO myapp;
GRANT
localhost:myapp davea$ psql -Upostgres
Password for user postgres:
psql (9.5.0, server 9.5.1)
Type "help" for help.
postgres=# GRANT USAGE ON schema public TO myapp;
GRANT
postgres=# q
localhost:myapp davea$ myapp
psql (9.5.0, server 9.5.1)
Type "help" for help.
myapp=> insert into search_codes (id, code, address_id) values (1, 'atlanta', 'GA');
ERROR: permission denied for relation search_codes
myapp=> select * FROM search_codes;
ERROR: permission denied for relation search_codes
myapp=> d search_codes;
Table "public.search_codes"
Column | Type | Modifiers
------------+-------------------+-----------------------------------------------------------
id | integer | not null default nextval('search_codes_id_seq'::regclass)
code | character varying |
address_id | character varying |
Indexes:
"search_codes_pkey" PRIMARY KEY, btree (id)
"index_search_codes_on_code" UNIQUE, btree (code)
"index_search_codes_on_address_id" btree (address_id)
Как правильно предоставить привилегии, чтобы мой пользователь мог вставить и выбрать из таблицы?
4 ответа
Лучший ответ
Ваша первая команда даст вам возможность составить список таблиц (вы можете просто знать, что они существуют)
GRANT USAGE ON SCHEMA public TO myapp
Затем вы должны предоставить SELECT, INSERT и т. Д. … всем таблицам в схеме public.
GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO myapp
Я рекомендую не давать все привилегии конкретному приложению.
Если у вас есть последовательности:
GRANT SELECT, UPDATE, USAGE ON ALL SEQUENCES IN SCHEMA public to myapp
Если у вас есть функции:
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO myapp
Тогда твой пример сработает.
Но вы все равно должны применить некоторую команду, если вы хотите, чтобы таблица, созданная в будущем, была доступна:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, UPDATE, INSERT, DELETE, TRIGGER ON TABLES TO myapp
У Postgresql очень странный механизм, и мне понадобилось время, чтобы понять это!
13
Rémi Desgrange
20 Окт 2017 в 13:56
Вы предоставляете разрешения в базе данных postgres вместо базы данных myapp.
Измените первую команду PSQL на
psql -Upostgres myapp
А потом выдайте гранты
2
Gary
16 Окт 2017 в 15:27
Сначала вы должны войти в новую базу данных, используя пользователя postgres.
psql your_db_name -U postgres -h your_host_name
Дать возможность перечислить таблицу
GRANT USAGE ON SCHEMA public TO your_user_name
Затем вы должны предоставить разрешение всем таблицам в схеме public.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user_name
Если у вас есть последовательности, то дайте разрешение
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user_name
2
Kamrujjaman Khan
7 Янв 2019 в 10:03