search

ericcsinger / ericcsinger.github.io

1 stars 0 forks source link

PGAdmin Azure AD Authentication Token Renewal - Eric's Blog #12

Open utterances-bot opened 10 months ago

utterances-bot commented 10 months ago

PGAdmin Azure AD Authentication Token Renewal - Eric's Blog

Introduction Microsoft recently released Azure Active Directory (AzureAD) authentication support for Azure PostgreSQL Flexible Server (AzPG). I’m a huge proponent of leveraging AzureAD whenever possible, over local accounts, keys, etc. There all sorts of pros to this approach, none of which I’m going to dive into. What we will dive into, is the fact that PGAdmin, the most popular GUI for PostgreSQL, doesn’t natively support Azure AD authentication, and how I worked around that.

https://ericcsinger.com/pgadmin-azure-ad-authentication-token-renewal/

AllenSuttonValocity commented 10 months ago

I have an Azure VM that hosts a Postgres instance, but we want to authenticate users to the DB using Azure credentials.

I can successfully connect to the VM with the AAD SSH extension or the exported credentials from "az ssh config -f " being directly used in OpenSSH.

The AADSSH extension has appropriately set up PAM for AAD credentials and this is used by the ssh daemon for login (port 22)

I would like postgres to use pam authentication also, and I can see attempts to authenticate on the server in the PAM module logs but I can't quite get the correct combination of parameters to authenticate using the public openssh cert.

Do you have any advice on how to amend the Powershell script to have pgAdmin request a token for DB access on port 5432

The line in pg_hba.conf is (with replacements)

hostssl all @ 0.0.0.0/0 pam

ericcsinger commented 10 months ago

Hi!

Thanks for your comment. Unfortunately, I think you're asking about something that's out of my expertise.

I run PaaS offerings of PSQL, nothing inside a VM. More so, I'm anything but a Linux expert :)

That said, you might want to look at setting up SAML authentication via PAM. I think that is effectively what Microsoft is doing in the background of their PaaS offering.

You might also be able to get something working via RADIUS. Check out the article below. Basically configure a RADIUS server to authenticate against AzureAD, and then configure RADIUS in PSQL. https://learn.microsoft.com/en-us/entra/architecture/auth-radius