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

> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
>> bryn(at)yugabyte(dot)com <mailto:bryn(at)yugabyte(dot)com> wrote:
>>
>> ...What am I doing wrong?
>
> You skipped over this part of my post and the documentation (Section 21.2):
> https://www.postgresql.org/docs/current/auth-pg-hba-conf.html <https://www.postgresql.org/docs/current/auth-pg-hba-conf.html>
>
> "The map-name is an arbitrary name that will be used to refer to this mapping in pg_hba.conf."
>
> This example below is for the ident auth method but the same syntax applies to peer.
>
> # TYPE DATABASE USER ADDRESS METHOD
> host all all 192.168.0.0/16 ident map=omicron
>
> pg_ident.conf and pg_hba.conf are two separate files and the only way information gets from the former to the latter is if you explicitly include the map name under METHOD for the the auth line.

Yes, Adrian, I see that I did slip up. Thanks, David, for pointing out this same error in your separate reply. I’m advertising my stupidity in this area rather effectively. My problem stems from the fact that the goal statement that my inner voice expresses seems so simple to state. 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".

It would have seemed to me, knowing just that the goal is achievable, that I could express this declaratively in one place—without needing to name the mapping between the system user's name and the cluster role's name, thus:

authentication type: local
authentication method: peer
system user: mary
cluster role: bob

I know that it isn't like this. But I have no intuition for why it could not be like this—and so it's easy for me to get muddled.

For the purpose of the tests that follow, I set up the O/S users "bob" and "mary" so that "id bob mary postgres" shows this:

id=1002(bob) gid=1001(postgres) groups=1001(postgres)
uid=1003(mary) gid=1001(postgres) groups=1001(postgres)
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114(ssl-cert)

And I set up the cluster-roles "bob" and "mary" so that "\du" shows this:

bob | | {}
mary | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then I started with this in "pg_hba.conf":

...
# TYPE DATABASE USER METHOD [auth-options]
# ---- -------------- -------------- ------ ----------------------------
local all bob peer
local all mary peer
...

together with en empty "pg_ident.conf". So far, after either "su bob" or "su mary", i was able to confirm that the bare "psql -d postgres" worked fine and that then "select session_user" showed, respectively, "bob" or "mary", too.

Then I changed "pg_hba.conf" to add a mapping for "mary" thus:

# TYPE DATABASE USER METHOD [auth-options]
# ---- -------------- -------------- ------ ----------------------------
local all bob peer
local all mary peer map=bllewell

But I left "pg_ident.conf" deliberately empty. I expected, now, that "psql -d postgres" would still work fine for "bob" but that if would fail for "mary". With this deliberate error in place, I found that after "su bob", the bare "psql -d postgres" worked fine. But after "su mary", the same command caused this error:

Peer authentication failed for user "mary"

I assume that the phrase « user "mary" » means the O/S user "mary".

It seems to me that the message « no entry for the mapping "bllewell" in "pg_ident.conf" » would be more helpful. But maybe that would need psychic powers.

Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

# MAPNAME SYSTEM-USERNAME PG-USERNAME
# ------- --------------- -----------
bllewell mary mary

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

As I'd expect, O/S "bob" still works fine and ends up as cluster-role "bob". But now, the attempt by O/S "mary" to connect using "psql -d postgres" fails, as it had ealier, with what boils sown to "computer says No":

Peer authentication failed for user "mary"

I still don't have a mental model that can explain this. As I reason it, the name "mary" is passed to the step that's informed by "pg_hba.conf" because it's available from the facts about the O/S user that's running the shell. Then, seeing "map=bllewell", the name "mary" is looked up in "pg_ident.conf" (just as it was in the previous test). Only now, instead of mapping it to the cluster-role "mary", which had worked, it now maps it to the role "bob". Why can it not connect, now, as "Bob"?

Clutching at straws, I reversed the mutual order of "mary" and "bob" in "pg_ident.conf"—even though that seemed wrong. It made no difference to the spelling of the « Peer authentication failed for user "mary" » error.

I've read the two relevant doc sections as carefully as I can. I can't see what I'm missing. I also confirmed with this query

select name, setting
from pg_settings
where category = 'File Locations';

that the files that I've been editing are indeed the files that the server uses.

Do I have to grant permission in some way to allow O/S "mary" to connect as cluster-role "bob". I though that I had exactly done this by the facts in "pg_hba.conf" and "pg_ident.conf".

I'm still missing something. What is it?