Azure/PostreSqlFlexDb

* PostreSql db managed by Azure.

• Authentication can use db users or Azure Entra ID's

• https://learn.microsoft.com/en-us/cli/azure/postgres/flexible-server?view=azure-cli-latest

export PGSERVER="test-psql"
export PGHOST=${PGSERVER}.postgres.database.azure.com
export PGUSER=<user>@<org>.co.nz
export PGPORT=5432
export PGDATABASE=testdb
# export PGPASSWORD="<Token>"
export PGPASSWORD="$(az account get-access-token --tenant 123...789 --resource-type 'oss-rdbms' --query accessToken | jq -r '.')"

psql
/d

PSQL commands

• \l - list db's
• \du - list user roles

• See Azure AD Roles in PG

  postgres=> select * from pgaadauth_list_principals(false);
         rolname        | principaltype |               objectid               |               tenantid               | ismfa | isadmin
  ----------------------+---------------+--------------------------------------+--------------------------------------+-------+---------
   me@org.co.nz         | user          | f46..........495c                    | 7c7...59c                            |     0 |       1
  (1 row)

  • ismfa = Is Multi factor enforced for role, isadmin = Azure Ad Admin role, can create other Azure AD enabled roles
  • principaltype = user, service(App'sManagedIdentities), group(Can contain users or service principals)

  • Object ID - Guid of User, Group or ServicePrincipal, use "Enterprise Applications" in portal to find Service Principal Object Id.

  • One PG Role -> Azure ID

• To connect VM with managed identity to PGSQL flex
  1. Create VM with managed identity e.g. myVMxyz

  2. from admin psql in portal:

     select * from pgaadauth_create_principal ('myVMxyz', false, false);
     select * from pgaadauth_list_principals(false);

  3. az postres flexible-server firewall-rule create --resource-group "rg" --name "myVMxyz" --rule-name "allow-app" --start-ip-address "<vm_ip>" --end_ip-address "<vm_ip"

  4. export url="http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://ossrdbms-add.database.windows.net/"

• YouTube Azure AD auth, with VM example getting Password from local url - https://youtu.be/uBd13oLQ1cg?feature=shared

Azure Impersonation / Delegated Auth

• Get user token, then Delegated Auth, Use User+On-behalf token. see. https://youtu.be/uBd13oLQ1cg?feature=shared

Azure PSQL Errors

• During pg_restore to Azure PGSQL Flex

  • Error: "pg_restore: error: could not execute query: ERROR:  must be able to SET ROLE "netbox"

• During pg_resore as role=azure_pg_admin

  • pg_restore: error: could not execute query: ERROR:  must be owner of table dcim_moduletype
    Command was: ALTER TABLE public.dcim_moduletype OWNER TO netbox;

• Fix
  • Try1

    1. Login with psql -d postgres -u <EntraID>

    2. => SET ROLE azure_pg_admin; // later => RESET ROLE;

       • => SET ROLE psqladmin; // ERROR: permission denied to set role "psqladmin"

  • Try2

    1. Login with psql -d postgres -u <EntraID>

    2. => DROP ROLE netbox; // ⛔️

  • Try3
    1. Login with psql -U psqladmin -D postgres

    2. => DROP ROLE netbox; // ✅ DROP ROLE

       • Worked!!!

    3. Logout and log back in with <EntraID> $ psql -d postgres -u <EntraID>

    4. => CREATE USER netbox CREATEDB CREATEROLE PASSWORD ; // ✅ CREATE ROLE

    5. => SET ROLE netbox; // ⛔️ ERROR: permission denied to set role "netbox"

    6. => SET ROLE azure_pg_admin; // ✅ SET

    7. => SET ROLE netbox; // ⛔️ ERROR: permission denied to set role "netbox"

    8. Logout and log back in as "netbox"

    9. => CREATE DATABASE netbox; // ✅ CREATE DATABASE

    10. => \l // ✅ DB netbox Owner: netbox

    11. logout
    12. restore using pg_restore
        • $ pg_restore -c -U netbox --dbname=netbox -v netbox_20250203_15h19+1300.psql.tar
  • psql restore has error

    • ERROR:  permission denied for schema public
      STATEMENT:  CREATE COLLATION public.natural_sort (provider = icu, locale = 'und-u-kn');

    • AI suggests => ALTER USER netbox WITH SUPERUSER; // ⛔️ ERROR: permission denied to alter role DETAIL: Only roles with the SUPERUSER attribute may change the SUPERUSER attribute.

    • psql login -U <EntraId>

    • => SET ROLE azuresu; // ⛔️ ERROR: permission denied to set role "azuresu"

    • =>