Content-Type: multipart/alternative;
 boundary="------------CWNKn97mKtxIMI4yWxrbi01i"
Message-ID: <63e54f32-14cb-6f77-02b2-d42f094b45c9@gmail.com>
Date: Mon, 19 Dec 2022 08:49:51 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.2.2
Subject: Re: Using pgadmin as an OAuth2 proxy for PostgreSQL
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <13f5e9d02dcf428b8e6fe25274ab6a1c@nordlb.de>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: <13f5e9d02dcf428b8e6fe25274ab6a1c@nordlb.de>
Archived-At: 
 <https://www.postgresql.org/message-id/63e54f32-14cb-6f77-02b2-d42f094b45c9%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------CWNKn97mKtxIMI4yWxrbi01i
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Why not authenticate Postgresql users directly to AD using GSSAPI?

On 12/19/22 07:12, Rettstadt, Tobias wrote:
>
> Hi all,
>
> our current project has the requirement that users should get read access 
> to 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 token that we get from Azure AD to a Postgres user in pgadmin, so 
> that the users 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 
> haven’t figured out how to deploy the database credentials. I know that I 
> could use a password file, but it has to be located in the storage 
> directory of the user, where he could download it using the storage 
> manager. Since users 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 password 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 activated in the config.
>
> We are running the latest Docker 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 
> dieser E-Mail enthaltenen Informationen sind vertraulich. Diese E-Mail ist 
> ausschliesslich fuer den Adressaten bestimmt und jeglicher Zugriff durch 
> andere Personen ist nicht zulaessig. Falls Sie nicht einer der genannten 
> Empfaenger sind, ist jede Veroeffentlichung, Vervielfaeltigung, Verteilung 
> oder sonstige in diesem Zusammenhang stehende 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 named recipient, any disclosure, copying, 
> distribution or related action is prohibited and might be unlawful. If the 
> e-mail is not intended for you, please notify the sender immediately and 
> delete it. **************************************************************** 

-- 
Angular momentum makes the world go 'round.
--------------CWNKn97mKtxIMI4yWxrbi01i
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    Why not authenticate Postgresql users directly to AD using GSSAPI?<br>
    <br>
    <div class="moz-cite-prefix">On 12/19/22 07:12, Rettstadt, Tobias
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:13f5e9d02dcf428b8e6fe25274ab6a1c@nordlb.de">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <meta name="Generator" content="Microsoft Word 15 (filtered
        medium)">
      <style>@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:#0563C1;
	text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:#954F72;
	text-decoration:underline;}span.E-MailFormatvorlage17
	{mso-style-type:personal-compose;
	font-family:"Calibri",sans-serif;
	color:windowtext;}.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri",sans-serif;
	mso-fareast-language:EN-US;}div.WordSection1
	{page:WordSection1;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
      <div class="WordSection1">
        <p class="MsoNormal">Hi all,<o:p></o:p></p>
        <p class="MsoNormal"><o:p> </o:p></p>
        <p class="MsoNormal"><span lang="EN-GB">our current project has
            the requirement that users should get read access to 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 token that we
            get from Azure AD to a Postgres user in pgadmin, so that the
            users can just log into using their Azure AD account and
            then get access to a number of databases that I have
            configured?<o:p></o:p></span></p>
        <p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
        <p class="MsoNormal"><span lang="EN-GB">I already tried the
            OAuth2 login in pgadmin and it’s working fine, but I haven’t
            figured out how to deploy the database credentials. I know
            that I could use a password file, but it has to be located
            in the storage directory of the user, where he could
            download it using the storage manager. Since users 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 password 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 activated in the config.<o:p></o:p></span></p>
        <p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
        <p class="MsoNormal"><span lang="EN-GB">We are running the
            latest Docker image dpage/pgadmin4 in a Kubernetes cluster.
            The pgadmin version is 6.17.<o:p></o:p></span></p>
        <p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
        <p class="MsoNormal"><span lang="EN-GB">Thanks in advance for
            your help and best regards,<o:p></o:p></span></p>
        <p class="MsoNormal"><span lang="EN-GB">Tobias<o:p></o:p></span></p>
      </div>
      ****************************************************************
      Die in dieser E-Mail enthaltenen Informationen sind vertraulich.
      Diese E-Mail ist ausschliesslich fuer den Adressaten bestimmt und
      jeglicher Zugriff durch andere Personen ist nicht zulaessig. Falls
      Sie nicht einer der genannten Empfaenger sind, ist jede
      Veroeffentlichung, Vervielfaeltigung, Verteilung oder sonstige in
      diesem Zusammenhang stehende 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 named recipient, any
      disclosure, copying, distribution or related action is prohibited
      and might be unlawful. If the e-mail is not intended for you,
      please notify the sender immediately and delete it.
      ****************************************************************
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------CWNKn97mKtxIMI4yWxrbi01i--