Re: Allow cluster owner to bypass authentication - Mailing list pgsql-hackers

From Stephen Frost
Subject Re: Allow cluster owner to bypass authentication
Date
Msg-id 20191227174946.GS3195@tamriel.snowman.net
Whole thread Raw
In response to Re: Allow cluster owner to bypass authentication  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
List pgsql-hackers
Greetings,

* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> On 2019-12-18 16:24, Stephen Frost wrote:
> >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,

For this part, at least, it's a non-issue for the Debian packaging
because it's hard-coded (more-or-less) to install as the postgres user.
If it wasn't though, I'm sure the template that's used to create the
default pg_hba.conf could be adjusted to match whatever you want.
Adding an option to allow the pg_hba.conf to have an entry instead
that's "whatever the database's unix ID is" then that might be an
alright option.

> and (2) it only allows
> access as "postgres" and not other users.

Right...  Is the idea here (which didn't seem to be outlined in the
initial email) that this will allow the DB "owner" to log in directly to
the DB as any role..?  If so, why would that be applied only to this
particular "owner" case and not to, say, all superusers (since they can
all do SET SESSION AUTHORIZATION already...).

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

If you have two lines in pg_hba.conf then you don't need an actual
mapping..

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

What pg_hba.conf is really all about is expression a relationship
between "some outside authentication system" and a DB user; that's
exactly what it's for.  Redefining it to be about something else strikes
me as a bad idea that's just going to be confusing and will require a
great deal more explaining whenever someone is first learning about PG.

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

They don't have to be the same entity, they just have to communicate
with each other, which isn't entirely unheard of.  We're also just
talking about defaults here- and what I'm trying to stress is that a
huge number of installations already use this.  If there's a serious
issue with it then perhaps there's something to discuss, but if not then
I'm not really anxious to move in a direction that's actively away from
what our users are already using without it being clearly a better
option, which this doesn't seem to be.

Thanks,

Stephen

Attachment

pgsql-hackers by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Allow cluster owner to bypass authentication
Next
From: Fabien COELHO
Date:
Subject: Re: [PATCH v1] pg_ls_tmpdir to show directories