On Wed, Jul 10, 2024 at 7:10 AM Peter J. Holzer <hjp-pg...@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. > > (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. > |_|_) | | > | | | h...@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" The solution proposed is about as close as I think you can get to the Windows reality and would be useful. 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. 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. You might be a member of many groups, all of which might have a server login (granted server roles) and be mapped into databases with potentially differently named database users, while SELECT @@USER will show your actual individual domain user name. I think this feature would be useful but I think the PostgreSQL role -> Active Directory Group mapping is where it should end. That effectively makes it a shared role, as who the connection was established as would be lost. > >
