![]() ![]() $env:PGPASSWORD=$(az account get-access-token -resource -query accessToken -output tsv) As you may have noted I assume you are running the commands from PowerShell on Windows. Next, we are going to retrieve an access token and store it in an environment variable called PGPASSWORD, which is a psql convention. az login -username -allow-no-subscriptions Let's log in first with a member of the administrator's group. So for the time being we have to stick with psql, Azure Data Studio or DBeaver. ☝ 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. This high-level diagram from Microsoft perfectly shows how the authentication works.įollowing that approach we first need to request an access token from the Microsoft Identity Platform. With that in place we are now able to connect with a member identity of psql-administrators.Ĭonnecting with your Azure AD credentials Therefor members of the Azure AD group psql-administrators have the same privileges then the local administrator. 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. ![]() You can check for the roles by issuing a \du or SELECT rolname FROM pg_roles on the psql command prompt.Īfter 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. ☝ In PostgreSQL roles, rules and groups really are the same, with the only difference that users have permission to login by default. As this is a managed PaaS service we as a customer don't have full access to every aspect of the server instance. Superuser, Create role, Create DB, Replication, Bypass RLSĪs 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. Some background informationīefore you added the Azure AD group, your PostgreSQL single server had the following default roles. 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. Create a database and roles on the server and lock down access.Enable Azure AD authentication by configuring the Azure AD settings on the PostgreSQL servers.With this cakewalk out of the way, we are going to work through the following high-level steps. The group's intended purposes should be obvious from their names ? ![]() 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 diagram below depicts the high-level architecture we are going to build. I have tested and adapted it to Azure AD environment. This article is based on a blog post by Yaser Raja, which can be found here. This is an often required use-case in corporate environments, that enforces a least privilege model. 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. In this blog post, I am going to demonstrate how we can manage database permissions with Azure AD groups. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |