These days, we all care about securing access to our data. The last thing we want is someone using a weak password on a database they forgot about, causing a security incident. When using different kinds of databases, managed by different teams, it can be difficult to ensure consistency across your data estate.
To help solve this problem, we’re excited to announce the Public Preview of the Azure Active Directory integration for Azure Database for PostgreSQL.
Through Azure Active Directory you can ensure that you have a single location where you can manage who gets access to your data. You can ensure that password complexity is enforced across the board. When a team member leaves, it's easy to revoke their access from all databases.
Let’s look how it works:
Configuring the Azure AD Administrator
First, we’ll need to configure which Azure AD user, or group, is managing who gets access to our database. We can do this by navigating to our database in the Azure portal, and setting the Azure AD administrator:
Now, we can connect to our database to manage which Azure AD users or groups we’d like to grant access to.
Connecting to your database using an Azure AD token
Now, we’ll need to authenticate using our Azure AD password. Instead of sending this password to our database, we’ll send it to the Azure Active Directory, and retrieve a time-limited token that grants us access.
These are the high-level steps we’ll follow:
First, in order to retrieve the token, we’ll use the Azure CLI.
Step 1: Ensure you are logged in to the Azure CLI:
az login
Step 2: Then call the following to retrieve an access token scoped to Azure Database for PostgreSQL:
az account get-access-token --resource-type oss-rdbms
The results of this is a JSON document, with the following structure:
{
"accessToken": "TOKEN",
"expiresOn": "...",
"subscription": "...",
"tenant": "...",
"tokenType": "Bearer"
}
The part marked here as TOKEN, which will be quite long, is your Azure AD token.
Step 3: Now, in order to connect, we’ll pass this as the password for our database. In the case of psql we’ll use the PGPASSWORD environment variable, due to the very long length of the password:
Windows Example:
set PGPASSWORD=<copy/pasted TOKEN value from step 2>
Linux/macOS Example:
export PGPASSWORD=<copy/pasted TOKEN value from step 2>
Now, we can use psql to login:
psql "host=mydb.postgres... user=user@tenant.onmicrosoft.com@mydb dbname=postgres"
If you’re looking for a good visual client to connect with, we recommend the PostgreSQL extension for Azure Data Studio.
Managing Azure AD user access
Once you are logged in as the Azure AD administrator, you can now create new roles for Azure AD access like this:
CREATE USER "user2@tenant.onmicrosoft.com" WITH LOGIN azure_ad_user;
This will validate that the user exists in the same Azure Active Directory tenant as your administrator, and grant them access to the database.
If you prefer to use a group to manage access, you can use the group name like this:
CREATE USER "DB_Read_Only" WITH LOGIN azure_ad_user;
Now any user that is a member of the group can login to the database. When you want to add someone new to have access, simply add them to the Azure AD group, without going to the database.
Note that permission management is still handled within the database. You can use regular GRANT/REVOKE statements to permit access. For example, we could revoke all privileges for the created group, and only let them access a specific table:
REVOKE ALL ON SCHEMA public FROM "DB_Read_Only";
GRANT SELECT ON analytics_table TO "DB_Read_Only";
Learn More
You can find more details in the official documentation. You can give the Azure AD integration a try today. If you have questions, please reach out to the AskAzureDBforPostgreSQL@service.microsoft.com alias.