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.
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:
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:
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 "firstname.lastname@example.org" 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";