>> You lass in the $USER to you client software where it executes a post-connect hook SQL script populating a temp table with that value, usually via a function.
A "post-connect hook SQF script" ? My (limited) understanding of this is that once you connect, you're on the server and everything on the client side (like $USER) is no longer in play. But a "post-connect hook SQF script" sounds like something you would run unconditionally after the connect which would then create/populate the temp table. The problem is that I need to do this outside of an app which could run something like that. Users will connect to the DB and then update a table using SQL at the prompt. And I want a post update trigger to identify who (linux user on the client side) just made that change. I was sort of hoping that this 8 character string (the linux user id) could be passed from client -> server as a parameter that is set on the user/client side, perhaps using that "-v" option, which could somehow be passed along to the server. But from what you said earlier, that only exists on the client side. Is there any mechanism for a client connect request to pass a variable like this to the server which it then could read on the server side? On Mon, Aug 17, 2020 at 5:53 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, August 17, 2020, David Gauthier <davegauthie...@gmail.com> > wrote: > >> OK, trying to piece together something that might work but I don't see >> the pieces falling into place. >> From the link you provided... >> >> "The most fundamental way to set these parameters is to edit the file >> postgresql.conf" >> So I'm fine with asking our IT guys to stick some lines in there for us. >> But will the thing that executes the file understand what $USER is ? Will >> this work... "osuser = $USER" >> > > This does not seem like something you’d setup at the server configuration > level...and no, writing osuser=$USER is going to be unintelligible to the > server. > > >> >> I tried this sort of thing through $PGOPTIONS... >> setenv PGOPTIONS "-c 'osuser=$USER'" >> But when I go to connect... >> psql: FATAL: unrecognized configuration parameter "'osuser" >> > >> I can avoid the error by just throwing a namespace in there... >> > > Expected > > >> atletx7-reg036:/home/dgauthie[ 214 ] --> setenv PGOPTIONS "-c >> 'os.user=$USER' " >> But once in, "show os.user" is undefined. >> > > Not sure, though maybe start with constants for values to isolate the > where info is being lost. I’m not absolutely positive that PGOPTIONS will > even work here and even if it does that method has some limitations if you > want to use things like connection poolers. It is, however, the existing > ideal way to accomplish the goal of having the connection pre-establish a > server GUC at startup without having to deal with SQL. > > >> >> I'm fine with a temp table approach, but don't really know where/how to >> create it in terms of pg sys files, init scripts or env vars like >> PGOPTIONS. >> > > You would interact with it using pure SQL. The how/where depends heavily > on your environment. You lass in the $USER to you client software where it > executes a post-connect hook SQL script populating a temp table with that > value, usually via a function. > > David J. > >
