public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ian Harding <[email protected]>
To: [email protected]
Subject: Re: [EXTERNAL] Re: SSPI Feature Request
Date: Wed, 10 Jul 2024 09:37:08 -0700
Message-ID: <CAMR4UwHS28+V7WkmUM4Drit3-t4so20CerOya1DMw9WnU5LyCA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <SY7PR01MB90072A084A61E7BE708EA3BEC70D2@SY7PR01MB9007.ausprd01.prod.outlook.com>
<[email protected]>
<SY7PR01MB9007B2AA3CC9763E13C9B630C7DB2@SY7PR01MB9007.ausprd01.prod.outlook.com>
<[email protected]>
<CAMR4UwHOHPC2Vvi0qmR3O+Xvs_FgUXO8-obQnZ0-FdBO_EPz8g@mail.gmail.com>
<[email protected]>
Windows somehow aggregates the permissions allowed for all the Server
Principals (logins) associated with global groups of which your account is
a member. It’s a disaster. We would shortcut that disaster by making a
single group a PostgreSQL login.
It would be bad, but not as awful as SQL Server. It would basically be a
shared PostgreSQL role that members could connect as with their windows
account Kerberos token.
On Wed, Jul 10, 2024 at 8:03 AM Peter J. Holzer <[email protected]> wrote:
> On 2024-07-10 07:27:29 -0700, Ian Harding wrote:
> >
> >
> > On Wed, Jul 10, 2024 at 7:10 AM Peter J. Holzer <[email protected]>
> wrote:
> >
> > On 2024-07-09 03:35:33 +0000, Buoro, John wrote:
> > > I've dusted off my C books and coded a solution.
> > [...]
> > > When using SSPI you can grant access to a user by giving the
> > > login name as firstname.lastname@SOMEDOMAIN for example.
> > > PostgresSQL has no concept of groups, just roles. The code
> > > provided allows you to specify a group name as a login. Example
> > > UserGroupName@SOMEDOMAIN It will search Active Directory \ LDAP
> > > for the current user's distinguished name and the domain
> > > component (DC) their account is defined in. Then it will obtain
> > > all the access groups which this account belongs to (excluding
> > > mail groups). It will compare the group name with what is
> > > defined in ProgreSQL. If there is a match, then that group name
> > > will be the identity of the user, so that for example...
> > >
> > > SELECT USER;
> > >
> > > ...will show UserGroupName@SOMEDOMAIN as the user, and NOT
> > > firstname.lastname@SOMEDOMAIN. This is because PostgreSQL
> > > appears not to have group support nor the ability to separate
> > > user identification and user authentication from what I can see
> > > in the source code.
> > >
> > > If the user's account (example firstname.lastname@SOMEDOMAIN) is
> > > specifically listed in the logins as well as the group (example
> > > UserGroupName@SOMEDOMAIN) then it will use the user
> > > firstname.lastname@SOMEDOMAIN rather than the group. If there
> > > are multiple groups defined in PostgreSQL that the user is a
> > > member of then the code will use the first matching group as
> > > obtained from Active Directory \ LDAP. It will not work out
> > > which group has the most \ highest privileges.
> >
> > I am confused. This doesn't seem to be what you were asking for and
> I'm
> > also unsure what scenario this is trying to address.
> >
> > I thought you wanted something like this:
> >
> > A user can authenticate with their AD name (DN, URN, or whatever),
> e.g.
> > [email protected]. A correspnding role in PostgreSQL is
> automatically
> > created if it doesn't already exist.
> >
> > The user's groups are also read from AD: [email protected],
> > [email protected], ... For each of these groups a GRANT is
> performed:
> > GRANT "[email protected]" TO "[email protected]";
> > GRANT "[email protected]" TO "[email protected]";
> > ...
> > The roles for these groups might also be automatically created but
> since
> > a role without privileges isn't very useful I'm not sure if that
> makes
> > sense.
> [...]
> >
> > The solution proposed is about as close as I think you can get to the
> Windows
> > reality
>
> I do think the scheme I outlined above would be possible (and maybe not
> even that hard to implement).
>
> > and would be useful.
>
> Frankly, it sounds like a support nightmare to me. Users can be members
> of dozens of access groups. If I understood John correctly, his code
> chooses the first one of them. But neither PostgreSQL nor Active
> Directory guarantees any order of group membership, so "first"
> essentially means "random". So I'm foreseeing lots of calls to the
> support hotline ("yesterday it worked and today it doesn't.").
>
> > A windows group is the only thing PostgreSQL would
> > know or care about. Individuals authenticate as thier individual selves
> but are
> > granted access as a member of the global group.
> >
> > MS SQL Server works like that except that, although there is no “login”
> with
> > your individual name, you are operating within the database as your
> individual
> > account. They can do that because they don’t require existence of a
> named login
> > for the individual.
>
> That sounds contradictory. How can they operate as their individual
> account if there are no logins for individuals? Do you mean something
> different by "account" and "login" (for me these are synonyms in this
> case since clearly "login" can't mean "the act of logging in" here)?
> Or is it important that the login is not "named"? That seems weird to
> me too since each active directory user has a name (or three).
>
>
> > I doubt that’s possible for PostgreSQL.
> >
> > As a MS SQL Server admin I can tell you that it is a complete mystery
> how a
> > user gained access to the database in this world.
>
> As a system administrator I hate complete mysteries so I don't think
> this is something we ought to strive for in PostgreSQL.
>
> hp
>
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | [email protected] | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [EXTERNAL] Re: SSPI Feature Request
In-Reply-To: <CAMR4UwHS28+V7WkmUM4Drit3-t4so20CerOya1DMw9WnU5LyCA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox