Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

On 10/29/22 20:38, David G. Johnston wrote:
> On Sat, Oct 29, 2022 at 8:20 PM Bryn Llewellyn <bryn@yugabyte.com 
> <mailto:bryn@yugabyte.com>> wrote:This is what I want:
> 
> 
>     1. I want to do this at the O/S prompt on the machine where my PG
>     cluster has been started: *"su mary"*.
> 
>     2. Then I want to start a session (I use "psql" here an an example)
>     like this: *"psql -d postgres"*.
> 
>     3. Then, at the "psql" prompt, I want *"select session_user"* to
>     show "bob".
> 
> 
> I seriously do not understand why in the world you want an 
> authentication system where you tell the server "my user name is mary" 
> and expect the server to then say "ok, but I'm going to log you in as 
> bob just for the fun of it".  The way I see it, when I tell the server 
> "my user name is mary", upon successful completion of the login I am 
> logged in as, wait for it........., mary.
> 
>     *Peer authentication failed for user "mary"*
> 
>     I assume that the phrase « user "mary" » means the O/S user "mary".
> 
> 
> No, the server is never going to give you an error message with your o/s 
> user name showing.  It is going to tell you "no, you may not login as 
> mary, because your name is bob and I have not been informed that you, 
> bob, are allowed to login as the user mary in this cluster".
> 
>     Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:
> 
>     *# MAPNAME    SYSTEM-USERNAME   PG-USERNAME*
>     *# -------    ---------------   -----------
>        bllewell   mary              mary
>     *
> 
> 
> As has been said numerous times, it is utterly pointless to define a 
> mapping like this - you get mary-is-mary for free just by saying peer.

+1
> 
>     **
>     So, "bob" is the simple case. And "mary" is one step harder. Now,
>     the unqualified "psql -d postgres" works again for "mary" (and it
>     still works, of course, for "bob").
> 
>     So far, so good. Now for the test, I mapped "mary" to "bob" in
>     "pg_ident.conf" thus:
> 
>     *# MAPNAME    SYSTEM-USERNAME   PG-USERNAME
>     # -------    ---------------   -----------
>        bllewell   mary              bob
>     *
> 
> 
> Now you are saying mary is allow to claim she is bob.  Which requires 
> the o/s user to be mary and her psql command line have "-U bob"
> 
> It is not possible to make an alias mapping work without specifying "-U" 
> on the psql command line.  Period.  The -U is precisely how you tell the 
> server you are using an alias - without it the server expects that the 
> o/s user is logging in using their own name as the requested login 
> role.  In that case either a peer entry for the user exists - and thus 
> authentication is successful - or it doesn't - and authentication will fail.

+1

> 
> David J.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com