From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: SET SESSION AUTHORIZATION and log file entries
Date: Tue, 4 Jan 2011 10:12:46 +0200
User-Agent: KMail/1.9.7
References: <201012291606.41505.achill@matrix.gatewaynet.com>
In-Reply-To: <201012291606.41505.achill@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-7"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201101041012.47556.achill@matrix.gatewaynet.com>

=D3=F4=E9=F2 Wednesday 29 December 2010 16:06:41 =EF/=E7 Achilleas Mantzios=
 =DD=E3=F1=E1=F8=E5:
> Hello,
> i noticed that the user name as displayed in the postgresql log (specifie=
d by %u) in the log_line_prefix
> does not show the user name of the effective user (current_user)as set by=
 "SET SESSION AUTHORIZATION"=20
> but, instead, shows the initial user which first authenticated against th=
e said connection.
> I think it would be better to have this user reported in the log files. (=
or have another entry e.g. %U to denote that)

Also i noticed (in 9.0.2) that both SET SESSION AUTHORIZATION and SET ROLE =
do not reflect this change
in pg_stat_get_activity function, (and subsequently in the pg_stat_activity=
 view).

I can imagine the use of either SET SESSION AUTHORIZATION and SET ROLE to a=
chieve personalized
connections in a connection pool, without sucrificing any of the beneficial=
 principles of having
a pool of equivalent connections.

I think having each user in an application connecting with his/her own cred=
entials and authorizations
is a very good thing. Currently i am thinking of configuring smth like this=
 in the jboss connection pool.
Unfortunately the current software only allows for subpools for each user, =
making the reuse and sharing of
connections impossible, but even if jboss and the postgresql jdbc driver al=
lowed to do that
(via SET SESSION AUTHORIZATION or SET ROLE) then again the situation would =
not be complete
since the entries in the log and pg_stat_activity would still show the init=
ial user who opened the connection.
(except there are other ways to change the current_user which i am missing).
I think, the combination of the two worlds (having both a generic single su=
perpool of equivalent connections which
everybody could use, on one hand, and having the possibility of completetly=
 personalizing connections
acquired from the pool according to the user of the application, having its=
 name appearing in stats
and in log files as usesysid, on the other hand) is something both desirabl=
e and missing from=20
the current solutions available.

In the case of a more simple web environment, that might not be smth very i=
mportant,
but in business environment, having each user reported separately in the po=
stgresql side, i think,
would be very good to have.

> --=20
> Achilleas Mantzios
>=20


=2D-=20
Achilleas Mantzios