Permissions issues with PostgreSQL on fresh install of NC 20 VM

jsalas424 · October 6, 2020, 4:34am

Hello,

I downloaded the newest NC 20 VM from ttps://www.hanssonit.se/nextcloud-vm/ . I am trying to migrate from Nextcloud Snap in an Ubuntu 20 VM to the official VM, but ran into a wall.

I was in the process of restoring the database as per official docs when I hit a permissions issue. I was able to drop the database, but was then blocked from creating a new one.

ncadmin@nextcloud:~$ PGPASSWORD="1234567890" psql -U ncadmin -d template1 -c "DROP DATABASE \"nextcloud_db\";"
DROP DATABASE

ncadmin@nextcloud:~$ PGPASSWORD="1234567890" psql -U ncadmin -d template1 -c "CREATE DATABASE \"nextcloud_db\";"
ERROR:  permission denied to create database

Full config:

root@nextcloud:/var/www/nextcloud# cat config/config.php
<?php
$CONFIG = array (
  'passwordsalt' => 'a6qkljshf92',
  'secret' => 'asdlkfjhwe982345',
  'trusted_domains' => 
  array (
    0 => 'localhost',
    1 => '192.168.1.144',
    2 => 'mydomain.ddns.org',
    3 => 'mydomain.ddns.org',
  ),
  'datadirectory' => '/mnt/ncdata',
  'dbtype' => 'pgsql',
  'version' => '20.0.0.9',
  'overwrite.cli.url' => 'https://mydomain.ddns.org/',
  'dbname' => 'nextcloud_db',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'ncadmin',
  'dbpassword' => '1234567890',
  'installed' => true,
  'instanceid' => 'och5mtox047w',
  'upgrade.disable-web' => 'true',
  'log_type' => 'file',
  'logfile' => '/var/log/nextcloud/nextcloud.log',
  'loglevel' => '2',
  'log.condition' => 
  array (
    'apps' => 
    array (
      0 => 'admin_audit',
    ),
  ),
  'mail_smtpmode' => 'smtp',
  'remember_login_cookie_lifetime' => '1800',
  'log_rotate_size' => '10485760',
  'trashbin_retention_obligation' => 'auto, 180',
  'versions_retention_obligation' => 'auto, 365',
  'simpleSignUpLink.shown' => false,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'filelocking.enabled' => true,
  'memcache.distributed' => '\\OC\\Memcache\\Redis',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/var/run/redis/redis-server.sock',
    'port' => 0,
    'timeout' => 0.5,
    'dbindex' => 0,
    'password' => 'abcdefg',
  ),
  'logtimezone' => 'Etc/UTC',
  'htaccess.RewriteBase' => '/',
  'enable_previews' => true,
  'enabledPreviewProviders' => 
  array (
    11 => 'OC\\Preview\\PNG',
    12 => 'OC\\Preview\\JPEG',
    13 => 'OC\\Preview\\GIF',
    14 => 'OC\\Preview\\BMP',
    15 => 'OC\\Preview\\MarkDown',
    16 => 'OC\\Preview\\MP3',
    17 => 'OC\\Preview\\TXT',
    18 => 'OC\\Preview\\Movie',
  ),
  'preview_max_x' => '2048',
  'preview_max_y' => '2048',
  'jpeg_quality' => '60',
  'maintenance' => true,
);

I have changed the name of my domain, all the passwords, and the salts. The rest is the same.

enoch85 · October 31, 2020, 10:38pm

You should run all pgsql commands as the postgres user.

sudo -u postgres ...

das1996 · February 4, 2021, 8:23pm

@jsalas424 Did you ever figure this out?

Executing the commandline
sudo -u postgres PGPASSWORD=“db_pw_from_config.php” psql -h localhost -U ncadmin -d template1 -c “CREATE DATABASE “nextcloud_db”;”

Returns permission denied error.

Removing the PGPASSWORD portion results in the console asking for the password for using ncadmin. Entering dbpw from config.php still returns permission denied.

@enoch85 What is the proper command line?

Bernie_O · February 4, 2021, 10:26pm

As far as I know the user postgres doesn’t need a password for psql. Try this:

sudo -u postgres psql -h localhost -d template1 -c 'CREATE DATABASE "nextcloud_db";'

das1996 · February 4, 2021, 10:39pm

It’s prompting for a password.

ncadmin@nextcloud:~$ sudo -u postgres psql -h localhost -d template1 -c 'CREATE DATABASE "nextcloud_db";'
Password for user postgres:
psql: error: fe_sendauth: no password supplied
ncadmin@nextcloud:~$

However if I just do sudo -u postgres psql for an interactive session no password prompt appears.

szaimen · February 4, 2021, 10:40pm

hm… Maybe this works for you?
sudo -Hiu postgres psql -h localhost -d template1 -c ‘CREATE DATABASE “nextcloud_db”;’

das1996 · February 4, 2021, 10:42pm

^^No go. Still asking for password.

szaimen · February 4, 2021, 10:55pm

Very strange

das1996 · February 4, 2021, 11:09pm

Take a look at Unable to restore database · Issue #1795 · nextcloud/vm · GitHub . Look for this block of code. It’s from the nextcloud docs. This does seem to work without prompting for any database passwords. Enoch indicates one should be able to recreate a database without granting the ncadmin database user additional privileges. In practice, I can’t get it work.

CREATE USER username WITH PASSWORD 'password';
CREATE DATABASE nextcloud TEMPLATE template0 ENCODING 'UNICODE';
ALTER DATABASE nextcloud OWNER TO username;
GRANT ALL PRIVILEGES ON DATABASE nextcloud TO username;

If I grant the database user CREATEDB role, then the psql command works.

postgres=# ALTER USER ncadmin  CREATEDB;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 ncadmin   | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}



ncadmin@nextcloud:~$ PGPASSWORD="12345678910"  psql -h localhost -U ncadmin -d template1 -c 'CREATE DATABASE "nextcloud_db";'
CREATE DATABASE
ncadmin@nextcloud:~$

Conclusion?

szaimen · February 4, 2021, 11:52pm

If you look at Database configuration — Nextcloud latest Administration Manual latest documentation you will see that is recommended to grant the database user CREATEDB permissions.
So if it works for you I’d say just do it like this or is there a problem with that?

das1996 · February 4, 2021, 11:56pm

^^Good eye! I completely missed that line. @enoch85 's appliance however sets up the database slightly differently. I imagine for security reasons¿? Hopefully he’ll comment on the proper way to do this on his implementation.

szaimen · February 5, 2021, 12:03am

Actually, afaics did he already link you to the correct lines:

github.com

nextcloud/vm/blob/d42e947b2632b0175e630004ab0fd2980ea7d815/nextcloud_install_production.sh#L338-L341


sudo -u postgres psql <<END
CREATE USER $NCUSER WITH PASSWORD '$PGDB_PASS';
CREATE DATABASE nextcloud_db WITH OWNER $NCUSER TEMPLATE template0 ENCODING 'UTF8';
END

Here are some other commands that might help:

github.com

szaimen/NcVM-migration/blob/531b64e045d186146ca93d008a25207a144257b9/migration.sh#L601-L613


# Restore database
echo "Dropping old Nextcloud DB..."
sudo -Hiu postgres psql \${nextcloudDatabase} -c "ALTER USER \${dbUser} WITH PASSWORD '\${dbPassword}'"
sudo -Hiu postgres psql -c "DROP DATABASE \${nextcloudDatabase};"
echo "Done"
echo "Creating new DB for Nextcloud..."
sudo -Hiu postgres psql -c "CREATE DATABASE \${nextcloudDatabase} WITH OWNER \${dbUser} TEMPLATE template0 ENCODING \"UTF8\";"
echo "Done"
echo "Restoring backup DB..."
sudo -Hiu postgres psql "\${nextcloudDatabase}" < "\${currentRestoreDir}/\${fileNameBackupDb}"
echo "Done"

das1996 · February 5, 2021, 12:17am

@szaimen A key point of confusion for me has been reconciling the docs.nextcloud.com information against Enoch85’s implementation. As with most things there’s 20 ways to skin a cat.

I think the take away is to follow the process that matches how the underlying server was configured. In my case since the appliance is used, a variation of lines 338-341 above should be used to do the drop and recreation.

If someone followed the docs.nextcloud.com to set up the server then their steps should be used.

Thanks for the links. I will review them shortly.

enoch85 · February 5, 2021, 3:20pm

Hi,

To save you the trouble, here you go:

# Export:
# sudo -u postgres pg_dump -Fc nextcloud_db > ncdb.dump
# Import:
# sudo -u postgres pg_restore --verbose --clean --no-acl --no-owner -h localhost -U ncadmin -d nextcloud_db /home/ncadmin/ncdb.dump

das1996 · February 7, 2021, 6:58am

@enoch85 Thanks for providing that.

After much experimentation I still had issues however depending on what the previous state of the database was.

The following is what I end up using which seems to work regardless of what state its in.

Backup

PGPASSWORD="${dbpassword}" pg_dump -h "${dbhost}" -U "${dbuser}" -d "${dbname}" -f "nextcloud-sql.bak"

Restore

sudo -u  postgres psql > /dev/null  2>&1  <<END
ALTER USER $dbuser SUPERUSER CREATEDB;
ALTER ROLE $dbuser WITH PASSWORD '$dbpassword';
END

PGPASSWORD="${dbpassword}" psql -q -U "${dbuser}" -h "${dbhost}"  -d template1 -c "DROP DATABASE IF EXISTS \"${dbname}\";"
PGPASSWORD="${dbpassword}" psql -q -U "${dbuser}" -h "${dbhost}"  -d template1 -c "CREATE DATABASE \"${dbname}\" OWNER \"${dbuser}\";"

PGPASSWORD="${dbpassword}" psql -q -U "${dbuser}" -h "${dbhost}" "${dbprotocol:-}" -d "${dbname}" -f "nextcloud-sql.bak" 2>&1 >/dev/null)"

sudo -u  postgres psql > /dev/null  2>&1  <<END
ALTER USER $dbuser NOSUPERUSER CREATEDB;
GRANT CONNECT, CREATE, TEMPORARY on DATABASE $dbname TO $dbuser;
REVOKE TEMP, CONNECT ON DATABASE $dbname FROM public;
END

Privileges seem to change when the database is recreated. I don’t know how important or not important this is. Commands in the last block restore the same privileges as were present for the original.

I made these changes to the backup script from https://codeberg.org/BernieO/ncupgrade.