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