Table: azure_postgresql_server

Azure Database for PostgreSQL is a relational database service based on the open-source Postgres database engine. It's a fully managed database-as-a-service that can handle mission-critical workloads with predictable performance, security, high availability, and dynamic scalability.

Examples

Basic info

select
  name,
  id,
  location
from
  azure_postgresql_server;

List servers with encryption disabled

select
  name,
  id,
  location,
  ssl_enforcement
from
  azure_postgresql_server
where
  ssl_enforcement = 'Disabled';

List servers that allow access to Azure services

select
  name,
  id,
  rule ->> 'Name' as rule_name,
  rule ->> 'Type' as rule_type,
  rule -> 'FirewallRuleProperties' ->> 'endIpAddress' as end_ip_address,
  rule -> 'FirewallRuleProperties' ->> 'startIpAddress' as start_ip_address
from
  azure_postgresql_server,
  jsonb_array_elements(firewall_rules) as rule
where
  rule ->> 'Name' = 'AllowAllWindowsAzureIps'
  and rule -> 'FirewallRuleProperties' ->> 'startIpAddress' = '0.0.0.0'
  and rule -> 'FirewallRuleProperties' ->> 'endIpAddress' = '0.0.0.0';

List servers without an Active Directory admin

select
  name,
  id,
  location
from
  azure_postgresql_server
where
  server_administrators is null;

List servers with log checkpoints disabled

select
  name,
  configurations ->> 'Name' as configuration_name,
  configurations -> 'ConfigurationProperties' ->> 'value' as configuration_value
from
  azure_postgresql_server,
  jsonb_array_elements(server_configurations) as configurations
where
  configurations ->> 'Name' = 'log_checkpoints'
  and configurations -> 'ConfigurationProperties' ->> 'value' = 'OFF';

List servers with a logging retention period greater than 3 days

select
  name,
  configurations ->> 'Name' as configuration_name,
  configurations -> 'ConfigurationProperties' ->> 'value' as configuration_value
from
  azure_postgresql_server,
  jsonb_array_elements(server_configurations) as configurations
where
  configurations ->> 'Name' = 'log_retention_days'
  and (
    configurations -> 'ConfigurationProperties' ->> 'value'
  ) :: INTEGER > 3;

List servers with geo-redundant backup storage disabled

select
  name,
  id,
  location,
  geo_redundant_backup
from
  azure_postgresql_server
where
  geo_redundant_backup = 'Disabled';

List private endpoint connection details

select
  name as server_name,
  id as server_id,
  connections ->> 'id' as connection_id,
  connections ->> 'privateEndpointPropertyId' as connection_private_endpoint_property_id,
  connections ->> 'privateLinkServiceConnectionStateActionsRequired' as connection_actions_required,
  connections ->> 'privateLinkServiceConnectionStateDescription' as connection_description,
  connections ->> 'privateLinkServiceConnectionStateStatus' as connection_status,
  connections ->> 'provisioningState' as connection_provisioning_state
from
  azure_postgresql_server,
  jsonb_array_elements(private_endpoint_connections) as connections;

Query examples

postgresql_servers_for_key_vault_key

Control examples

.inspect azure_postgresql_server

Azure PostgreSQL Server

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
administrator_login	text	Specifies the username of the administrator for this server.
akas	jsonb	Array of globally unique identifier strings (also known as) for the resource.
backup_retention_days	bigint	Backup retention days for the server.
byok_enforcement	text	Status showing whether the server data encryption is enabled with customer-managed keys.
cloud_environment	text	The Azure Cloud Environment.
earliest_restore_date	timestamp with time zone	Specifies the earliest restore point creation time.
firewall_rules	jsonb	A list of firewall rules for a server.
fully_qualified_domain_name	text	The fully qualified domain name of the server.
geo_redundant_backup	text	Indicates whether Geo-redundant is enabled, or not for server backup.
id	text	Contains ID to identify a server uniquely.
infrastructure_encryption	text	Status showing whether the server enabled infrastructure encryption. Possible values include: 'InfrastructureEncryptionEnabled', 'InfrastructureEncryptionDisabled'.
location	text	The resource location.
master_server_id	text	The master server id of a replica server.
minimal_tls_version	text	Enforce a minimal Tls version for the server. Possible values include: 'TLS10', 'TLS11', 'TLS12', 'TLSEnforcementDisabled'.
name	text	The friendly name that identifies the server.
private_endpoint_connections	jsonb	A list of private endpoint connections on a server.
public_network_access	text	Indicates whether or not public network access is allowed for this server. Value is optional but if passed in, must be 'Enabled' or 'Disabled'. Possible values include: 'PublicNetworkAccessEnumEnabled', 'PublicNetworkAccessEnumDisabled'.
region	text	The Azure region/location in which the resource is located.
replica_capacity	bigint	The maximum number of replicas that a master server can have.
replication_role	text	The replication role of the server.
resource_group	text	The resource group which holds this resource.
server_administrators	jsonb	A list of server administrators.
server_configurations	jsonb	A list of configurations for a server.
server_keys	jsonb	A list of server keys for a server.
server_security_alert_policy	jsonb	Server security alert policy associated with the PostgreSQL Server.
sku_capacity	bigint	The scale up/out capacity, representing server's compute units.
sku_family	text	The family of hardware.
sku_name	text	The name of the sku. For example: 'B_Gen4_1', 'GP_Gen5_8'.
sku_size	text	The size code, to be interpreted by resource as appropriate.
sku_tier	text	The tier of the particular SKU. Possible values include: 'Basic', 'GeneralPurpose', 'MemoryOptimized'.
ssl_enforcement	text	Enable ssl enforcement or not when connect to server. Possible values include: 'Enabled', 'Disabled'.
storage_auto_grow	text	Indicates whether storage auto grow is enabled, or not.
storage_mb	bigint	Indicates max storage allowed for a server.
subscription_id	text	The Azure Subscription ID in which the resource is located.
tags	jsonb	A map of tags for the resource.
title	text	Title of the resource.
type	text	The resource type of the SQL server.
user_visible_state	text	A state of a server that is visible to user. Possible values include: 'ServerStateReady', 'ServerStateDropping', 'ServerStateDisabled', 'ServerStateInaccessible'.
version	text	Specifies the version of the server.

turbot/azure