💾Setup Database - Part 2

This page will guide you to set up group permissions in the database.

Granting access to your Entra ID Group

• Connect to the database using the AZ Auth connection, to be able to grant access to your Entra ID Group. Right click on MRPN Database - AZ Auth and click on New query. This will open query processor for you.

• In the query processor, execute the following command, don't forget to replace mrpn_database_users with your group's name:

SELECT * FROM pgaadauth_create_principal('mrpn_database_users', false, false);

• Now clear the query processor and then run this command to verify if the role was added with correct permissions:

SELECT * FROM pgaadauth_list_principals(false);

• Once you confirm that your group has been added correctly (make sure it says 0 in ismfa and in isadmin columns) for your newly added group.

• Now close the query processor without saving the query.

Granting read-write access to your group

By default, when you add a group, it grants no access to the group for your database. To allow read-write access to your group, you need to log in as DB Admin and grant the access to your group.

• Double-click on MRPN Database - DB Admin connection, select your database (mrpn in this example) and click on New query. This will open query processor for you.

• Run the following commands to grant write access to your group:

Make sure to replace mrpn with your database name and mrpn_database_users with your Entra ID group name.

GRANT CONNECT ON DATABASE mrpn TO mrpn_database_users;

GRANT USAGE ON SCHEMA public TO mrpn_database_users;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mrpn_database_users;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO mrpn_database_users;

These commands will grant write access to all the tables in the public schema to your group. You can customize these commands to make them more restrictive in nature. These commands also allow access to your group for any tables created in future inside public schema.

• If you see Query execution finished without any errors, that means your role now has full read write access to your database!