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 1sRaJs-00Gilc-Sb for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 16:37:29 +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 1sRaJr-007KUT-8n for pgsql-general@arkaria.postgresql.org; Wed, 10 Jul 2024 16:37:27 +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 1sRaJq-007KSz-LM for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 16:37:26 +0000 Received: from mail-pg1-x52c.google.com ([2607:f8b0:4864:20::52c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sRaJm-001Oup-6f for pgsql-general@lists.postgresql.org; Wed, 10 Jul 2024 16:37:25 +0000 Received: by mail-pg1-x52c.google.com with SMTP id 41be03b00d2f7-70b2421471aso3549053a12.0 for ; Wed, 10 Jul 2024 09:37:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720629441; x=1721234241; 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=sEMOFV76t8UZkk2pEEKrH2eELc3rmWgSdCkecBudN2g=; b=KBAF7SIpNM4IFUiUi7YhaZl7s8LP/ire9w8y1Vmfz1xI259WbADHHaNxdseUwRfNL/ LWJaHTpr3/B70ODKtqSzN8VSb/ler3urUse1nf+Frf8eN7EqK0VWmRreL4p+eJry4sa2 19/WDpsEq7EFUAI8mu8qmL+bK6GdnyiApCvEco1a76NJi66yEuY+KYMd2m4QZlzY/BfH kNpl4gwsOI+E07AY264KAoFAekiE/nqC9Faxg1GdRZFCF5mrYktAJqk9DRNWTTC4MR1o VwFtrTAggI5gv2QBMeRaeZjjp+w8MQmB6oicx09QShU2Bh+n8fNw36HxtOmuoNX5/b5Q SojQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720629441; x=1721234241; 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=sEMOFV76t8UZkk2pEEKrH2eELc3rmWgSdCkecBudN2g=; b=GS35x5YJjjw+yA3v/nScKJO7JcqDoBwbG1IdcAAdwVU9GRPoGET8lxHNuxZQb77ezF v8VZVWoePiyk0XSHIXWd55dGhk8Etz/UYMUJt3OAIi6QqcJzBNrzpIoGFVG/VD+o6q/J UULfjhAvN53HJZF6Iu3f8RGwRnR3HHubtw/CfkpdslGHi0ngi9SDLjHqft3GVz16FBvN DwCu7PccS+frDRGv+mLfZqTuH/ZFfjj6QRSC+aOJV/az4FApHwy3EXoGhvsOnV7cHTh8 Ye8wtjuiXm7LZYqvZwEwSOh9zY6HmzIlJ58WgE0udNJpf/xMXypMRzznTKMTnTBzDh/p uZcg== X-Gm-Message-State: AOJu0YzPIoNKxezeBGGMIJOyV9RO4QhBLUkv0i48X2Kb8uyhlCTuQV9i iTiQVZQTq7eLyr/bj66fw2qM6uDBCu8oCAe6h/3iprXoaDe8V7wO0u4Qz7tYEHRben0BdDgQZ/0 kh8FimHU9E3ISKQAvxTRZ86yvcmzGfg== X-Google-Smtp-Source: AGHT+IGzn7+BDrVxFINfAlJWIoNQMEuz9wRx7eQZsu3CxVhs9veTEw73a4Su0KTiUAtLNhPhpX9E0eKt2nCw+GGWIjs= X-Received: by 2002:a05:6a20:4303:b0:1c0:bf35:ef4c with SMTP id adf61e73a8af0-1c29821493emr7102471637.11.1720629440740; Wed, 10 Jul 2024 09:37:20 -0700 (PDT) MIME-Version: 1.0 References: <475d191dec8afe65530e81d1748582ee@postgresql.org> <20240710141012.lgflly6uaa3oxcng@hjp.at> <20240710150339.abtwoqoz4iym4amh@hjp.at> In-Reply-To: <20240710150339.abtwoqoz4iym4amh@hjp.at> From: Ian Harding Date: Wed, 10 Jul 2024 09:37:08 -0700 Message-ID: Subject: Re: [EXTERNAL] Re: SSPI Feature Request To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000009b06a4061ce74224" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009b06a4061ce74224 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Windows somehow aggregates the permissions allowed for all the Server Principals (logins) associated with global groups of which your account is a member. It=E2=80=99s a disaster. We would shortcut that disaster by makin= g a single group a PostgreSQL login. It would be bad, but not as awful as SQL Server. It would basically be a shared PostgreSQL role that members could connect as with their windows account Kerberos token. On Wed, Jul 10, 2024 at 8:03=E2=80=AFAM Peter J. Holzer = wrote: > On 2024-07-10 07:27:29 -0700, Ian Harding wrote: > > > > > > 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. 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. > > > > 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. > [...] > > > > The solution proposed is about as close as I think you can get to the > Windows > > 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 > 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= =9Clogin=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 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. > > > > 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 > > -- > _ | Peter J. Holzer | Story must make more sense than reality. > |_|_) | | > | | | hjp@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" > --0000000000009b06a4061ce74224 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Windows somehow aggregates the permissions allowed for al= l the Server Principals (logins) associated with global groups of which you= r account is a member. It=E2=80=99s a disaster. We would shortcut that disa= ster by making a single group a PostgreSQL login.=C2=A0

It would be bad, but not as awful as SQL Se= rver. It would basically be a shared PostgreSQL role that members could con= nect as with their windows account Kerberos token.=C2=A0=C2=A0
On Wed, = Jul 10, 2024 at 8:03=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2024-07-10 07:27:29 -0700, Ian Harding wrote:
>
>
> On Wed, Jul 10, 2024 at 7:10=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:=
>
>=C2=A0 =C2=A0 =C2=A0On 2024-07-09 03:35:33 +0000, Buoro, John wrote: >=C2=A0 =C2=A0 =C2=A0> I've dusted off my C books and coded a sol= ution.
>=C2=A0 =C2=A0 =C2=A0[...]
>=C2=A0 =C2=A0 =C2=A0> When using SSPI you can grant access to a user= by giving the
>=C2=A0 =C2=A0 =C2=A0> login name as firstname.lastname@SOMEDOMAIN fo= r example.
>=C2=A0 =C2=A0 =C2=A0> PostgresSQL has no concept of groups, just rol= es. The code
>=C2=A0 =C2=A0 =C2=A0> provided allows you to specify a group name as= a login. Example
>=C2=A0 =C2=A0 =C2=A0> UserGroupName@SOMEDOMAIN It will search Active= Directory \ LDAP
>=C2=A0 =C2=A0 =C2=A0> for the current user's distinguished name = and the domain
>=C2=A0 =C2=A0 =C2=A0> component (DC) their account is defined in. Th= en it will obtain
>=C2=A0 =C2=A0 =C2=A0> all the access groups which this account belon= gs to (excluding
>=C2=A0 =C2=A0 =C2=A0> mail groups). It will compare the group name w= ith what is
>=C2=A0 =C2=A0 =C2=A0> defined in ProgreSQL. If there is a match, the= n that group name
>=C2=A0 =C2=A0 =C2=A0> will be the identity of the user, so that for = example...
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> SELECT USER;
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> ...will show UserGroupName@SOMEDOMAIN as the u= ser, and NOT
>=C2=A0 =C2=A0 =C2=A0> firstname.lastname@SOMEDOMAIN. This is because= PostgreSQL
>=C2=A0 =C2=A0 =C2=A0> appears not to have group support nor the abil= ity to separate
>=C2=A0 =C2=A0 =C2=A0> user identification and user authentication fr= om what I can see
>=C2=A0 =C2=A0 =C2=A0> in the source code.
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> If the user's account (example firstname.l= astname@SOMEDOMAIN) is
>=C2=A0 =C2=A0 =C2=A0> specifically listed in the logins as well as t= he group (example
>=C2=A0 =C2=A0 =C2=A0> UserGroupName@SOMEDOMAIN) then it will use the= user
>=C2=A0 =C2=A0 =C2=A0> firstname.lastname@SOMEDOMAIN rather than the = group. If there
>=C2=A0 =C2=A0 =C2=A0> are multiple groups defined in PostgreSQL that= the user is a
>=C2=A0 =C2=A0 =C2=A0> member of then the code will use the first mat= ching group as
>=C2=A0 =C2=A0 =C2=A0> obtained from Active Directory \ LDAP. It will= not work out
>=C2=A0 =C2=A0 =C2=A0> which group has the most \ highest privileges.=
>
>=C2=A0 =C2=A0 =C2=A0I am confused. This doesn't seem to be what you= were asking for and I'm
>=C2=A0 =C2=A0 =C2=A0also unsure what scenario this is trying to address= .
>
>=C2=A0 =C2=A0 =C2=A0I thought you wanted something like this:
>
>=C2=A0 =C2=A0 =C2=A0A user can authenticate with their AD name (DN, URN= , or whatever), e.g.
>=C2=A0 =C2=A0 =C2=A0a.user@some.domain. A correspnding role in PostgreS= QL is automatically
>=C2=A0 =C2=A0 =C2=A0created if it doesn't already exist.
>
>=C2=A0 =C2=A0 =C2=A0The user's groups are also read from AD: group1= @some.domain,
>=C2=A0 =C2=A0 =C2=A0group2@some.domain, ... For each of these groups a = GRANT is performed:
>=C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 GRANT "group1@some.domain" = TO "a.user@some.domain";
>=C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 GRANT "group2@some.domain" = TO "a.user@some.domain";
>=C2=A0 =C2=A0 =C2=A0=C2=A0 =C2=A0 ...
>=C2=A0 =C2=A0 =C2=A0The roles for these groups might also be automatica= lly created but since
>=C2=A0 =C2=A0 =C2=A0a role without privileges isn't very useful I&#= 39;m not sure if that makes
>=C2=A0 =C2=A0 =C2=A0sense.
[...]
>
> The solution proposed is about as close as I think you can get to the = Windows
> 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 t= o 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 selve= s but are
> granted access as a member of the global group.=C2=A0
>
> MS SQL Server works like that except that, although there is no =E2=80= =9Clogin=E2=80=9D with
> your individual name, you are operating within the database as your in= dividual
> 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 sy= nonyms in this
case since clearly "login" can't mean "the act of loggin= g in" here)?
Or is it important that the login is not "named"? That seems weir= d to
me too since each active directory user has a name (or three).


> I doubt that=E2=80=99s possible for PostgreSQL.=C2=A0
>
> 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.

=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;
--0000000000009b06a4061ce74224--