Tom Lane wrote:
The rule is very simple: the first entry that is able to match an incoming connection request is the one that's used. "Match" is on the basis of connection type (local or TCP) and the requested database name and user name. When the match occurs, the connection is checked using the specified auth method, and if that fails then it's rejected.
That matches my reading of the official docs, but I'm certain I've managed to find a situation where the matched line isn't clear, and at least once instance where I managed to find a required behaviour that seemed to require two mutually exclusive "local.... " lines. I haven't been able to reproduce either issue in further testing today, and the entries I needed to add to one database cluster are working exactly as required.
I *think* the problems may have come up with a pair of slightly odd "one user+all databases"/"any user+one database" access rules, where the authentication on the first line was ident, and the second md5. Ident kept getting used for certain connections where I thought it shouldn't have been. I may also be confusing this with issues I've had configuring MySQL access rules.
# From Debian Sarge stock install local all postgres ident sameuser local all all ident sameuser
The first one is really redundant since the second one would match all the same connections (ie, local connections with username postgres) and it specifies the same handling.
Debian's Postgres package includes a script to VACUUM ANALYZE all databases periodically, and it runs as system user postgres. I've inserted my new entries in between those two entries above, and everything seems to be fine.
# Added for local software using PG local template1 all ident local sameuser all md5 local all root trust
These three are all complete no-ops where you have them, because the local/all/all entry will already have siphoned off every possible local connection. You'd need to put them in front of the local/all/all entry if you want them to do anything.
*nod* That sounds like what I had figured, but supposedly these were "required" for some other software running on this machine. ("Single-purpose machine? What's that?")
Note however that you almost certainly do not want that "trust" entry, since it'd allow anyone local to connect by saying eg "psql -U root". There's not a lot of point in intermixing trust and non-trust methods for connections from the same machine.
*nod* It appears to be irrelevant anyway because there's no root user in pg_shadow, so that entry will never match.
I suspect a copy-and-paste from third-party docs that assumed a bare or nearly-bare pg_hba.conf.
# More entries from stock Debian package host all all 127.0.0.1 255.255.255.255 ident sameuser host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff ident sameuser host all all ::ffff:127.0.0.1/128 ident sameuser # another local config - the real entry contains a real IP host all all [host IP] 255.255.255.255 trust
These seem reasonably sane assuming that's what you want. Their relative order doesn't matter since no two can match the same connection. (I think --- I don't recall at the moment if 127.0.0.1 can match an IPv6 connection on ::ffff:127.0.0.1.)
The entries from the stock Debian setup are likely there for completeness, just to make sure everything is covered.
# Last stock entry host all all 0.0.0.0 0.0.0.0 reject
This one is a waste of space, since the default is to reject anyway if there's no match.
If nothing else it's a reminder of the default behaviour. Personally, I prefer to be a bit verbose and pedantic with ACL systems like this, unless there is a significant performance hit from the extra entries.
Thanks for the detailed breakdown! -kgd
