AW: pgadmin hangs with "waiting for lock" after exception

Von: Khushboo Vashi <khushboo(dot)vashi(at)enterprisedb(dot)com>
Gesendet: Donnerstag, 30. März 2023 07:13
An: Rettstadt, Tobias <ext(dot)tobias(dot)rettstadt(at)nordlb(dot)de>
Cc: pgadmin-support(at)postgresql(dot)org; Al-Zoubi, Ahmad <ahmad(dot)al-zoubi(at)nordlb(dot)de>
Betreff: Re: pgadmin hangs with "waiting for lock" after exception

On Tue, Mar 28, 2023 at 6:21 PM Rettstadt, Tobias <ext(dot)tobias(dot)rettstadt(at)nordlb(dot)de<mailto:ext(dot)tobias(dot)rettstadt(at)nordlb(dot)de>> wrote:
Hi,

we are using pgadmin 6.20 with a PostgreSQL database for the user settings and Azure AD login via OIDC. We are running pgadmin in Kubernetes using the official docker image with the following environment variables:

- env:
- name: PGADMIN_DEFAULT_EMAIL
value: pgadmin4(at)pgadmin(dot)org<mailto:pgadmin4(at)pgadmin(dot)org>
- name: PGADMIN_DEFAULT_PASSWORD
valueFrom:
secretKeyRef:
key: adminPassword
name: pgadmin
- name: PGADMIN_LISTEN_PORT
value: "8080"
- name: OAUTH2_CLIENT_SECRET
valueFrom:
secretKeyRef:
key: clientSecret
name: pgadmin
- name: PGADMIN_CONFIG_OAUTH2_CONFIG
value: '[ { ''OAUTH2_NAME'': ''azure'', ''OAUTH2_DISPLAY_NAME'': ''Azure-AD'',
''OAUTH2_CLIENT_ID'': 'clientid'', ''OAUTH2_CLIENT_SECRET'':
''$(OAUTH2_CLIENT_SECRET)'', ''OAUTH2_TOKEN_URL'': ''https://login.microsoftonline.com/tenant/oauth2/v2.0/token'',
''OAUTH2_AUTHORIZATION_URL'': ''https://login.microsoftonline.com/tenant/oauth2/v2.0/authorize'',
''OAUTH2_SERVER_METADATA_URL'': ''https://login.microsoftonline.com/tenant/v2.0/.well-known/openid-configuration'',
''OAUTH2_API_BASE_URL'': ''https://graph.microsoft.com/v1.0/'', ''OAUTH2_USERINFO_ENDPOINT'':
''me'', ''OAUTH2_SCOPE'': ''User.Read openid email profile'', ''OAUTH2_USERNAME_CLAIM'':
None, ''OAUTH2_ICON'': None, ''OAUTH2_BUTTON_COLOR'': None } ]'
- name: DB_USER
value: pgadmin
- name: DB_PASSWORD
valueFrom:
secretKeyRef:
key: databasePassword
name: pgadmin
- name: DB_HOST
value: “1.2.3.4”
- name: DB_PORT
value: "5432"
- name: DB_NAME
value: pgadmin
- name: PGADMIN_CONFIG_CONFIG_DATABASE_URI
value: '''postgresql://$(DB_USER):$(DB_PASSWORD)@$(DB_HOST):$(DB_PORT)/$(DB_NAME)'''
- name: PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION
value: "False"
- name: PGADMIN_CONFIG_AUTHENTICATION_SOURCES
value: '[''oauth2'', ''internal'']'
- name: PGADMIN_CONFIG_MASTER_PASSWORD
value: "True"
- name: PGADMIN_CONFIG_LOG_FILE
value: '''/var/lib/pgadmin/logs/pgadmin.log'''
- name: PGADMIN_CONFIG_FILE_LOG_LEVEL
value: "20"
- name: PGADMIN_CONFIG_UPGRADE_CHECK_ENABLED
value: "False"

Approximately once a week pgadmin hangs when a user is trying to connect the database. Here is the relevant excerpt from pgadmin.log:

2023-03-27 16:35:59,619: INFO pgadmin: Waiting for a lock.

2023-03-27 16:35:59,619: INFO pgadmin: Acquired a lock.

2023-03-27 16:35:59,619: ERROR pgadmin: 'auth_source_manager'

Traceback (most recent call last):

File "/venv/lib/python3.10/site-packages/flask/app.py", line 1517, in full_dispatch_request

rv = self.dispatch_request()

File "/venv/lib/python3.10/site-packages/flask/app.py", line 1503, in dispatch_request

return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)

File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 75, in oauth_logout

logout_user()

File "/venv/lib/python3.10/site-packages/flask_security/utils.py", line 206, in logout_user

_logout_user()

File "/venv/lib/python3.10/site-packages/flask_login/utils.py", line 230, in logout_user

user_logged_out.send(current_app._get_current_object(), user=user)

File "/venv/lib/python3.10/site-packages/blinker/base.py", line 263, in send

return [(receiver, receiver(sender, **kwargs))

File "/venv/lib/python3.10/site-packages/blinker/base.py", line 263, in <listcomp>

return [(receiver, receiver(sender, **kwargs))

File "/pgadmin4/pgadmin/__init__.py", line 750, in current_user_cleanup

_driver.gc_own()

File "/pgadmin4/pgadmin/utils/driver/psycopg2/__init__.py", line 251, in gc_own

mgr.release()

File "/pgadmin4/pgadmin/utils/driver/psycopg2/server_manager.py", line 483, in release

conn.cancel_transaction(conn.conn_id[5:])

File "/pgadmin4/pgadmin/utils/driver/psycopg2/connection.py", line 1696, in cancel_transaction

with ConnectionLocker(self.manager.kerberos_conn):

File "/pgadmin4/pgadmin/utils/locker.py", line 36, in __enter__

if session['auth_source_manager']['current_source'] == \

KeyError: 'auth_source_manager'

2023-03-28 07:25:33,796: INFO pgadmin: Connection Request for server#2

2023-03-28 07:25:33,815: INFO pgadmin: Waiting for a lock.

It looks like the lock that was acquired on 2023-03-27 at 16:35 is not released because of the error. Then when a user tries to connect to a database the next day, pgadmin waits for the lock forever. In this case only a restart helps.
Any ideas what could cause this behaviour?
This happens only if the user session expired and, without refreshing the pgAdmin browser window, the user tries to connect to the database.
In this case, a browser window refresh would be good enough not to restart the pgAdmin server.

However, pgAdmin should handle this scenario as well. Please log this issue @ https://github.com/pgadmin-org/pgadmin4/issues.

Thanks,
Khushboo

Hi Khushboo,

thanks for your reply. If this problem occurs, no user is able to connect to the database anymore until I restart the Kubernetes pods. Users see the loading icon when trying connect to the server and get an empty error pop-up after approximately one minute. Perhaps it’s also worth mentioning that all users are connecting to the same PostgreSQL server. So it’s not an issue that can be resolved by just refreshing the browser window. I will create an issue for that as you suggested.

Regards,
Tobias
**************************************************************** Die in dieser E-Mail enthaltenen Informationen sind vertraulich. Diese E-Mail ist ausschliesslich fuer den Adressaten bestimmt und jeglicher Zugriff durch andere Personen ist nicht zulaessig. Falls Sie nicht einer der genannten Empfaenger sind, ist jede Veroeffentlichung, Vervielfaeltigung, Verteilung oder sonstige in diesem Zusammenhang stehende Handlung untersagt und unter Umstaenden ungesetzlich. Sollte diese Nachricht nicht fuer Sie bestimmt sein, so bitten wir Sie, den Absender unverzueglich zu informieren und die E-Mail zu loeschen. **************************************************************** The information contained in this e-mail is confidential. This e-mail is intended solely for the addressee(s) and may not be accessed by anyone else. If you are not a named recipient, any disclosure, copying, distribution or related action is prohibited and might be unlawful. If the e-mail is not intended for you, please notify the sender immediately and delete it. ****************************************************************