Database user is not allowed to re-create the database

[mkllnk]

Description

A change in the way we use the postgresql role means that the CREATEDB privilege is not passed on to the user creation. We can't drop and create the database. That is useful when importing another database, for example when switching production servers.

Expected Behavior

DISABLE_DATABASE_ENVIRONMENT_CHECK=1 I_AM_SURE=1 bundle exec rails db:drop db:create
# database created

Actual Behaviour

DISABLE_DATABASE_ENVIRONMENT_CHECK=1 I_AM_SURE=1 bundle exec rails db:drop db:create
# error on db:create

Steps to Reproduce

1. Fresh server, for example with vagrant destroy; vagrant up.
2. Provision and deploy.
3. Try to re-create the database.

Animated Gif/Screenshot

Severity

Your Environment

• Version used:
• Browser name and version:
• Operating System and version (desktop or mobile):

Possible Fix

We added some workarounds in ebd62cd which could now be removed if we upgrade the postgresql role.

Workaround

Give the user permission manually:

# sudo -u postgres psql
alter user ofn_user createdb;

[jibees]

Could we test it with a local setup (and only local...)?

[mkllnk]

Yes, we can totally test it locally. That's what I mean in the testing steps with vagrant up. It creates a local virtual machine.

[jibees]

Ok so vagrant up seems to work like a charm.

I tried to provision and deploy.

Provisionning:
I had to add this ansible_python_interpreter=/usr/bin/python3

diff --git a/inventory/hosts b/inventory/hosts
index 419cff3..0e1b0af 100644
--- a/inventory/hosts
+++ b/inventory/hosts
@@ -2,7 +2,7 @@
 # Local containers for testing

 [vagrant]
-local_vagrant ansible_host=127.0.0.1 ansible_user=vagrant ansible_port=2222 ansible_ssh_private_key_file=~/.vagrant.d/insecure_private_key ansible_ssh_common_args='-o StrictHostKeyChecking=no'
+local_vagrant ansible_host=127.0.0.1 ansible_user=vagrant ansible_port=2222 ansible_ssh_private_key_file=~/.vagrant.d/insecure_private_key ansibl
e_ssh_common_args='-o StrictHostKeyChecking=no' ansible_python_interpreter=/usr/bin/python3

 [lxc]
 ofn.local

But the provisioning failed:

TASK [ssh_keys : add ssh keys users_sysadmin] *************************************************************************************
failed: [local_vagrant] (item=enricostn) => {"ansible_loop_var": "item", "changed": false, "item": "enricostn", "msg": "Failed to lookup user ofn-admin: 'getpwnam(): name not found: ofn-admin'"}
failed: [local_vagrant] (item=pau) => {"ansible_loop_var": "item", "changed": false, "item": "pau", "msg": "Failed to lookup user ofn-admin: 'getpwnam(): name not found: ofn-admin'"}

PLAY RECAP ************************************************************************************************************************
local_vagrant              : ok=2    changed=0    unreachable=0    failed=1    skipped=2    rescued=0    ignored=0

Deploying:

TASK [Gathering Facts] ************************************************************************************************************
fatal: [local_vagrant]: FAILED! => {"msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chmod: invalid mode: ‘A+user:openfoodnetwork:rx:allow’\nTry 'chmod --help' for more information.\n}). For information on working around this, see https://docs.ansible.com/ansible-core/2.12/user_guide/become.html#risks-of-becoming-an-unprivileged-user"}

PLAY RECAP ************************************************************************************************************************
local_vagrant              : ok=0    changed=0    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0

[mkllnk]

I had to add this ansible_python_interpreter=/usr/bin/python3

Did you run setup.yml first? That one links python so that Ansible works.