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

On Sat, Oct 29, 2022 at 8:20 PM Bryn Llewellyn <bryn(at)yugabyte(dot)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.

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

David J.