ERROR: database "testdb" is being accessed by other users

[chuegel]

Hi,

the last PR added the pgbouncer auth_user function to the template1 db. I guess this introduced this issue but I'm not sure:

postgres=# create database testdb;
CREATE DATABASE
postgres=# CREATE USER testuser WITH PASSWORD 'test';
CREATE ROLE
postgres=# grant all privileges on database testdb to testuser;
GRANT
postgres=# \q
root@patroni2:~# psql -h 192.168.100.105 -p 5000 -U testuser -d testdb
Password for user testuser:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

testdb=>
testdb=>
testdb=> \q
root@patroni2:~# psql -h 192.168.100.105 -p 5000 -U postgres
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

postgres=# drop database testdb;
ERROR:  database "testdb" is being accessed by other users
DETAIL:  There are 2 other sessions using the database.

datid | datname |  pid   | leader_pid | usesysid |  usename  | application_name | client_addr | client_hostname | client_port |         backend_start         | xact_start |          query_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |       query_id       |    query    |  backend_type
-------+---------+--------+------------+----------+-----------+------------------+-------------+-----------------+-------------+-------------------------------+------------+-------------------------------+-------------------------------+-----------------+------------+-------+-------------+--------------+----------------------+-------------+----------------
 22152 | testdb  | 282562 |            |    16409 | pgbouncer |                  | 127.0.0.1   |                 |       12470 | 2023-07-09 07:47:02.641675+00 |            | 2023-07-09 07:47:04.869308+00 | 2023-07-09 07:47:04.869348+00 | Client          | ClientRead | idle  |             |              | -4199849316459484872 | DISCARD ALL | client backend
 22152 | testdb  | 282581 |            |    22153 | testuser  |                  | 127.0.0.1   |                 |       12482 | 2023-07-09 07:47:04.894712+00 |            |                               | 2023-07-09 07:47:04.899916+00 | Client          | ClientRead | idle  |             |              |                      |             | client backend
(2 rows)

A couple of minutes later the session of the testuser is closed but the pgbouncer remains idle:

datid | datname |  pid   | leader_pid | usesysid |  usename  | application_name | client_addr | client_hostname | client_port |        backend_start         | xact_start |          query_start          |         state_change          | wait_event_type | wait_event | state | backend_xid | backend_xmin |       query_id       |    query    |  backend_type
-------+---------+--------+------------+----------+-----------+------------------+-------------+-----------------+-------------+------------------------------+------------+-------------------------------+-------------------------------+-----------------+------------+-------+-------------+--------------+----------------------+-------------+----------------
 22152 | testdb  | 284286 |            |    16409 | pgbouncer |                  | 127.0.0.1   |                 |       59344 | 2023-07-09 07:50:09.49027+00 |            | 2023-07-09 07:50:14.553132+00 | 2023-07-09 07:50:14.553186+00 | Client          | ClientRead | idle  |             |              | -4199849316459484872 | DISCARD ALL | client backend
(1 row)

postgres=# drop database testdb;
ERROR:  database "testdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

[vitabaks]

Use drop database testdb with (force);