Thread: 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
Attachment
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
Attachment
> > 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
Attachment
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
Attachment
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
Attachment
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
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
Attachment
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
Attachment
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
Attachment
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
Attachment
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