Configure permissions in Azure Database for PostgreSQL

In this lab exercises, you’ll assign RBAC roles to control access to Azure Database for PostgreSQL resources and PostgreSQL GRANTS to control access to database operations.

Before you start

You need your own Azure subscription to complete this exercise. If you do not have an Azure subscription, create an Azure free trial.

To complete these exercises, you need to install a PostgreSQL server that is connected to Microsoft Entra ID - formerly Azure Active Directory

Create a Resource Group

  1. In a web browser, navigate to the Azure portal. Sign in using an Owner or Contributor account.
  2. Under Azure services, select Resource Groups, then select + Create.
  3. Check that the correct subscription is displayed, then enter the Resource group name as rg-PostgreSQL_Entra. Select a Region.
  4. Select Review + create. Then select Create.

Create an Azure Database for PostgreSQL flexible server

  1. Under Azure services, select + Create a resource.
    1. In Search the Marketplace, type azure database for postgresql flexible server, choose Azure Database for PostgreSQL Flexible Server and click Create.
  2. On the Flexible server Basics tab, enter each field as follows:
    1. Subscription - your subscription.
    2. Resource group - rg-PostgreSQL_Entra.
    3. Server name - psql-postgresql-fx7777 (Server name must be globally unique, so replace 7777 with four random numbers).
    4. Region - select the same region as the resource group.
    5. PostgreSQL version - select 16.
    6. Workload type - Development.
    7. Compute + storage - Burstable, B1ms.
    8. Availability zone - No preference.
    9. High availability - leave unchecked.
    10. Authentication Method - choose PostgreSQL and Microsoft Entra authentication
    11. Set Microsoft Entra admin - select Set admin
      1. Search for your account in Select Microsoft Entra Admins and (o) your account and click Select
    12. In admin username, enter demo.
    13. In password, enter a suitably complex password.
    14. Select Next: Networking >.
  3. On the Flexible server Networking tab, enter each field as follows:
    1. Connectivity method: (o) Public access (allowed IP addresses) and Private endpoint
    2. Public access, select Allow public access to this resource through the internet using a public IP address
    3. Under Firewall rules, select + Add current client IP address, to add your current IP address as a firewall rule. You can optionally name this firewall rule to something meaningful. Also add Add 0.0.0.0 - 255.255.255.255 and click Continue
  4. Select Review + create. Review your settings, then select Create to create your Azure Database for PostgreSQL Flexible server. When the deployment is complete, select Go to resource ready for the next step.

Install Azure Data Studio

To install Azure Data Studio for use with Azure Database for PostgreSQL:

  1. In a browser, navigate to Download and install Azure Data Studio and under the Windows platform, select User installer (recommended). The executable file is downloaded to your Downloads folder.
  2. Select Open file.
  3. The License agreement is displayed. Read and accept the agreement, then select Next.
  4. In Select additional Tasks, select Add to PATH, and any other additions you require. Select Next.
  5. The Ready to Install dialog box is displayed. Review your settings. Select Back to make changes or select Install.
  6. The Completing the Azure Data Studio Setup Wizard dialog box is displayed. Select Finish. Azure Data Studio starts.

Install the PostgreSQL extension

  1. Open Azure Data Studio if it is not already open.
  2. From the left menu, select Extensions to display the Extensions panel.
  3. In the search bar, enter PostgreSQL. The PostgreSQL extension for Azure Data Studio icon is displayed.
  4. Select Install. The extension installs.

Connect to Azure Database for PostgreSQL flexible server

  1. Open Azure Data Studio if it is not already open.
  2. From the left menu, select Connections.
  3. Select New Connection.
  4. Under Connection Details, in Connection type select PostgreSQL from the drop-down list.
  5. In Server name, enter the full server name as it appears on the Azure portal.
  6. In Authentication type, leave Password.
  7. In User name and Password, enter the user name demo and the complex password you created above
  8. Select [ x ] Remember password.
  9. The remaining fields are optional.
  10. Select Connect. You are connected to the Azure Database for PostgreSQL server.
  11. A list of the server databases is displayed. This includes system databases, and user databases.

Create the zoo database

  1. Either navigate to the folder with your exercise script files, or download the Lab2_ZooDb.sql from MSLearn PostgreSQL Labs.
  2. Open Azure Data Studio if it is not already open.
  3. Select File, Open file and navigate to the folder where you saved the script. Select ../Allfiles/Labs/02/Lab2_ZooDb.sql and Open. If a trust warning is displayed select Open.
  4. Run the script. The zoodb database is created.

Create a new user account in Microsoft Entra ID

[!NOTE] In most production or development environments, it is very possible you won’t have the subscription account privileges to create accounts on your Microsoft Entra ID service. In that case, if allowed by your organization, try asking your Microsoft Entra ID administrator to create a test account for you. If you are unable to get the test Entra account, skip this section and continue to the GRANT access to Azure Database for PostgreSQL section.

  1. In the Azure portal, sign in using an Owner account and navigate to Microsoft Entra ID.
  2. Under Manage, select Users.
  3. At the top-left, select New user and then select Create new user.
  4. In the New user page, enter these details and then select Create:
    • User principal name: Choose a Principle name
    • Display Name: Choose a Display Name
    • Password: Untick Auto-generate password and then enter a strong password. Take note of the principal name and password.
    • Click Review + create

    [!TIP] When the user is created, make a note of the full User principal name so that you can use it later to log in.

Assign the Reader role

  1. In the Azure portal, select All resources and then select your Azure Database for PostgreSQL resource.
  2. Select Access control (IAM) and then select Role assignments. The new account doesn’t appear in the list.
  3. Select + Add and then select Add role assignment.
  4. Select the Reader role, and then select Next.
  5. Choose + Select members, add the new account you added in the previous step to the list of members and then select Next.
  6. Select Review + Assign.

Test the Reader role

  1. In the top-right of the Azure portal, select your user account and then select Sign out.
  2. Sign in as the new user, with the user principal name and the password that you noted. Replace the default password if you’re prompted to and make a note of the new one.
  3. Choose Ask me later if prompted for Multi Factor Authentication
  4. In the portal home page, select All resources and then select your Azure Database for PostgreSQL resource.
  5. Select Stop. An error is displayed, because the Reader role enables you to see the resource but not change it.

Assign the Contributor role

  1. In the top-right of the Azure portal, select the new account’s user account and then select Sign out.
  2. Sign in using your original Owner account.
  3. Navigate to your Azure Database for PostgreSQL resource, and then select Access Control (IAM).
  4. Select + Add and then select Add role assignment.
  5. Choose Privileged administrator roles
  6. Select the Contributor role, and then select Next.
  7. Add the new account you previously added to the list of members and then select Next.
  8. Select Review + Assign.
  9. Select Role Assignments. The new account now has assignments for both Reader and Contributor roles.

Test the Contributor role

  1. In the top-right of the Azure portal, select your user account and then select Sign out.
  2. Sign in as the new account, with the user principal name and password that you noted.
  3. In the portal home page, select All resources and then select your Azure Database for MySQL resource.
  4. Select Stop and then select Yes. This time, the server stops without errors because the new account has the necessary role assigned.
  5. Select Start to ensure that the PostgreSQL server is ready for the next steps.
  6. In the top-right of the Azure portal, select the new account’s user account and then select Sign out.
  7. Sign in using your original Owner account.

GRANT access to Azure Database for PostgreSQL

  1. Open Azure Data Studio and connect to your Azure Database for PostgreSQL server using the demo user that you set as the administrator above.

  2. In the query pane, execute this code against postgres database. Twelve user roles should be returned, including the demo role that you’re using to connect:

     SELECT rolname FROM pg_catalog.pg_roles;

  3. To create a new role, execute this code

     CREATE ROLE dbuser WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'R3placeWithAComplexPW!';
 GRANT CONNECT ON DATABASE zoodb TO dbuser;

    [!NOTE] Make sure to replace the password in the script above for a complex password.

  4. To list the new role, execute the above SELECT query in pg_catalog.pg_roles again. You should see the dbuser role listed.

  5. To enable the new role to query and modify data in the animal table in the zoodb database, execute this code against the zoodb database:

     GRANT SELECT, INSERT, UPDATE, DELETE ON animal TO dbuser;

Test the new role

  1. In Azure Data Studio, in the list of CONNECTIONS select the new connection button.
  2. In the Connection type list, select PostgreSQL.
  3. In the Server name textbox, type the fully qualified server name for your Azure Database for PostgreSQL resource. You can copy it from the Azure portal.
  4. In the Authentication type list, select Password.
  5. In the Username textbox, type dbuser and in the Password textbox type the complex password you created the account with.
  6. Select the Remember password checkbox and then select Connect.

  7. Select New query and then execute this code:

     SELECT * FROM animal;

  8. To test whether you have the UPDATE privilege, execute this code:

     UPDATE animal SET name = 'Linda Lioness' WHERE ani_id = 7;
 SELECT * FROM animal;

  9. To test whether you have the DROP privilege, execute this code. If there’s an error, examine the error code:

     DROP TABLE animal;

  10. To test whether you have the GRANT privilege, execute this code:

     GRANT ALL PRIVILEGES ON animal TO dbuser;

These tests demonstrate that the new user can execute Data Manipulation Language (DML) commands to query and modify data but can’t use Data Definition Language (DDL) commands to change the schema. Also, the new user can’t GRANT any new privileges to circumvent the permissions.

Clean-Up

You will not use this PostgreSQL server again so please delete the resource group you created which will remove the server.