Postgresql ошибка нет доступа к таблице

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

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.

Как решить проблему? и как посмотреть лог 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

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

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

psql -Upostgres myapp

psql your_db_name -U postgres -h your_host_name

GRANT USAGE ON SCHEMA public TO your_user_name

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