Re: [Patch] Using Windows groups for SSPI authentication - Mailing list pgsql-hackers

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