Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRYIO-00GOTD-2s for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 14:27:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sRYIM-005gbj-M5 for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 14:27:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRYIM-005gbO-6G for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 14:27:46 +0000 Received: from mail-pl1-x635.google.com ([2607:f8b0:4864:20::635]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sRYIJ-001NpK-8g for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 14:27:44 +0000 Received: by mail-pl1-x635.google.com with SMTP id d9443c01a7336-1f4c7b022f8so46403765ad.1 for ; Wed, 10 Jul 2024 07:27:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720621661; x=1721226461; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=TegUjDeTcuqZTU1j0S2j6677UkI9qoGYp8pntuuN7R8=; b=EonmfVWZYJ9/zGtr+ghnDAGF9xuir23hI6RE2QD+ZBzlCE41FO/yKOVuul9tPfpNiy DH+uyclEAcZeKfghmgCMDqbuT1QGo4Vmk0VtTvejXy6IxlhAu0gcA/0PBY+QalvrIuQH tgxGwdAG2LBmC7wKLL8kxS6cfUAidxt+MR0qk9xury5G5u8DgpZVyVZMfIpRs7iIhjtZ nJMoYYKDyDyGB6Vz9b1YG5NcomiW5oHxKXCUVnuPLvpvb+m6U29IyhVLHKhzyho1yUaB b/CNKEch5s0uDU1cwSXrKk/U5wyZQUJe7mji1krqm17iDPpr96xtF4k4QaDa45ObfxXO +bVg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720621661; x=1721226461; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=TegUjDeTcuqZTU1j0S2j6677UkI9qoGYp8pntuuN7R8=; b=qQAV8hxuthJayQa/a9EtEOlM3pkch8DyrHgm3jJZKdag/wnEqWx2i4stNBJEvKZOMq zbf1TiJQhbZejr2yHjQiz2+RhR+FGDCi1k0DHpj2VYfzRiDBL05CymKzGy4cxi0iMoRc QrFgZ7bSQ7AhNptx2zql32emZAtuG7eTcJMfR8fPfNFJ0XxhndVcZjZfjZd2HD/RP0hl ROt1p+gd594yBQTLSj/ei1EJ7CRcWckQiUEFeWmGMtbU0c8c8kehQxH7jV1uiLi8dHCk YZh5Em3xux32tii/ukNXpqhM+kMOac4SfrnbeccUV+37qInTYWcUjOBXhiKb2w9FkM5v 01RA== X-Gm-Message-State: AOJu0YygJ2WuudDx4f4eEDtvDH/wh1wHWIIyuf+dKir6ieHFt2aAR10d 703KL+9xbKJsPC8NdkRzhdqpptCj90+RqOKFTEDS+FAr+/ZNGRX9o61FoaplX6ovXH0QCuxj+aE YWhklY8pBjBYMPSg9n2i3hQuVFFcixQ== X-Google-Smtp-Source: AGHT+IGEw7AICt5HRjVPg8qk9wrVF3O8cqXQ9GVj5WoDcbrHoGuGmLVgOY5psze0m/c0LV2v7X4GWAAwQPANbgv3GQ8= X-Received: by 2002:a17:90a:66c5:b0:2c5:32c3:a777 with SMTP id 98e67ed59e1d1-2ca35c7baefmr4387920a91.28.1720621661174; Wed, 10 Jul 2024 07:27:41 -0700 (PDT) MIME-Version: 1.0 References: <475d191dec8afe65530e81d1748582ee@postgresql.org> <20240710141012.lgflly6uaa3oxcng@hjp.at> In-Reply-To: <20240710141012.lgflly6uaa3oxcng@hjp.at> From: Ian Harding Date: Wed, 10 Jul 2024 07:27:29 -0700 Message-ID: Subject: Re: [EXTERNAL] Re: SSPI Feature Request To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000e84243061ce572c2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e84243061ce572c2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jul 10, 2024 at 7:10=E2=80=AFAM Peter J. Holzer = 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. Exampl= e > > 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 se= e > 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!" 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 =E2=80=9Clo= gin=E2=80=9D with your individual name, you are operating within the database as your individual account. They can do that because they don=E2=80=99t require exi= stence of a named login for the individual. I doubt that=E2=80=99s 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. > > --000000000000e84243061ce572c2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, Jul 10, 2024 at 7:10=E2=80=AFAM 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 nam= e 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. Examp= le
> UserGroupName@SOMEDOMAIN
> It will search Active Directory \ LDAP for the current user's dist= inguished
> name and the domain component (DC) their account is defined in.
> Then it will obtain all the access groups which this account belongs t= o
> (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 a= bility to
> separate user identification and user authentication from what I can s= ee in the
> source code.
>
> If the user's account (example firstname.lastname@SOMEDOMAIN) is s= pecifically
> listed in the logins as well as the group (example UserGroupName@SOMED= OMAIN)
> then it will use the user firstname.lastname@SOMEDOMAIN rather than th= e 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 Activ= e
> 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&#= 39;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:
=C2=A0 =C2=A0 GRANT "group1@some.domain" TO "a.user@some.dom= ain";
=C2=A0 =C2=A0 GRANT "group2@some.domain" TO "a.user@some.dom= ain";
=C2=A0 =C2=A0 ...
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 ma= kes
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.

=C2=A0 =C2=A0 =C2=A0 =C2=A0 hp

--
=C2=A0 =C2=A0_=C2=A0 | Peter J. Holzer=C2=A0 =C2=A0 | Story must make more = sense than reality.
|_|_) |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 |
| |=C2=A0 =C2=A0| hjp@hjp.a= t=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 -- Charles Stross, &q= uot;Creative writing
__/=C2=A0 =C2=A0| http://www.hjp.at/ |=C2=A0 =C2=A0 =C2=A0 =C2=A0challenge!&q= uot;

The solution= proposed is about as close as I think you can get to the Windows reality a= nd 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.=C2=A0

MS SQL Server works like that except that, a= lthough there is no =E2=80=9Clogin=E2=80=9D with your individual name, you = are operating within the database as your individual account. They can do t= hat because they don=E2=80=99t require existence of a named login for the i= ndividual. I doubt that=E2=80=99s possible for PostgreSQL.=C2=A0

As a MS SQL Server admin I can tel= l you that it is a complete mystery how a user gained access to the databas= e in this world. You might be a member of many groups, all of which might h= ave a server login (granted server roles) and be mapped into databases with= potentially differently named database users, while SELECT @@USER will sho= w your actual individual domain user name.=C2=A0
I think this feature would be useful but I think t= he PostgreSQL role -> Active Directory Group mapping is where it should = end. That effectively makes it a shared role, as who the connection was est= ablished as would be lost.=C2=A0

--000000000000e84243061ce572c2--