Обсуждение: Need reference doc on precedence/ordering for pg_hba.conf

Поиск
Список
Период
Сортировка

Need reference doc on precedence/ordering for pg_hba.conf

От
Kris Deugau
Дата:
In particular, something quite a bit more verbose than the offical docs
on www.postgresql.org/docs (which are good, but not quite detailed
enough for me to unravel which entry in one particular pg_hba.conf is
taking precedence, and how to add several new entries that won't break
anything else).  Every experiment I've tried has *almost* worked in the
manner I'm understanding from every bit of documentation I've found, but
something always breaks.  :(

Failing that, a diagnostic poke to the head to tell me where in this
config I should put entries that refer to both local socket connections
and remote TCP/IP connections for one specific database that I want MD5
(or crypt, for the old 6.x client :/  ) authentication on:

=========
# From Debian Sarge stock install
local   all         postgres       ident sameuser
local   all         all            ident sameuser

# Added for local software using PG
local   template1   all            ident
local   sameuser    all            md5
local   all         root           trust

# 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

# Last stock entry
host    all   all   0.0.0.0      0.0.0.0           reject
=========

(Comments trimmed out, whitespace mostly compacted;  linewraps are not
present in the real file - but my new entries don't function as expected
when I put them where I *think* they should go.)

-kgd

Re: Need reference doc on precedence/ordering for pg_hba.conf

От
Tom Lane
Дата:
Kris Deugau <kdeugau@vianet.ca> writes:
> Failing that, a diagnostic poke to the head to tell me where in this
> config I should put entries that refer to both local socket connections
> and remote TCP/IP connections for one specific database that I want MD5
> (or crypt, for the old 6.x client :/  ) authentication on:

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.

The relative positioning of "local" and "host" entries is therefore
irrelevant, because they can never both match the same connection
request.  The relative order of "local" entries is important, and
so is the relative order of "host" entries.

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

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

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

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

            regards, tom lane

Re: Need reference doc on precedence/ordering for pg_hba.conf

От
Kris Deugau
Дата:
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