On Mon, Aug 17, 2020 at 5:46 PM David Gauthier <davegauthie...@gmail.com> wrote:
> >> 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? > The following link details what you can provide via libpq - which is what psql exposes: https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-PARAMKEYWORDS The item of note here is "options" - which can be set directly on the connection string or passed in from the environment via PGOPTIONS Note that in theory Customized Options can be passed this way: https://www.postgresql.org/docs/13/runtime-config-custom.html But as I've said this particular usage for customized options is something I am unfamiliar with and is possibly not workable if your description of the attempt is accurate. Otherwise yes, before handing an open session back to the caller you will want to run some SQL against that connection that sets up the environment in the way you desire. If this isn't something you can arrange then you should probably just give up on the idea of having the server be aware of unrelated O/S level identification provided out of band and just give each user their own login and then inspect current_user or session_user. David J.
