passing linux user to PG server as a variable ?

[David Gauthier]

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 !

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

[Tim Clarke]

On 17/08/2020 20:52, David Gauthier wrote:
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?


Tim Clarke



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

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

[David G. Johnston]

On Mon, Aug 17, 2020 at 12:53 PM David Gauthier 
wrote:

> 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""
>
The part of the description that says "like the \set meta-command" means
you need to read its description as well.  There, and through links
therein, you will learn that what you are creating is a variable within the
psql client itself, not on the server.  Usage of that client-side variable
is documented.  As long as you don't need the variable on the server, and
oftentimes you do not, then this feature will work just fine for you.

The SHOW SQL command (and other documented options[1]) is a server command
and inspects server variables.  If you really need to create one of those
in the current session it may be possible - though I believe you have to
use a namespace prefix (i.e., your sysinfo.osuser) to get the system to
recognize a user-created variable name.  There is some work on improving
things in this area.  Though worse case you can just stick the desired
value into a temporary table and maybe create some function wrappers to
modify/access it.

David J.

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

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

[David Gauthier]

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 Mon, Aug 17, 2020 at 4:07 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Aug 17, 2020 at 12:53 PM David Gauthier 
> wrote:
>
>> 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""
>>
> The part of the description that says "like the \set meta-command" means
> you need to read its description as well.  There, and through links
> therein, you will learn that what you are creating is a variable within the
> psql client itself, not on the server.  Usage of that client-side variable
> is documented.  As long as you don't need the variable on the server, and
> oftentimes you do not, then this feature will work just fine for you.
>
> The SHOW SQL command (and other documented options[1]) is a server command
> and inspects server variables.  If you really need to create one of those
> in the current session it may be possible - though I believe you have to
> use a namespace prefix (i.e., your sysinfo.osuser) to get the system to
> recognize a user-created variable name.  There is some work on improving
> things in this area.  Though worse case you can just stick the desired
> value into a temporary table and maybe create some function wrappers to
> modify/access it.
>
> David J.
>
> [1] https://www.postgresql.org/docs/devel/config-setting.html
>
>
>
>

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

[David G. Johnston]

On Monday, August 17, 2020, David Gauthier  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.

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

[David Gauthier]

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

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

[Ian Barwick]

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

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

[David G. Johnston]

On Mon, Aug 17, 2020 at 5:46 PM David Gauthier 
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.

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

[David G. Johnston]

On Mon, Aug 17, 2020 at 5:46 PM David Gauthier 
wrote:

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

If you are giving a user a direct connection to the DB so they can run SQL
they should have their own individual credentials.

Regardless, if you rely on runtime variables there is no way to prevent the
value of those variables from being changed by the user.

David J.

"Go" (lang) standard driver

[Edson Richter]

Is there any standard PostgreSQL driver for Go language?
Do members of this group could share experiences with Go + PostgreSQL 
development?

Thanks,

E.R.

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

[Paul Förster]

Hi David,

> On 17. Aug, 2020, at 21:52, David Gauthier  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

Index tuple deduplication limitations in pg13

[Matthias van de Meent]

Hi,

I was reading through the new features of PG13 (beta), and noticed
that deduplication is disabled for float(4, 8) and numeric (and jsonb,
...) due to that the datums of those types could be not binary equal,
but equal according for the opclass used.

But, if the ordering of operator-class equal tuples is already
system-defined, could the physical ordering of index tuples in a btree
(with deduplication enabled for "unsafe" opclasses) be updated from
[index_columns, tid] to [index_columns,
image_compare(non_datum_equal_columns), tid], giving a stable sorting
of opclass-equal and image-equal values and enabling safe consistent
deduplication?

This need not be a default-on feature (opt-in would be fine, as I
guess it could have a performance penalty), but having the ability to
use btree index tuple deduplication for numeric, float and other data
types would count as a win in my book.

-Matthias