> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> wrote:
>
>> b...@yugabyte.com <mailto:b...@yugabyte.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?
