Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using pgadmin as an OAuth2 proxy for PostgreSQL

Using pgadmin as an OAuth2 proxy for PostgreSQL

From
"Rettstadt, Tobias"
Date:

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. ****************************************************************

Re: Using pgadmin as an OAuth2 proxy for PostgreSQL

From
Ron
Date:
Why not authenticate Postgresql users directly to AD using GSSAPI?

On 12/19/22 07:12, Rettstadt, Tobias wrote:
@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;}

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.

Re: Using pgadmin as an OAuth2 proxy for PostgreSQL

From
Samed YILDIRIM
Date:
Hi Tobias, Ron,

On Mon, 19 Dec 2022 at 16:50, Ron <ronljohnsonjr@gmail.com> wrote:
Why not authenticate Postgresql users directly to AD using GSSAPI?

Despite its name, Azure AD is not an Active Directory. It doesn't support LDAP and Kerberos out of box.

I have done the implementation you are looking for. I need to dig into my backups to find it. Therefore, if you are ready to cook by yourself, I'm giving the recipe rather than the food :)

You need to have full control over the operating system that PostgreSQL runs on. Otherwise, it won't work for you.
PostgreSQL supports PAM authentication. And, PAM supports running arbitrary executables thanks to pam_exec module. There are many applications of Azure AD authentication written in many programming languages. I used one[1] of them written in Python as an example. And, I wrote a python script that validates users' credentials. The rest is simple. I created a user group, aad, in PostgreSQL and added developers to that user group. If they are a member of that group, they are authenticated against Azure AD thanks to pg_hba.conf file :) I'm adding 2 lines below as an example.

local      all          +aad                               pam pamservice=postgresql-pam-aad
hostssl    all          +aad       0.0.0.0/0               pam pamservice=postgresql-pam-aad


Best regards.
Samed YILDIRIM


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.

Re: Using pgadmin as an OAuth2 proxy for PostgreSQL

From
Ron
Date:
Why am I not surprised?

On 12/19/22 09:17, Samed YILDIRIM wrote:
Hi Tobias, Ron,

On Mon, 19 Dec 2022 at 16:50, Ron <ronljohnsonjr@gmail.com> wrote:
Why not authenticate Postgresql users directly to AD using GSSAPI?

Despite its name, Azure AD is not an Active Directory. It doesn't support LDAP and Kerberos out of box.


--
Angular momentum makes the world go 'round.

Possible options to import xml files from desktop to postgress/AWS-RDS

From
M Sarwar
Date:
Hello All,
I need to upload the xml files data which are on my desktop or AWS-S3 data into Postgres which is running on AWS RDS.
Hopefully, someone had situation in the past.
Can you please share your experience or thoughts on this?

Thanks a lot,
Sarwar

Re: Possible options to import xml files from desktop to postgress/AWS-RDS

From
Samed YILDIRIM
Date:
Hello Sarwar,
  • What is the structure of your XML files?
  • In which way do you want to store them in PostgreSQL?
  • Do you have any programming experience?
  • ....
There are countless ways to import xml files into PostgreSQL. For example, if you want to keep them as a file, you can store them on a ByteA column or as a large object. Or, if you prefer to keep them as text, you can store them on a text column.

Do you need to parse an XML file and populate the data into a table? You can write a script to parse it and insert into the target table. Or, you can use xml functions of PostgreSQL to parse them by feeding the content of your XML file and directly store their outputs. I can't mention all possibilities here. :) I'm putting some links below. RDS for PostgreSQL has a special extension to access files in S3 buckets. It doesn't support XML. But, it can be useful to fetch data from S3 to feed XML functions in PostgreSQL.



Best regards.
Samed YILDIRIM


On Thu, 22 Dec 2022 at 23:01, M Sarwar <sarwarmd02@outlook.com> wrote:
Hello All,
I need to upload the xml files data which are on my desktop or AWS-S3 data into Postgres which is running on AWS RDS.
Hopefully, someone had situation in the past.
Can you please share your experience or thoughts on this?

Thanks a lot,
Sarwar

Re: Possible options to import xml files from desktop to postgress/AWS-RDS

From
M Sarwar
Date:
Hello Samed,
  1. I am getting XML files which are generated by Oracle Reports.
  2. I need to store them in the columns of Postgress tables which are xml data type. I need to do this because I will be using xmldata queries to populate the same table in the PostreSql.
  3. I have backend programming experience with some exposure to front end programming experience as well.

From: Samed YILDIRIM <samed@reddoc.net>
Sent: Thursday, December 22, 2022 4:17 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Possible options to import xml files from desktop to postgress/AWS-RDS
 
Hello Sarwar,
  • What is the structure of your XML files?
  • In which way do you want to store them in PostgreSQL?
  • Do you have any programming experience?
  • ....
There are countless ways to import xml files into PostgreSQL. For example, if you want to keep them as a file, you can store them on a ByteA column or as a large object. Or, if you prefer to keep them as text, you can store them on a text column.

Do you need to parse an XML file and populate the data into a table? You can write a script to parse it and insert into the target table. Or, you can use xml functions of PostgreSQL to parse them by feeding the content of your XML file and directly store their outputs. I can't mention all possibilities here. :) I'm putting some links below. RDS for PostgreSQL has a special extension to access files in S3 buckets. It doesn't support XML. But, it can be useful to fetch data from S3 to feed XML functions in PostgreSQL.



Best regards.
Samed YILDIRIM


On Thu, 22 Dec 2022 at 23:01, M Sarwar <sarwarmd02@outlook.com> wrote:
Hello All,
I need to upload the xml files data which are on my desktop or AWS-S3 data into Postgres which is running on AWS RDS.
Hopefully, someone had situation in the past.
Can you please share your experience or thoughts on this?

Thanks a lot,
Sarwar

Re: Possible options to import xml files from desktop to postgress/AWS-RDS

From
M Sarwar
Date:
 
Hello Samed,
  1. I am getting XML files which are generated by Oracle Reports.
  2. I need to store them in the columns of Postgress tables which are xml data type. I need to do this because I will be using xmldata queries to populate the same table in the PostreSql.
  3. I have backend programming experience with some exposure to front end programming experience as well.
  4. Yes, our data is primarily going to come thru S3 bucket. I need to explore S3 options as well.

Thanks a lot!
Sarwar



From: Samed YILDIRIM <samed@reddoc.net>
Sent: Thursday, December 22, 2022 4:17 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Possible options to import xml files from desktop to postgress/AWS-RDS
 
Hello Sarwar,
  • What is the structure of your XML files?
  • In which way do you want to store them in PostgreSQL?
  • Do you have any programming experience?
  • ....
There are countless ways to import xml files into PostgreSQL. For example, if you want to keep them as a file, you can store them on a ByteA column or as a large object. Or, if you prefer to keep them as text, you can store them on a text column.

Do you need to parse an XML file and populate the data into a table? You can write a script to parse it and insert into the target table. Or, you can use xml functions of PostgreSQL to parse them by feeding the content of your XML file and directly store their outputs. I can't mention all possibilities here. :) I'm putting some links below. RDS for PostgreSQL has a special extension to access files in S3 buckets. It doesn't support XML. But, it can be useful to fetch data from S3 to feed XML functions in PostgreSQL.



Best regards.
Samed YILDIRIM


On Thu, 22 Dec 2022 at 23:01, M Sarwar <sarwarmd02@outlook.com> wrote:
Hello All,
I need to upload the xml files data which are on my desktop or AWS-S3 data into Postgres which is running on AWS RDS.
Hopefully, someone had situation in the past.
Can you please share your experience or thoughts on this?

Thanks a lot,
Sarwar