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

Поиск
Список
Период
Сортировка
От Bryn Llewellyn
Тема "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
Дата
Msg-id 8A74D5A7-3737-484E-9293-6E7BE0E783E2@yugabyte.com
обсуждение исходный текст
Ответы Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should  (Jeremy Smith <jeremy@musicsmith.net>)
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
Adrian, thank you for your reply to my « Seeking the correct term of art for the (unique) role that is usually called "postgres"... » thread here:


I'm starting a new thread because my question, now, has nothing to do with the role whose interim name was deemed to be best spelled "bootstrap super user" for the time being.

This question is about "peer" authentication. I am able to make it work as long as my O/S user's name (what "pg_ident.conf" calls the "SYSTEM-USERNAME") is spelled identically to my partner cluster role's name (what "pg_ident.conf" calls the "PG-USERNAME"). But the doc for this file explains that you can define a mapping in "pg_ident.conf", give it any "MAPNAME" that you want, and map a "SYSTEM-USERNAME"to a differently spelled "PG-USERNAME". Or, as you put it, Adrian"

The purpose of mapping would be to do something like map OS user foo to PG user usr.

I want to get this to work because I want to use a role-name that has a dollar-sign in it (I don't care that this isn't in line with the Standard) and because the O/S uses dollar-sign in a reserved way and I don't want to go against the convention there by escaping things.

Here, I simply used o/s user "bob" and cluster role "alice".

And, yes, I did read the two doc sections "The pg_hba.conf File" and "User Name Maps" (for Version 11 'cos that's what I'm using). The latter shows this example:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
...
omicron         robert                  bob

And I simply decided to follow its spirit with "bob" mapping to "alice", thus:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
...
bllewell        bob                     alice

Here's my "pg_hba.conf":

...
local   all             postgres                                peer # See the essay at the start.
local   all             alice                                   peer
local   all             bob                                     peer
local   all             all                                     peer
...

For reasons that will become clear in a moment, the file has entries for both "bob" and "alice".

Here's how I created the O/S user:

adduser bob # Password «x»
usermod -g postgres bob

And here's how I created the cluster role:

create role alice with
  nosuperuser
  createrole
  createdb
  noreplication
  nobypassrls
  connection limit -1
  login password 'x';

(You can see that my plan is to follow the advice from  the section "Role Attributes".) Again, for reasons that will become clear in a moment, I also created the role "bob" using an otherwise identically spelled "create role" statement.

Then I bounced the cluster thus (as my "postgres" O/S user):

sudo systemctl stop  postgresql
sudo systemctl start postgresql
pg_ctl reload -D /etc/postgresql/11/main/

(I know that I could've used "systemctl restart ".) Like I said elsewhere, the "reload" seems to be superfluous. But it costs nothing to do it.

Then I did "su bob" and first did this sanity test:

psql -h localhost -p 5432 -d postgres -U alice

That worked fine—and "select current_role" showed "alice".

Then I did the spelling for "peer", to authorize explicitly as "bob":

psql -d postgres -U bob

That worked too so that "select current_role" now showed "bob". Finally, I omitted "bob" here in the belief that this would make my mapping kick in and authorize using the cluster role "alice":

psql -d postgres

It got me in without error. (And, as hoped for, there was no password challenge.) But "select current_role" showed that the mapping had been ignored and that I was connected again as "bob".

What am I doing wrong?


В списке pgsql-general по дате отправления:

Предыдущее
От: "Peter J. Holzer"
Дата:
Сообщение: Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12
Следующее
От: Jeremy Smith
Дата:
Сообщение: Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should