On 2019-12-18 16:24, Stephen Frost wrote:
As for the question about how to set up pg_hba.conf so that just the DB
owner can log in via peer, the Debian/Ubuntu packages are deployed, by
default, with an explicit message and entry:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local all postgres peer
Which represents pretty much exactly what you're going for here, doesn't
it..?
This is similar but not exactly the same thing: (1) It doesn't work if
the OS user name and the PG superuser name are not equal, and (2) it
only allows access as "postgres" and not other users. Both of these
issues can be worked around to some extent by setting up pg_ident.conf
maps, but that can become a bit cumbersome. The underlying problem is
that "peer" is expressing a relationship between OS user and DB user,
but what we (arguably) want is a relationship between the client OS user
and the server OS user, and making "peer" do the latter is just hacking
around the problem indirectly.
Of course, later on in the default Debian/Ubuntu install is:
# "local" is for Unix domain socket connections only
local all all peer
and is what a very large number of our users are running with, because
it's a sensible default installation, even for multi-user systems. If
you aren't considering the authentication method when you're creating
new users, then that's an education problem, not a technical one.
Well, if this is the pg_hba.conf setup and I am considering the
authentication method when creating new users, then my only safe option
is to not create any new users. Because which OS users exist is not
controlled by the DBA. If the OS admin and the DBA are the same entity,
then peer is obviously very nice, but if not, then peer is a trap.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
