Обсуждение: SSPI Feature Request

Disclaimer

***************************************************************************
PRIVATE & CONFIDENTIAL
This email may contain legally privileged, confidential information or copyright material of the sender or a third party. This email and any attachments are intended for the addressee(s) only. If you are not the intended recipient, please contact the sender by reply email and delete this email and any attachments immediately. You must not read, copy, use, distribute or disclose the contents of this email or any attachments without the consent of the sender or the relevant third party. The sender does not accept responsibility for any unauthorised use or reliance on the contents of this email including any attachments. Except as required by law, the sender does not represent or warrant that the integrity of this email has been maintained or that it is free from errors, viruses, interceptions or interference. Any views expressed by the sender in this email and any attachments are those of the individual sender, except where the sender specifically states them to be the views of a relevant third party.
This notice should not be removed from this email.
***************************************************************************

On 2024-04-19 11:53, Buoro, John wrote:
<snip>
> SSPI Kerberos\NTLM authentication (Windows environment) currently only
> authenticates users, however, it does not authenticate a user against
> an LDAP \ Active Directory group.
<snip>
> Can you please look at making this possible?

Sounds like it'd be pretty useful. :)

Is this something that Harvey Norman would be interested in sponsoring?

   ie. hiring a suitable PostgreSQL developer (not me!) to implement it

There are quite a few skilled PostgreSQL developers around these days,
so (in theory) it shouldn't be *too hard* find someone the right person.

?

Regards and best wishes,

Justin Clift

Hi,

I've dusted off my C books and coded a solution.

In order to achieve the ability to authenticate domain groups in PostgreSQL, I have made changes to the auth.c file attached which is located under \src\backend\libpq\ from source version 16.3.2
I've checked 17 beta 2 and it would easily be able to be inserted in there too.
The changes are in three sections that are bound using the following tags...
// START @@@@@@@@@@@@@@@@@@
// FINISH @@@@@@@@@@@@@@@@@@

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.

The code supplied makes calls to Windows APIs. It does not have code to allow non-Windows systems to make equivalent calls.
The code has directives (#ifdef WIN32 #else #endif) with empty sections defined for non-WIN32 code to go into.

The following explains how SSPI authentication is set up, firstly on the server and then for the clients.
This complete information appears to be missing from nearly all official documentation.

For SSPI to work the pg_hba.conf file on the server needs to be modified (added) with the following line...

# TYPE DATABASE USER ADDRESS METHOD
host all all all sspi include_realm=1

This will allow users from different subdomains to be authenticated - but only after their user or group is defined in the Logins.

When hosting PostgreSQL on a Windows server, please make sure that your server has registered the Service Principle Name (SPN).
This must be done by someone with administrative access to Active Directory from the server itself.

setspn -A postgres/<Server FQDN> <Service Account>
Or the service account is replaced with the hostname if the service is runs as "Network Service".
setspn -A postgres/<Server FQDN> <hostname>

Example:
setspn -S POSTGRES/au.SOMEDOMAIN.com SVRCTSTAP032
setspn -S POSTGRES/SVRCTSTAP032.au.SOMEDOMAIN.com SVRCTSTAP032

Direct assignment of domain users in PostgreSQL Logins is done by adding users using the following format examples:
Admin.Name1@SOMEDOMAIN and NOT SOMEDOMAIN\Admin.Name1 where the distinguished name is CN=Admin Name1,OU=Admins,DC=SOMEDOMAIN,DC=com
Some.Name2@SYDNEY and NOT SYDNEY\Some.Name2 where the distinguished name is CN=Some Name2,OU=Users,DC=SYDNEY,DC=SOMEDOMAIN,DC=com

For users clients like pgAdmin, users will need to edit their server connection properties and enter in the Username field their name formatted as defined above (example Some.Name2@SYDNEY)
They will need to enable Kerberos authentication.

Assignment of GROUPS however in PostgreSQL Logins is done by using the same format as Logins (above). Example:
GRP_IT_DBA@CORP where the distinguished name is CN=GRP_IT_DBA,OU=Permissions,OU=Groups,DC=CORP,DC=SOMEDOMAIN,DC=com
More importantly, for the users clients like pgAdmin, they need to edit their server connection properties and enter in the Username field their account name format.
Example Admin.Name1@SOMEDOMAIN assuming that SOMEDOMAIN\Admin.Name1 is a member of the group CORP\GRP_IT_DBA
Their account is NOT specified in PostgreSQL Logins itself.
Users should not use the group name like GRP_IT_DBA@CORP in the Username field of pgAdmin server connection properties, just use the user's account name.
They will need to enable Kerberos authentication.

Hopefully, someone will be able to pick this up and develop this further.

NOTE: The silly thing with the pgAdmin client is the Server Connection Username MUST be specified, even though you enable Kerberos authentication which should really obtain the credentials used to run the pgAdmin process itself if you don't specify a Username. But that is for someone else to figure out.

Regards,
John Buoro

-----Original Message-----
From: Justin Clift <justin@postgresql.org>
Sent: Friday, April 19, 2024 8:05 PM
To: Buoro, John <John.Buoro@au.harveynorman.com>
Cc: pgsql-general@postgresql.org
Subject: [EXTERNAL] Re: SSPI Feature Request

CAUTION This email originated from an EXTERNAL source. Do not click on any links or open any attachments unless you recognise the sender and know that the content is safe.

On 2024-04-19 11:53, Buoro, John wrote:
<snip>
> SSPI Kerberos\NTLM authentication (Windows environment) currently only
> authenticates users, however, it does not authenticate a user against
> an LDAP \ Active Directory group.
<snip>
> Can you please look at making this possible?

Sounds like it'd be pretty useful. :)

Is this something that Harvey Norman would be interested in sponsoring?

ie. hiring a suitable PostgreSQL developer (not me!) to implement it

There are quite a few skilled PostgreSQL developers around these days, so (in theory) it shouldn't be *too hard* find someone the right person.

?

Regards and best wishes,

Justin Clift

Disclaimer

***************************************************************************
PRIVATE & CONFIDENTIAL
This email may contain legally privileged, confidential information or copyright material of the sender or a third party. This email and any attachments are intended for the addressee(s) only. If you are not the intended recipient, please contact the sender by reply email and delete this email and any attachments immediately. You must not read, copy, use, distribute or disclose the contents of this email or any attachments without the consent of the sender or the relevant third party. The sender does not accept responsibility for any unauthorised use or reliance on the contents of this email including any attachments. Except as required by law, the sender does not represent or warrant that the integrity of this email has been maintained or that it is free from errors, viruses, interceptions or interference. Any views expressed by the sender in this email and any attachments are those of the individual sender, except where the sender specifically states them to be the views of a relevant third party.
This notice should not be removed from this email.
***************************************************************************

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!"

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!"