Ошибка нет доступа к таблице postgresql - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

Здравствуйте, почему не получается выдать права пользователю, хотя раньше всегда работал данный способ в моем случае.

CREATE DATABASE bot;
CREATE USER botuser WITH PASSWORD 'botpassword';
GRANT ALL PRIVILEGES ON DATABASE bot TO botuser;

c bot

CREATE TABLE example (id int PRIMARY KEY, name text)

#Дальше через пользователя postgres, делаю коннект к базе командой:
 psql -d bot -U botuser

SELECT * FROM example;

#Выходит ошибка: нет доступа к таблице example

#Но если даю права именно на SELECT то получается под пользователем забирать данные:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO botuser;

В чем может быть проблема?

Источник

Перейти к контенту

I kept getting this error when using flyway to deploy database changes. I do some manual setup first, such as creating the database, so flyway wouldn’t need those super-admin permissions.

My Fix

I had to ensure that the database user that flyway job used had ownership rights to the public schema, so that the flyway user could then assign the right to use the schema to other roles.

Additional setup Details

I am using AWS RDS (both regular and Aurora), and they don’t allow super users in the databases. RDS reserves super users for use by AWS, only, so that consumers are unable to break the replication stuff that is built in. However, there’s a catch-22 that you must be an owner in postgres to be able to modify it.

My solution was to create a role that acts as the owner (‘owner role’), and then assign both my admin user and the flyway user to the owner role, and use ALTER scripts for each object to assign the object’s owner to the owner role.

I missed the public schema, since that was auto-created when I created the database script manually. The public schema defaulted to my admin role rather than the shared owner role. So when the flyway user tried to assign public schema permissions to other roles, it didn’t have the authority to do that. An error was not thrown during flyway execution, however.

I kept getting this error when using flyway to deploy database changes. I do some manual setup first, such as creating the database, so flyway wouldn’t need those super-admin permissions.

My Fix

I had to ensure that the database user that flyway job used had ownership rights to the public schema, so that the flyway user could then assign the right to use the schema to other roles.

Additional setup Details

Как решить проблему? и как посмотреть лог puma? не могу найти нигде.

Ситуация такая: хочу сделать staging для проекта.

1) сделал деплой проекта на тестовый сервер (raspberry).

2) Восстановил базу (postgresql) из бэкапа с продакшн. Миграции не накатывал, их 1000 и очень много отваливается, приходится лезть и комментировать код, да и это не вариант.

Использую nginx, который смотрит на puma.
Запускаю

bundle exec puma -e production -b unix:///home/ivanov/www/ivanov/current/apps/www/tmp/sockets/puma.sock

и вываливается ошибка:

DEPRECATION WARNING: You have Rails 2.3-style plugins in vendor/plugins! Support for these plugins will be removed in Rails 4.0. Move them out and bundle them in your Gemfile, or fold them in to your app as lib/myplugin/* and config/initializers/myplugin.rb. See the release notes for more on this: http://weblog.rubyonrails.org/2012/1/4/rails-3-2-0-rc2-has-been-released. (called from <top (required)> at /home/ivanov/www/ivanov/releases/20170622115720/apps/www/config/environment.rb:9)
/home/ivanov/www/ivanov/releases/20170622115720/apps/www/config/initializers/bypass_ssl_verification_for_open_uri.rb:1: warning: already initialized constant OpenSSL::SSL::VERIFY_PEER
/home/ivanov/.rvm/gems/ruby-2.0.0-p247@parts-script-new-admin/gems/actionpack-3.2.13/lib/action_dispatch/http/mime_type.rb:102: warning: already initialized constant Mime::PDF
/home/ivanov/.rvm/gems/ruby-2.0.0-p247@parts-script-new-admin/gems/actionpack-3.2.13/lib/action_dispatch/http/mime_type.rb:102: warning: previous definition of PDF was here
! Unable to load application: ActiveRecord::StatementInvalid: PG::Error: ОШИБКА:  нет доступа к отношению site_configs
: SELECT  "site_configs".* FROM "site_configs"  LIMIT 1
/home/ivanov/.rvm/gems/ruby-2.0.0-p247@parts-script-new-admin/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `async_exec': PG::Error: ОШИБКА:  нет доступа к отношению site_configs (ActiveRecord::StatementInvalid)
: SELECT  "site_configs".* FROM "site_configs"  LIMIT 1

В database.yml указан owners таблицы, и у него стоит GRANT ALL privileges ON DATABASE

в логах приложения:

Connecting to database specified by database.yml
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Connecting to database specified by database.yml
Connecting to database specified by database.yml

Я использую 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 очень странный механизм, и мне понадобилось время, чтобы понять это!

Rémi Desgrange
20 Окт 2017 в 13:56

Вы предоставляете разрешения в базе данных postgres вместо базы данных myapp.

Измените первую команду PSQL на

psql -Upostgres myapp

А потом выдайте гранты

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

Kamrujjaman Khan
7 Янв 2019 в 10:03

NataNov

0 / 0 / 0

Регистрация: 09.04.2021

Сообщений: 4

Создание пользователя с правами на таблицы в схеме

09.04.2021, 14:54. Показов 4730. Ответов 1

Метки нет (Все метки)

Добрый день!
Изучаю PostgreSql и не могу разобраться в следующем.
Хочу создать пользователя с правами выбора из всех таблиц некоторой схемы в БД.
Все дальнейшие действия делаю в утилите psql под суперпользователем postgres:

SQL

CREATE DATABASE my_db;
c my_db
CREATE schema my_schema;
SET search_path TO my_schema,public;
CREATE TABLE my_table (f1 int2,f2 text);
INSERT INTO my_table VALUES (1,'aaa');
CREATE ROLE user1 login password 'user1';
GRANT SELECT ON ALL TABLES IN schema my_schema TO user1;

Затем подключаюсь к базе my_db под пользователем user1:

c my_db user1

До сих пор все шло прекрасно. Но дальше пытаюсь сделать select от имени пользователя user1:

SQL

1	SELECT * FROM my_schema.my_table;

выходит ошибка — нет доступа к схеме my_schema

пытаюсь по другому:

SQL

1 2	SET search_path TO my_schema,public; SELECT * FROM my_table;

Ошибка — нет доступа к таблице my_table!

Что я делаю не так?!

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь

grgdvo

1184 / 914 / 367

Регистрация: 02.09.2012

Сообщений: 2,785

10.04.2021, 09:09

Сообщение было отмечено NataNov как решение

Решение

Схема — такой же объект базы, как и таблицы.
Вы дали доступ на все таблицы в схеме, но не дали доступ к самой схеме.
См. что-то типа

SQL

1	GRANT USAGE ON SCHEMA my_schema TO user1;

Источник

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

43k4 gold badges36 silver badges60 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

Источник

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

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

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

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

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

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

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

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 очень странный механизм, и мне понадобилось время, чтобы понять это!

Rémi Desgrange
20 Окт 2017 в 13:56

Вы предоставляете разрешения в базе данных postgres вместо базы данных myapp.

Измените первую команду PSQL на

psql -Upostgres myapp

А потом выдайте гранты

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

Kamrujjaman Khan
7 Янв 2019 в 10:03

Источник

The first comment nailed the most likely reason this is happening. Quoting the release announcement:

PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.

The reason your fix didn’t work is that all actions you took on database postgres in regards to user admin‘s privileges on schema public concern only that schema within the database postgres. Schema public on database postgres is not the same schema public as the one on newly created mydb.

Also, this:

GRANT ALL ON DATABASE mydb TO admin;

grants privileges on the database itself, not things within the database. admin can now drop the database, for example, still without being able to create tables in schema public. My guess is that you wanted to make admin also the owner of mydb, in which case you need to add

ALTER DATABASE mydb OWNER TO admin;

Or you need to repeat your GRANT USAGE, CREATE ON SCHEMA public TO admin; on mydb.

Here’s some more documentation on secure schema usage patterns the PostgreSQL 15 change was based on.

Источник