Can be totally different if you use some connection pooler like pgpool or pgbouncer - these applications can reuse Postgres server sessions for more user sessions.
BTW, AFAIK, it's not possible to change the session authentication information by
using SET SESSION AUTHORIZATION [1] if the current user is not a superuser.
But it would be very nice to have a feature to change the session authorization
of current user even without superuser's privilege by supplying a password of
the user specified in SET SESSION AUTHORIZATION. This feature allows
to use PostgreSQL's native privileges via connection pools -- i.e. without
needs to open a dedicated connection for authenticated user. Is it possible
to implement it?
there is a workaround with security definer function and SET role TO ?
No there isn't. According to [2] "SET ROLE cannot be used within SECURITY
DEFINER function". Furthermore, SET ROLE doesn't affects the session_user's
function result which can be used by a logic.
you want to modify result of session_user? It's looks like possible security issue to me.
I want to be able to change the session user without creating the new connection, like this
(pseudo REPL):
notsuperuser > SELECT current_user, session_user;
notsuperuser notsuperuser
notsuperuser > SET SESSION AUTHORIZATION notsuperuser2 PASSWORD 'password_of_notsuperuser2';
It needs a change in PGPROC - and maybe invalidation some memory structures. I don't know why it is limited to superuser only.
Pavel
postgres=# create role tom ; CREATE ROLE Time: 91.461 ms postgres=# select current_user; ┌──────────────┐ │ current_user │ ╞══════════════╡ │ pavel │ └──────────────┘ (1 row)
Time: 15.692 ms postgres=# set role tom; SET Time: 0.609 ms postgres=> select current_user; ┌──────────────┐ │ current_user │ ╞══════════════╡ │ tom │ └──────────────┘ (1 row)
