public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Re: [EXTERNAL] Re: SSPI Feature Request
Date: Wed, 10 Jul 2024 16:10:12 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <SY7PR01MB9007B2AA3CC9763E13C9B630C7DB2@SY7PR01MB9007.ausprd01.prod.outlook.com>
References: <SY7PR01MB90072A084A61E7BE708EA3BEC70D2@SY7PR01MB9007.ausprd01.prod.outlook.com>
<[email protected]>
<SY7PR01MB9007B2AA3CC9763E13C9B630C7DB2@SY7PR01MB9007.ausprd01.prod.outlook.com>
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.
[email protected]. A correspnding role in PostgreSQL is automatically
created if it doesn't already exist.
The user's groups are also read from AD: [email protected],
[email protected], ... For each of these groups a GRANT is performed:
GRANT "[email protected]" TO "[email protected]";
GRANT "[email protected]" TO "[email protected]";
...
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.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [EXTERNAL] Re: SSPI Feature Request
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox