azure_ad_groupazure_ad_service_principalazure_ad_userazure_alert_managementazure_api_managementazure_app_configurationazure_app_service_environmentazure_app_service_function_appazure_app_service_planazure_app_service_web_appazure_app_service_web_app_slotazure_application_gatewayazure_application_insightazure_application_security_groupazure_automation_accountazure_automation_variableazure_bastion_hostazure_batch_accountazure_cognitive_accountazure_compute_availability_setazure_compute_diskazure_compute_disk_accessazure_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_ssh_keyazure_compute_virtual_machineazure_compute_virtual_machine_metric_cpu_utilizationazure_compute_virtual_machine_metric_cpu_utilization_dailyazure_compute_virtual_machine_metric_cpu_utilization_hourlyazure_compute_virtual_machine_scale_setazure_compute_virtual_machine_scale_set_network_interfaceazure_compute_virtual_machine_scale_set_vmazure_container_groupazure_container_registryazure_cosmosdb_accountazure_cosmosdb_mongo_collectionazure_cosmosdb_mongo_databaseazure_cosmosdb_restorable_database_accountazure_cosmosdb_sql_databaseazure_data_factoryazure_data_factory_datasetazure_data_factory_pipelineazure_data_lake_analytics_accountazure_data_lake_storeazure_databox_edge_deviceazure_databricks_workspaceazure_diagnostic_settingazure_dns_zoneazure_eventgrid_domainazure_eventgrid_topicazure_eventhub_namespaceazure_express_route_circuitazure_firewallazure_firewall_policyazure_frontdoorazure_hdinsight_clusterazure_healthcare_serviceazure_hpc_cacheazure_hybrid_compute_machineazure_hybrid_kubernetes_connected_clusterazure_iothubazure_iothub_dpsazure_key_vaultazure_key_vault_deleted_vaultazure_key_vault_keyazure_key_vault_key_versionazure_key_vault_managed_hardware_security_moduleazure_key_vault_secretazure_kubernetes_clusterazure_kubernetes_service_versionazure_kusto_clusterazure_lbazure_lb_backend_address_poolazure_lb_nat_ruleazure_lb_outbound_ruleazure_lb_probeazure_lb_ruleazure_locationazure_log_alertazure_log_profileazure_logic_app_workflowazure_machine_learning_workspaceazure_management_groupazure_management_lockazure_mariadb_serverazure_monitor_activity_log_eventazure_mssql_elasticpoolazure_mssql_managed_instanceazure_mssql_virtual_machineazure_mysql_flexible_serverazure_mysql_serverazure_nat_gatewayazure_network_interfaceazure_network_security_groupazure_network_watcherazure_network_watcher_flow_logazure_policy_assignmentazure_policy_definitionazure_postgresql_flexible_serverazure_postgresql_serverazure_private_dns_zoneazure_providerazure_public_ipazure_recovery_services_backup_jobazure_recovery_services_vaultazure_redis_cacheazure_resource_groupazure_resource_linkazure_role_assignmentazure_role_definitionazure_route_tableazure_search_serviceazure_security_center_auto_provisioningazure_security_center_automationazure_security_center_contactazure_security_center_jit_network_access_policyazure_security_center_settingazure_security_center_sub_assessmentazure_security_center_subscription_pricingazure_service_fabric_clusterazure_servicebus_namespaceazure_signalr_serviceazure_spring_cloud_serviceazure_sql_databaseazure_sql_serverazure_storage_accountazure_storage_blobazure_storage_blob_serviceazure_storage_containerazure_storage_queueazure_storage_share_fileazure_storage_syncazure_storage_tableazure_storage_table_serviceazure_stream_analytics_jobazure_subnetazure_subscriptionazure_synapse_workspaceazure_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, locationfrom azure_postgresql_server;
List servers with encryption disabled
select name, id, location, ssl_enforcementfrom azure_postgresql_serverwhere 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_addressfrom azure_postgresql_server, jsonb_array_elements(firewall_rules) as rulewhere 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, locationfrom azure_postgresql_serverwhere server_administrators is null;
List servers with log checkpoints disabled
select name, configurations ->> 'Name' as configuration_name, configurations -> 'ConfigurationProperties' ->> 'value' as configuration_valuefrom azure_postgresql_server, jsonb_array_elements(server_configurations) as configurationswhere 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_valuefrom azure_postgresql_server, jsonb_array_elements(server_configurations) as configurationswhere 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_backupfrom azure_postgresql_serverwhere 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_statefrom azure_postgresql_server, jsonb_array_elements(private_endpoint_connections) as connections;
Query examples
Control examples
- postgresql_server_expected_tag_values
- postgresql_server_mandatory
- postgresql_server_prohibited
- postgresql_server_tag_limit
- postgresql_server_untagged
- postgres_db_server_allow_access_to_azure_services_disabled
- postgres_db_server_connection_throttling_on
- postgres_db_server_geo_redundant_backup_enabled
- postgres_db_server_latest_tls_version
- postgres_db_server_log_checkpoints_on
- postgres_db_server_log_connections_on
- postgres_db_server_log_disconnections_on
- postgres_db_server_log_retention_days_3
- postgres_server_private_link_used
- postgres_sql_server_encrypted_at_rest_using_cmk
- postgres_sql_ssl_enabled
- postgresql_server_infrastructure_encryption_enabled
- postgresql_server_public_network_access_disabled
.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. |