How to manage PostgreSQL database permissions using Azure AD groups

Introduction

In this blog post, I am going to demonstrate how we can manage database permissions with Azure AD groups.

More specifically we are going to walk through the steps required to give members of one Azure AD group read-only permissions and members of another group read-write permissions to a single database.

This is an often required use-case in corporate environments, that enforces a least privilege model.

This article is based on a blog post by Yaser Raja, which can be found here. I have tested and adapted it to Azure AD environment. ✌🏽

Target architecture

The diagram below depicts the high-level architecture we are going to build.

I assume you've already created a PostgreSQL single server and three Azure AD groups called psql-administrators, psql-demodb-readonly and psql-demodb-readwrite. The group's intended purposes should be obvious from their names 😉

With this cakewalk out of the way, we are going to work through the following high-level steps.

1. Enable Azure AD authentication by configuring the Azure AD settings on the PostgreSQL servers
2. Create a database and roles on the server and lock down access

So let's get to work... 👷🏽‍♂️

Enabling Azure AD Authentication

This is the easiest part. To enable authentication, navigate to Settings > Active Directory admin and add your Azure AD group holding the identities that should be allowed to administrate your PostgreSQL single server.

Some background information

Before you added the Azure AD group, your PostgreSQL single server had the following default roles.

As you can see, your local admin (your_admin) is a member of azure_pg_admin, however, it is not member of the azure_superuser role, which holds the superuser attribute. As this is a managed PaaS service we as a customer don't have full access to every aspect of the server instance.  

☝ In PostgreSQL roles, rules and groups really are the same, with the only difference that users have permission to login by default. You can check for the roles by issuing a \du or SELECT rolname FROM pg_roles; on the psql command prompt.

After you added the Azure AD group, as shown in the screenshot from above, Azure created a couple of additional roles, which are azure_ad_admin, azure_ad_mfa, azure_ad_user and last but not least psql-administrators.

Our role, that maps to the Azure AD group psql-administrators, is a member of azure_ad_admin, which itself is a member of azure_pg_admin. Therefor members of the Azure AD group psql-administrators have the same privileges then the local administrator.

With that in place we are now able to connect with a member identity of psql-administrators.

Connecting with your Azure AD credentials

This high-level diagram from Microsoft perfectly shows how the authentication works.

Following that approach we first need to request an access token from the Microsoft Identity Platform.

☝ You won't be able to access your server via pgAdmin, as it has a hard-coded limit on the password length, which gets exceeded by the access token length. So for the time being we have to stick with psql, Azure Data Studio or DBeaver.

Let's log in first with a member of the administrator's group.

az login --username privileged.user@tenant.com --allow-no-subscriptions

Next, we are going to retrieve an access token and store it in an environment variable called PGPASSWORD, which is a psql convention. As you may have noted I assume you are running the commands from PowerShell on Windows.

$env:PGPASSWORD=$(az account get-access-token --resource https://ossrdbms-aad.database.windows.net --query accessToken --output tsv)

Or alternatively, use the slightly shorter form

$env:PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)

☝ You can double check the received token by examining the environment variable and parsing the content with https://jwt.ms

Finally, we are able to connect using any of the psql variants bellow.

psql "host=psql-azureblue.postgres.database.azure.com user=psql-administrators@psql-azureblue dbname=postgres"

psql -h psql-azureblue.postgres.database.azure \
     -U psql-administrators@psql-azureblue \
     -d postgres
     
psql --host=psql-azureblue.postgres.database.azure \
     --username=psql-administrators@psql-azureblue \
     --dbname=postgres 

☝ Pay close attention to the way the username is structured. As we configured an Azure AD group as the PostgreSQL AD Administrator, we have to use that one as the username. Also we need to append the server name (psql-azureblue in this example).

Create a database and read-only role

Now that we are connected, we are going to create a demodb and add a read-only role that maps to our Azure AD group called psql-demodb-readonly.

postgres=> create database demodb;

☝ You can list the current databases using select datname from pg_database; or alternatively use \l.

For each database you create, PostgreSQL creates a schema named public and grants access to this schema to a special role that's also named public. All new users and roles inherit their permissions.

So don't forget to switch the connection to the new database after creation, as we need to alter the public schema of our demodb.

💡 In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures and operators. A database can contain one or more schemas and each schema belongs to only one database - source

postgres=> \conninfo
You are connected to database "postgres" as user "psql-administrators@psql-azureblue" on host "psql-azureblue.postgres.database.azure.com" (address "1.2.3.4") at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

postgres=> \q 

> psql "host=psql-azureblue.postgres.database.azure.com user=psql-administrators@psql-azureblue dbname=demodb"

demodb=>

Let's make sure that we are the owner of the public schema and have the necessary rights to perform the subsequent commands.

demodb=> alter schema public owner to current_user;

First, we are going to revoke the default create permissions...

demodb=> revoke create on schema public from public; 

And then revoke the public role's ability to connect to the database...

demodb=> revoke all on database demodb from public;

Next, we are going to create a new read-only role and make it member of the existing azure_ad_role. It's important to assign the role the login attribute, as we don't want to create a role for each member of that Azure AD group.

demodb=> create role "psql-demodb-readonly" with login in role azure_ad_user;

Now grant this role permission to connect to our database.

demodb=> grant connect on database demodb to "psql-demo-db-readonly";

And grant the role usage access to the schema.

demodb=> grant usage on schema public to "psql-demo-db-readonly";

Finally, we give the role the required read privilege so members can issue SELECT statements all tables in the database.

demodb=> grant select on all tables in schema public to "psql-demodb-readonly";

As we also want to give read-only access to new tables and views that currently do not exist, we need to alter the default privileges for the schema.

demodb=> alter default privileges in schema public grant select on tables to "psql-demodb-readonly";

Now members of the Azure AD group psql-demodb-readonly can connect and issue SELECT statements on the demodb database.

Create read-write role

Creating a role that allows for reading and writing basically follows the same steps as described above. Let's walk through this quickly.

First, create a role that maps our Azure AD group holding the writers to the PostgreSQL server.

demodb=> create role "psql-demo-readwrite" with login in role azure_ad_user;

Next grant connect permissions to our database.

demodb=> grant connect on database demodb to "psql-demodb-readwrite";

Now we grant usage privileges to schema public.

demodb=> grant usage on schema public to "psql-demodb-readwrite"; 

Allow members of the role to create new objects like tables in the public schema.

grant usage, create on schema public to "psql-demodb-readwrite"; 

Grant the listed privileges for all tables and views in the schema.

grant select, insert, update, delete on all tables in schema public to "pgsql-demodb-readwrite";

Also, grant privileges to tables and views added in the future.

alter default privileges in schema public grant select, insert, update, delete on tables to "psql-demodb-readwrite"; 

Grant permissions to all sequences.

grant usage on all sequences in schema public to "psql-demodb-readwrite";

And for future sequences...

alter default privileges in schema public grant usage on sequences to "psql-demodb-readwrite";

And that's it. Now all members of the Azure AD group psql-demodb-readwrite should have write access to database demodb.

Final thoughts

Access Token Lifetime

Keep in mind, access tokens are rather short-lived. In this case, they are only valid for 5 minutes! So don't be surprised to be confronted with a connection error, when switching from one connection to another using psql. Just retrieve another token and you are good to go!

Giving it a test ride

For sure you'll want to test out if everything works as expected. Keep in mind that you have to adjust your psql connection string accordingly, when connecting with a member Id of the AAD group psql-demodb-readonly or psql-demodb-readwrite respectively.

az account --username limited.readonly.user@azureblue.io \
           --allow-no-subscriptions

$env:PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)

psql "host=psql-azureblue.postgres.database.azure.com user=psql-demodb-readonly@psql-azureblue dbname=demodb"

☝🏽 Note the parameter to user here.

Conclusion

This article became longer than I envisioned while doing my research. However, I found it important to give more details on every step so my readers don't fall for the same traps I did 😫

Although the pattern is not that complicated to follow, it still seems to be error-prone as many things need to be done manually. This, however, can be mitigated by automating the database creation by leveraging e.g. Azure DevOps pipelines and Terraform.

I hope you enjoyed reading this article. In case of any questions leave a comment or drop me a mail. 📫

Stay well, Matthias 😎

Further reading