Setting up postgres database in azure with wal_level logical and SST

database.ts

const resourceGroup = new azurenative.resources.ResourceGroup("resource-group");

// Define the scope where the role will be applied
const scope = `/subscriptions/${process.env.AZURE_SUBSCRIPTION_ID}`;

// Create a custom role definition for managing Container Apps
export const role = new azurenative.authorization.RoleDefinition(
  'role',
  {
    scope: scope,
    permissions: [
      {
        actions: [
          'Microsoft.DBforPostgreSQL/flexibleServers/*',
          'Microsoft.DBforPostgreSQL/flexibleServers/read',
          'Microsoft.DBforPostgreSQL/flexibleServers/write',
          'Microsoft.DBforPostgreSQL/flexibleServers/restart/action',
        ],
        notActions: [],
        dataActions: [],
        notDataActions: [],
      },
    ],
    assignableScopes: [scope],
    roleType: 'CustomRole',
  }
);

export const managedIdentity = new azurenative.managedidentity.UserAssignedIdentity(
  'managed-identity',
  {
    resourceGroupName: resourceGroup.name,
    tags: {
      'sst:stage': $app.stage,
      'sst:app': $app.name,
    },
  }
);

export const roleAssignment = new azurenative.authorization.RoleAssignment(
  'role-assignment',
  {
    scope: resourceGroup.id,
    roleDefinitionId: containerAppRole.id, // Contributor Role ID
    principalId: managedIdentity.principalId, // Managed Identity Principal ID
    principalType: azurenative.authorization.PrincipalType.ServicePrincipal,
  }
);


export const server = new azurenative.dbforpostgresql.Server(
  'database',
  {
    resourceGroupName: resourceGroup.name,
    location: resourceGroup.location,
    createMode: azurenative.dbforpostgresql.CreateMode.Create,
    highAvailability: {
      mode: azurenative.dbforpostgresql.HighAvailabilityMode.Disabled,
    },
    availabilityZone: '1',
    administratorLogin: 'postgres',
    administratorLoginPassword: 'password',
    sku: {
      name: 'Standard_D2ds_v4',
      tier: azurenative.dbforpostgresql.SkuTier.GeneralPurpose,
    },
    storage: {
      storageSizeGB: 256,
    },
    backup: {
      backupRetentionDays: 7,
      geoRedundantBackup:
        azurenative.dbforpostgresql.GeoRedundantBackupEnum.Disabled,
    },
    version: '17',
    tags: {
      'sst:stage': $app.stage,
      'sst:app': $app.name,
    },
  },
  { retainOnDelete: true }
);

// Allow all Azure services
export const allowAzureServices = new azurenative.dbforpostgresql.FirewallRule(
  'allow-azure-services',
  {
    resourceGroupName: resourceGroup.name,
    serverName: server.name,
    startIpAddress: '0.0.0.0',
    endIpAddress: '0.0.0.0',
  }
);



const walLevelConfig = new azurenative.dbforpostgresql.Configuration(
  'wal-level',
  {
    resourceGroupName: resourceGroup.name,
    serverName: server.name,
    configurationName: 'wal_level',
    source: 'user-override',
    value: 'logical',
  }
);

// Set the wal_level configuration to logical
const maxWorkerProcesses = new azurenative.dbforpostgresql.Configuration(
  'max-worker-processes',
  {
    resourceGroupName: resourceGroup.name,
    serverName: server.name,
    configurationName: 'max_worker_processes',
    source: 'user-override',
    value: '16',
  }
);

const configurations = new azurenative.dbforpostgresql.getConfigurationOutput({
  resourceGroupName: resourceGroup.name,
  serverName: server.name,
  configurationName: 'wal_level',
});

configurations.apply(config => {
  if (config.isConfigPendingRestart) {
    new azurenative.resources.AzureCliScript(
      'restart-script',
      {
        resourceGroupName: resourceGroup.name,
        forceUpdateTag: Date.now().toString(),
        azCliVersion: '2.69.0',
        identity: {
          type: 'UserAssigned',
          userAssignedIdentities: [managedIdentity.id],
        },
        scriptContent: $interpolate`az postgres flexible-server restart --name ${server.name} --resource-group ${resourceGroup.name}`,
        timeout: 'PT30M', // Set the timeout duration
        cleanupPreference: 'Always',
        retentionInterval: 'P1D',
        kind: 'AzureCliScript',
      },
      { dependsOn: [walLevelConfig, maxWorkerProcesses, managedIdentity] }
    );
  }
});

export const connection = $interpolate`postgresql://${server.administratorLogin}:password@${server.fullyQualifiedDomainName}:5432/postgres?sslmode=require`;