Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p7Fwn-0005sC-Vn for pgsql-admin@arkaria.postgresql.org; Mon, 19 Dec 2022 13:12:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p7Fwl-00025N-Tf for pgsql-admin@arkaria.postgresql.org; Mon, 19 Dec 2022 13:12:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p7Fwl-00022w-Hr for pgsql-admin@lists.postgresql.org; Mon, 19 Dec 2022 13:12:47 +0000 Received: from mail-2.nordlb.de ([194.8.123.28]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p7Fwi-0005sr-A5 for pgsql-admin@lists.postgresql.org; Mon, 19 Dec 2022 13:12:47 +0000 X-IronPort-AV: E=McAfee;i="6500,9779,10565"; a="10491979" X-IronPort-AV: E=Sophos;i="5.96,255,1665439200"; d="scan'208,217";a="10491979" Received: from unknown (HELO julia-mo1.kbk.nordlb.local) ([10.192.52.92]) by DENLB9070MS015P.kbk.nordlb.local with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 19 Dec 2022 14:12:43 +0100 Received: from denlb9080ms007p (localhost [127.0.0.1]) by julia-mo1.kbk.nordlb.local (Postfix) with ESMTP id 93F2140566F8 for ; Mon, 19 Dec 2022 14:12:42 +0100 (CET) Received: from DENLB9070MS010P.kbk.nordlb.local (DENLB9070MS010P.kbk.nordlb.local [14.192.52.21]) by julia-mo1.kbk.nordlb.local (Postfix) with ESMTPS id 61C8A40566FA for ; Mon, 19 Dec 2022 14:12:42 +0100 (CET) Received: from denlb9070ms012p.kbk.nordlb.local ([14.192.52.41]) by DENLB9070MS016P.kbk.nordlb.local with ESMTP/TLS/ECDHE-RSA-AES128-GCM-SHA256; 19 Dec 2022 14:12:42 +0100 Received: from DENLB9070MS013P.kbk.NordLB.local (14.192.52.42) by DENLB9070MS012P.kbk.NordLB.local (14.192.52.41) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2507.16; Mon, 19 Dec 2022 14:12:41 +0100 Received: from DENLB9070MS013P.kbk.NordLB.local ([14.192.52.42]) by DENLB9070MS013P.kbk.NordLB.local ([14.192.52.42]) with mapi id 15.01.2507.016; Mon, 19 Dec 2022 14:12:41 +0100 From: "Rettstadt, Tobias" To: "'pgsql-admin@lists.postgresql.org'" Subject: Using pgadmin as an OAuth2 proxy for PostgreSQL Thread-Topic: Using pgadmin as an OAuth2 proxy for PostgreSQL Thread-Index: AdkTqMf59vC/ENTtRJC5VJOrz2kTqA== Date: Mon, 19 Dec 2022 13:12:41 +0000 Message-ID: <13f5e9d02dcf428b8e6fe25274ab6a1c@nordlb.de> Accept-Language: de-DE, en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [14.192.52.123] x-tm-as-product-ver: SMEX-14.0.0.3092-9.0.1002-27334.000 x-tm-as-result: No-10--30.358500-8.000000 x-tmase-matchedrid: oa72zvIYYFhEXwnTCGfm3f0peXGEEBlvDvKSaIxu6kpXG3yI9k2vbCpY AmrFxIhiLkTtdgQKNN5Se6rq1H4K1f+z7xWvCPyPzH6d90mb4+KOVGny5q72hiPS9JdK3W4/tAg K4TtXrP1vs7O6BK2XsPvZiwgq9df7/aMKPFRj7xIiPTMUjkOgkooxrw8IqAD9mCNknSXswf8PY7 Ec0tWekwx/sJ74sgV22YheuNJRVHBL457zKCcujwMRX/OkXIq9r1DBYy/XaTVtfzoljzPXO6PKi GylfzQ5l2GUABdhPsxHkly7AY5JBbngHc5D9d6kXKHfOUu3xIDTlIO7ZQZ97wJC0RSJvaysLjCk gV4jGGkpqExN7FiAmz5PjqZ0S6BxYh5uOPXd4paIf3m0sUfx59VYleRFiu07myiLZetSf8nPPeN 6HN6d7N9pjzubZ2rHGkbMgf4CpRh59KgBkV+mwI+e3mN5UrNTzKbs+3eNKmNbly+9qbZa9w== x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No x-tmase-result: 10--30.358500-8.000000 x-tmase-version: SMEX-14.0.0.3092-9.0.1002-27334.000 x-tm-snts-smtp: 54938AD9B0CCC47E6D89F8F77DD82161090747BCDEBDA20C2D78807FCA85B7E32000:9 x-exchange-nodisclaimer: 1 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_000_13f5e9d02dcf428b8e6fe25274ab6a1cnordlbde_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_13f5e9d02dcf428b8e6fe25274ab6a1cnordlbde_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi all, our current project has the requirement that users should get read access t= o our backend databases. The users are stored in Azure AD and my first idea= was to use OAuth2 / OIDC to authenticate the users. Since pgadmin provides= OAuth2 authentication, is it possible to somehow map roles in the access t= oken that we get from Azure AD to a Postgres user in pgadmin, so that the u= sers can just log into using their Azure AD account and then get access to = a number of databases that I have configured? I already tried the OAuth2 login in pgadmin and it's working fine, but I ha= ven't figured out how to deploy the database credentials. I know that I cou= ld use a password file, but it has to be located in the storage directory o= f the user, where he could download it using the storage manager. Since use= rs should not be able to access the password, we cannot use this. It would = also be feasible if the owner of the password would have to enter the passw= ord on the machine of each of the users, but even if I select "Save passwor= d", the password is not saved, even though master passwords and password sa= ving are activated in the config. We are running the latest Docker image dpage/pgadmin4 in a Kubernetes clust= er. The pgadmin version is 6.17. Thanks in advance for your help and best regards, Tobias **************************************************************** Die in die= ser E-Mail enthaltenen Informationen sind vertraulich. Diese E-Mail ist aus= schliesslich fuer den Adressaten bestimmt und jeglicher Zugriff durch ander= e Personen ist nicht zulaessig. Falls Sie nicht einer der genannten Empfaen= ger sind, ist jede Veroeffentlichung, Vervielfaeltigung, Verteilung oder so= nstige in diesem Zusammenhang stehende Handlung untersagt und unter Umstaen= den ungesetzlich. Sollte diese Nachricht nicht fuer Sie bestimmt sein, so b= itten wir Sie, den Absender unverzueglich zu informieren und die E-Mail zu = loeschen. **************************************************************** = The information contained in this e-mail is confidential. This e-mail is in= tended solely for the addressee(s) and may not be accessed by anyone else. = If you are not a named recipient, any disclosure, copying, distribution or = related action is prohibited and might be unlawful. If the e-mail is not in= tended for you, please notify the sender immediately and delete it. *******= ********************************************************* --_000_13f5e9d02dcf428b8e6fe25274ab6a1cnordlbde_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,

 

our current project has the req= uirement that users should get read access to our backend databases. The us= ers are stored in Azure AD and my first idea was to use OAuth2 / OIDC to au= thenticate the users. Since pgadmin provides OAuth2 authentication, is it possible to somehow map roles in the= access token that we get from Azure AD to a Postgres user in pgadmin, so t= hat the users can just log into using their Azure AD account and then get a= ccess to a number of databases that I have configured?

 

I already tried the OAuth2 logi= n in pgadmin and it’s working fine, but I haven’t figured out h= ow to deploy the database credentials. I know that I could use a password f= ile, but it has to be located in the storage directory of the user, where he could download it using the storage manager. Since u= sers should not be able to access the password, we cannot use this. It woul= d also be feasible if the owner of the password would have to enter the pas= sword on the machine of each of the users, but even if I select “Save password”, the password = is not saved, even though master passwords and password saving are activate= d in the config.

 

We are running the latest Docke= r image dpage/pgadmin4 in a Kubernetes cluster. The pgadmin version is 6.17= .

 

Thanks in advance for your help= and best regards,

Tobias

**************************************************************** Die in die= ser E-Mail enthaltenen Informationen sind vertraulich. Diese E-Mail ist aus= schliesslich fuer den Adressaten bestimmt und jeglicher Zugriff durch ander= e Personen ist nicht zulaessig. Falls Sie nicht einer der genannten Empfaenger sind, ist jede Veroeffentli= chung, Vervielfaeltigung, Verteilung oder sonstige in diesem Zusammenhang s= tehende Handlung untersagt und unter Umstaenden ungesetzlich. Sollte diese = Nachricht nicht fuer Sie bestimmt sein, so bitten wir Sie, den Absender unverzueglich zu informieren und die= E-Mail zu loeschen. ******************************************************= ********** The information contained in this e-mail is confidential. This e= -mail is intended solely for the addressee(s) and may not be accessed by anyone else. If you are not a name= d recipient, any disclosure, copying, distribution or related action is pro= hibited and might be unlawful. If the e-mail is not intended for you, pleas= e notify the sender immediately and delete it. ***********************************************************= ***** --_000_13f5e9d02dcf428b8e6fe25274ab6a1cnordlbde_--