RE: [EXTERNAL] Re: SSPI Feature Request

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(dot)lastname(at)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(at)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(at)SOMEDOMAIN as the user, and NOT firstname(dot)lastname(at)SOMEDOMAIN(dot)
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(dot)lastname(at)SOMEDOMAIN) is specifically listed in the logins as well as the group (example UserGroupName(at)SOMEDOMAIN) then it will use the user firstname(dot)lastname(at)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(dot)Name1(at)SOMEDOMAIN and NOT SOMEDOMAIN\Admin.Name1 where the distinguished name is CN=Admin Name1,OU=Admins,DC=SOMEDOMAIN,DC=com
Some(dot)Name2(at)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(dot)Name2(at)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(at)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(dot)Name1(at)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(at)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(at)postgresql(dot)org>
Sent: Friday, April 19, 2024 8:05 PM
To: Buoro, John <John(dot)Buoro(at)au(dot)harveynorman(dot)com>
Cc: pgsql-general(at)postgresql(dot)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

The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful.

This email has been scanned for viruses and malware, and may have been automatically archived by Mimecast, a leader in email security and cyber resilience. Mimecast integrates email defenses with brand protection, security awareness training, web security, compliance and other essential capabilities. Mimecast helps protect large and small organizations from malicious activity, human error and technology failure; and to lead the movement toward building a more resilient world. To find out more, visit our website.