Permission to create databases is required to run against an existing Postgres database

jhansen2015 commented 1 month ago

Problem statement: Cannot run sameersbn/redmine:5.1.2-1 against an existing Postgres (10) database without permission to create databases.

Versions:

sameersbn/redmine:5.1.2-1
Posgres 10

Role and database created with:

  CREATE ROLE redmine
    LOGIN ENCRYPTED PASSWORD 'password'
    NOINHERIT VALID UNTIL 'infinity'
  ;

  CREATE DATABASE redmine
    WITH ENCODING='UTF8'
    OWNER=redmine
  ;

Log from container:

...
redmine-1   | New image version. Clearing cache
redmine-1   | Creating database...
postgres-1  | 2024-05-27 21:37:56.658 UTC [84] ERROR:  permission denied to create database
postgres-1  | 2024-05-27 21:37:56.658 UTC [84] STATEMENT:  CREATE DATABASE "redmine" ENCODING = 'unicode'
redmine-1   | PG::InsufficientPrivilege: ERROR:  permission denied to create database
redmine-1   | Couldn't create 'redmine' database. Please check your configuration.
redmine-1   | rake aborted!
redmine-1   | ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create database (ActiveRecord::StatementInvalid)
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:49:in `exec'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:49:in `block (2 levels) in execute'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:48:in `block in execute'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `execute'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/schema_statements.rb:46:in `create_database'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/postgresql_database_tasks.rb:26:in `create'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:133:in `create'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:193:in `block in create_current'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:506:in `block (2 levels) in each_current_configuration'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:503:in `each'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:503:in `block in each_current_configuration'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:502:in `each'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:502:in `each_current_configuration'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:193:in `create_current'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/railties/databases.rake:45:in `block (2 levels) in <top (required)>'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/rake-13.2.1/exe/rake:27:in `<top (required)>'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli/exec.rb:58:in `load'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli/exec.rb:58:in `kernel_load'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli/exec.rb:23:in `run'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli.rb:451:in `exec'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor/command.rb:28:in `run'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor/invocation.rb:127:in `invoke_command'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor.rb:527:in `dispatch'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli.rb:34:in `dispatch'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor/base.rb:584:in `start'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli.rb:28:in `start'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/exe/bundle:28:in `block in <top (required)>'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/friendly_errors.rb:117:in `with_friendly_errors'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/exe/bundle:20:in `<top (required)>'
redmine-1   | /usr/local/bin/bundle:25:in `load'
redmine-1   | /usr/local/bin/bundle:25:in `<main>'
redmine-1   |
redmine-1   | Caused by:
redmine-1   | PG::InsufficientPrivilege: ERROR:  permission denied to create database (PG::InsufficientPrivilege)
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:49:in `exec'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:49:in `block (2 levels) in execute'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:48:in `block in execute'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activesupport-6.1.7.7/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `execute'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/schema_statements.rb:46:in `create_database'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/postgresql_database_tasks.rb:26:in `create'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:133:in `create'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:193:in `block in create_current'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:506:in `block (2 levels) in each_current_configuration'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:503:in `each'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:503:in `block in each_current_configuration'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:502:in `each'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:502:in `each_current_configuration'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:193:in `create_current'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/activerecord-6.1.7.7/lib/active_record/railties/databases.rake:45:in `block (2 levels) in <top (required)>'
redmine-1   | /home/redmine/data/tmp/bundle/ruby/3.2.0/gems/rake-13.2.1/exe/rake:27:in `<top (required)>'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli/exec.rb:58:in `load'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli/exec.rb:58:in `kernel_load'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli/exec.rb:23:in `run'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli.rb:451:in `exec'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor/command.rb:28:in `run'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor/invocation.rb:127:in `invoke_command'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor.rb:527:in `dispatch'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli.rb:34:in `dispatch'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/vendor/thor/lib/thor/base.rb:584:in `start'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/cli.rb:28:in `start'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/exe/bundle:28:in `block in <top (required)>'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/lib/bundler/friendly_errors.rb:117:in `with_friendly_errors'
redmine-1   | /usr/local/bundle/gems/bundler-2.5.9/exe/bundle:20:in `<top (required)>'
redmine-1   | /usr/local/bin/bundle:25:in `load'
redmine-1   | /usr/local/bin/bundle:25:in `<main>'
redmine-1   | Tasks: TOP => db:create
redmine-1   | (See full trace by running task with --trace)
redmine-1   |
redmine-1 exited with code 0
redmine-1   | Symlinking dotfiles...
redmine-1   | Installing configuration templates...
redmine-1   | Configuring redmine...
redmine-1   | Configuring redmine::database
redmine-1   | Configuring redmine::logger...
redmine-1   | Configuring redmine::unicorn...
redmine-1   | Configuring redmine::secret_token...
redmine-1   | Configuring redmine::max_concurrent_ajax_uploads...
redmine-1   | Configuring redmine::sudo_mode...
redmine-1   | Configuring redmine::autologin_cookie...
redmine-1   | Configuring redmine::backups...
redmine-1   | Configuring redmine::rmagic::font...
redmine-1   | Configuring redmine::avatar::url...
...
redmine-1   | Creating database...
postgres-1  | 2024-05-27 21:37:58.874 UTC [86] ERROR:  permission denied to create database
redmine-1   | PG::InsufficientPrivilege: ERROR:  permission denied to create database
postgres-1  | 2024-05-27 21:37:58.874 UTC [86] STATEMENT:  CREATE DATABASE "redmine" ENCODING = 'unicode'
redmine-1   | Couldn't create 'redmine' database. Please check your configuration.
redmine-1   | rake aborted!
redmine-1   | ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create database (ActiveRecord::StatementInvalid)
...

jhansen2015 commented 1 month ago

Created a PR with a new DB_CREATE option (default: true for backward compatibility) to allow skipping the db:create step.

jcormier commented 2 weeks ago

Thanks for reporting this. I'm a bit behind at work right now but have not forgotten about this.

Is it possible for the code to check if the db exists before trying to create it instead?

jhansen2015 commented 1 week ago

Thanks for reporting this. I'm a bit behind at work right now but have not forgotten about this.

Thanks for taking time to look at it!

Is it possible for the code to check if the db exists before trying to create it instead?

I didn't find any code to check for db existence.

The migrate_database() function in the functions file creates the database and migrates it using the rake tasks db:create and db:migrate. As far as I can tell, there are no relevant options to the db:create task, and there is no separate rake db:... task for checking for db existence.

The PR (#558) maintains backwards compatibility with existing behavior:

The new DB_CREATE option defaults to true, which is the pre-existing behavior, and, like the pre-existing behavior, if creation fails, the script will exit, although now with a more helpful message.
If the value is overridden (set to false), then the rake db:create step will be skipped altogether.

What do you think?

sameersbn / docker-redmine

Permission to create databases is required to run against an existing Postgres database #557