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:
https://www.postgresql.org/message-id/e75abfa8-72af-701c-cf6f-5336a1a35...@aklaver.com <https://www.postgresql.org/message-id/e75abfa8-72af-701c-cf6f-5336a1a35...@aklaver.com> 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?
