Re: passing linux user to PG server as a variable ?

>> 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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Monday, August 17, 2020, David Gauthier <davegauthierpg(at)gmail(dot)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.
>
>