Postgresql-HA oc_admin pool_passwd issue

[joepa37]

I have this issue pool_passwd file does not contain an entry for "oc_admin" when creating an instance with postgresql-ha cluster

Next OC\DB\Exceptions\DbalException: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL: md5 authentication failed
Wed, Nov 2 2022 12:51:40 am | DETAIL: pool_passwd file does not contain an entry for "oc_admin" in /var/www/html/lib/private/DB/Exceptions/DbalException.php:71
Wed, Nov 2 2022 12:51:40 am | Stack trace:
Wed, Nov 2 2022 12:51:40 am | #0 /var/www/html/lib/private/DB/ConnectionAdapter.php(59): OC\DB\Exceptions\DbalException::wrap(Object(Doctrine\DBAL\Exception))
Wed, Nov 2 2022 12:51:40 am | #1 /var/www/html/lib/private/legacy/OC_DB.php(55): OC\DB\ConnectionAdapter->prepare('SHOW SERVER_VER...', NULL, NULL)
Wed, Nov 2 2022 12:51:40 am | #2 /var/www/html/lib/private/legacy/OC_DB.php(128): OC_DB::prepare('SHOW SERVER_VER...', NULL, NULL)
Wed, Nov 2 2022 12:51:40 am | #3 /var/www/html/lib/private/legacy/OC_Util.php(759): OC_DB::executeAudited(Array)
Wed, Nov 2 2022 12:51:40 am | #4 /var/www/html/lib/private/legacy/OC_Util.php(739): OC_Util::checkDatabaseVersion()
Wed, Nov 2 2022 12:51:40 am | #5 /var/www/html/lib/private/Console/Application.php(157): OC_Util::checkServer(Object(OC\SystemConfig))
Wed, Nov 2 2022 12:51:40 am | #6 /var/www/html/console.php(99): OC\Console\Application->loadCommands(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
Wed, Nov 2 2022 12:51:40 am | #7 /var/www/html/occ(11): require_once('/var/www/html/c...')
Wed, Nov 2 2022 12:51:40 am | #8 {main}Retrying install...
Wed, Nov 2 2022 12:51:51 am | An unhandled exception has been thrown:
Wed, Nov 2 2022 12:51:51 am | Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred in the driver: SQLSTATE[08006] [7] FATAL: md5 authentication failed
Wed, Nov 2 2022 12:51:51 am | DETAIL: pool_passwd file does not contain an entry for "oc_admin" in /var/www/html/lib/private/DB/Connection.php:139
Wed, Nov 2 2022 12:51:51 am | Stack trace:
Wed, Nov 2 2022 12:51:51 am | #0 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1519): OC\DB\Connection->connect()
Wed, Nov 2 2022 12:51:51 am | #1 /var/www/html/3rdparty/doctrine/dbal/src/Statement.php(73): Doctrine\DBAL\Connection->getWrappedConnection()
Wed, Nov 2 2022 12:51:51 am | #2 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1016): Doctrine\DBAL\Statement->__construct('SHOW SERVER_VER...', Object(OC\DB\Connection))
Wed, Nov 2 2022 12:51:51 am | #3 /var/www/html/lib/private/DB/Connection.php(238): Doctrine\DBAL\Connection->prepare('SHOW SERVER_VER...')
Wed, Nov 2 2022 12:51:51 am | #4 /var/www/html/lib/private/DB/ConnectionAdapter.php(56): OC\DB\Connection->prepare('SHOW SERVER_VER...', NULL, NULL)
Wed, Nov 2 2022 12:51:51 am | #5 /var/www/html/lib/private/legacy/OC_DB.php(55): OC\DB\ConnectionAdapter->prepare('SHOW SERVER_VER...', NULL, NULL)
Wed, Nov 2 2022 12:51:51 am | #6 /var/www/html/lib/private/legacy/OC_DB.php(128): OC_DB::prepare('SHOW SERVER_VER...', NULL, NULL)
Wed, Nov 2 2022 12:51:51 am | #7 /var/www/html/lib/private/legacy/OC_Util.php(759): OC_DB::executeAudited(Array)
Wed, Nov 2 2022 12:51:51 am | #8 /var/www/html/lib/private/legacy/OC_Util.php(739): OC_Util::checkDatabaseVersion()
Wed, Nov 2 2022 12:51:51 am | #9 /var/www/html/lib/private/Console/Application.php(157): OC_Util::checkServer(Object(OC\SystemConfig))
Wed, Nov 2 2022 12:51:51 am | #10 /var/www/html/console.php(99): OC\Console\Application->loadCommands(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
Wed, Nov 2 2022 12:51:51 am | #11 /var/www/html/occ(11): require_once('/var/www/html/c...')
Wed, Nov 2 2022 12:51:51 am | #12 {main}

Since pgpool requires to specify a list of users and passwords in the pool_passwd file; For some reason, the nextcloud installation process internally creates an oc_admin user (maybe password genered here), but pgpool won't recognize it until it is added to this pool_passwd file with the password the user was created with.

Everything seems to work fine with the mariadb-galera cluster; so this is an issue only for postgresql-ha cluster.

[jessebot]

I haven't tried spinning up nextcloud with an HA postgresql cluster yet, but could you post the database/postgresql sections of your values.yaml? This is my current postgres config in my values.yaml:

# Uses: https://github.com/bitnami/charts/tree/master/bitnami/postgresql
  postgresql:
    enabled: true
    global:
      postgresql:
        auth:
          username: nextcloud
          database: nextcloud
          existingSecret: nextcloud-pgsql-credentials
          secretKeys:
            userPasswordKey: nextcloudPassword
            adminPasswordKey: postgresPassword
    volumePermissions:
      enabled: true
    primary:
      podAnnotations:
        k8up.io/backupcommand: "sh -c 'PGDATABASE=\"$POSTGRES_DB\" PGUSER=\"$POSTGRES_USER\" PGPASSWORD=\"$POSTGRES_PASSWORD\" pg_dump --clean'"
        k8up.io/file-extension: .sql
      pgHbaConfiguration: |-
        local all all trust
        host all all 127.0.0.1/32 md5
        host all nextcloud 10.0.0.0/8 md5
      initdb:
        scripts:
          my_init_script.sql: |
            ALTER DATABASE nextcloud OWNER TO nextcloud;
            GRANT ALL PRIVILEGES ON DATABASE nextcloud TO nextcloud;
            GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO nextcloud;
      persistence:
        enabled: true
        # storageClass: "standard"
        existingClaim: nextcloud-postgresql

Perhaps we should include something in the init_script or an initContainer that updates this pool_passwd file? Open to suggestions and collaboration on this :)

[mak241265]

@joepa37 the problem is pgpool does not recognize oc_admin user and its password...please make sure to set customUsers which set the user and password of oc_admin in a file called pgpool_passwd

[2fst4u]

@joepa37 the problem is pgpool does not recognize oc_admin user and its password...please make sure to set customUsers which set the user and password of oc_admin in a file called pgpool_passwd

I'm running into this issue but I can't set "oc_ncadmin" (the name of my user it gives an error for) because of the underscore. How can you add the user if it isn't valid according to pgpool?