Non-Admin Users in Azure PostgreSQL

Overview

Enabling uuid_generate_v4() for Non-Admin Users in Azure PostgreSQL

This document provides step-by-step instructions on enabling the use of the uuid_generate_v4() function for non-admin users in an Azure PostgreSQL instance. This function, provided by the uuid-ossp extension, is often used to generate unique identifiers in PostgreSQL.

By following these steps, non-admin users should be able to successfully use the uuid_generate_v4() function in Azure PostgreSQL. This configuration enhances their capability to generate unique identifiers within the database while maintaining a secure permission structure.

Prerequisites

• Access to the PostgreSQL database as an azure_superuser or equivalent admin user.
• A non-admin user for whom the uuid_generate_v4() function needs to be enabled.

Steps

1. Connect to the PostgreSQL Database as a Superuser
   1. Use a database client (e.g., pgAdmin, Azure Data Studio, or psql command-line tool) to log in as a user with the azure_superuser role or equivalent privileges.
2. Install the uuid-ossp Extension
   1. To use uuid_generate_v4(), the uuid-ossp extension must be installed in the PostgreSQL database. Run the following SQL command to create this extension if it is not already installed:
   2. CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
   3. Note: This command needs to be run only once per database. If the extension is already installed, this step can be skipped.
3. Grant Permissions to the Non-Admin User
   1. Once the uuid-ossp extension is installed, grant the necessary permissions to the non-admin user to allow them to use the uuid_generate_v4() function.
   2. Replace your_non_admin_user with the actual username of the non-admin user who needs access:
   3. GRANT USAGE ON SCHEMA public TO your_non_admin_user;
   4. GRANT EXECUTE ON FUNCTION uuid_generate_v4() TO your_non_admin_user;
4. Test Access for the Non-Admin User
   1. To ensure that the non-admin user can use the uuid_generate_v4() function, log in as that user and run the following command:
   2. SELECT uuid_generate_v4();

If everything is set up correctly, this command should generate a UUID without any permission errors.

Troubleshooting

Permission Denied Error

If the non-admin user receives a “permission denied” error, double-check that both GRANT commands were executed correctly and that the non-admin user is specified accurately.

Missing Extension 

If uuid_generate_v4() is not recognized, verify that the uuid-ossp extension has been installed in the database.