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

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.

(There would also have to be a way to revoke privileges if the AD user
loses membership in an AD group. Or maybe those GRANTs could be scoped
to a session?)

This would allow the complete user/group administration to be outsourced
to AD. Only GRANTs to database objects like tables, views or functions
would need to be done at each database.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"