= 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
   1. from admin psql in portal: {{{
select * from pgaadauth_create_principal ('myVMxyz', false, false);
select * from pgaadauth_list_principals(false);
}}}
   1. 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"
   1. 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>
     1. => 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>
     1. => DROP ROLE netbox; // ⛔️
   * Try3
     1. Login with psql -U psqladmin -D postgres
     1. => DROP ROLE netbox; // ✅ DROP ROLE
        * Worked!!!

     1. Logout and log back in with <EntraID> $ psql -d postgres -u <EntraID>
     1. => CREATE USER netbox CREATEDB CREATEROLE PASSWORD '';  // ✅ CREATE ROLE
     1. => SET ROLE netbox;  // ⛔️ ERROR:  permission denied to set role "netbox"
     1. => SET ROLE azure_pg_admin;  // ✅ SET
     1. => SET ROLE netbox;  // ⛔️ ERROR:  permission denied to set role "netbox"

     1. Logout and log back in as "netbox"
     1. => CREATE DATABASE netbox; // ✅ CREATE DATABASE
     1. => \l // ✅ DB netbox Owner: netbox
     1. logout
     1. 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"
    * =>