Re: [EXTERNAL] Re: SSPI Feature Request - Mailing list pgsql-general
From | Peter J. Holzer |
---|---|
Subject | Re: [EXTERNAL] Re: SSPI Feature Request |
Date | |
Msg-id | 20240710141012.lgflly6uaa3oxcng@hjp.at Whole thread Raw |
In response to | RE: [EXTERNAL] Re: SSPI Feature Request ("Buoro, John" <John.Buoro@au.harveynorman.com>) |
Responses |
Re: [EXTERNAL] Re: SSPI Feature Request
|
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!"
Attachment
pgsql-general by date: