Installing on PostgreSQL fails if the database exists and the installer creates a new user #11311

When installing Nextcloud on PostgreSQL if the given database user has create role permissions a new database user is created by prepending oc_ to the admin user name; this new user will be the one used by Nextcloud. If the given database user does not have create role permissions then that given user is the one used instead.

After that a database with the given database name is created and the user, either the new one or the given one, depending on the case, is made its owner. However, if a database with the given database name already exists, all the privileges for the public role are revoked from the database instead.

The problem is that when the given database user has create role permissions and the given database name exists the new user has no permissions on the existing database, so it can not connect to it and then the installation fails with Error while trying to create admin user: Failed to connect to the database: An exception occured in driver: SQLSTATE[08006] [7] FATAL: permission denied for database "nextcloud" DETAIL: User does not have CONNECT privilege.

This failure is related to the recent failures in the integration tests run by Drone on PostgreSQL; for more information about that please see nextcloud/spreed#1205.

How to reproduce:

• Start a Docker container for the PostgreSQL server
  • In a terminal, leave PostgreSQL running with docker run --rm --name postgres-installation-failure-db postgres:10
    • If no user is explicitly given then the Docker container will use postgres by default (no matter if the image is actually postgres:10.5 or earlier)
• Create a database for Nextcloud in the PostgreSQL server
  • In another terminal, execute docker exec --interactive --tty --user postgres postgres-installation-failure-db psql -c "CREATE DATABASE nextcloud;"
• Create a Docker container for the Nextcloud server (it is assumed that you have a Nextcloud Git directory somewhere)
  • In the previous terminal, execute docker run --rm --volume /PATH/TO/THE/NEXTCLOUD/GIT/DIRECTORY/:/var/www/html --volume /var/www/html/data --volume /var/www/html/config --interactive --tty --network container:postgres-installation-failure-db --name postgres-installation-failure nextcloudci/php7.1:php7.1-16 bash
• Install Nextcloud on PostgreSQL (all the following commands are performed in the Bash session inside the container)
  • Change to the Nextcloud directory with cd /var/www/html
  • Perform the installation with php occ maintenance:install --admin-pass=admin --database=pgsql --database-name=nextcloud --database-host=localhost --database-user=postgres --database-pass=it-does-not-matter

Possible fixes:
I do not really know which one would be better, as my database knowledge is pretty limited and rusty :-P

• After revoking the privileges, if a new user was created, make the new user the owner of the existing database with something like

$query = $connection->prepare("ALTER DATABASE " . addslashes($this->dbName) . " OWNER TO " . addslashes($this->dbUser));
try {
	$query->execute();
} catch (DatabaseException $e) {
	$this->logger->error('Error while trying to set database owner');
	$this->logger->logException($e);
}

• After revoking the privileges, if a new user was created, grant all privileges on the existing database to the new user with something like

$query = $connection->prepare("GRANT ALL PRIVILEGES ON DATABASE " . addslashes($this->dbName) . " TO " . addslashes($this->dbUser));
try {
	$query->execute();
} catch (DatabaseException $e) {
	$this->logger->error('Error while trying to grant database permissions');
	$this->logger->logException($e);
}

• Other approaches I have not tried :-)