Restarting from scratch - create database permission issues

[monkey128]

Infos:

• Used Zammad version: 3.6
• Used Zammad installation source: rpm package
• Operating system: CentOS 7
• Browser + version:

Expected behavior:

• After successfully dropping the zammad database, I want to recreate it with the command

zammad run rake db:create

according to the documentation:
https://docs.zammad.org/en/latest/migration/otrs.html#restarting-from-scratch

Actual behavior:

• The database isn’t created because of a permission issue.

PG::InsufficientPrivilege: ERROR:  permission denied to create database
: CREATE DATABASE "zammad" ENCODING = 'utf8'
Couldn't create 'zammad' database. Please check your configuration.
rake aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create database

Things I tried:

• I tried as root and as zammad user.
  First time I had the problem on a test machine where a couple of things were configured (wrong?). So I tried with a fresh CentOS install (only installed zammad, created the admin user and deleted default user “Nicole Braun”), but I still couldn’t recreate the database.

Is there a ‘hidden secret’ like with the dropping command which I am too blind to see?

[MrGeneration]

On package installations the zammad user usually may not create the database itself.
This is the reason the restore script falls back to the psql way:

zammad/zammad/blob/develop/contrib/backup/functions#L184-L187

[monkey128]

Thanks for your reply.

@MrGeneration

On package installations the zammad user usually may not create the database itself.

So, is the documentation wrong on that part? As it addresses package installations.
Quote from documentation: (link in my first post)

If you installed the Zammad DEB or RPM package

$ zammad run rake db:drop
$ zammad run rake db:create
$ zammad run rake db:migrate
$ zammad run rake db:seed

What I did now:
I changed to user postgres and created the database manually with
psql -c "CREATE DATABASE zammad OWNER zammad;"
After that the migrate and seed commands ran without errors.

Is that a proper way to do it? Or could it lead to problems when updating the package later?

To sum it up (for me): User zammad may not create the database, but user postgres may not execute the zammad run rake ... commands, only zammad and root may. Therefore I cannot just send the four commands as postgres.
Is that the problem behind the error I got?

[MrGeneration]

@monkey128

So, is the documentation wrong on that part? As it addresses package installations.
Quote from documentation: (link in my first post)

Technically it’s not incorrect.
It simply depends on your zammad users permission.
By default, as far as I’m aware, the zammad user is not allowed to create zammad* namespaced databases which is the technical root of issue.

@monkey128

I changed to user postgres and created the database manually with
psql -c "CREATE DATABASE zammad OWNER zammad;"
After that the migrate and seed commands ran without errors.

Is that a proper way to do it? Or could it lead to problems when updating the package later?

This method is absolutely fine, no need to worry!

@monkey128

To sum it up (for me): User zammad may not create the database, but user postgres may not execute the zammad run rake ... commands, only zammad and root may. Therefore I cannot just send the four commands as postgres.
Is that the problem behind the error I got?

zammad run .... requires to be run either as user zammad or root - any other user of your system will bring issues.