I have a GeoServer (2.17.2) Store that connects to a Postgres DB (10.10). We use the database authorization settings per https://docs.geoserver.org/latest/en/user/data/database/sqlsession.html
SET SESSION AUTHORIZATION ${GSUSER, geoserver}
RESET SESSION AUTHORIZATION
However, if a user logs in to the GeoServer admin webpage or makes requests with a user that’s not a Role in our Postgres DB, we’ll can end up with a ton of postgres SET SESSION AUTHORIZATION idle connections which often puts us up against our Postgres connection limit (even though our Store’s max connections is 10).
What’s the best way to handle this scenario and avoid having so many idle SET SESSION AUTHORIZATION connections?
Below is a section of logging from the SQL query:
SELECT
pid
,datname
,usename
,application_name
,client_hostname
,client_port
,backend_start
,query_start
,query
,state
FROM pg_stat_activity;
9557 | dpddb | postgres | PostgreSQL JDBC Driver | | 47186 | 2023-11-12 02:55:27.283684+00 | 2023-11-12 02:55:27.312986+00 | SET SESSION AUTHORIZATION admin | idle
9559 | dpddb | postgres | PostgreSQL JDBC Driver | | 47190 | 2023-11-12 02:55:27.32088+00 | 2023-11-12 02:55:27.353287+00 | SET SESSION AUTHORIZATION admin | idle
40167 | dpddb | dpd | PostgreSQL JDBC Driver | | 47038 | 2023-11-10 21:28:34.628205+00 | 2023-11-11 01:37:10.846282+00 | COMMIT | idle
9561 | dpddb | postgres | PostgreSQL JDBC Driver | | 47194 | 2023-11-12 02:55:27.363625+00 | 2023-11-12 02:55:27.39148+00 | SET SESSION AUTHORIZATION admin | idle
9563 | dpddb | postgres | PostgreSQL JDBC Driver | | 47198 | 2023-11-12 02:55:27.399799+00 | 2023-11-12 02:55:27.431584+00 | SET SESSION AUTHORIZATION admin | idle
9565 | dpddb | postgres | PostgreSQL JDBC Driver | | 47202 | 2023-11-12 02:55:27.441868+00 | 2023-11-12 02:55:27.490883+00 | SET SESSION AUTHORIZATION admin | idle
9567 | dpddb | postgres | PostgreSQL JDBC Driver | | 47206 | 2023-11-12 02:55:27.499114+00 | 2023-11-12 02:55:27.530898+00 | SET SESSION AUTHORIZATION admin | idle
9569 | dpddb | postgres | PostgreSQL JDBC Driver | | 47210 | 2023-11-12 02:55:27.540316+00 | 2023-11-12 02:55:27.572156+00 | SET SESSION AUTHORIZATION admin | idle
9571 | dpddb | postgres | PostgreSQL JDBC Driver | | 47214 | 2023-11-12 02:55:27.581341+00 | 2023-11-12 02:55:27.611681+00 | SET SESSION AUTHORIZATION admin | idle
9573 | dpddb | postgres | PostgreSQL JDBC Driver | | 47218 | 2023-11-12 02:55:27.622521+00 | 2023-11-12 02:55:27.654071+00 | SET SESSION AUTHORIZATION admin | idle
9575 | dpddb | postgres | PostgreSQL JDBC Driver | | 47222 | 2023-11-12 02:55:27.663294+00 | 2023-11-12 02:55:27.701135+00 | SET SESSION AUTHORIZATION admin | idle
9577 | dpddb | postgres | PostgreSQL JDBC Driver | | 47226 | 2023-11-12 02:55:27.712217+00 | 2023-11-12 02:55:27.740405+00 | SET SESSION AUTHORIZATION admin | idle
9579 | dpddb | postgres | PostgreSQL JDBC Driver | | 47230 | 2023-11-12 02:55:27.74965+00 | 2023-11-12 02:55:27.788457+00 | SET SESSION AUTHORIZATION admin | idle
9581 | dpddb | postgres | PostgreSQL JDBC Driver | | 47234 | 2023-11-12 02:55:27.799541+00 | 2023-11-12 02:55:27.831232+00 | SET SESSION AUTHORIZATION admin | idle
9583 | dpddb | postgres | PostgreSQL JDBC Driver | | 47238 | 2023-11-12 02:55:27.840926+00 | 2023-11-12 02:55:27.873867+00 | SET SESSION AUTHORIZATION admin | idle
9585 | dpddb | postgres | PostgreSQL JDBC Driver | | 47242 | 2023-11-12 02:55:34.451948+00 | 2023-11-12 02:55:34.482225+00 | SET SESSION AUTHORIZATION admin | idle
9587 | dpddb | postgres | PostgreSQL JDBC Driver | | 47246 | 2023-11-12 02:55:34.491193+00 | 2023-11-12 02:55:34.519067+00 | SET SESSION AUTHORIZATION admin | idle
9589 | dpddb | postgres | PostgreSQL JDBC Driver | | 47250 | 2023-11-12 02:55:35.029405+00 | 2023-11-12 02:55:35.057332+00 | SET SESSION AUTHORIZATION admin | idle
9591 | dpddb | postgres | PostgreSQL JDBC Driver | | 47254 | 2023-11-12 02:55:35.065128+00 | 2023-11-12 02:55:35.091207+00 | SET SESSION AUTHORIZATION admin | idle
9593 | dpddb | postgres | PostgreSQL JDBC Driver | | 47258 | 2023-11-12 02:55:35.760041+00 | 2023-11-12 02:55:35.788074+00 | SET SESSION AUTHORIZATION admin | idle
9595 | dpddb | postgres | PostgreSQL JDBC Driver | | 47262 | 2023-11-12 02:55:35.795851+00 | 2023-11-12 02:55:35.822487+00 | SET SESSION AUTHORIZATION admin | idle
9597 | dpddb | postgres | PostgreSQL JDBC Driver | | 47266 | 2023-11-12 02:55:36.451746+00 | 2023-11-12 02:55:36.480868+00 | SET SESSION AUTHORIZATION admin | idle
9599 | dpddb | postgres | PostgreSQL JDBC Driver | | 47270 | 2023-11-12 02:55:36.488368+00 | 2023-11-12 02:55:36.516611+00 | SET SESSION AUTHORIZATION admin | idle
9602 | dpddb | postgres | PostgreSQL JDBC Driver | | 47274 | 2023-11-12 02:55:37.242311+00 | 2023-11-12 02:55:37.271173+00 | SET SESSION AUTHORIZATION admin | idle
9604 | dpddb | postgres | PostgreSQL JDBC Driver | | 47278 | 2023-11-12 02:55:37.278805+00 | 2023-11-12 02:55:37.30821+00 | SET SESSION AUTHORIZATION admin | idle
9606 | dpddb | postgres | PostgreSQL JDBC Driver | | 47282 | 2023-11-12 02:55:38.082236+00 | 2023-11-12 02:55:38.114275+00 | SET SESSION AUTHORIZATION admin | idle
9608 | dpddb | postgres | PostgreSQL JDBC Driver | | 47286 | 2023-11-12 02:55:38.12218+00 | 2023-11-12 02:55:38.15626+00 | SET SESSION AUTHORIZATION admin | idle
9610 | dpddb | postgres | PostgreSQL JDBC Driver | | 47290 | 2023-11-12 02:55:38.876842+00 | 2023-11-12 02:55:38.90645+00 | SET SESSION AUTHORIZATION admin | idle
9612 | dpddb | postgres | PostgreSQL JDBC Driver | | 47294 | 2023-11-12 02:55:38.916825+00 | 2023-11-12 02:55:38.94558+00 | SET SESSION AUTHORIZATION admin | idle
15 | | | | | | 2023-10-10 23:45:20.721797+00 | | |
14 | | | | | | 2023-10-10 23:45:20.721359+00 | | |
16 | | | | | | 2023-10-10 23:45:20.721959+00 | | |
(48 rows)