[Bug]: When postgres is used, failed to create admin account if username contains hyphen

[Bai-Chiang]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• Nextcloud Server is running on 64bit capable CPU, PHP and OS.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

During initial setup, if try to create an admin account if username contains hyphen it will fail, when using postgres.

Steps to reproduce

1. Run this docker-compose.yml

version: "2.1"
services:
  nextcloud:
    image: nextcloud:25
    container_name: nextcloud
    volumes:
      - nextcloud:/var/www/html
    depends_on:
      - postgres
    ports:
      - 8080:80
    restart: unless-stopped

  postgres:
    image: postgres:15-alpine
    container_name: postgres
    volumes:
      - postgres:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=nextcloud
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
    restart: unless-stopped

volumes:
  nextcloud:
  postgres:

2. Try to create an admin account with username john-smith.
3. It would fail with

Error
PostgreSQL username and/or password not valid You need to enter details of an existing account.

4. Check postgres logs

2023-03-07 23:06:17.365 UTC [58] ERROR:  syntax error at or near "-" at character 20
2023-03-07 23:06:17.365 UTC [58] STATEMENT:  CREATE USER oc_john-smith CREATEDB PASSWORD '3o7ZpltFWzOpggvZzPnuauA6mZINW7'
2023-03-07 23:06:17.379 UTC [59] FATAL:  password authentication failed for user "oc_john-smith"
2023-03-07 23:06:17.379 UTC [59] DETAIL:  Role "oc_john-smith" does not exist.
	Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

Expected behavior

Successfully create john-smith admin account and show welcome page.

Installation method

Community Docker image

Operating system

Debian/Ubuntu

PHP engine version

None

Web server

Apache (supported)

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

Fresh Nextcloud Server install

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

No response

List of activated Apps

Nextcloud is not installed - only a limited number of commands are available
An unhandled exception has been thrown:
Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  password authentication failed for user "oc_john-smith" in /var/www/html/lib/private/DB/Connection.php:139
Stack trace:
#0 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1519): OC\DB\Connection->connect()
#1 /var/www/html/3rdparty/doctrine/dbal/src/Statement.php(73): Doctrine\DBAL\Connection->getWrappedConnection()
#2 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1016): Doctrine\DBAL\Statement->__construct('SHOW SERVER_VER...', Object(OC\DB\Connection))
#3 /var/www/html/lib/private/DB/Connection.php(238): Doctrine\DBAL\Connection->prepare('SHOW SERVER_VER...')
#4 /var/www/html/lib/private/DB/ConnectionAdapter.php(56): OC\DB\Connection->prepare('SHOW SERVER_VER...', NULL, NULL)
#5 /var/www/html/lib/private/legacy/OC_DB.php(55): OC\DB\ConnectionAdapter->prepare('SHOW SERVER_VER...', NULL, NULL)
#6 /var/www/html/lib/private/legacy/OC_DB.php(128): OC_DB::prepare('SHOW SERVER_VER...', NULL, NULL)
#7 /var/www/html/lib/private/legacy/OC_Util.php(753): OC_DB::executeAudited(Array)
#8 /var/www/html/lib/private/legacy/OC_Util.php(733): OC_Util::checkDatabaseVersion()
#9 /var/www/html/lib/private/Console/Application.php(159): OC_Util::checkServer(Object(OC\SystemConfig))
#10 /var/www/html/console.php(99): OC\Console\Application->loadCommands(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#11 /var/www/html/occ(11): require_once('/var/www/html/c...')
#12 {main}

Next OC\DB\Exceptions\DbalException: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  password authentication failed for user "oc_john-smith" in /var/www/html/lib/private/DB/Exceptions/DbalException.php:71
Stack trace:
#0 /var/www/html/lib/private/DB/ConnectionAdapter.php(59): OC\DB\Exceptions\DbalException::wrap(Object(Doctrine\DBAL\Exception))
#1 /var/www/html/lib/private/legacy/OC_DB.php(55): OC\DB\ConnectionAdapter->prepare('SHOW SERVER_VER...', NULL, NULL)
#2 /var/www/html/lib/private/legacy/OC_DB.php(128): OC_DB::prepare('SHOW SERVER_VER...', NULL, NULL)
#3 /var/www/html/lib/private/legacy/OC_Util.php(753): OC_DB::executeAudited(Array)
#4 /var/www/html/lib/private/legacy/OC_Util.php(733): OC_Util::checkDatabaseVersion()
#5 /var/www/html/lib/private/Console/Application.php(159): OC_Util::checkServer(Object(OC\SystemConfig))
#6 /var/www/html/console.php(99): OC\Console\Application->loadCommands(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#7 /var/www/html/occ(11): require_once('/var/www/html/c...')
#8 {main}

Nextcloud Signing status

No response

Nextcloud Logs

{"reqId":"q2OrFWlsk3ke3gbj8ck8","level":2,"time":"2023-03-07T23:05:42+00:00","remoteAddr":"192.168.122.1","user":"--","app":"no app in context","method":"GET","url":"/","message":"Could not detect any host in http:///data/htaccesstest.txt","userAgent":"Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0","version":"","data":[]}
{"reqId":"q2OrFWlsk3ke3gbj8ck8","level":2,"time":"2023-03-07T23:05:42+00:00","remoteAddr":"192.168.122.1","user":"--","app":"no app in context","method":"GET","url":"/","message":"Could not detect any host in https:///data/htaccesstest.txt","userAgent":"Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0","version":"","data":[]}
{"reqId":"Paqg1gO16SmUaJuaIYA1","level":2,"time":"2023-03-07T23:06:17+00:00","remoteAddr":"192.168.122.1","user":"--","app":"no app in context","method":"POST","url":"/index.php","message":"Could not detect any host in http:///data/htaccesstest.txt","userAgent":"Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0","version":"","data":[]}
{"reqId":"Paqg1gO16SmUaJuaIYA1","level":2,"time":"2023-03-07T23:06:17+00:00","remoteAddr":"192.168.122.1","user":"--","app":"no app in context","method":"POST","url":"/index.php","message":"Could not detect any host in https:///data/htaccesstest.txt","userAgent":"Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0","version":"","data":[]}
{"reqId":"Paqg1gO16SmUaJuaIYA1","level":2,"time":"2023-03-07T23:06:17+00:00","remoteAddr":"192.168.122.1","user":"--","app":"no app in context","method":"POST","url":"/index.php","message":"Error trying to connect as \"postgres\", assuming database is setup and tables need to be created","userAgent":"Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0","version":"25.0.4.1","exception":{"Exception":"Doctrine\\DBAL\\Exception\\SyntaxErrorException","Message":"An exception occurred while executing a query: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near \"-\"\nLINE 1: CREATE USER oc_john-smith CREATEDB PASSWORD '3o7ZpltFWzOpggv...\n                           ^","Code":7,"Trace":[{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php","line":1780,"function":"convert","class":"Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter","type":"->","args":[["Doctrine\\DBAL\\Driver\\PDO\\Exception"],["Doctrine\\DBAL\\Query"]]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Connection.php","line":1719,"function":"handleDriverException","class":"Doctrine\\DBAL\\Connection","type":"->","args":[["Doctrine\\DBAL\\Driver\\PDO\\Exception"],["Doctrine\\DBAL\\Query"]]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Statement.php","line":193,"function":"convertExceptionDuringQuery","class":"Doctrine\\DBAL\\Connection","type":"->","args":[["Doctrine\\DBAL\\Driver\\PDO\\Exception"],"CREATE USER oc_john-smith CREATEDB PASSWORD '3o7ZpltFWzOpggvZzPnuauA6mZINW7'",[],[]]},{"file":"/var/www/html/lib/private/Setup/PostgreSQL.php","line":174,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"/var/www/html/lib/private/Setup/PostgreSQL.php","line":73,"function":"createDBUser","
class":"OC\\Setup\\PostgreSQL","type":"->","args":[["OC\\DB\\Connection"]]},{"file":"/var/www/html/lib/private/Setup.php","line":353,"function":"setupDatabase","class":"OC\\Setup\\PostgreSQL","type":"->","args":["john-smith"]},{"file":"/var/www/html/core/Controller/SetupController.php","line":69,"function":"install","class":"OC\\Setup","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/lib/base.php","line":980,"function":"run","class":"OC\\Core\\Controller\\SetupController","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/index.php","line":36,"function":"handleRequest","class":"OC","type":"::","args":[]}],"File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php","Line":58,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDO\\Exception","Message":"SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near \"-\"\nLINE 1: CREATE USER oc_john-smith CREATEDB PASSWORD '3o7ZpltFWzOpggv...\n                           ^","Code":7,"Trace":[{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","line":94,"function":"new","class":"Doctrine\\DBAL\\Driver\\PDO\\Exception","type":"::","args":[["PDOException",["42601",7,"ERROR:  syntax error at or near \"-\"\nLINE 1: CREATE USER oc_john-smith CREATEDB PASSWORD '3o7ZpltFWzOpggv...\n                           ^"]]]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Statement.php","line":189,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDO\\Statement","type":"->","args":[null]},{"file":"/var/www/html/lib/private/Setup/PostgreSQL.php","line":174,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"/var/www/html/lib/private/Setup/PostgreSQL.php","line":73,"function":"createDBUser","class":"OC\\Setup\\PostgreSQL","type":"->","args":[["OC\\DB\\Connection"]]},{"file":"/var/www/html/lib/private/Setup.php","line":353,"function":"setupDatabase","class":"OC\\Setup\\PostgreSQL","type":"->","args":["john-smith"]},{"file":"/var/www/html/core/Controller/SetupController.php","line":69,"function":"install","class":"OC\\Setup","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/lib/base.php","line":980,"function":"run","class":"OC\\Core\\Controller\\SetupController","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/index.php","line":36,"function":"handleRequest","class":"OC","type":"::","args":[]}],"File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php","Line":26,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near \"-\"\nLINE 1: CREATE USER oc_john-smith CREATEDB PASSWORD '3o7ZpltFWzOpggv...\n                           ^","Code":"42601","Trace":[{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","line":92,"function":"execute","class":"PDOStatement","type":"->","args":[null]},{"file":"/var/www/html/3rdparty/doctrine/dbal/src/Statement.php","line":189,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDO\\Statement","type":"->","args":[null]},{"file":"/var/www/html/lib/private/Setup/PostgreSQL.php","line":174,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"/var/www/html/lib/private/Setup/PostgreSQL.php","line":73,"function":"createDBUser","class":"OC\\Setup\\PostgreSQL","type":"->","args":[["OC\\DB\\Connection"]]},{"file":"/var/www/html/lib/private/Setup.php","line":353,"function":"setupDatabase","class":"OC\\Setup\\PostgreSQL","type":"->","args":["john-smith"]},{"file":"/var/www/html/core/Controller/SetupController.php","line":69,"function":"install","class":"OC\\Setup","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/lib/base.php","line":980,"function":"run","class":"OC\\Core\\Controller\\SetupController","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/index.php","line":36,"function":"handleRequest","class":"OC","type":"::","args":[]}],"File":"/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php","Line":92}},"message":"Error trying to connect as \"postgres\", assuming database is setup and tables need to be created","exception":{},"CustomMessage":"Error trying to connect as \"postgres\", assuming database is setup and tables need to be created"}}
{"reqId":"Paqg1gO16SmUaJuaIYA1","level":3,"time":"2023-03-07T23:06:17+00:00","remoteAddr":"192.168.122.1","user":"--","app":"no app in context","method":"POST","url":"/index.php","message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  password authentication failed for user \"oc_john-smith\"","userAgent":"Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0","version":"25.0.4.1","exception":{"Exception":"Doctrine\\DBAL\\Exception","Message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  password authentication failed for user \"oc_john-smith\"","Code":7,"Trace":[{"file":"/var/www/html/lib/private/Setup/PostgreSQL.php","line":110,"function":"connect","class":"OC\\DB\\Connection","type":"->","args":[]},{"file":"/var/www/html/lib/private/Setup.php","line":353,"function":"setupDatabase","class":"OC\\Setup\\PostgreSQL","type":"->","args":["john-smith"]},{"file":"/var/www/html/core/Controller/SetupController.php","line":69,"function":"install","class":"OC\\Setup","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/lib/base.php","line":980,"function":"run","class":"OC\\Core\\Controller\\SetupController","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/index.php","line":36,"function":"handleRequest","class":"OC","type":"::","args":[]}],"File":"/var/www/html/lib/private/DB/Connection.php","Line":139,"message":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  password authentication failed for user \"oc_john-smith\"","exception":{},"CustomMessage":"Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL:  password authentication failed for user \"oc_john-smith\""}}

Additional info

docker logs:

docker logs nextcloud

Initializing nextcloud 25.0.4.1 ...
New nextcloud instance
Initializing finished
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 172.18.0.3. Set the 'ServerName' directive globally to suppress this message
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 172.18.0.3. Set the 'ServerName' directive globally to suppress this message
[Tue Mar 07 23:05:19.936337 2023] [mpm_prefork:notice] [pid 1] AH00163: Apache/2.4.54 (Debian) PHP/8.1.16 configured -- resuming normal operations
[Tue Mar 07 23:05:19.936419 2023] [core:notice] [pid 1] AH00094: Command line: 'apache2 -D FOREGROUND'
192.168.122.1 - - [07/Mar/2023:23:05:42 +0000] "GET / HTTP/1.1" 200 3905 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0"
192.168.122.1 - - [07/Mar/2023:23:06:17 +0000] "POST /index.php HTTP/1.1" 200 3559 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/110.0"

docker logs postgres

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
sh: locale: not found
2023-03-07 23:05:14.016 UTC [30] WARNING:  no usable system locales were found
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2023-03-07 23:05:15.011 UTC [36] LOG:  starting PostgreSQL 15.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit
2023-03-07 23:05:15.015 UTC [36] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-03-07 23:05:15.026 UTC [39] LOG:  database system was shut down at 2023-03-07 23:05:14 UTC
2023-03-07 23:05:15.033 UTC [36] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

2023-03-07 23:05:15.194 UTC [36] LOG:  received fast shutdown request
waiting for server to shut down....2023-03-07 23:05:15.201 UTC [36] LOG:  aborting any active transactions
2023-03-07 23:05:15.205 UTC [36] LOG:  background worker "logical replication launcher" (PID 42) exited with exit code 1
2023-03-07 23:05:15.205 UTC [37] LOG:  shutting down
2023-03-07 23:05:15.210 UTC [37] LOG:  checkpoint starting: shutdown immediate
2023-03-07 23:05:15.302 UTC [37] LOG:  checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.032 s, sync=0.042 s, total=0.098 s; sync files=250, longest=0.022 s, average=0.001 s; distance=4222 kB, estimate=4222 kB
2023-03-07 23:05:15.310 UTC [36] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2023-03-07 23:05:15.443 UTC [1] LOG:  starting PostgreSQL 15.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit
2023-03-07 23:05:15.443 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-03-07 23:05:15.443 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-03-07 23:05:15.451 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-03-07 23:05:15.462 UTC [52] LOG:  database system was shut down at 2023-03-07 23:05:15 UTC
2023-03-07 23:05:15.474 UTC [1] LOG:  database system is ready to accept connections
2023-03-07 23:06:17.365 UTC [58] ERROR:  syntax error at or near "-" at character 20
2023-03-07 23:06:17.365 UTC [58] STATEMENT:  CREATE USER oc_john-smith CREATEDB PASSWORD '3o7ZpltFWzOpggvZzPnuauA6mZINW7'
2023-03-07 23:06:17.379 UTC [59] FATAL:  password authentication failed for user "oc_john-smith"
2023-03-07 23:06:17.379 UTC [59] DETAIL:  Role "oc_john-smith" does not exist.
	Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

[szaimen]

Hi, can you check if it works with postgres:14-alpine?

[Bai-Chiang]

No, it doesn't work with postgres:14-alpine
similar error message

postgres   | 2023-03-07 23:22:23.426 UTC [58] ERROR:  syntax error at or near "-" at character 20
postgres   | 2023-03-07 23:22:23.426 UTC [58] STATEMENT:  CREATE USER oc_john-smith CREATEDB PASSWORD 'eK2APhhVjfKEvnj9zQ3XyhgvsxDW2P'
postgres   | 2023-03-07 23:22:23.443 UTC [59] FATAL:  password authentication failed for user "oc_john-smith"
postgres   | 2023-03-07 23:22:23.443 UTC [59] DETAIL:  Role "oc_john-smith" does not exist.
postgres   | 	Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

[szaimen]

Hi, can you check if #37116 fixes the issue for you? Thanks a lot!

[Bai-Chiang]

Sorry I don't know php or database. I can't tell #37116 fixed this issue or not. I could test if there is an docker image release.

[szaimen]

Okay