2015-12-20 19:08 GMT+01:00 Dmitry Igrishin <dmit...@gmail.com>: > > > 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. >
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) >> >> >> >> >>> >>> [2] http://www.postgresql.org/docs/9.4/static/sql-set-role.html >>> >>> -- >>> // Dmitry. >>> >>> >> > > > -- > // Dmitry. > >
