Re: Allow cluster owner to bypass authentication

Greetings,

* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> On 2019-12-17 05:40, Stephen Frost wrote:
> >* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> >>The idea is that if you connect over a Unix-domain socket and the local
> >>(effective) user is the same as the server's (effective) user, then
> >>access should be granted immediately without any checking of
> >>pg_hba.conf.  Because it's "your own" server and you can do anything you
> >>want with it anyway.
> >
> >While I understand where you're generally coming from, I'm not entirely
> >convinced that this is a good direction to go in.  Yes, you could go
> >change pg_hba.conf (maybe..)- but would doing so trigger an email to
> >someone else?  Would you really be able to change pg_hba.conf when you
> >consider more restrictive environments, like where there are SELinux
> >checks?  These days, a simple getpeerid() doesn't actually convey all of
> >the information about a process that would let you be confident that the
> >client really has the same access to the system that the running PG
> >server does.
>
> I realize that there are a number of facilities nowadays to do enhanced
> security setups.  But let's consider what 99% of users are using.  If the
> database server runs as user X and you are logged in as user X, you should
> be able to manage the database server that is running as user X without
> further restrictions.  Anything else would call into question the entire
> security model that postgres is built around.  But also, there is an option
> to turn this off in my patch, if you really have the need.

If we want to talk about what 99% of users are using, I'd suggest we
consider what our packagers are doing, and have been for many, many
years, which is setting up pg_hba.conf with peer auth...

> >>This addresses the shortcomings of using peer as the default mechanism
> >>in initdb.  In a subsequent step, my idea would be to make the default
> >>initdb authentication setup to use md5 (or scram, tbd.) for both local
> >>and host.
> >
> >I'm definitely in favor of having 'peer' be used by default in initdb.
>
> 'peer' is not good default for initdb.  Consider setting up a database
> server on a notional multiuser host with peer authentication.  As soon as
> you create a database user, that would allow some random OS user to log into
> your database server, if the name matches.  'peer' is useful if there is a
> strong coordination between the OS user creation and the database user
> creation.  But the default set up by initdb should really only let the
> instance owner in by default and require some additional authentication
> (like passwords) from everybody else.  'peer' cannot express that.

And so saying it's not a good default for initdb strikes me as pretty
darn odd.  If we're going to change our defaults here, I'd argue that we
should be looking to reduce the amount of difference between what
packagers do here and what our built-in defaults are, not invent a new
GUC to do something that pg_hba.conf can already be configured to do.

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

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.

If you're curious about where that entry for Debian came from, I can
shed some light on that too-

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=303274

Thanks,

Stephen