Thread: [Patch] Using Windows groups for SSPI authentication

Russell Foster <russell.foster.coding@gmail.com> writes:
> I have some code that I've been using that supports adding and
> authenticating Windows groups via the pg_ident file. This is useful for
> sysadmins as it lets them control database access outside the database
> using Windows groups. It has a new
> indicator (+), that signifies the identifier is a Windows group, as in the
> following example:

> # MAPNAME SYSTEM-USERNAME PG-USERNAME
> "Users" "+User group" postgres

While I don't object to adding functionality to access Windows groups,
I do object to using syntax that makes random assumptions about what a
user name can or can't be.

There was a prior discussion of this in the context of some other patch
that had a similar idea.  [ digs in archives... ]  Ah, here it is:

https://www.postgresql.org/message-id/flat/4ba3ad54-bb32-98c6-033a-ccca7058fc2f%402ndquadrant.com

It doesn't look like we arrived at any firm consensus about what to
do instead, but maybe you can find some ideas there.

            regards, tom lane

Russell Foster <russell.foster.coding@gmail.com> writes:
> Going to take a guess at what you mean by:
>> I do object to using syntax that makes random assumptions about what a
>> user name can or can't be.

> Are you referring to the "+" syntax in the ident file? I chose that because
> somewhere else (hba?) using the same syntax for groups. The quotes are just
> there to make the  group name case sensitive.

If this were a Postgres group name, I'd say yeah we already broke
the case of spelling group names with a leading "+".  (Which I'm
not very happy about either, but the precedent is there.)

However, this isn't.  Unless I'm totally confused, the field you're
talking about is normally an external, operating-system-defined name.
I do not think it's wise to make any assumptions about what those
can be.

By the same token, the idea of using a "pg_" prefix as discussed
in the other thread will not work here :-(.

After a few minutes' thought, the best I can can come up with is
to extend the syntax of identmap files with an "options" field,
so that your example becomes something like

# MAPNAME SYSTEM-USERNAME PG-USERNAME OPTIONS
"Users"   "User group"    postgres    windows-group

I'm envisioning OPTIONS as allowing a comma- or space-separated
list of keywords, which would give room to grow for other special
features we might want later.

            regards, tom lane

Russell Foster <russell.foster.coding@gmail.com> writes:
> I understand your concerns overall, and the solution you propose seems
> reasonable. But are we just using "windows-group" because the code is not
> there today to check for a user in another OS group?

It's not clear to me whether Windows groups have exact equivalents in
other OSes.  If we think the concept is generic, I'd be okay with
spelling the keyword system-group or the like.  The patch you
proposed looked pretty Windows-specific though.  Somebody with more
SSPI knowledge than me would have to opine on whether "sspi-group"
is a reasonable name.

            regards, tom lane

Greetings,

* Russell Foster (russell.foster.coding@gmail.com) wrote:
> Right after I sent that I realized that sspi-group was a bad idea, not sure
> if that's even a thing. Tried to cancel as it was still in moderation, but
> it made it through anyways! You are right, it is very windows specific. I
> can make it windows-group as you said, and resubmit.

Please don't top-post on these lists..

> On Tue, Oct 13, 2020 at 4:32 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Russell Foster <russell.foster.coding@gmail.com> writes:
> > > I understand your concerns overall, and the solution you propose seems
> > > reasonable. But are we just using "windows-group" because the code is not
> > > there today to check for a user in another OS group?
> >
> > It's not clear to me whether Windows groups have exact equivalents in
> > other OSes.  If we think the concept is generic, I'd be okay with
> > spelling the keyword system-group or the like.  The patch you
> > proposed looked pretty Windows-specific though.  Somebody with more
> > SSPI knowledge than me would have to opine on whether "sspi-group"
> > is a reasonable name.

While not exactly the same, of course, they are more-or-less equivilant
to Unix groups (it's even possible using NSS to get Unix groups to be
backed by Windows groups) and so calling it 'system-group' does seem
like it'd make sense, rather than calling it "Windows groups" or
similar.

One unfortunate thing regarding this is that, unless things have
changed, this won't end up working with GSS (unless we add the unix
group support and that's then backed by AD as I described above) since
the ability to check group membership using SSPI is an extension to the
Kerberos protocol, which never included group membership information in
it, and therefore while this would work for Windows clients connecting
to Windows servers, it won't work for Windows clients connecting to Unix
servers with GSSAPI authentication.

The direction I had been thinking of addressing that was to add an
option to pg_hba.conf's 'gss' auth method which would allow reaching out
to check group membership against an AD server.  In a similar vein, we
could add an option to the 'sspi' auth method to check the group
membership, rather than having this done in pg_ident.conf, which is
really intended to allow mapping between system usernames and PG
usernames which are different, not really for controlling authentication
based on group membership when the username is the same.

Russell, thoughts on that..?

Thanks,

Stephen

On Thu, Oct 15, 2020 at 11:31 AM Stephen Frost <sfrost@snowman.net> wrote:

> Please don't top-post on these lists..
Didn't even know what that was, had to look it up. Hopefully it is
resolved. Gmail does too many things for you!

> While not exactly the same, of course, they are more-or-less equivilant
> to Unix groups (it's even possible using NSS to get Unix groups to be
> backed by Windows groups) and so calling it 'system-group' does seem
> like it'd make sense, rather than calling it "Windows groups" or
> similar.
>
> One unfortunate thing regarding this is that, unless things have
> changed, this won't end up working with GSS (unless we add the unix
> group support and that's then backed by AD as I described above) since
> the ability to check group membership using SSPI is an extension to the
> Kerberos protocol, which never included group membership information in
> it, and therefore while this would work for Windows clients connecting
> to Windows servers, it won't work for Windows clients connecting to Unix
> servers with GSSAPI authentication.
>
> The direction I had been thinking of addressing that was to add an
> option to pg_hba.conf's 'gss' auth method which would allow reaching out
> to check group membership against an AD server.  In a similar vein, we
> could add an option to the 'sspi' auth method to check the group
> membership, rather than having this done in pg_ident.conf, which is
> really intended to allow mapping between system usernames and PG
> usernames which are different, not really for controlling authentication
> based on group membership when the username is the same.
>
> Russell, thoughts on that..?

So are you saying something like this where its an option to the sspi method?

# TYPE DATABASE USER        ADDRESS  MASK        METHOD
hostssl all      some_user   0.0.0.0  0.0.0.0     sspi group="Windows Group"

I guess the code wouldn't change much, unless you mean for it to do a
more generic ldap query. Seems OK to me, but I guess the hba could
become more verbose. The map is nice as it allows your HBA to be very
precise in how your connections and database users are represented,
and the ident map file is there to group those external identities. I
can't say I have a strong opinion either way though.

Greetings,

* Russell Foster (russell.foster.coding@gmail.com) wrote:
> On Thu, Oct 15, 2020 at 11:31 AM Stephen Frost <sfrost@snowman.net> wrote:
>
> > Please don't top-post on these lists..
> Didn't even know what that was, had to look it up. Hopefully it is
> resolved. Gmail does too many things for you!

Indeed!  This looks much better, thanks!

> > While not exactly the same, of course, they are more-or-less equivilant
> > to Unix groups (it's even possible using NSS to get Unix groups to be
> > backed by Windows groups) and so calling it 'system-group' does seem
> > like it'd make sense, rather than calling it "Windows groups" or
> > similar.
> >
> > One unfortunate thing regarding this is that, unless things have
> > changed, this won't end up working with GSS (unless we add the unix
> > group support and that's then backed by AD as I described above) since
> > the ability to check group membership using SSPI is an extension to the
> > Kerberos protocol, which never included group membership information in
> > it, and therefore while this would work for Windows clients connecting
> > to Windows servers, it won't work for Windows clients connecting to Unix
> > servers with GSSAPI authentication.
> >
> > The direction I had been thinking of addressing that was to add an
> > option to pg_hba.conf's 'gss' auth method which would allow reaching out
> > to check group membership against an AD server.  In a similar vein, we
> > could add an option to the 'sspi' auth method to check the group
> > membership, rather than having this done in pg_ident.conf, which is
> > really intended to allow mapping between system usernames and PG
> > usernames which are different, not really for controlling authentication
> > based on group membership when the username is the same.
> >
> > Russell, thoughts on that..?
>
> So are you saying something like this where its an option to the sspi method?
>
> # TYPE DATABASE USER        ADDRESS  MASK        METHOD
> hostssl all      some_user   0.0.0.0  0.0.0.0     sspi group="Windows Group"

Yes, something along those lines.

> I guess the code wouldn't change much, unless you mean for it to do a
> more generic ldap query. Seems OK to me, but I guess the hba could
> become more verbose. The map is nice as it allows your HBA to be very
> precise in how your connections and database users are represented,
> and the ident map file is there to group those external identities. I
> can't say I have a strong opinion either way though.

No, no, not suggesting you need to rewrite it as a generic LDAP query-
that would be a patch that I'd like to see but is a different feature
from this and wouldn't even be applicable to SSPI (it'd be for GSS..
and perhaps some other methods, but with SSPI we should use the SSPI
methods- I can't think of a reason to go to an LDAP query when the group
membership is directly available from SSPI, can you?).

The pg_ident is specifically intended to be a mapping from external user
identities to PG users.  Reading back through the thread, in the end it
seems like it really depends on what we're trying to solve here and
perhaps it's my fault for misunderstanding your original goal, but maybe
we get two features out of this in the end, and for not much more code.

Based on your example pg_ident.conf (which I took as more of a "this is
what using this would look like" and not as literally as I think you
meant it, now that I read back through it), there's a use-case of:

"Allow anyone in this group to log in as this *specific* PG user"

The other use-case is:

"Allow users in this group to be able to log into this PG server"

(The latter use-case potentially being further extended to
"automatically create the PG user if it doesn't already exist",
something which has been discussed elsewhere previously and is what
folks coming from other database systems may be used to).

The former would be more appropriate in pg_ident.conf, the latter would
fit into pg_hba.conf, both are useful.

To the prior discussion around pg_ident.conf, I do think having the
keyword being 'system-group' would fit well, but something we need to
think about is that multiple auth methods work with pg_ident and we need
to either implement the functionality for each of them, or make it clear
that it doesn't work- in particular, if you have 'system-group' as an
option in pg_ident.conf and you're using 'peer' auth on a Unix system,
we either need to make it work (which should be pretty easy..?), or
refuse to accept that map for that auth-method if it's not going to
work.

As it relates to pg_hba.conf- if you don't think it'd be much additional
code and you'd be up for it, I do think it'd be awesome to address that
use-case as well, but I do agree it's a separate feature and probably
committed independently.

Or, if I've managed to misunderstand again, please let me know. :)

Thanks!

Stephen