pgAdmin: cannot login

[luber]

I have deployed the cluster with pgAdmin UI:

  users:
    - name: postgres
    - name: admin
      options: "SUPERUSER" 
  userInterface:
    pgAdmin:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgadmin4:ubi8-4.30-0
      dataVolumeClaimSpec:
        storageClassName: "longhorn"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi

I was able to connect to Postgres service (NodePort) from an external tool with postgres and admin users successfully...
However, when I tried to connect from pgAdmin UI - I am getting error.

In the /var/lib/pgadmin/error_log file on the running pod I can see there is an issue with ACL:

[Thu May 05 15:39:02.530463 2022] [core:notice] [pid 24:tid 139635001445824] AH00094: Command line: '/usr/sbin/httpd -D FOREGROUND'
[Thu May 05 17:38:16.732745 2022] [wsgi:error] [pid 26:tid 139634699790080] WARNING: Failed to set ACL on the directory containing the configuration database:
[Thu May 05 17:38:16.732784 2022] [wsgi:error] [pid 26:tid 139634699790080]            [Errno 1] Operation not permitted: '/var/lib/pgadmin'
[Thu May 05 17:38:16.732790 2022] [wsgi:error] [pid 26:tid 139634699790080] 
[Thu May 05 17:38:16.732825 2022] [wsgi:error] [pid 26:tid 139634699790080] HINT   : You may need to manually set the permissions on
[Thu May 05 17:38:16.732829 2022] [wsgi:error] [pid 26:tid 139634699790080]          /var/lib/pgadmin to allow daemon to write to it.
[Thu May 05 17:38:16.732832 2022] [wsgi:error] [pid 26:tid 139634699790080] 
[Thu May 05 17:38:25.840229 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870] 2022-05-05 17:38:25,826: ERROR\tflask.app:\t404 Not Found: The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.
[Thu May 05 17:38:25.840631 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870] Traceback (most recent call last):
[Thu May 05 17:38:25.840668 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]   File "/usr/lib/python3.6/site-packages/pgadmin4-web/flask/app.py", line 1813, in full_dispatch_request
[Thu May 05 17:38:25.842789 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]     rv = self.dispatch_request()
[Thu May 05 17:38:25.842817 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]   File "/usr/lib/python3.6/site-packages/pgadmin4-web/flask/app.py", line 1791, in dispatch_request
[Thu May 05 17:38:25.842827 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]     self.raise_routing_exception(req)
[Thu May 05 17:38:25.842834 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]   File "/usr/lib/python3.6/site-packages/pgadmin4-web/flask/app.py", line 1774, in raise_routing_exception
[Thu May 05 17:38:25.842842 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]     raise request.routing_exception
[Thu May 05 17:38:25.842850 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]   File "/usr/lib/python3.6/site-packages/pgadmin4-web/flask/ctx.py", line 336, in match_request
[Thu May 05 17:38:25.842858 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]     self.url_adapter.match(return_rule=True)
[Thu May 05 17:38:25.842866 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]   File "/usr/lib/python3.6/site-packages/pgadmin4-web/werkzeug/routing.py", line 1799, in match
[Thu May 05 17:38:25.842874 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870]     raise NotFound()
[Thu May 05 17:38:25.842888 2022] [wsgi:error] [pid 26:tid 139634428282624] [remote 10.42.2.62:42870] werkzeug.exceptions.NotFound: 404 Not Found: The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.

Environment

• Platform: Kubernetes
• Platform Version: 1.21.8
• PGO Image Tag: ubi8-5.1.0-0
• Postgres Version 14
• Storage: longhorn

[luber]

Looks like the issue occurs in new versions of pgAdmin. Now it is running as a non-root pgadmin user (UID: 5050).
So pod security context must be modified:

     securityContext:
       runAsUser: 5050
       runAsGroup: 5050
       fsGroup: 5050
       fsGroupChangePolicy: "OnRootMismatch"

[khahv]

Hi @luber, please provide detail steps on how to modify the pod security context.
Thanks

[luber]

Hi @luber, please provide detail steps on how to modify the pod security context. Thanks

@makegamelike Unfortunately I have not found how to do that with PGO CRD, so I had to deploy it separately using official pgAdmin container image: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

[patrycju]

I've had the same issue. Root cause is not the permissions on /var/lib/pgadmin directory, if you change/add the user to the database, operator will sync it with pgadmin:

(on pgadmin pod)

bash-4.4$ sqlite3 /var/lib/pgadmin/pgadmin4.db 
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> select * from user;
1||||0|||internal
2|user@pgo|user@pgo|$pbkdf2-sha512$25000$EWKs9V6LcY7RGuPcO2cMAQ$iz0TXHjBZUY/qUWSi1nxttWgmT4Kem/hVhJTb5itW7BChPZUVfOiTdedxvlUa75Kx1D65xs9i0tSCGXrLW.89Q|1||any|internal
3|letmein@pgo|letmein@pgo|$pbkdf2-sha512$25000$9B5DaA0BIKQUgnDuHSOEkA$McMHZZvNbYcYfIwnvAuimCiG5IxXd4dJ6jRZO1QxH/7gX1ex.vv0Kv0azENyWdF1hMzVxWml86vNTRsrY8N0yQ|1||any|internal

The issue is with documentation as pgadmin users are created with @pgo suffix, e.g. when you create someuser user in the database, then login to pgadmin is someuser@pgo, and not as documentation indicates someuser.

[jmckulk]

pgAdmin4 requires that usernames are emails and checks for the something@something format. When syncing the users defined in the spec and the pgAdmin database we have to add a suffix. When you log in to pgAdmin you need to add the @pgo to the end of your username.

We'll get the docs updated to reflect this. Thanks for pointing it out.

[andrewlecuyer]

The PGO documentation has been updated to specify that @pgo is included as the suffix for all pgAdmin 4 user accounts created by PGO:

#3201

This change will therefore be included in the next PGO release.