turbot/azure

steampipe plugin install azuresteampipe plugin install azure
azure_ad_groupazure_ad_service_principalazure_ad_userazure_api_managementazure_app_service_environmentazure_app_service_function_appazure_app_service_planazure_app_service_web_appazure_application_security_groupazure_compute_availability_setazure_compute_diskazure_compute_disk_encryption_setazure_compute_disk_metric_read_opsazure_compute_disk_metric_read_ops_dailyazure_compute_disk_metric_read_ops_hourlyazure_compute_disk_metric_write_opsazure_compute_disk_metric_write_ops_dailyazure_compute_disk_metric_write_ops_hourlyazure_compute_imageazure_compute_resource_skuazure_compute_snapshotazure_compute_virtual_machineazure_compute_virtual_machine_metric_cpu_utilizationazure_compute_virtual_machine_metric_cpu_utilization_dailyazure_compute_virtual_machine_metric_cpu_utilization_hourlyazure_cosmosdb_accountazure_cosmosdb_mongo_databaseazure_cosmosdb_sql_databaseazure_data_factoryazure_data_factory_datasetazure_data_factory_pipelineazure_diagnostic_settingazure_express_route_circuitazure_firewallazure_key_vaultazure_key_vault_keyazure_key_vault_secretazure_kubernetes_clusterazure_locationazure_log_alertazure_log_profileazure_management_lockazure_mysql_serverazure_network_interfaceazure_network_security_groupazure_network_watcherazure_network_watcher_flow_logazure_policy_assignmentazure_policy_definitionazure_postgresql_serverazure_providerazure_public_ipazure_resource_groupazure_role_assignmentazure_role_definitionazure_route_tableazure_security_center_auto_provisioningazure_security_center_contactazure_security_center_settingazure_security_center_subscription_pricingazure_sql_databaseazure_sql_serverazure_storage_accountazure_storage_blobazure_storage_blob_serviceazure_storage_containerazure_storage_queueazure_storage_tableazure_storage_table_serviceazure_subnetazure_subscriptionazure_tenantazure_virtual_networkazure_virtual_network_gateway

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';

.inspect azure_postgresql_server

Azure PostgreSQL Server

NameTypeDescription
administrator_logintextSpecifies the username of the administrator for this server.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
backup_retention_daysbigintBackup retention days for the server.
byok_enforcementtextStatus showing whether the server data encryption is enabled with customer-managed keys.
earliest_restore_datetimestamp without time zoneSpecifies the earliest restore point creation time.
firewall_rulesjsonbA list of firewall rules for a server.
fully_qualified_domain_nametextThe fully qualified domain name of the server.
geo_redundant_backuptextIndicates whether Geo-redundant is enabled, or not for server backup.
idtextContains ID to identify a server uniquely.
infrastructure_encryptiontextStatus showing whether the server enabled infrastructure encryption. Possible values include: 'InfrastructureEncryptionEnabled', 'InfrastructureEncryptionDisabled'.
locationtextThe resource location.
master_server_idtextThe master server id of a replica server.
minimal_tls_versiontextEnforce a minimal Tls version for the server. Possible values include: 'TLS10', 'TLS11', 'TLS12', 'TLSEnforcementDisabled'.
nametextThe friendly name that identifies the server.
private_endpoint_connectionsjsonbA list of private endpoint connections on a server.
public_network_accesstextIndicates 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'.
regiontextThe Azure region/location in which the resource is located.
replica_capacitybigintThe maximum number of replicas that a master server can have.
replication_roletextThe replication role of the server.
resource_grouptextThe resource group which holds this resource.
server_administratorsjsonbA list of server administrators.
server_configurationsjsonbA list of configurations for a server.
sku_capacitybigintThe scale up/out capacity, representing server's compute units.
sku_familytextThe family of hardware.
sku_nametextThe name of the sku. For example: 'B_Gen4_1', 'GP_Gen5_8'.
sku_sizetextThe size code, to be interpreted by resource as appropriate.
sku_tiertextThe tier of the particular SKU. Possible values include: 'Basic', 'GeneralPurpose', 'MemoryOptimized'.
ssl_enforcementtextEnable ssl enforcement or not when connect to server. Possible values include: 'Enabled', 'Disabled'.
storage_auto_growtextIndicates whether storage auto grow is enabled, or not.
storage_mbbigintIndicates max storage allowed for a server.
subscription_idtextThe Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
typetextThe resource type of the SQL server.
user_visible_statetextA state of a server that is visible to user. Possible values include: 'ServerStateReady', 'ServerStateDropping', 'ServerStateDisabled', 'ServerStateInaccessible'.
versiontextSpecifies the version of the server.