Date: Wed, 10 Jul 2024 16:10:12 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: SSPI Feature Request
Message-ID: <20240710141012.lgflly6uaa3oxcng@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <SY7PR01MB90072A084A61E7BE708EA3BEC70D2@SY7PR01MB9007.ausprd01.prod.outlook.com>
 <475d191dec8afe65530e81d1748582ee@postgresql.org>
 <SY7PR01MB9007B2AA3CC9763E13C9B630C7DB2@SY7PR01MB9007.ausprd01.prod.outlook.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="akwwoisnmmnqidcy"
Content-Disposition: inline
In-Reply-To: <SY7PR01MB9007B2AA3CC9763E13C9B630C7DB2@SY7PR01MB9007.ausprd01.prod.outlook.com>
Archived-At: <https://www.postgresql.org/message-id/20240710141012.lgflly6uaa3oxcng%40hjp.at>
Precedence: bulk


--akwwoisnmmnqidcy
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 distinguish=
ed
> 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 use=
r, so
> that for example...
>=20
> SELECT USER;
>=20
> ...will show UserGroupName@SOMEDOMAIN as the user, and NOT
> firstname.lastname@SOMEDOMAIN.
> This is because PostgreSQL appears not to have group support nor the abil=
ity to
> separate user identification and user authentication from what I can see =
in the
> source code.
>=20
> If the user's account (example firstname.lastname@SOMEDOMAIN) is specific=
ally
> listed in the logins as well as the group (example UserGroupName@SOMEDOMA=
IN)
> then it will use the user firstname.lastname@SOMEDOMAIN rather than the g=
roup.
> If there are multiple groups defined in PostgreSQL that the user is a mem=
ber 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

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--akwwoisnmmnqidcy
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmaOljwACgkQ8g5IURL+
KF050Q/8DbGPDWfasgk6myMQ591FznTCc0saJPUFJ2YTAig3H1mQsesRPEMwEbki
VJdahGZOAsXKIQKkbG+gxSjWjJPqiWphvAmWWiHGEFFKzEohHL8cR3IMgT8RvtDD
fQUI79J1zHRoPt/2hvrZwNRdJN0CRPmprfQ1NNMAjPHwQjwMbCT18iSFj1h1umtq
5jMkdAw/pBI9wFrS1Pp6lKg3sfavdzsS6jsMqQIhCuxMP/Q2kJI2ucHq+e3TrTdP
XchQMsPyaBFAiNeGrwLfwVI8gyPUJqz3ecGq1htQFpTDA1hMUayepQt7iaWPPCD0
V706QkCr25CRpKrnv88BC52KqkBhsLxS0qj3F+D77kTtSZ2GC6cww4PQ3pg9vO8f
Q3e9kAnVtdom2dZqRDBUoO6++47GsGJ2WJI/IMv4Iy8spedAeXNnuTRXhN5JRDgr
APdtDygIhChEWGMubHnys/Y8v+a+SJzsnwAxz+837ilIbPPIYojtFdgFLucHTHBa
zXcCSvBKjKwIm64xc8MZXlyRvjZqNOfpm9sWzua9W9HQB3qTkhSRvT+X2p6ikCtk
ybpCaWEDQg7h8MKXV18ll3BRzTRI+pDy8y7B5B7lZLQBT4nBpwQwDkq2r0VJdWl9
vcUu9q8wzL7rIMpMrsW77vyW6kgaqKTWnFt6uS9Oaxpxu8hptiw=
=NgyT
-----END PGP SIGNATURE-----

--akwwoisnmmnqidcy--