Thread: distinguishing different database connections

Hi,

can I access information on the current connection from within a trigger
function?
I need to identify different server connections somehow. Something like
a "connection id" would be enough.

The background:

When a user starts a frontend application (NT, Access) he starts a new
connection to our Linux database server. But for all users the "postgres
user" is the same, since the ODBC connection is hard linked into the
application. The problem is that we need the NT user name within our
trigger (C) functions.

The idea:
Let's execute a function "logon" whenever an instance of the application
is started. This function will get the NT user name as a parameter. It
will recognize the current connection id and it would store the pair
(connection id, NT user name) in a table. Then, whenever some trigger
needs to know the user name (for logging actions), it could lookup the
user name with the current connection id.

Can we do this? Please help.

Thank you very much,

Markus

You can use backend's PID from a trigger, it is unique.

On Fri, 22 Jun 2001, Markus Wagner wrote:

> Hi,
> 
> can I access information on the current connection from within a trigger
> function?
> I need to identify different server connections somehow. Something like
> a "connection id" would be enough.
> 
> The background:
> 
> When a user starts a frontend application (NT, Access) he starts a new
> connection to our Linux database server. But for all users the "postgres
> user" is the same, since the ODBC connection is hard linked into the
> application. The problem is that we need the NT user name within our
> trigger (C) functions.
> 
> The idea:
> Let's execute a function "logon" whenever an instance of the application
> is started. This function will get the NT user name as a parameter. It
> will recognize the current connection id and it would store the pair
> (connection id, NT user name) in a table. Then, whenever some trigger
> needs to know the user name (for logging actions), it could lookup the
> user name with the current connection id.
> 
> Can we do this? Please help.
> 
> Thank you very much,
> 
> Markus
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 

-- 
--
Alex Pilosov            | http://www.acedsl.com/home.html
CTO - Acecape, Inc.     | AceDSL:The best ADSL in Bell Atlantic area
325 W 38 St. Suite 1005 | (Stealth Marketing Works! :)
New York, NY 10018      |

Hi Markus,

On Friday, 22. June 2001 09:59, Markus Wagner wrote:
> Hi,
>
> can I access information on the current connection from within a
> trigger function?
> I need to identify different server connections somehow. Something
> like a "connection id" would be enough.

I think, there is no such thing like a connection id.

> The background:
>
> When a user starts a frontend application (NT, Access) he starts a
> new connection to our Linux database server. But for all users the
> "postgres user" is the same, since the ODBC connection is hard linked
> into the application. The problem is that we need the NT user name
> within our trigger (C) functions.
>
> The idea:
> Let's execute a function "logon" whenever an instance of the
> application is started. This function will get the NT user name as a
> parameter. It will recognize the current connection id and it would
> store the pair (connection id, NT user name) in a table. Then,
> whenever some trigger needs to know the user name (for logging
> actions), it could lookup the user name with the current connection
> id.

What about looking how web applications handle this? A "session id", 
which you have to manage yourself, might do what you want:

<just_an_idea>
- logon creates a session id
- actions that need to keep track of users should be done within 
functions called by the client, providing the session id as a parameter.
</idea>

HTH.
Christof

How about creating a temporary table with the data?  That'll be persistant
for the session.

On Fri, 22 Jun 2001, Markus Wagner wrote:

> Hi,
> 
> can I access information on the current connection from within a trigger
> function?
> I need to identify different server connections somehow. Something like
> a "connection id" would be enough.
> 
> The background:
> 
> When a user starts a frontend application (NT, Access) he starts a new
> connection to our Linux database server. But for all users the "postgres
> user" is the same, since the ODBC connection is hard linked into the
> application. The problem is that we need the NT user name within our
> trigger (C) functions.
> 
> The idea:
> Let's execute a function "logon" whenever an instance of the application
> is started. This function will get the NT user name as a parameter. It
> will recognize the current connection id and it would store the pair
> (connection id, NT user name) in a table. Then, whenever some trigger
> needs to know the user name (for logging actions), it could lookup the
> user name with the current connection id.

Hi, Is postgresql database available for  WindowsNT platform.

manika

-------------------------------------------------------------------

From:-                          | Ms. Manika Dey.                |Ph.No:--Engineer-SC (Comp. Tech.)      | IPR -- 02712
-69276 I.P.R                          |          EXT 336,315BHAT, GANDHINAGAR              | Residence -- 079 -
6873114Gujrat-- 382 428              | FAX --- 69017 ------------------------------------------------------------------