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)
_______________________________________________
Geoserver-users mailing list
Please make sure you read the following two resources before posting to this
list:
- Earning your support instead of buying it, but Ian Turton:
http://www.ianturton.com/talks/foss4g.html#/
- The GeoServer user list posting guidelines:
http://geoserver.org/comm/userlist-guidelines.html
If you want to request a feature or an improvement, also see this:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
