Обсуждение: passing linux user to PG server as a variable ?

9.6.7 on linux

I need to insert the linux username of a user on the client side into a col using an insert statement.  I realize that the server knows nothing about who the linux user was on a client, but I was thinking that I might be able to pass that in somehow through a variable.

 

Looking at psql command line options, I see "-v" (lowercase) which is described as...

-v assignment
--set=assignment
--variable=assignment

Perform a variable assignment, like the \set meta-command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. To set a variable with an empty value, use the equal sign but leave off the value. These assignments are done during a very early stage of start-up, so variables reserved for internal purposes might get overwritten later.

So I tried that without success.  

"-v sysinfo.osuser=foo" 

failed the connect with...

"psql: could not set variable "sysinfo.osuser""

Next I tried...

"-v osuser=foo"

This didn't fail the connect, but once I got in...

"show osuser" gave... 

"ERROR:  unrecognized configuration parameter "osuser""

I don't even know if this approach has any legs or not given what I want to do.  I'm just trying ideas hoping something will work.  But if it might work, is this setting a variable like this something that can be run unconditionally whenever a linux user connects to the DB on a client server ?  If so, where would this be inserved in the connect process ? TO get the linux user, I would just tap $USER or backtick `whoami` or something like that.

Of course if there is another way to accomplish my goal, I'm all ears :-)

Thanks in advance for any replies/ideas !

How many users do you have to identify? Have them log in to your application and thence to PG then you can pick up the PG CURRENT_USER var?

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"

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...

atletx7-reg036:/home/dgauthie[ 214 ] --> setenv PGOPTIONS "-c 'os.user=$USER' "

But once in, "show os.user" is undefined.

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. 

On 2020/08/18 4:52, David Gauthier wrote:

> Thanks in advance for any replies/ideas !

You could hackily repurpose the "application_name" connection parameter:

   $ whoami
   ibarwick
   $ psql -d "host=localhost dbname=postgres user=postgres application_name=$USER"
   psql (14devel)
   Type "help" for help.

   postgres=# SELECT application_name FROM pg_stat_activity WHERE pid=pg_backend_pid();
    application_name
   ------------------
    ibarwick
   (1 row)


Disclaimer: I have not applied any thought to any possible pitfalls and side
effects resulting from this approach.


Regards

Ian Barwick

-- 
Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

Hi David,

> On 17. Aug, 2020, at 21:52, David Gauthier <davegauthierpg@gmail.com> wrote:
>
> 9.6.7 on linux

ok, 12.4 here, but anyway. Try this:

$ psql -v osuser=foo
psql (12.4)
Type "help" for help.

postgres=# \echo :osuser
foo

Cheers,
Paul

    David Gauthier wrote:

> I can avoid the error by just throwing a namespace in there...
> atletx7-reg036:/home/dgauthie[ 214 ] --> setenv PGOPTIONS "-c
> 'os.user=$USER' "
> But once in, "show os.user" is undefined.

It's documented to work [1], but you need to remove these single
quotes. For instance:

$ env PGOPTIONS="-c os.user=$USER" psql -d postgres
psql (12.4 (Debian 12.4-1.pgdg90+1))
Type "help" for help.

postgres=> show "os.user";
 os.user
---------
 daniel
(1 row)

If characters needed to be quoted in the value, backslash
should be used as the quote character. Since it's also
a quote character for the shell, two levels of quoting
are needed:

$ env PGOPTIONS="-c os.user=user\\ is\\ $USER" psql -d postgres
psql (12.4 (Debian 12.4-1.pgdg90+1))
Type "help" for help.

postgres=> show "os.user";
    os.user
----------------
 user is daniel
(1 row)


[1] https://www.postgresql.org/docs/current/config-setting.html


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite