Обсуждение: Proper way to clean-up connection for reuse (`DISCARD ALL` and default role)
Proper way to clean-up connection for reuse (`DISCARD ALL` and default role)
От
Logan MAUZAIZE
Дата:
Hello!
We encountered an issue with connection pooling from .Net driver (https://github.com/npgsql/npgsql).
We encountered an issue with connection pooling from .Net driver (https://github.com/npgsql/npgsql).
A few weeks ago, in order to give same access privileges for multi-service access (service/API, https://sqitch.org/, satellite services, ...), we enabled default role (`ALTER ROLE <specific role> SET ROLE <shared role>`).
Feature has been validated with "scripting" (and then with a POC):
```sql
select current_user;
create table ...;
-- check table owner
```
However, when using .Net apps we figured out owner wasn't expected one (the connection role instead of default one). After investigating our (and driver) code base. We find out that .Net driver use `DISCARD ALL` statement in order to clean-up pooled connection.
While cleaning-up reused/pooled connection before they are offered back to the pool is expected in order to avoid side effects, it seems `DISCARD ALL` isn't currenty the proper way to achieve clean-up as it results in different behavior than a fresh connection.
Here's test statements to check behavior of: fresh connection, `DISCARD ALL`, `RESET ROLE`, `SET SESSION AUTHORIZATION DEFAULT` and `RESET ALL`:
```sql
select current_user;
-- result: default role
discard all; select current_user;
-- result: connection role
reset role; select current_user;
-- result: default role
set session authorization default; select current_user;
-- result: connection role
reset role; select current_user;
-- result: default role
reset all; select current_user;
-- result: default role
set session authorization default; select current_user;
-- result: connection role
reset all; select current_user;
-- result: connection role
-- result: default role
discard all; select current_user;
-- result: connection role
reset role; select current_user;
-- result: default role
set session authorization default; select current_user;
-- result: connection role
reset role; select current_user;
-- result: default role
reset all; select current_user;
-- result: default role
set session authorization default; select current_user;
-- result: connection role
reset all; select current_user;
-- result: connection role
```
Can you please:
1. clarify if `DISCARD ALL` behavior is the expected one or is it a bug?
1. If not a bug, specify proper way to clean-up connection for reuse?
Thank you very much for your help!
Note: discussion is coming from https://github.com/npgsql/npgsql/issues/5997
Logan MAUZAIZE <logan.mauzaize@gmail.com> writes:
> 1. clarify if `DISCARD ALL` behavior is the expected one or is it a bug?
DISCARD ALL is documented to do SET SESSION AUTHORIZATION DEFAULT,
and for me it does that, that is "session_authorization" reverts to
the login role, "role" reverts to "none", and as a consequence
current_user becomes the login role. I'm bemused by your claim
that "reset role" can cause current_user to become something
other than the current session_authorization ... maybe you are
doing something strange with setting "role" as an ALTER USER SET
or ALTER DATABASE SET property? Your example seems to omit
necessary setup.
(If you are doing that, you probably need to be running the latest
minor releases to see the same behavior I'm seeing; the changes for
CVE-2024-10978 affected some behavior that's related to this.)
One thing that is not well documented is that RESET ALL doesn't
touch either "session_authorization" or "role". I suppose the
reasoning is that those things are session properties specified
by the SQL standard. It's a little weird, but it's stood for
many years and I doubt we'd consider changing it now.
regards, tom lane
Re: Proper way to clean-up connection for reuse (`DISCARD ALL` and default role)
От
Logan MAUZAIZE
Дата:
Hi Tom,
Regarding "some" missing context as stated, only thing that has been done is assigning a default role to the login one with `ALTER ROLE <login> SET ROLE <default>`.
If it can help:
```sql
SELECT version();
-- PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit
```
Le lun. 20 janv. 2025 à 18:06, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Logan MAUZAIZE <logan.mauzaize@gmail.com> writes:
> 1. clarify if `DISCARD ALL` behavior is the expected one or is it a bug?
DISCARD ALL is documented to do SET SESSION AUTHORIZATION DEFAULT,
and for me it does that, that is "session_authorization" reverts to
the login role, "role" reverts to "none", and as a consequence
current_user becomes the login role. I'm bemused by your claim
that "reset role" can cause current_user to become something
other than the current session_authorization ... maybe you are
doing something strange with setting "role" as an ALTER USER SET
or ALTER DATABASE SET property? Your example seems to omit
necessary setup.
(If you are doing that, you probably need to be running the latest
minor releases to see the same behavior I'm seeing; the changes for
CVE-2024-10978 affected some behavior that's related to this.)
One thing that is not well documented is that RESET ALL doesn't
touch either "session_authorization" or "role". I suppose the
reasoning is that those things are session properties specified
by the SQL standard. It's a little weird, but it's stood for
many years and I doubt we'd consider changing it now.
regards, tom lane