Message-ID: <3B371F9F.956DF8B0@imsd.uni-mainz.de>
Date: Mon, 25 Jun 2001 13:25:19 +0200
From: Markus Wagner <wagner@imsd.uni-mainz.de>
Organization: IMSD
MIME-Version: 1.0
To: Cedar Cox <cedarc@visionforisrael.com>
CC: pgsql-interfaces@postgresql.org
Subject: Re: passing Windows NT user name to postgres triggers for logging
References: 
 <Pine.LNX.4.21.0106241230440.10751-100000@nanu.visionforisrael.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi Cedar,

thank you very much for your tip! It seems to be very elegant.

Doesn't this mean that pg distinguishes between the connections? I am
still wondering if I could use this distinction. Then I would let every
user let execute a function "LOGIN" at app startup. This function would
make one entry in a global table "sessions", inserting <username> and
<connection id>. The log function would then lookup the user name with
the matching connection.

I will try your suggestion first. But I think it will be somewhat
tricky.

Markus

Cedar Cox wrote:
> 
> One thought comes to mind.  First you must be able to get the NT username
> somehow and put it in a temporary table in PG.  I suggest doing this at
> connect time.  Second, write your functions/triggers such that they will
> grab the username from the temporary table and use it for logging.
> Temporary tables are visible only to the connection that created them so
> you can have many connections that create temp tables with the same table
> name.
> 
> Thoughts?
> 
> -Cedar
> 
> On Thu, 21 Jun 2001, Markus Wagner wrote:
> 
> > Hi,
> >
> > we are currently using the following configuration:
> > There is a postgresql database running on linux. User applications are
> > running on Windows NT, using Access as a frontend. The tables are
> > connected as static ODBC links with username and password in their DSN
> > definitions.
> >
> > This means: Many different users may connect to our application server
> > with different NT user accounts. But within the scope of the postgres
> > database there is only one user. This is the user the ODBC connections
> > are defined with.
> >
> > Our problem:
> >
> > When implementing logging functions/triggers in postgresql, we have only
> > the pg username (getpgusername()), but we need the NT user name of the
> > users that connect to our terminal server and open our access frontend.
> >
> > My questions:
> >
> > Is there any trick to get the NT user names passed through the
> > connection or SQL statement?
> >
> > For example, if we decide to let every user get its own postgres
> > account, there would still be the problem that one has to define one
> > single username when linking the ODBC data source into access.
> >
> > Are there any other solutions?
> >
> > We simple need to get the users name in our triggers.
> >
> > Thank you a lot,
> >
> > Markus
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly