ERROR: permission denied to create extension "pg_trgm". HINT: Must be part of the azure_pg_admin role to create this extension.

ERROR: permission denied to create extension "pg_trgm". HINT: Must be part of the azure_pg_admin role to create this extension.

The issue happens for Azure Single Postgres Server. (A)

The issue doesn't happen for Azure Flexible Postgres Server. (B)

The issue doesn't happen for regular Postgres Server (C)

(A) Azure Postgres Single Server:

psql -h my_host.postgres.database.azure.com -U my_user@psql-prod-euw-common-2 -d my_db -W

Password:
psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1), server 11.18)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

my_user=> select version();
                           version                          
-------------------------------------------------------------
 PostgreSQL 11.18, compiled by Visual C++ build 1800, 64-bit
(1 row)


my_user=> create table abc(a varchar(100));
CREATE TABLE

my_user=> CREATE EXTENSION IF NOT EXISTS pg_trgm;
ERROR:  permission denied to create extension "pg_trgm"
HINT:  Must be part of the azure_pg_admin role to create this extension.


my_user=> CREATE INDEX CONCURRENTLY IF NOT EXISTS abc_idx ON abc USING gin(LOWER (a) gin_trgm_ops);
ERROR:  operator class "gin_trgm_ops" does not exist for access method "gin"

my_user=> 

(B) Azure Postgres Flexible Server:

azureuser@my-azure-jump-machine:~$ psql -h my-flexdb.postgres.database.azure.com -U my_user -d my_db -W
Password:
psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1), server 13.9)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

my_db=> select version();
                                               version                                              
-----------------------------------------------------------------------------------------------------
 PostgreSQL 13.9 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

my_db=> CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION
my_db=> create table abc(a varchar(100));
CREATE TABLE
my_db=> CREATE INDEX CONCURRENTLY IF NOT EXISTS abc_idx ON abc USING gin(LOWER (a) gin_trgm_ops);
CREATE INDEX
my_db=> \d abc
                        Table "public.abc"
 Column |          Type          | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
 a      | character varying(100) |           |          |
Indexes:
    "abc_idx" gin (lower(a::text) gin_trgm_ops)

my_db=>

(C)

I've installed Postgres via docker-compose.

cat docker-compose.yaml

version: '3'

services:

  pg_db_14:
    image: postgres:14
    container_name: postgres_db_14
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: 123456
      POSTGRES_DB: postgres
    ports:
      - "5434:5432"
    volumes:
      - pg_data_14:/var/lib/postgresql/data

volumes:
  pg_data_14:

postgres=# select version();
                                                           version                                                          
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.7 (Debian 14.7-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

create user my_user with password '1234567';

create database my_db;

grant all privileges on database my_db to my_user;

grant my_user to postgres;

===================================

dmitryr@dmitryr-mac my_postgres_and_my_sql % psql -h localhost -p 5434 -U my_user -d my_db -W

Password:
psql (13.1, server 14.7 (Debian 14.7-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
Some psql features might not work.
Type "help" for help.

my_db=>

===================================

my_db=> CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION
my_db=>

===================================

create table abc(a varchar(100));

my_db=> CREATE INDEX CONCURRENTLY IF NOT EXISTS abc_idx ON abc USING gin(LOWER (a) gin_trgm_ops);
CREATE INDEX
my_db=>

my_db=> \d abc
Table "public.abc"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
a | character varying(100) | | |
Indexes:
"abc_idx" gin (lower(a::text) gin_trgm_ops)

my_db=>

[e-mail]