Обсуждение: passing Windows NT user name to postgres triggers for logging
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
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 >
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