Subject: SSPI Feature Request

From: "Buoro, John" <[email-masked]>
Date: Fri, 19 Apr 2024 01:53:17 +0000

Hi,

SSPI Kerberos\NTLM authentication (Windows environment) currently only authenticates users, however, it does not authenticate a user against an LDAP \ Active Directory group.
This makes administration complex because an administrator would need to add\remove each user to\from an instance or if a user changes role then their permissions would need to be altered.
If you have many instances and many users then this becomes a long process which can be prone to error.

Industry best practices would be to define group(s) and assign permissions and roles to these and have SSPI authenticate users against these groups.
The responsibility of granting or altering permissions is at the LDAP \ Active Directory level which is its prime purpose.
This is something that other RDBMS can do and it would make PostgreSQL a far more attractive solution from that perspective.

Can you please look at making this possible?

This has been raised before (below) but nothing has been progressed further...
https://www.postgresql.org/message-id/20201016160029.GO19056%40tamriel.snowman.net

Many thanks.
John.

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.

---

From: Justin Clift <[email-masked]>
Date: Fri, 19 Apr 2024 20:05:26 +1000

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

---

From: "Buoro, John" <[email-masked]>
Date: Tue, 9 Jul 2024 03:35:33 +0000

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 <[email-masked]> 
Sent: Friday, April 19, 2024 8:05 PM
To: Buoro, John <[email-masked]>
Cc: [email-masked]
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.

<snip>

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

---

From: "Peter J. Holzer" <[email-masked]>
Date: Wed, 10 Jul 2024 16:10:12 +0200

> 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.
> It will search Active Directory \ LDAP for the current user's distinguished
> name and the domain component (DC) their account is defined in.
> 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.

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-masked]. A correspnding role in PostgreSQL is automatically
created if it doesn't already exist.

The user's groups are also read from AD: [email-masked],
[email-masked], ... For each of these groups a GRANT is performed:
    GRANT "[email-masked]" TO "[email-masked]";
    GRANT "[email-masked]" TO "[email-masked]";
    ...
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-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Ian Harding <[email-masked]>
Date: Wed, 10 Jul 2024 07:27:29 -0700

On Wed, Jul 10, 2024 at 7:10 AM Peter J. Holzer <[email-masked]> wrote:

> > If there is a match, then that group name will be the identity of the
> user, so
> > that for example...
> >
> > SELECT USER;
> >

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.