Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux