az postgres flexible-server db create doesn't create public schema

[tobiasdiez]

Describe the bug

When you use az postgres flexible-server db create to create a new database in a flexible postgresql server, the new newly created db doesn't have a public schema. Moreover, if one tries to create a schema afterwards using az postgres flexible-server execute ... -q "create schema public;" then one gets a "permission denied for database xyz" error. Strangely enough, create schema public; works from the psql cloud shell (from the azure portal).

Related command

az postgres flexible-server db create

Errors

There is no error reported. However, there are now schemas created either:

 \dn
List of schemas
 Name | Owner 
------+-------
(0 rows)

or after deleting the "Alle Benutzer" (all users in German) group, a public schema exists but without any of the usual permissions

\dn+ public
                  List of schemas
  Name  | Owner  | Access privileges | Description 
--------+--------+-------------------+-------------
 public | Github |                   | 
(1 row)

(Github is the user that created the db)

Issue script & Debug output

There is no error reported.

Expected behavior

There always should be a public schema.

Environment Summary

azure-cli 2.48.1 *

core 2.48.1 * telemetry 1.0.8

Extensions: communication 1.6.0 rdbms-connect 1.0.3

Dependencies: msal 1.20.0 azure-mgmt-resource 22.0.0

Python location 'C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\python.exe' Extensions directory 'C:\Users\Tobia.azure\cliextensions'

Python (Windows) 3.10.10 (tags/v3.10.10:aad5f6a, Feb 7 2023, 17:05:00) [MSC v.1929 32 bit (Intel)]

Additional context

No response

[azure-client-tools-bot-prd[bot]]

Hi @tobiasdiez,

2.48.1 is not the latest Azure CLI(2.49.0).

Please upgrade to the latest Azure CLI version by following https://learn.microsoft.com/en-us/cli/azure/update-azure-cli.

[yonzhan]

Thank you for opening this issue, we will look into it.

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @sunilagarwal, @lfittl-msft, @sr-msft, @niklarin.

Issue Details

---

### Describe the bug When you use [az postgres flexible-server db create](https://learn.microsoft.com/en-us/cli/azure/postgres/flexible-server/db?view=azure-cli-latest#az-postgres-flexible-server-db-create) to create a new database in a flexible postgresql server, the new newly created db doesn't have a public schema. Moreover, if one tries to create a schema afterwards using `az postgres flexible-server execute ... -q "create schema public;"` then one gets a "permission denied for database xyz" error. Strangely enough, `create schema public;` works from the psql cloud shell (from the azure portal). ### Related command az postgres flexible-server db create ### Errors There is no error reported. ### Issue script & Debug output There is no error reported. ### Expected behavior There always should be a public schema. ### Environment Summary azure-cli 2.48.1 * core 2.48.1 * telemetry 1.0.8 Extensions: communication 1.6.0 rdbms-connect 1.0.3 Dependencies: msal 1.20.0 azure-mgmt-resource 22.0.0 Python location 'C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\python.exe' Extensions directory 'C:\Users\Tobia\.azure\cliextensions' Python (Windows) 3.10.10 (tags/v3.10.10:aad5f6a, Feb 7 2023, 17:05:00) [MSC v.1929 32 bit (Intel)] ### Additional context _No response_

Author:	tobiasdiez
Assignees:	-
Labels:	`bug`, `Service Attention`, `PostgreSQL`, `customer-reported`, `Auto-Assign`, `Auto-Resolve`
Milestone:	-

[navba-MSFT]

Adding Service team to look into this.

[tobiasdiez]

I did a bit more digging and it seems the issue comes from the fact that the db create command creates a new db which is owned by the first (?) AD admin and not by azure_pg_admin as the other db's.

[gcbikram]

@tobiasdiez i observed the same thing, it happens with terraform as well

[schmflorian]

I was able to recreate that behaviour as of today:

• just like @tobiasdiez said previously Databases that are created in the azure portal are owned by the first AD admin and not the azure_pg_admin
• I was able to work around that issue though by using pgAdmin 4 and creating the nessecary databases there

[LandryDubus]

Facing the same permission denied issue when trying to make schema changes on a custom schema after running AZ CLI az postgres flexible-server db create command. The database created seems to be assigned the owner randomly between the AAD admin (rarely) and the internal postgresql admin (often) as both authentication modes are enabled on the server in my case. This is painful as the owner is not always consistent and not always consistent with the admin user used to make the schema changes.

[Va55ago]

This is also tripping us up. We have a pipeline that: 1) Runs an IaC template to create the PostgreSQL Flexible Server instance 2) Runs az postgres flexible-server db create to create the initial database 3) Connects to the database as the local postgres administrator account and runs liquibase update to bring the database schema up-to-date.

Unfortunately, the first time the pipeline runs, it creates the database with a - seemingly - random owner: Either the local postgres admin or one of the Entra admin groups. If the local postgres admin user is chosen then step 3 works fine. If not, then we find that the local postgres admin user has no permission over the database, nor can it grant itself any permissions over the database. The liquibase update in step 3 then fails.

If the azure cli command could be updated so that the local postgres admin user is consistently set as the owner by default, that would be great. As a bonus, the az cli command could also be extended to allow the owner to be specified (to override the default).

[nachoalonsoportillo]

@tobiasdiez, @gcbikram @schmflorian, @LandryDubus, @Va55ago Thanks for reporting this issue. We're assigning as owner of a database created via the Databases API the user returned by the following query:

SELECT usename FROM pg_catalog.pg_user, pg_catalog.pg_group WHERE pg_catalog.pg_user.usesysid = ANY(pg_catalog.pg_group.grolist) and pg_catalog.pg_group.groname='azure_pg_admin' LIMIT 1;

And if that query doesn't return a row, then the owner is set to azuresu.

We're planning to change this behavior so that the owner of all databases created through this API are set to azure_pg_admin.

We'll also evaluate the possibility of changing that API so that it accepts an optional ownerName or ownerOid attribute which, after validating if it's an acceptable owner, we use that one. And, when neither ownerName nor ownerOid are provided, it will always default to azure_pg_admin.