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