Thread: Extending Session / Logged User info

Dear PGSQL Masters!

I have a complicated question.

Is there any way to extend the session information (what visible in
pgadmin's "Server Status")?

I explain what I want to do:

We used alternative DB in a place and I need to port this.
In this DB we using different db users for every real users.
When the program logging to the server it input for username/pwd, and
log with it.
So if John XY logged in, the session is shown as "John XY", which is a
real user, and db user too.

This meaning that if we have 80 users, we need to create 80 db users.

It is very useful when you want to see who use the db, but very tiring
when you want to create or manage them (plus role, etc).

I thought that (if possible), we can avoid this mechanism, if Session
can store additional informations.

Then I need to create only ONE DB user, I have an aux table with
usernames/pwds, and if the logging was successful, I need only put
this real username into Session's additional "field".

In the view I can see all users as one, but with plus column I can see
the "real username".

Is PGSQL supports this kind of "Session information extending", or any
way to I can use one user with more real user - and to get this info
(the real username)?

Maybe the solution is a table with:
Session ID (PID???), UserName, Logged in

If PID is unique in all lifetime of the server, and I can get only the
last logging in of each PID-s.


Thanks for your help:
    dd

On Tue, 2012-01-31 at 04:16 -0800, durumdara wrote:
> Dear PGSQL Masters!
>
> I have a complicated question.
>
> Is there any way to extend the session information (what visible in
> pgadmin's "Server Status")?
>
> I explain what I want to do:
>
> We used alternative DB in a place and I need to port this.
> In this DB we using different db users for every real users.
> When the program logging to the server it input for username/pwd, and
> log with it.
> So if John XY logged in, the session is shown as "John XY", which is a
> real user, and db user too.
>
> This meaning that if we have 80 users, we need to create 80 db users.
>
> It is very useful when you want to see who use the db, but very tiring
> when you want to create or manage them (plus role, etc).
>

Creating 80 users is not a really big burden. Managing privileges for 80
users will be. So you need to create some roles, on which you'll
grant/revoke privileges, and you'll make each user member of one or more
of the roles.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org