I managed to install a NextCloud 13.server on raspbian stretch using a PostgreSQL database.
For PostgreSQL I followed: https://opensource.com/article/17/10/set-postgres-database-your-raspberry-pi
I created Linux user ‘nxtcld’ which I intended as the database user for NextCloud. And of course a Linux user pi exists.
Since I have finished the next cloud installation, it works fine. But in order to create and restore backups I have to administrate the postgres database ‘nxtcld’
The PostgreSQL installation has these users: pi, postgres, nxtcld, oc_ocadmin.
In a backup I found that all tables seems to have owner ‘oc_ocadmin’. All database belong to user ‘postgres’
In Linux and for the usage of psql it is very comfortable to have Linux user with the same name. Among others those are present:
**pi:**x:1000:1000:,:/home/pi:/bin/bash
postgres:x:111:117:PostgreSQL administrator,:/var/lib/postgresql:/bin/bash
nxtcld:x:1001:1001:nextcloud,:/home/nxtcld:/bin/bash
But currently I do not have a user ‘oc_ocadmin’. That causes some trouble with psql on NextCloud database. The authentication is difficult.
E.g. . A restore of a text format dump with
PGPASSWORD=“password from NextCloud config” psql -U oc_ocadmin nxtcld2 < /var/lib/postgresql/testbackupnc
is not a working, while
sudo -u postgres psql postgres
starts a psql session for user postgres without password because the Linux user exists.
Is it correct that database user ‘postgres’ is owner of the database ‘nxtcld’ or should I change that to db user ‘nxtcld’ or user ‘oc_ocadmin’.
Is it correct that tables belong to oc_ocadmin? Is it useful and recommended to create a user Linux user ‘oc_ocadmin’?
I think that has a lot to do with security and I would like to have a simple but secure configuration.