Azure database for Postgres Flexible server - Why creating extension uuid-ossp requires azure_pg_admin role?

Question

My question is with respect to Azure Database for Postgres Flexible server (version PG16).

I tried to create extension on current database (logged into database as db owner) however I see an error stating "ERROR: Only members of "azure_pg_admin" are allowed to use CREATE EXTENSION"

Where as for uuid-ossp extenstion Azure documentation states “This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

Question : Why do we need to assign azure_pg_admin role to a database owner to create extenstion on his own database? These extensions are already part of available extension list.

m_event_db=> select version();

                               version

PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.2.0, 64-bit

(1 row)

m_event_db=>

m_event_db=> \du;

                                                                         List of roles

     Role name          |                         Attributes                         |

                                                                                                                        Member of

----------------------------+------------------------------------------------------------+----------------------------------------------------------------------------------------------

azure_pg_admin | Cannot login | {pg_read_all_data,pg_monitor,pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend,pg_checkpoint,pg_create_subscription}

azuresu | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

dbadmin | Create role, Create DB, Bypass RLS | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,azure_pg_admin,m_event_db,m_event_db}

m_event_db | | {pg_stat_scan_tables}

m_event_db=> \l

                                                                              List of databases

      Name          |           Owner            | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |                     Access privileges

------------------------+----------------------------+----------+------------+------------+------------+-----------------+-----------------------------------------------------------

                    |                            |          |            |            |            |                 | m_event_db=CTc/m_event_db

                    |                            |          |            |            |            |                 | azure_pg_admin=CTc/azure_pg_admin

                    |                            |          |            |            |            |                 | azure_pg_admin=CTc/azure_pg_admin

m_event_db=> SELECT * FROM pg_extension;

-------+-------------+----------+--------------+----------------+------------+-----------+--------------

14258 | plpgsql | 10 | 11 | f | 1.0 | |

25901 | uuid-ossp | 10 | 2200 | t | 1.1 | |

25912 | pgstattuple | 10 | 2200 | t | 1.5 | |

(3 rows)

m_event_db=> SELECT * FROM pg_available_extensions WHERE name = 'uuid-ossp';

name | default_version | installed_version | comment

-----------+-----------------+-------------------+-------------------------------------------------

uuid-ossp | 1.1 | 1.1 | generate universally unique identifiers (UUIDs)

(1 row)

m_event_db=> SELECT * FROM pg_available_extensions WHERE name = 'pgstattuple';

name     | default_version | installed_version |           comment

-------------+-----------------+-------------------+-----------------------------

pgstattuple | 1.5 | 1.5 | show tuple-level statistics

(1 row)

m_event_db=> CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

ERROR: Only members of "azure_pg_admin" are allowed to use CREATE EXTENSION

m_event_db=> CREATE EXTENSION IF NOT EXISTS pgstattuple;

ERROR: Only members of "azure_pg_admin" are allowed to use CREATE EXTENSION

m_event_db=>

Accepted Answer

Hi @Tulsiraja Shastry,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Why do we need to assign azure_pg_admin role to a database owner to create extenstion on his own database? These extensions are already part of available extension list.

The Azure Database for PostgreSQL flexible server instance is created with the three default roles defined. You can see these roles by running the command: SELECT rolname FROM pg_roles;

azure_pg_admin
azuresu
your server admin user

Your server admin user is a member of the azure_pg_admin role. However, the server admin account isn't part of the azuresu role. Since this service is a managed PaaS service, only Microsoft is part of the super user role.

For more information, please refer the document: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-create-users.

In your case, since you are not a member of the "azure_pg_admin" role, you will need to ask a member of that role to create the extension for you. Alternatively, you can ask your database administrator to grant you the required privileges to create extensions.
You can also use the Azure Portal to create extensions for your Azure Database for PostgreSQL Flexible Server.

For more information, please refer the document: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer

As mentioned in the comments under the accepted answer, this behaviour recently changed (to mitigate a vulnerability actually), but unfortunately the fix caused other problems, as experienced by the OP.
Vote on this feedback post if you want to see this changed:
https://feedback.azure.com/d365community/idea/b50ec30b-4dd8-ef11-95f5-000d3a7e7c18

Share via

Azure database for Postgres Flexible server - Why creating extension uuid-ossp requires azure_pg_admin role?

1 additional answer

Your answer