Re: [EXTERNAL] Re: SSPI Feature Request

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 <hjp-pgsql(at)hjp(dot)at> wrote:

> On 2024-07-10 07:27:29 -0700, Ian Harding wrote:
> >
> >
> > On Wed, Jul 10, 2024 at 7:10 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at>
> 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(dot)lastname(at)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(at)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(at)SOMEDOMAIN as the user, and NOT
> > > firstname(dot)lastname(at)SOMEDOMAIN(dot) 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(dot)lastname(at)SOMEDOMAIN) is
> > > specifically listed in the logins as well as the group (example
> > > UserGroupName(at)SOMEDOMAIN) then it will use the user
> > > firstname(dot)lastname(at)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.
> > a(dot)user(at)some(dot)domain(dot) A correspnding role in PostgreSQL is
> automatically
> > created if it doesn't already exist.
> >
> > The user's groups are also read from AD: group1(at)some(dot)domain,
> > group2(at)some(dot)domain, ... For each of these groups a GRANT is
> performed:
> > GRANT "group1(at)some(dot)domain" TO "a(dot)user(at)some(dot)domain";
> > GRANT "group2(at)some(dot)domain" TO "a(dot)user(at)some(dot)domain";
> > ...
> > 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.
> |_|_) | |
> | | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>