Re: [EXTERNAL] Re: SSPI Feature Request - Mailing list pgsql-general

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@hjp.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.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.
>     a.user@some.domain. A correspnding role in PostgreSQL is automatically
>     created if it doesn't already exist.
>
>     The user's groups are also read from AD: group1@some.domain,
>     group2@some.domain, ... For each of these groups a GRANT is performed:
>         GRANT "group1@some.domain" TO "a.user@some.domain";
>         GRANT "group2@some.domain" TO "a.user@some.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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"