No permissions for postgres extensions in CosmosDB for PostgreSQL

Question

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 default superuser "postgres". If I don't have permissions for extensions, then aren't these docs that say that and pgcrypto is listed supported wrong since I don't permissions to use them even if they are installed? Does anyone know how to use pg extensions with CosmosDB for PostgreSQL?

Update 1

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"
  }

Answer 1

I tried the same and it is working fine for me make sure you also logged in correctly using plsql and default citus user only.

In below screenshot you can see that I logged in my Azure Cosmos DB for PostgreSQL Cluster with default citus user and when I tried to create pgcrypto extension its throwing error as already exist because it is already present.

If you are getting permission denied error, you can first grant create permission to particular user with that database.

GRANT CREATE ON DATABASE database_name to username;

With below code I tested it:

--create a table users:
CREATE TABLE users (  
id SERIAL PRIMARY KEY,  
email TEXT NOT NULL UNIQUE,  
password TEXT NO NULL  
);

--Inser encrypted pasword in it
INSERT INTO users (email, password) VALUES (  
'[email protected]',  
crypt('johnspassword', gen_salt('bf'))  
);

Successful execution:

Note:- here I connected with by default citus database.