Re: [PATCH] Support using "all" for the db user in pg_ident.conf - Mailing list pgsql-hackers

From Isaac Morland
Subject Re: [PATCH] Support using "all" for the db user in pg_ident.conf
Date
Msg-id CAMsGm5eLhyRMr-did1Hjc0gh_FzJ0nmnvAxzDktnE5dq1jRv_g@mail.gmail.com
Whole thread Raw
In response to [PATCH] Support using "all" for the db user in pg_ident.conf  (Jelte Fennema <Jelte.Fennema@microsoft.com>)
List pgsql-hackers
On Tue, 27 Dec 2022 at 10:54, Jelte Fennema <Jelte.Fennema@microsoft.com> wrote:

This change makes it much easier to have a certain database
administrator peer or cert authentication, that allows connecting as
any user. Without this change you would need to add a line to
pg_ident.conf for every user that is in the database.

In some small sense this is a breaking change if anyone is using "all"
as a user currently and has pg_ident.conf rules for it. This seems
unlikely, since "all" was already handled specially in pg_hb.conf.
Also it can easily be worked around by quoting the all token in
pg_ident.conf. As long as this is called out in the release notes
it seems okay to me. However, if others disagree there would
be the option of changing the token to "pg_all". Since any
pg_ prefixed users are reserved by postgres there can be no user.
For now I used "all" though to stay consistent with pg_hba.conf.

+1 from me. I recently was setting up a Vagrant VM for testing and wanted to allow the OS user which runs the application to connect to the database as whatever user it wants and was surprised to find I had to list all the potential target DB users in the pg_ident.conf (in production it uses password authentication and each server gets just the passwords it needs stored in ~/.pgpass). I like the idea that both config files would be consistent, although the use of keywords such as "replication" in the DB column has always made me a bit uncomfortable.

Related question: is there a reason why pg_ident.conf can't/shouldn't be replaced by a system table? As far as I can tell, it's just a 3-column table, essentially, with all columns in the primary key. This latest proposal changes that a little; strictly, it should probably introduce a second table with just two columns identifying which OS users can connect as any user, but existing system table style seems to suggest that we would just use a special value in the DB user column for "all".

pgsql-hackers by date:

Previous
From: Jelte Fennema
Date:
Subject: [PATCH] Support using "all" for the db user in pg_ident.conf
Next
From: Tom Lane
Date:
Subject: Re: Error-safe user functions