From: Andrey Chudnovskiy <Andrey.Chudnovskiy@microsoft.com>
To: Jacob Champion <jchampion@timescale.com>, mahendrakar s
	<mahendrakarforpg@gmail.com>
CC: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
	"smilingsamay@gmail.com" <smilingsamay@gmail.com>, "andres@anarazel.de"
	<andres@anarazel.de>, Mahendrakar Srinivasarao <mahendrakars@microsoft.com>
Subject: RE: [EXTERNAL] Re: [PoC] Federated Authn/z with OAUTHBEARER
Thread-Topic: [EXTERNAL] Re: [PoC] Federated Authn/z with OAUTHBEARER
Thread-Index: AQHYzK5Qv3O2da3q3kaTP3NyRDt1m63o9YOAgAEYeACAAFiTcA==
Date: Wed, 21 Sep 2022 22:10:25 +0000
Message-ID: 
 <MN0PR21MB31694BAC193ECE1807FD45358F4F9@MN0PR21MB3169.namprd21.prod.outlook.com>
References: <d1b467a78e0e36ed85a09adf979d04cf124a9d4b.camel@vmware.com>
 <e866a4a3-0ba6-c7ae-3abc-aaff0c033558@iki.fi>
 <2f06a4aef1d5defe4b7aaec01478572e5557d32a.camel@vmware.com>
 <8a5a35b31c3f25f6b047e77def0445a60399981d.camel@vmware.com>
 <CABkiuWrg7eiP=g9pqG9Y3w5SZuj4i=Kmsak6+ex-13xppHaBiA@mail.gmail.com>
 <fa8b5b3a-9f09-b402-20cb-92c35fe24d9f@timescale.com>
 <CAAWbhmhmBe9v3aCffz5j8Sg4HMWWkB5FvTDCSZ_Vh8E1fX91Gw@mail.gmail.com>
In-Reply-To: 
 <CAAWbhmhmBe9v3aCffz5j8Sg4HMWWkB5FvTDCSZ_Vh8E1fX91Gw@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
msip_labels: 
 MSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_ActionId=4df332d6-b80c-45c9-bb5f-340b28856cea;MSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_ContentBits=0;MSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Enabled=true;MSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Method=Standard;MSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_Name=Internal;MSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_SetDate=2022-09-21T21:20:22Z;MSIP_Label_f42aa342-8706-4288-bd11-ebb85995028c_SiteId=72f988bf-86f1-41af-91ab-2d7cd011db47;
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=microsoft.com;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: MN0PR21MB3169:EE_|BYAPR21MB1349:EE_
x-ms-office365-filtering-correlation-id: ea8d49c1-a9e5-44d7-6cfe-08da9c1e15a6
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam: BCL:0;
x-microsoft-antispam-message-info: 
 C7/q9sup00kA0CnRk+UeNzA3QpB9FXIXAY7eDx5a6HJixp6dnkpR1FKjbAk2/kMg9tp56+ZJUh3b55wKp31idEcCWhYzpQLejcq7BAqxEB7VS5M1jqlqMgADywPJCSzExryuKYBXYg4bH+M5Xuhe+jSTPBHjvD6jnYQTmACNu61HRlZz9x1J0aUbr7DuENniXV0/bNL0yzA0pNsXq65UbAhwkk5CCVbIiVscqYx4GFZ4AZZWNTBQ/H9q1P5mj7cKae3WMMF0lnu3+86i0kpEmDX7ZKtoSsMgqSPtCKO37g2hWBLZzyMDLv6vgOWwoY1JgYtECB6X8X7Lu2PV0sw4jBnelZLEAk0HoZBx1D+HE998BtKxfG2VuctRIbbtbtFrfUEApaN3xIIjHQsCFRw3cOeBmWHE+M9J68YMFwu0Xo9Tc3tLdTUOdbxDFvxrMvtyTWOgGNE60mIN/cRIOMQv4iSehJjrPAv+CL7FSrvW0/1Tiagie7EDeEOx04cxnO9iz2q1GCAmCmTTTt8kbB+sM8FLWERQqk15JDS+QrnaoJxSkE0kb8507nUKtxT0uATIrhVFp6+asI5vnmBaP9V6ST43cWg6Eo+W0m+6G/0u6r2ohazWOMAl1Z+dqCyyoG3FhbQUCcNNdHjvFaWhqjMgu7N6L5AZsE/XiMxkvIjTiimy3vDIB83iTdWzLC+cDYclY5tu1tDoTGsa/rn1k5/vUHtIby3/uf+J9ot0MGAcVgaBbczYFvMVt5M3hJVPeCNg/V2lPRItJDfAOVkhAgyGDhTqox+BrcU7UJjGDjP4kZ8VrqpsFeo23Lvn8f0Pm0XC
x-forefront-antispam-report: 
 CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:MN0PR21MB3169.namprd21.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230022)(4636009)(376002)(136003)(39860400002)(366004)(346002)(396003)(451199015)(9686003)(186003)(107886003)(71200400001)(6506007)(7696005)(53546011)(2906002)(26005)(8990500004)(83380400001)(5660300002)(52536014)(66946007)(55016003)(10290500003)(478600001)(110136005)(54906003)(4326008)(66446008)(66556008)(66476007)(76116006)(41300700001)(316002)(8676002)(966005)(64756008)(8936002)(82950400001)(82960400001)(33656002)(86362001)(38070700005)(122000001)(38100700002);DIR:OUT;SFP:1102;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?RkKqNEO1ItTwlPLLRh+yhqubT1PEUBBO2gWZGqEI/aAR17JKteaIg3/QAbmT?=
 =?us-ascii?Q?1ij5yBVY0tZkvAegQrWLa3Cgk9kcXN3gTJvOatc0RLb6476n+iCYspVNB4Zt?=
 =?us-ascii?Q?UDcelvj4GN5opnHZFEqpZUktcHawu5bjSmFErKdKtk8yy8aIkdXklt2bIYEr?=
 =?us-ascii?Q?92uyUuYWex+dKnwWn5W7gJvvdxY18b6LApOfO6N+Z/rU9H5yq7EISDESaad5?=
 =?us-ascii?Q?Lcx3zoU3MMFlkq7mVZZvE2TNTQTEEGojRP7GoUmlR4WGp4cgmCf/rl85RMQ8?=
 =?us-ascii?Q?mvFHgiGxDhwH76xkLQ7cvsPuu3tTJcUczeth3bmGTq9TVz+qFAHGiu741eK5?=
 =?us-ascii?Q?MZdrITvwyo5S4A4BHfTnFpA+0e9RECS1CV4uf1G/kOEXYwzwmixyBwqw/Or4?=
 =?us-ascii?Q?TZ4a5czyyi+/VJ9nrHfDojLVxexiQMuzhnpZwW/Yc3IW1T66GngD/g5VEnkP?=
 =?us-ascii?Q?NALk9PkHcQyc1R1V1HVUPVPMHYN6YoTS3ZW0ndvzh2uxB4wnQKeoOHaUqznB?=
 =?us-ascii?Q?S7azk4+QsHIf02NbsVPQEnB1P/TskjuPtfq/Gp6jlY8wzhTw06gvq0pHNJCB?=
 =?us-ascii?Q?RP/CriOtnqd9IaN4SeIquuNOxKQ8vZqN9AJdoZ48cFyGV/n73uqi5VjlOYA6?=
 =?us-ascii?Q?V6ySEKtAIh7T902ktWbfr+mHykx3KLwiCxLlnGa85ix15BoJtW2fbD5nvxMa?=
 =?us-ascii?Q?r21qkQofTL7HnSayaHK9sQxFHK+uiem4olERaU8QnyEVVB+xBv9vUPNncvHM?=
 =?us-ascii?Q?x+2BbOT2/CXZMW5z2egOZS1RZd/H2A0+lF9P+UzMfQ8rrEgQGsiZAcI562Zg?=
 =?us-ascii?Q?Tsh9J8ChPUPqFTG2th9/DLzFMELkHAQhxnrhKAP22yj0mgKg3pVWEVZn9wTO?=
 =?us-ascii?Q?xKy8ugS6xq/hhqVMWmOW9AyTsrQ4whF3wtL+ovEYvmAim5q5/vxiow+LetUw?=
 =?us-ascii?Q?kd7vd50wwGB45++6M+6UnMg7RWeec394nVLR6J1dzhlPXDYQvaCrpRajJd4u?=
 =?us-ascii?Q?TYcU9qfvYImk0YkbIGeBxvfnnVFKY6RPbko6+jzgizT3e+fb6D7rlXipkfKH?=
 =?us-ascii?Q?w/NqH7ENGlntzHWNUQfZyL/MXO9X8rXCz7C7d8IXFiW3uQK2ETHBIU5NnInQ?=
 =?us-ascii?Q?j5sK/0DFvSBlFxMkrBoD2799K8yH7FJmvysY2fcMphsDfKCtWKjVEIsl20p6?=
 =?us-ascii?Q?rOK86glBz9Jik5l4HkG7n/eKzKamaytV8ENO+5FwA4ETAMYsb2DEazfPJLbA?=
 =?us-ascii?Q?RDntAZDMExg+OqvGyywSVFSnKDGbov6kpLJ5O/LMWi7EBpe6zChcBWRXsUOB?=
 =?us-ascii?Q?GOX3zAIKz6V2Z3PO9rgXcCJX/bi/Am/ea+Z7ZXPxQYvEnxqz2NeSAspbBphx?=
 =?us-ascii?Q?3AV97L36cPYGQbh2QqQEHqrm1sLy8ONTZsPqYCjKkrPHY5Xv7b+PxtwvWhwI?=
 =?us-ascii?Q?xk/hIMlMNjNx6XFlIlGL6+CUw8V3WiiMs78MuvlMcGG75UW+y95Qkzqr2c97?=
 =?us-ascii?Q?CFnwUq+2Qsk7FSDZXukFGEP2LokLf8Ak/2Tt?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: microsoft.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: MN0PR21MB3169.namprd21.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 ea8d49c1-a9e5-44d7-6cfe-08da9c1e15a6
X-MS-Exchange-CrossTenant-originalarrivaltime: 21 Sep 2022 22:10:25.2371
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 72f988bf-86f1-41af-91ab-2d7cd011db47
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 aN1XRiz33qsUtPDFqYY5nrdqKVOTOXwQhPw4ssdWoT9rjnf3GY7baNK1pMU1gk4/MFPItc743OZzv3duXPXGUg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BYAPR21MB1349
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/MN0PR21MB31694BAC193ECE1807FD45358F4F9%40MN0PR21MB3169.namprd21.prod.outlook.com>
Precedence: bulk

We can support both passing the token from an upstream client and libpq imp=
lementing OAUTH2 protocol to obtaining one.

Libpq implementing OAUTHBEARER is needed for community/3rd party tools to h=
ave user-friendly authentication experience:
1. For community client tools, like pg_admin, psql etc.=20
   Example experience: pg_admin would be able to open a popup dialog to aut=
henticate customer and keep refresh token to avoid asking the user frequent=
ly.
2. For 3rd party connectors supporting generic OAUTH with any provider. Use=
ful for datawiz clients, like Tableau or ETL tools. Those can support both =
user and client OAUTH flows.

Libpq passing toked directly from an upstream client is useful in other sce=
narios:
1. Enterprise clients, built with .Net / Java and using provider-specific a=
uthentication libraries, like MSAL for AAD. Those can also support more adv=
ance provider-specific token acquisition flows.
2. Resource-tight (like IoT) clients. Those can be compiled without optiona=
l libpq flag not including the iddawc or other dependency.

Thanks!
Andrey.

-----Original Message-----
From: Jacob Champion <jchampion@timescale.com>=20
Sent: Wednesday, September 21, 2022 9:03 AM
To: mahendrakar s <mahendrakarforpg@gmail.com>
Cc: pgsql-hackers@postgresql.org; smilingsamay@gmail.com; andres@anarazel.d=
e; Andrey Chudnovskiy <Andrey.Chudnovskiy@microsoft.com>; Mahendrakar Srini=
vasarao <mahendrakars@microsoft.com>
Subject: [EXTERNAL] Re: [PoC] Federated Authn/z with OAUTHBEARER

[You don't often get email from jchampion@timescale.com. Learn why this is =
important at https://aka.ms/LearnAboutSenderIdentification ]

On Tue, Sep 20, 2022 at 4:19 PM Jacob Champion <jchampion@timescale.com> wr=
ote:
> > 2.     Add support to pass on the OAuth bearer token. In this
> > obtaining the bearer token is left to 3rd party application or user.
> >
> >         ./psql -U <username> -d 'dbname=3Dpostgres=20
> > oauth_client_id=3D<client_id> oauth_bearer_token=3D<token>
>
> This hurts, but I think people are definitely going to ask for it,=20
> given the frightening practice of copy-pasting these (incredibly=20
> sensitive
> secret) tokens all over the place...

After some further thought -- in this case, you already have an opaque Bear=
er token (and therefore you already know, out of band, which provider needs=
 to be used), you're willing to copy-paste it from whatever service you got=
 it from, and you have an extension plugged into Postgres on the backend th=
at verifies this Bearer blob using some procedure that Postgres knows nothi=
ng about.

Why do you need the OAUTHBEARER mechanism logic at that point? Isn't that i=
dentical to a custom password scheme? It seems like that could be handled c=
ompletely by Samay's pluggable auth proposal.

--Jacob