How to create private endpoint for postgres SQL flexible server?

Hi

Yes it seems Feasible

To deploy a PostgreSQL flexible server with a private endpoint connection, you need to associate a network security group to it and delegate the appropriate subnet as shown below.

// Postgres NSG
resource postgresNsg 'Microsoft.Network/networkSecurityGroups@2021-05-01' = {
  name: 'postgresNsg'
  location: location
  properties: {
    securityRules: [
      // Allow inbound traffic on port 5432 (PostgreSQL default port) from specific sources
      {
        name: 'AllowPostgresInbound',
        properties: {
          priority: 100,
          direction: 'Inbound',
          access: 'Allow',
          protocol: 'Tcp',
          sourceAddressPrefix: '*', // Update this to restrict to specific IPs if needed
          sourcePortRange: '*',
          destinationAddressPrefix: '*',
          destinationPortRange: '5432',
          description: 'Allow inbound traffic on port 5432 from specific sources'
        }
      },
      // Allow outbound traffic to any destination and any port
      {
        name: 'AllowAllOutbound',
        properties: {
          priority: 200,
          direction: 'Outbound',
          access: 'Allow',
          protocol: '*',
          sourceAddressPrefix: '*',
          sourcePortRange: '*',
          destinationAddressPrefix: '*',
          destinationPortRange: '*',
          description: 'Allow all outbound traffic'
        }
      }
    ]
  }
}

In this code snippet, we're defining an NSG resource called postgresNsg with two security rules:

1. AllowPostgresInbound: Please update as per your req.
2. AllowAllOutbound: Please update as per your req.
3. . Additionally, adjust the security rules according to your specific security requirements and network architecture.

Kindly check and accept answer , if it works Thanks

To add a subnet delegation within the existing resources block in your Bicep code, you can include a resource definition for the subnet delegation. Here's how you can add the subnet delegation:

// ---- Existing resources ----
resource vnet 'Microsoft.Network/virtualNetworks@2022-11-01' existing =  {
  name: vnetName

  resource privateEndpointsSubnet 'subnets' existing = {
    name: privateEndpointsSubnetName
  }

  resource pgSQLSubnet 'subnets' existing = {
    name: pgSQLSubnetName
  }
}

// Subnet delegation for PostgreSQL
resource pgSQLSubnetDelegation 'Microsoft.Network/virtualNetworks/subnets/delegations@2022-03-01' = {
  name: '${vnetName}/${pgSQLSubnetName}/pgsqlSubnetDelegation'
  parent: vnet
  properties: {
    serviceName: 'Microsoft.DBforPostgreSQL/flexibleServers'
    actions: [
      'Microsoft.Network/virtualNetworks/subnets/join/action'
    ]
  }
}

I tried with the bicep provided by you but I still cannot connect to the Postgres SQL via the jumphost.

This is the NSG I attached to each subnet in the virtual network

// Private endpoints subnet NSG
resource privateEndpointsSubnetNsg 'Microsoft.Network/networkSecurityGroups@2022-11-01' = {
  name: 'nsg-privateEndpointsSubnet'
  location: location
  properties: {
    securityRules: [
      {
        name: 'DenyAllOutBound'
        properties: {
          description: 'Deny outbound traffic from the private endpoints subnet'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: privateEndpointsSubnetPrefix
          destinationAddressPrefix: '*'
          access: 'Deny'
          priority: 1000
          direction: 'Outbound'
        }
      }
    ]
  }
}

resource bastionSubnetNsg 'Microsoft.Network/networkSecurityGroups@2022-11-01' = {
  name: 'nsg-bastionSubnet'
  location: location
  properties: {
    securityRules: [
      {
        name: 'Bastion.In.Allow.Https'
        properties: {
          description: 'Allow inbound Https traffic from the from the Internet to the Bastion Host'
          protocol: 'Tcp'
          sourcePortRange: '*'
          sourceAddressPrefix: 'Internet'
          destinationPortRange: '443'
          destinationAddressPrefix: '*'
          access: 'Allow'
          priority: 100
          direction: 'Inbound'
        }
      }
      {
        name: 'Bastion.In.Allow.GatewayManager'
        properties: {
          protocol: 'Tcp'
          sourcePortRange: '*'
          sourceAddressPrefix: 'GatewayManager'
          destinationPortRanges: [
            '443'
            '4443'
          ]
          destinationAddressPrefix: '*'
          access: 'Allow'
          priority: 110
          direction: 'Inbound'
        }
      }
      {
        name: 'Bastion.In.Allow.LoadBalancer'
        properties: {
          protocol: 'Tcp'
          sourcePortRange: '*'
          sourceAddressPrefix: 'AzureLoadBalancer'
          destinationPortRange: '443'
          destinationAddressPrefix: '*'
          access: 'Allow'
          priority: 120
          direction: 'Inbound'
        }
      }
      {
        name: 'Bastion.In.Allow.BastionHostCommunication'
        properties: {
          protocol: '*'
          sourcePortRange: '*'
          sourceAddressPrefix: 'VirtualNetwork'
          destinationPortRanges: [
            '8080'
            '5701'
          ]
          destinationAddressPrefix: 'VirtualNetwork'
          access: 'Allow'
          priority: 130
          direction: 'Inbound'
        }
      }      
      {
        name: 'DenyAllInBound'
        properties: {
          protocol: '*'
          sourcePortRange: '*'
          sourceAddressPrefix: '*'
          destinationPortRange: '*'
          destinationAddressPrefix: '*'
          access: 'Deny'
          priority: 1000
          direction: 'Inbound'
        }
      }
      
      {
        name: 'Bastion.Out.Allow.SshRdp'
        properties: {
          description: 'Allow outbound RDP and SSH from the Bastion Host subnet to elsewhere in the vnet'
          protocol: 'Tcp'
          sourcePortRange: '*'
          sourceAddressPrefix: '*'
          destinationPortRanges: [
            '22'
            '3389'
          ]
          destinationAddressPrefix: 'VirtualNetwork'
          access: 'Allow'
          priority: 100
          direction: 'Outbound'
        }
      }
      {
        name: 'Bastion.Out.Allow.AzureMonitor'
        properties: {
          description: 'Allow outbound traffic from the Bastion Host subnet to Azure Monitor'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: bastionSubnetPrefix
          destinationAddressPrefix: 'AzureMonitor'
          access: 'Allow'
          priority: 110
          direction: 'Outbound'
        }
      }
      {
        name: 'Bastion.Out.Allow.AzureCloudCommunication'
        properties: {
          protocol: 'Tcp'
          sourcePortRange: '*'
          sourceAddressPrefix: '*'
          destinationPortRange: '443'
          destinationAddressPrefix: 'AzureCloud'
          access: 'Allow'
          priority: 120
          direction: 'Outbound'
        }
      }
      {
        name: 'Bastion.Out.Allow.BastionHostCommunication'
        properties: {
          protocol: '*'
          sourcePortRange: '*'
          sourceAddressPrefix: 'VirtualNetwork'
          destinationPortRanges: [
            '8080'
            '5701'
          ]
          destinationAddressPrefix: 'VirtualNetwork'
          access: 'Allow'
          priority: 130
          direction: 'Outbound'
        }
      }
      {
        name: 'Bastion.Out.Allow.GetSessionInformation'
        properties: {
          protocol: '*'
          sourcePortRange: '*'
          sourceAddressPrefix: '*'
          destinationAddressPrefix: 'Internet'
          destinationPortRanges: [
            '80'
            '443'
          ]
          access: 'Allow'
          priority: 140
          direction: 'Outbound'
        }
      }      
      {
        name: 'DenyAllOutBound'
        properties: {
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: '*'
          destinationAddressPrefix: '*'
          access: 'Deny'
          priority: 1000
          direction: 'Outbound'
        }
      }
    ]
  }
}

// Jump box subnet NSG 
resource jumpboxSubnetNsg 'Microsoft.Network/networkSecurityGroups@2022-11-01' = {
  name: 'nsg-jumpboxSubnet'
  location: location
  properties: {
    securityRules: [
      {
        name: 'Jumpbox.In.Allow.SshRdp'
        properties: {
          description: 'Allow inbound RDP and SSH from the Bastion Host subnet'
          protocol: 'Tcp'
          sourcePortRange: '*'
          sourceAddressPrefix: bastionSubnetPrefix
          destinationPortRanges: [
            '22'
            '3389'
          ]
          destinationAddressPrefix: jumpboxSubnetPrefix
          access: 'Allow'
          priority: 100
          direction: 'Inbound'
        }
      }
      {
        name: 'Jumpbox.Out.Allow.PrivateEndpoints'
        properties: {
          description: 'Allow outbound traffic from the jumpbox subnet to the Private Endpoints subnet.'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: jumpboxSubnetPrefix
          destinationAddressPrefix: privateEndpointsSubnetPrefix
          access: 'Allow'
          priority: 100
          direction: 'Outbound'
        }
      }
      {
        name: 'Jumpbox.Out.Allow.Internet'
        properties: {
          description: 'Allow outbound traffic from all VMs to Internet'
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: jumpboxSubnetPrefix
          destinationAddressPrefix: 'Internet'
          access: 'Allow'
          priority: 130
          direction: 'Outbound'
        }
      }
      {
        name: 'DenyAllOutBound'
        properties: {
          protocol: '*'
          sourcePortRange: '*'
          destinationPortRange: '*'
          sourceAddressPrefix: jumpboxSubnetPrefix
          destinationAddressPrefix: '*'
          access: 'Deny'
          priority: 1000
          direction: 'Outbound'
        }
      }
    ]
  }
}

// PG SQL subnet NSG
resource pgSQLSubnetNsg 'Microsoft.Network/networkSecurityGroups@2022-11-01' = {
  name: 'nsg-pgSQLSubnet'
  location: location
  properties:{
    securityRules: [
      // Allow inbound traffic on port 5432 (PostgreSQL default port) from specific sources
      {
        name: 'AllowPostgresInbound'
        properties: {
          priority: 100
          direction: 'Inbound'
          access: 'Allow'
          protocol: 'Tcp'
          sourceAddressPrefix: '*' // Update this to restrict to specific IPs if needed
          sourcePortRange: '*'
          destinationAddressPrefix: '*'
          destinationPortRange: '5432'
          description: 'Allow inbound traffic on port 5432 from specific sources'
        }
      }
      // Allow outbound traffic to any destination and any port
      {
        name: 'AllowAllOutbound'
        properties: {
          priority: 200
          direction: 'Outbound'
          access: 'Allow'
          protocol: '*'
          sourceAddressPrefix: '*'
          sourcePortRange: '*'
          destinationAddressPrefix: '*'
          destinationPortRange: '*'
          description: 'Allow all outbound traffic'
        }
      }
    ]
  }
}

I also modified a bit on postgres bicep file

// Parameters
param location string
param env string
param nameAffix string
param administratorLogin string
// existing resource name params 
param vnetName string
param privateEndpointsSubnetName string
param pgSQLSubnetName string

@description('Virtual Network RuleName')
param virtualNetworkRuleName string = 'AllowSubnet'

@secure()
param administratorLoginPassword string

// Variables
var serverName = 'psql-${nameAffix}-${uniqueString(resourceGroup().id)}'

var pgsqlDnsZoneName = 'privatelink.postgres.database.azure.com'

// ---- Existing resources ----
resource vnet 'Microsoft.Network/virtualNetworks@2022-11-01' existing =  {
  name: vnetName

  resource privateEndpointsSubnet 'subnets' existing = {
    name: privateEndpointsSubnetName
  }

  resource pgSQLSubnet 'subnets' existing = {
    name: pgSQLSubnetName
  }

}

// Subnet delegation for PostgreSQL
resource pgSQLSubnetDelegation 'Microsoft.Network/virtualNetworks/subnets/delegations@2022-09-01' = {
  name: '${vnetName}-${pgSQLSubnetName}-pgsqlSubnetDelegation'
  parent: vnet::pgSQLSubnet
  properties: {
    serviceName: 'Microsoft.DBforPostgreSQL/flexibleServers'
    actions: [
      'Microsoft.Network/virtualNetworks/subnets/join/action'
    ]
  }
}


resource pgsqlDnsZone 'Microsoft.Network/privateDnsZones@2020-06-01' = {
  name: pgsqlDnsZoneName
  location: 'global'
  properties: {}

  resource vnetLink 'virtualNetworkLinks' = {
    name: '${pgsqlDnsZoneName}-link'
    location: 'global'
    properties: {
      registrationEnabled: false
      virtualNetwork: {
        id: vnet.id
      }
    }
  }

}

// Postgres
resource server 'Microsoft.DBforPostgreSQL/flexibleServers@2022-12-01' = {
  name: serverName
  location: location
  sku: {
    name: 'Standard_D4ds_v4'
    tier: 'GeneralPurpose'
  }
  properties: {
    version: '16'
    administratorLogin: administratorLogin
    administratorLoginPassword: administratorLoginPassword
    network: {
      delegatedSubnetResourceId: vnet::pgSQLSubnet.id
      privateDnsZoneArmResourceId: pgsqlDnsZone.id
    }
    highAvailability: {
      mode: 'Disabled'
    }
    storage: {
      storageSizeGB: 128
    }
    backup: {
      backupRetentionDays: 7
      geoRedundantBackup: 'Disabled'
    }
  }


}

Do you have any idea what is wrong with my bicep files? Thank you

I think previous error got resolved , Is this is the another error? can you please share the error details

I got this error

"No HTTP resource was found that matches the request URI '
https://
xxx/Microsoft.Network/virtualNetworks/xxxx/subnets/snet-pgsql/delegations/xxxSubnetDelegation?api-version=2022-09-01'."

But the resources are created and I tried to ping it with the jumphost but got "Request timeout". Seems the jumphost knows the private ip address of the database but nothing can return back.