Search Postgresql Archives

Re: Session Identifiers

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2015-12-20 19:08 GMT+01:00 Dmitry Igrishin <dmitigr@xxxxxxxxx>:


2015-12-20 21:00 GMT+03:00 Pavel Stehule <pavel.stehule@xxxxxxxxx>:


2015-12-20 18:56 GMT+01:00 Dmitry Igrishin <dmitigr@xxxxxxxxx>:


2015-12-20 19:44 GMT+03:00 Pavel Stehule <pavel.stehule@xxxxxxxxx>:


2015-12-20 17:30 GMT+01:00 Dmitry Igrishin <dmitigr@xxxxxxxxx>:
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)



 




--
// Dmitry.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux