2015-12-20 21:00 GMT+03:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2015-12-20 18:56 GMT+01:00 Dmitry Igrishin <dmit...@gmail.com>: > >> >> >> 2015-12-20 19:44 GMT+03:00 Pavel Stehule <pavel.steh...@gmail.com>: >> >>> >>> >>> 2015-12-20 17:30 GMT+01:00 Dmitry Igrishin <dmit...@gmail.com>: >>> >>>> 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'; SET SESSION AUTHORIZATION notsuperuser2 > SELECT current_user, session_user; notsuperuser2 notsuperuser2
I don't see any security issue here. > 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) > > > > >> >> [2] http://www.postgresql.org/docs/9.4/static/sql-set-role.html >> >> -- >> // Dmitry. >> >> > -- // Dmitry.
