No permissions for postgres extensions in CosmosDB for PostgreSQL

Gabriel Chaney 10

I am using CosmosDB for PostgreSQL and I need permissions to pgcrypto pg extension with the default "citus" user but don't. I can't give myself access because I can't log in as the "postgres" user in CosmosDB for PostgreSQL. If I don't have permissions for extensions, then aren't these docs completely wrong? https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/reference-extensions

GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee

2023-07-06T03:00:38.79+00:00
Hi,@Gabriel Chaney Welcome to the MS Q&A platform and Thank you for reaching out with your query regarding CosmosDB for PostgreSQL

I understand that you are having trouble accessing the pgcrypto pg extension with the default "citus" user and are unable to log in as the "postgres" user in CosmosDB for PostgreSQL.

Have you tried this command and still facing the issue? Once you have granted the necessary permissions, you can use the pgcrypto pg extension with the "citus" user.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO citus;

Regards

Geetha

Gabriel Chaney 10

@GeethaThatipatri-MSFT

Thank you for your response. Here's the output from that command:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO citus;
WARNING:  no privileges were granted for "citus_tables"
WARNING:  no privileges were granted for "pg_stat_statements_info"
WARNING:  no privileges were granted for "pg_stat_statements"
WARNING:  no privileges were granted for "pg_buffercache"
GRANT

After running the command, if I right join with the pg_extension table with the role table, here's what I get:

select extname, extversion, rolname FROM pg_extension
right JOIN pg_roles ON pg_extension.extowner = pg_roles.oid order by extname, rolname;
      extname       | extversion |          rolname
--------------------+------------+---------------------------
 btree_gin          | 1.3        | postgres
 btree_gist         | 1.7        | postgres
 citext             | 1.6        | postgres
 citus              | 11.3-1     | postgres
 citus_columnar     | 11.3-1     | postgres
 cube               | 1.5        | postgres
 dblink             | 1.2        | postgres
 earthdistance      | 1.1        | postgres
 fuzzystrmatch      | 1.1        | postgres
 hll                | 2.16       | postgres
 hstore             | 1.8        | postgres
 intarray           | 1.5        | postgres
 ltree              | 1.2        | postgres
 pg_buffercache     | 1.3        | postgres
 pg_cron            | 1.5        | postgres
 pg_freespacemap    | 1.2        | postgres
 pg_partman         | 4.7.2      | postgres
 pg_prewarm         | 1.2        | postgres
 pg_stat_statements | 1.10       | postgres
 pg_trgm            | 1.6        | postgres
 pgaadauth          | 1.0        | postgres
 pgcrypto           | 1.3        | postgres
 pgrowlocks         | 1.2        | postgres
 pgstattuple        | 1.5        | postgres
 plpgsql            | 1.0        | postgres
 semver             | 0.32.0     | postgres
 sslinfo            | 1.2        | postgres
 tablefunc          | 1.0        | postgres
 tdigest            | 1.4.0      | postgres
 topn               | 2.5.0      | postgres
 unaccent           | 1.1        | postgres
 uuid-ossp          | 1.1        | postgres
 vector             | 0.4.2      | postgres
 xml2               | 1.1        | postgres
                    |            | azure_ad_user
                    |            | citus
                    |            | pg_checkpoint
                    |            | pg_database_owner
                    |            | pg_execute_server_program
                    |            | pg_monitor
                    |            | pg_read_all_data
                    |            | pg_read_all_settings
                    |            | pg_read_all_stats
                    |            | pg_read_server_files
                    |            | pg_signal_backend
                    |            | pg_stat_scan_tables
                    |            | pg_write_all_data
                    |            | pg_write_server_files
(48 rows)

Ujjwal, Atul 0 Reputation points

2023-09-20T15:37:56.38+00:00

I am getting same issue when i tried to add "pgvector" with "citus" user.
Is there any solution regarding this ?

1 answer

GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee

2023-07-10T14:11:27.4433333+00:00
Hi @Gabriel Chaney Let me correct my above comment on referring to the documentation

on your first question, you can create pgcrypto by using the below command and then you should be able to use the extension.

CREATE EXTENSION pgcrypto;

Regarding your second question thanks for sharing the additional information

This is absolutely fine, it skipped GRANTS for all managed schemas you will never be able to connect as "postgres" user, as it's not exposed to the end user

I hope this information helps.

Regards

Geetha
Please sign in to rate this answer.
Gabriel Chaney 10 Reputation points

2023-07-12T18:25:13.4233333+00:00

Yes thank you. I knew about the warnings not being a blocker. To add more context, I am using Hasura GraphQL to build the database schema. It is running in an Azure Container Instance and takes the "citus" connection string as an environment variable:

HASURA_GRAPHQL_DATABASE_URL="postgres://citus:mypassword@my-cosmos-pg-db.tkgkgkjgkjkj.postgres.cosmos.azure.com:5432/citus?sslmode=require"

The Hasura migration functionality is what requires pgcrypto. When I run:

hasura migrate apply --endpoint http://my-hasura-engine.eastus.azurecontainer.io --admin-secret myadminsecret

I get this error:

{ "error": "query execution failed", "path": "$", "code": "postgres-error", "internal": { "arguments": [], "error": { "description": null, "exec_status": "FatalError", "hint": null, "message": "must be owner of extension pgcrypto", "status_code": "42501" }, "prepared": false, "statement": "SET check_function_bodies = false;\nCREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;\nCOMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';\n" }

Gabriel Chaney 10 Reputation points

2023-07-14T05:46:15.8133333+00:00

To give more context, I am using Hasura GraphQL to build the database schema. It is running in an Azure Container Instance and takes the "citus" connection string as an environment variable:

HASURA_GRAPHQL_DATABASE_URL="postgres://citus:mypassword@my-cosmos-pg-db.tkgkgkjgkjkj.postgres.cosmos.azure.com:5432/citus?sslmode=require"

The Hasura migration functionality is what requires pgcrypto. When I run:

hasura migrate apply --endpoint http://my-hasura-engine.eastus.azurecontainer.io --admin-secret myadminsecret

I get this error:

{ "error": "query execution failed", "path": "$", "code": "postgres-error", "internal": { "arguments": [], "error": { "description": null, "exec_status": "FatalError", "hint": null, "message": "must be owner of extension pgcrypto", "status_code": "42501" }, "prepared": false, "statement": "SET check_function_bodies = false;\nCREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;\nCOMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';\n" }

Is this happening because of the comment?

GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee

2023-07-18T16:36:47.65+00:00

@Gabriel Chaney Thanks for sharing additional information, in this case, I would recommend raising a support case to investigate more.

Regards

Geetha

Susan Bartholow 0 Reputation points

2023-07-27T19:43:13.1733333+00:00

I am seeing something similar. The application is attempting to change the schema but getting an error. Is there a support ticket I can follow?

Message : ERROR: must be owner of extension ltree

AJITH KUMAR RAI 0 Reputation points

2024-09-18T16:13:14.6333333+00:00

We are trying to execute the following command to create the extension:

CREATE EXTENSION vector;

But getting below error:

ERROR: permission denied to create extension "vector"

HINT: Must be superuser to create this extension. SQL state: 42501
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

No permissions for postgres extensions in CosmosDB for PostgreSQL

1 answer

Your answer