Обсуждение: Allow cluster owner to bypass authentication

This is an implementation of the idea I mentioned in [0].

The naming and description perhaps isn't ideal yet but it works in
principle.

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.

I included an option to turn this off because (a) people are going to
complain, (b) you need this for the test suites to be able to test
pg_hba.conf, and (c) conceivably, someone might want to have all access
to go through pg_hba.conf for some auditing reasons (perhaps via PAM).

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.


[0]:
https://www.postgresql.org/message-id/29164e47-8dfb-4737-2a61-e67a18f847f3%402ndquadrant.com

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Aug 15, 2019 at 9:07 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
>
> This is an implementation of the idea I mentioned in [0].
>
> The naming and description perhaps isn't ideal yet but it works in
> principle.
>
> 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.
>
> I included an option to turn this off because (a) people are going to
> complain, (b) you need this for the test suites to be able to test
> pg_hba.conf, and (c) conceivably, someone might want to have all access
> to go through pg_hba.conf for some auditing reasons (perhaps via PAM).
>
> 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.
>


This has been hanging around for a while. I guess the reason it hasn't
got much attention is that on its own it's not terribly useful.
However, when you consider that it's a sensible prelude to setting a
more secure default for auth in initdb (I'd strongly advocate
SCRAM-SHA-256 for that) it takes on much more significance.

The patch on its own is very small and straightforward, The actual
code is smaller than the docco.

Let's do this so we can move to a better default auth.

cheers

andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Greetings,

* 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 included an option to turn this off because (a) people are going to
> complain, (b) you need this for the test suites to be able to test
> pg_hba.conf, and (c) conceivably, someone might want to have all access
> to go through pg_hba.conf for some auditing reasons (perhaps via PAM).

Auditing is certainly an important consideration.

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

I am, however, slightly confused as to why we'd then want to, in a
subsequent step, make the default set up use md5 or scram...?

* Andrew Dunstan (andrew.dunstan@2ndquadrant.com) wrote:
> This has been hanging around for a while. I guess the reason it hasn't
> got much attention is that on its own it's not terribly useful.
> However, when you consider that it's a sensible prelude to setting a
> more secure default for auth in initdb (I'd strongly advocate
> SCRAM-SHA-256 for that) it takes on much more significance.

I'm all for improving the default for auth in initdb, but why wouldn't
that be peer auth first, followed by SCRAM..?  If that's what you're
suggesting then great, but that wasn't very clear from the email text,
at least.  I've not done more than glanced at the patch.

Thanks,

Stephen

> > This has been hanging around for a while. I guess the reason it hasn't
> > got much attention is that on its own it's not terribly useful.
> > However, when you consider that it's a sensible prelude to setting a
> > more secure default for auth in initdb (I'd strongly advocate
> > SCRAM-SHA-256 for that) it takes on much more significance.
>
> I'm all for improving the default for auth in initdb, but why wouldn't
> that be peer auth first, followed by SCRAM..?  If that's what you're
> suggesting then great, but that wasn't very clear from the email text,
> at least.



What this is suggesting is in effect, for the db owner only and only
on a Unix domain socket, peer auth falling back to whatever is in the
hba file. That makes setting something like scram-sha-256 as the
default more practicable.

If we don't do something like this then changing the default could
cause far more disruption than our users might like.

>  I've not done more than glanced at the patch.

That might pay dividends :-)

cheers

andrew


-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.

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

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, Dec 17, 2019 at 5:27 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> 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.

I feel like this is taking a policy decision that properly belongs in
pg_hba.conf and making it into a GUC. If you're introducing a GUC
because it's not possible to configure the behavior that you want in
pg_hba.conf, then I think the solution to that is to enhance
pg_hba.conf so that it can support the behavior you want to configure.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

On 2019-12-18 15:09, Robert Haas wrote:
> I feel like this is taking a policy decision that properly belongs in
> pg_hba.conf and making it into a GUC. If you're introducing a GUC
> because it's not possible to configure the behavior that you want in
> pg_hba.conf, then I think the solution to that is to enhance
> pg_hba.conf so that it can support the behavior you want to configure.

Yeah, I was not really happy with that either.  So I tried a new 
approach:  Introduce a new pg_hba.conf line type "localowner" that 
matches on Unix-domain socket connections if the user at the client end 
matches the owner of the postgres process.  Then the behavior I'm after 
can be expressed with a pg_hba.conf entry like

localowner  all  all  trust

or similar, as one chooses.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Greetings,

* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> On 2019-12-18 15:09, Robert Haas wrote:
> >I feel like this is taking a policy decision that properly belongs in
> >pg_hba.conf and making it into a GUC. If you're introducing a GUC
> >because it's not possible to configure the behavior that you want in
> >pg_hba.conf, then I think the solution to that is to enhance
> >pg_hba.conf so that it can support the behavior you want to configure.
>
> Yeah, I was not really happy with that either.  So I tried a new approach:
> Introduce a new pg_hba.conf line type "localowner" that matches on
> Unix-domain socket connections if the user at the client end matches the
> owner of the postgres process.  Then the behavior I'm after can be expressed
> with a pg_hba.conf entry like
>
> localowner  all  all  trust
>
> or similar, as one chooses.

Ugh, no thanks.  We already have enough top-level "Types" that I really
don't like inventing another that's "almost like this other one, but not
quite".

Why not have a special user that can be used for Type: local pg_hba.conf
lines?  So you'd have:

local all localowner peer

That way you're:

a) only keeping the types we have today
b) using peer auth, which is what this actually is
c) NOT using 'trust', which we shouldn't because it's bad
d) matching up to what Debian has been doing for decades already

Thanks,

Stephen

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

Stephen Frost <sfrost@snowman.net> writes:
> Why not have a special user that can be used for Type: local pg_hba.conf
> lines?  So you'd have:
> local all localowner peer
> That way you're:
> a) only keeping the types we have today
> b) using peer auth, which is what this actually is
> c) NOT using 'trust', which we shouldn't because it's bad
> d) matching up to what Debian has been doing for decades already

But ... if "peer" auth allowed all the cases Peter wants to allow,
we'd not be having this discussion in the first place, would we?

The specific case of concern here is the database's OS-owner wanting
to connect as some other database role than her OS user name.
"peer" doesn't allow that, at least not without messy additional
configuration in the form of a username map.

While the syntax you suggest above could be made to implement that,
it doesn't seem very intuitive to me.  Maybe what we want is some
additional option that acts like a prefab username map:

local all all peer let_OS_owner_in_as_any_role

Bikeshedding the actual option name is left for the reader.  We'd
also have to think whether a regular "map" option can be allowed
on the same line, and if so how the two would interact.  (It might
be as easy as "allow connection if either option allows it".)

            regards, tom lane

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> 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.

Not sure about whether this is an interesting consideration or not.
If you don't trust the OS-level admin, don't you basically need to
go find a different computer to work on?

Still, I take your point that "peer" does risk letting in a set of
connections wider than what the DBA was thinking about.  Enlarging
on my other response that what we want is an auth option not a whole
new auth type, maybe we could invent another auth option that limits
which OS user names are accepted by "peer", with an easy special case
if you only want to allow the server's OS owner.  (Note that this
is *not* the existing "role" column, which restricts the database
role name not the external name; nor is it something you can do
with a username map, at least not with the current definition of
those.)

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Why not have a special user that can be used for Type: local pg_hba.conf
> > lines?  So you'd have:
> > local all localowner peer
> > That way you're:
> > a) only keeping the types we have today
> > b) using peer auth, which is what this actually is
> > c) NOT using 'trust', which we shouldn't because it's bad
> > d) matching up to what Debian has been doing for decades already
>
> But ... if "peer" auth allowed all the cases Peter wants to allow,
> we'd not be having this discussion in the first place, would we?

I'm still not entirely convinced it doesn't, but that's also because I
keep thinking we're talking about a sensible default here and I'm coming
to realize that the idea here is to let the cluster owner not just
bypass auth to connect as their own DB user, but to allow the cluster
own to connect as ANY database role, and that's not a sensible *default*
setting for us to have, imv.

> The specific case of concern here is the database's OS-owner wanting
> to connect as some other database role than her OS user name.
> "peer" doesn't allow that, at least not without messy additional
> configuration in the form of a username map.

Yes, to allow that you'd need to have a mapping.  Theoretically, we
could have a mapping automatically exist which could be used for that,
but now I'm trying to understand what the use-case here is for actual
deployments.  If this is for testing- great, let's have some flag
somewhere that we can enable for testing but we shouldn't have it as the
*default*.

> While the syntax you suggest above could be made to implement that,
> it doesn't seem very intuitive to me.  Maybe what we want is some
> additional option that acts like a prefab username map:
>
> local all all peer let_OS_owner_in_as_any_role

Or ... map=pg_os_user_allow

and declare 'pg_*' as system-defined special mappings, like "OS user" ->
"anyone".

> Bikeshedding the actual option name is left for the reader.  We'd
> also have to think whether a regular "map" option can be allowed
> on the same line, and if so how the two would interact.  (It might
> be as easy as "allow connection if either option allows it".)

Allowing multiple maps to be used is a different feature.

Thanks,

Stephen

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> > 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.
>
> Not sure about whether this is an interesting consideration or not.
> If you don't trust the OS-level admin, don't you basically need to
> go find a different computer to work on?
>
> Still, I take your point that "peer" does risk letting in a set of
> connections wider than what the DBA was thinking about.  Enlarging
> on my other response that what we want is an auth option not a whole
> new auth type, maybe we could invent another auth option that limits
> which OS user names are accepted by "peer", with an easy special case
> if you only want to allow the server's OS owner.  (Note that this
> is *not* the existing "role" column, which restricts the database
> role name not the external name; nor is it something you can do
> with a username map, at least not with the current definition of
> those.)

Sure you can do this with an existing map- just define a mapping and
only include in it the users you want to allow.  If no mapping matches,
then your connection is denied.

If you want an equality match in your mapping, then you have to provide
one, like so:

default         /^(.*)$                 \1

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> But ... if "peer" auth allowed all the cases Peter wants to allow,
>> we'd not be having this discussion in the first place, would we?

> I'm still not entirely convinced it doesn't, but that's also because I
> keep thinking we're talking about a sensible default here and I'm coming
> to realize that the idea here is to let the cluster owner not just
> bypass auth to connect as their own DB user, but to allow the cluster
> own to connect as ANY database role,

Right.

> and that's not a sensible *default*
> setting for us to have, imv.

There's certainly a discussion to be had about whether that should be
the default or not (and I too am doubtful that it should be); but I think
Peter made a sufficient case that it'd be useful if it were easy to set
things up that way.  Right now it's a tad painful.

>> While the syntax you suggest above could be made to implement that,
>> it doesn't seem very intuitive to me.  Maybe what we want is some
>> additional option that acts like a prefab username map:
>> 
>> local all all peer let_OS_owner_in_as_any_role

> Or ... map=pg_os_user_allow

> and declare 'pg_*' as system-defined special mappings, like "OS user" ->
> "anyone".

Maybe, but then we'd need to allow multiple map options.  Still, if
the semantics are "union of what any map allows", that doesn't
seem too hard.

> Allowing multiple maps to be used is a different feature.

Not really; I think it is quite reasonable to want "OS owner can
connect as anyone" plus "joe should be allowed to connect as charlie".
If you want to add the latter to a working setup, you shouldn't have
to suddenly figure out how to reimplement "map=pg_os_user_allow" at
a lower level of detail.  That's a recipe for mistakes.

            regards, tom lane

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Still, I take your point that "peer" does risk letting in a set of
>> connections wider than what the DBA was thinking about.  Enlarging
>> on my other response that what we want is an auth option not a whole
>> new auth type, maybe we could invent another auth option that limits
>> which OS user names are accepted by "peer", with an easy special case
>> if you only want to allow the server's OS owner.  (Note that this
>> is *not* the existing "role" column, which restricts the database
>> role name not the external name; nor is it something you can do
>> with a username map, at least not with the current definition of
>> those.)

> Sure you can do this with an existing map- just define a mapping and
> only include in it the users you want to allow.  If no mapping matches,
> then your connection is denied.

Oh, hm ... that wasn't my mental model of it, and the documentation
doesn't really spell that out anywhere.  It would be reasonable for
people to assume that the default behavior is equivalent to a map
with no entries, and I don't see anything in the docs that really
contradicts that.  As best I can tell from the above, the default
corresponds to an explicitly-written map like

    default  /^(.*)$  \1

which seems unreasonably complicated; it's sure going to look
like line noise to somebody who's not already familiar with
regex notation.

The other issue is that you can't actually implement the behavior
Peter wants with the existing username map facility, because there's
no wildcard for the database role name column.  You can't write

    pg_os_user_allow  postgres  .*

and even if you could, that's not a great solution because it
hard-wires the OS username of the database server's owner.

I think it'd be great if this behavior could be implemented
within the notation, because we could then just set up a
non-empty default pg_ident.conf with useful behavioral
examples in the form of prefab maps.  In particular, we
should think about how hard it is to do "I want the default
behavior plus allow joe to connect as charlie".  If the
default is a one-liner that you can copy and add to,
that's a lot better than if you have to reverse-engineer
what to write.

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> But ... if "peer" auth allowed all the cases Peter wants to allow,
> >> we'd not be having this discussion in the first place, would we?
>
> > I'm still not entirely convinced it doesn't, but that's also because I
> > keep thinking we're talking about a sensible default here and I'm coming
> > to realize that the idea here is to let the cluster owner not just
> > bypass auth to connect as their own DB user, but to allow the cluster
> > own to connect as ANY database role,
>
> Right.
>
> > and that's not a sensible *default*
> > setting for us to have, imv.
>
> There's certainly a discussion to be had about whether that should be
> the default or not (and I too am doubtful that it should be); but I think
> Peter made a sufficient case that it'd be useful if it were easy to set
> things up that way.  Right now it's a tad painful.

I'm also concerned with the "where does this end?" question.  What if
the role is set to not allow connections?  What if the database is set
to not allow connections?  I mean, sure, it's the OS user, so they can
do *anything*, technically, but we generally want some intelligent
safe-guards in place where we make them jump through an extra hoop or
two to make a change that could really break things.  Further, some of
those "safe-guards" that we have might be "auditing requirements" to
other people who expect to see in their audit logs when a change is made
to, say, allow the OS user to log in as some other role.  I suppose as
long as this can be turned off (and, ideally, isn't the default anyway)
then hopefully it won't be too much of an issue.

> >> While the syntax you suggest above could be made to implement that,
> >> it doesn't seem very intuitive to me.  Maybe what we want is some
> >> additional option that acts like a prefab username map:
> >>
> >> local all all peer let_OS_owner_in_as_any_role
>
> > Or ... map=pg_os_user_allow
>
> > and declare 'pg_*' as system-defined special mappings, like "OS user" ->
> > "anyone".
>
> Maybe, but then we'd need to allow multiple map options.  Still, if
> the semantics are "union of what any map allows", that doesn't
> seem too hard.

I agree that doesn't seem too hard and generally seems reasonable.
Seems like we might also want an explicit way of saying '*' on the
right-hand-side, or something like it, so users could set this up for
anyone they want and not only have this option exist for the user who
happens to be logging in with the same unix uid of the PG server.

> > Allowing multiple maps to be used is a different feature.
>
> Not really; I think it is quite reasonable to want "OS owner can
> connect as anyone" plus "joe should be allowed to connect as charlie".
> If you want to add the latter to a working setup, you shouldn't have
> to suddenly figure out how to reimplement "map=pg_os_user_allow" at
> a lower level of detail.  That's a recipe for mistakes.

Eh, I wouldn't argue if someone wrote a single patch that does both, but
considering we don't support multiple maps today, I wouldn't push on
someone wanting to extend the way maps work today to require that they
implement support for multiple maps too.

Thanks,

Stephen

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> Still, I take your point that "peer" does risk letting in a set of
> >> connections wider than what the DBA was thinking about.  Enlarging
> >> on my other response that what we want is an auth option not a whole
> >> new auth type, maybe we could invent another auth option that limits
> >> which OS user names are accepted by "peer", with an easy special case
> >> if you only want to allow the server's OS owner.  (Note that this
> >> is *not* the existing "role" column, which restricts the database
> >> role name not the external name; nor is it something you can do
> >> with a username map, at least not with the current definition of
> >> those.)
>
> > Sure you can do this with an existing map- just define a mapping and
> > only include in it the users you want to allow.  If no mapping matches,
> > then your connection is denied.
>
> Oh, hm ... that wasn't my mental model of it, and the documentation
> doesn't really spell that out anywhere.

That documentation also refers to 'ident' still, unfortunately.

> It would be reasonable for
> people to assume that the default behavior is equivalent to a map
> with no entries, and I don't see anything in the docs that really
> contradicts that.  As best I can tell from the above, the default
> corresponds to an explicitly-written map like
>
>     default  /^(.*)$  \1
>
> which seems unreasonably complicated; it's sure going to look
> like line noise to somebody who's not already familiar with
> regex notation.

Right- the default mapping is an 'equality' mapping, which, implemented
as a regexp, looks like the above.  When it comes to what happens when
you add 'map=' to an entry in your pg_hba.conf, I view that as "I am
replacing the default mapping with this one of my own".  That's
necessary if your OS users don't map to your DB users (I want to be able
to support having 'alice' map to 'bob', and 'bob' map to 'alice',
without 'alice' being allowed to log in as 'alice' or 'bob' to log in as
'bob'...).

> The other issue is that you can't actually implement the behavior
> Peter wants with the existing username map facility, because there's
> no wildcard for the database role name column.  You can't write
>
>     pg_os_user_allow  postgres  .*
>
> and even if you could, that's not a great solution because it
> hard-wires the OS username of the database server's owner.

Yeah, that is true, though we could make both halves of that work, I
would think.

> I think it'd be great if this behavior could be implemented
> within the notation, because we could then just set up a
> non-empty default pg_ident.conf with useful behavioral
> examples in the form of prefab maps.  In particular, we
> should think about how hard it is to do "I want the default
> behavior plus allow joe to connect as charlie".  If the
> default is a one-liner that you can copy and add to,
> that's a lot better than if you have to reverse-engineer
> what to write.

This direction certainly sounds more appealing to me.

Thanks,

Stephen

Hi Peter,

On 12/27/19 3:22 PM, Stephen Frost wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 
>> I think it'd be great if this behavior could be implemented
>> within the notation, because we could then just set up a
>> non-empty default pg_ident.conf with useful behavioral
>> examples in the form of prefab maps.  In particular, we
>> should think about how hard it is to do "I want the default
>> behavior plus allow joe to connect as charlie".  If the
>> default is a one-liner that you can copy and add to,
>> that's a lot better than if you have to reverse-engineer
>> what to write.
> 
> This direction certainly sounds more appealing to me.

Any thoughts on the discussion between Stephen and Tom?

Regards,
-- 
-David
david@pgmasters.net

On 2020-03-27 15:58, David Steele wrote:
> Hi Peter,
> 
> On 12/27/19 3:22 PM, Stephen Frost wrote:
>> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>>
>>> I think it'd be great if this behavior could be implemented
>>> within the notation, because we could then just set up a
>>> non-empty default pg_ident.conf with useful behavioral
>>> examples in the form of prefab maps.  In particular, we
>>> should think about how hard it is to do "I want the default
>>> behavior plus allow joe to connect as charlie".  If the
>>> default is a one-liner that you can copy and add to,
>>> that's a lot better than if you have to reverse-engineer
>>> what to write.
>>
>> This direction certainly sounds more appealing to me.
> 
> Any thoughts on the discussion between Stephen and Tom?

It appears that the whole discussion of what a new default security 
configuration could or should be hasn't really moved to a new consensus, 
so given the time, I think it's best that we leave things as they are 
and continue the exploration at some future time.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 4/5/20 6:15 AM, Peter Eisentraut wrote:
> On 2020-03-27 15:58, David Steele wrote:
>> Hi Peter,
>>
>> On 12/27/19 3:22 PM, Stephen Frost wrote:
>>> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>>>
>>>> I think it'd be great if this behavior could be implemented
>>>> within the notation, because we could then just set up a
>>>> non-empty default pg_ident.conf with useful behavioral
>>>> examples in the form of prefab maps.  In particular, we
>>>> should think about how hard it is to do "I want the default
>>>> behavior plus allow joe to connect as charlie".  If the
>>>> default is a one-liner that you can copy and add to,
>>>> that's a lot better than if you have to reverse-engineer
>>>> what to write.
>>>
>>> This direction certainly sounds more appealing to me.
>>
>> Any thoughts on the discussion between Stephen and Tom?
> 
> It appears that the whole discussion of what a new default security 
> configuration could or should be hasn't really moved to a new consensus, 
> so given the time, I think it's best that we leave things as they are 
> and continue the exploration at some future time.

Sounds good. I've marked the patch RwF.

Regards,
-- 
-David
david@pgmasters.net