Date: Wed, 10 Jul 2024 17:03:39 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: SSPI Feature Request
Message-ID: <20240710150339.abtwoqoz4iym4amh@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>
 <20240710141012.lgflly6uaa3oxcng@hjp.at>
 <CAMR4UwHOHPC2Vvi0qmR3O+Xvs_FgUXO8-obQnZ0-FdBO_EPz8g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="eejhkru3qxy54sb4"
Content-Disposition: inline
In-Reply-To: <CAMR4UwHOHPC2Vvi0qmR3O+Xvs_FgUXO8-obQnZ0-FdBO_EPz8g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240710150339.abtwoqoz4iym4amh%40hjp.at>
Precedence: bulk


--eejhkru3qxy54sb4
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-07-10 07:27:29 -0700, Ian Harding wrote:
>=20
>=20
> On Wed, Jul 10, 2024 at 7:10=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at=
> wrote:
>=20
>     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.
>=20
>     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.
>=20
>     I thought you wanted something like this:
>=20
>     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.
>=20
>     The user's groups are also read from AD: group1@some.domain,
>     group2@some.domain, ... For each of these groups a GRANT is performed:
>     =C2=A0 =C2=A0 GRANT "group1@some.domain" TO "a.user@some.domain";
>     =C2=A0 =C2=A0 GRANT "group2@some.domain" TO "a.user@some.domain";
>     =C2=A0 =C2=A0 ...
>     The roles for these groups might also be automatically created but si=
nce
>     a role without privileges isn't very useful I'm not sure if that makes
>     sense.
[...]
>=20
> The solution proposed is about as close as I think you can get to the Win=
dows
> 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 b=
ut are
> granted access as a member of the global group.=C2=A0
>=20
> MS SQL Server works like that except that, although there is no =E2=80=9C=
login=E2=80=9D with
> your individual name, you are operating within the database as your indiv=
idual
> account. They can do that because they don=E2=80=99t 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=E2=80=99s possible for PostgreSQL.=C2=A0
>=20
> 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

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmaOosMACgkQ8g5IURL+
KF3K0xAAnaZXHViDEIXyiEzrOwP93A8SCRToHw4t0INVPOcywGmdQjFgrlVq1uZV
nfmZ8BxTAJGyQ39Sej1ytKaWqr/cf2aR0aQ4zkFRhXyVQ9L1BKW3VNrPLBIZ4Gui
/BhKzx8x835BQZ1fbYv9XYpNPN46wOcHbDdLKnknOktxwqAOvWbzJ8gKn62RXCqg
Y0NhEa9UgaB+ln1DTnGzAVZirmbnrdKo4SmVyAlf/r+8bG4xf3QNCnV/5+OEpfTW
C7Hekhf7Fxpj+6lggumqFtB+kDdLVYev0Za6qdZRrUyUKmj+Zh+NN1aHzE+Ehg5e
fwontApq8o1Dn6/pyTb7TRfHnMzg5Yn+VCwSAm9VVGToRNKgP4yT7opirDtgqq/a
PTq2NY7q5izqk14L7+mrwGaTDqWGh5xpxptTyHISbusSBG2gRRwY8DYhEhAModzt
zMboEFCPChmvoU2N0vv2la/PQKCvHQtkM6gXIJdBpHQaOb7DPBvxFO7fJHsyk+C3
SvrTM0KnXPph/9028woeXadPgGODzVdpJFNzLT09YC3Sn2lqNDPFTKx0vPI7dknJ
VFQ1wkjbxQR/DGEPN4w/3GMCuM89G5ky9gDXTKWHiPofbBoYZPBKVmYeOx77ml6c
pws9gTTUclqBxa5fJ5v+h6QMLFXoJu/+qFudxK2Lkw4cY5580cw=
=yKv/
-----END PGP SIGNATURE-----

--eejhkru3qxy54sb4--