steampipe plugin install azure

Table: azure_postgresql_server - Query Azure PostgreSQL Servers using SQL

Azure Database for PostgreSQL is a fully managed relational database service based on the open-source Postgres database engine. It's built to support the Postgres community edition, allowing users to leverage community-driven features and plugins. This service provides built-in high availability, security, and automated scaling to help businesses focus on application development rather than database management.

Table Usage Guide

The azure_postgresql_server table provides insights into PostgreSQL servers within Azure Database for PostgreSQL. As a database administrator or developer, explore server-specific details through this table, including configuration settings, performance tiers, and resource usage. Utilize it to manage server settings, monitor resource consumption, and ensure optimal performance for your PostgreSQL databases within Azure.

Examples

Basic info

Explore which PostgreSQL servers are currently running on your Azure platform and where they are located. This information can assist in managing server distribution and planning for future resources.

select
name,
id,
location
from
azure_postgresql_server;
select
name,
id,
location
from
azure_postgresql_server;

List servers with encryption disabled

Discover the segments that contain servers with disabled encryption, enabling you to identify potential security vulnerabilities and take necessary action to enhance data protection.

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

List servers that allow access to Azure services

Explore which servers allow access to Azure services, a crucial element in managing security and controlling access. You can also pinpoint specific servers without an Active Directory admin, helping you identify potential vulnerabilities and areas that may require additional security measures.

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';
select
name,
s.id,
json_extract(rule.value, '$.Name') as rule_name,
json_extract(rule.value, '$.Type') as rule_type,
json_extract(
rule.value,
'$.FirewallRuleProperties.endIpAddress'
) as end_ip_address,
json_extract(
rule.value,
'$.FirewallRuleProperties.startIpAddress'
) as start_ip_address
from
azure_postgresql_server as s,
json_each(firewall_rules) as rule
where
json_extract(rule.value, '$.Name') = 'AllowAllWindowsAzureIps'
and json_extract(
rule.value,
'$.FirewallRuleProperties.startIpAddress'
) = '0.0.0.0'
and json_extract(
rule.value,
'$.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;
select
name,
id,
location
from
azure_postgresql_server
where
server_administrators is null;

List servers with log checkpoints disabled

Determine the areas in which log checkpoints are disabled on your Azure PostgreSQL servers. This can help identify potential security vulnerabilities and improve your database management.

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';
select
name,
json_extract(configurations.value, '$.Name') as configuration_name,
json_extract(
configurations.value,
'$.ConfigurationProperties.value'
) as configuration_value
from
azure_postgresql_server,
json_each(server_configurations) as configurations
where
json_extract(configurations.value, '$.Name') = 'log_checkpoints'
and json_extract(
configurations.value,
'$.ConfigurationProperties.value'
) = 'OFF';

List servers with a logging retention period greater than 3 days

Determine the servers in your Azure PostgreSQL setup that have a logging retention period of more than 3 days. This is useful for ensuring your logging policies meet your organization's data retention requirements.

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;
select
name,
json_extract(configurations.value, '$.Name') as configuration_name,
json_extract(
json_extract(configurations.value, '$.ConfigurationProperties'),
'$.value'
) as configuration_value
from
azure_postgresql_server,
json_each(server_configurations) as configurations
where
json_extract(configurations.value, '$.Name') = 'log_retention_days'
and cast(
json_extract(
json_extract(configurations.value, '$.ConfigurationProperties'),
'$.value'
) as INTEGER
) > 3;

List servers with geo-redundant backup storage disabled

Discover the segments where servers are running without geo-redundant backup storage. This is useful for identifying potential risk areas in your server infrastructure where data loss may occur in the event of a server failure.

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

List private endpoint connection details

Explore the status and details of private endpoint connections on your Azure PostgreSQL server. This can help in identifying any required actions or understanding the current provisioning state of these connections.

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;
select
name as server_name,
s.id as server_id,
json_extract(connections.value, '$.id') as connection_id,
json_extract(connections.value, '$.privateEndpointPropertyId') as connection_private_endpoint_property_id,
json_extract(
connections.value,
'$.privateLinkServiceConnectionStateActionsRequired'
) as connection_actions_required,
json_extract(
connections.value,
'$.privateLinkServiceConnectionStateDescription'
) as connection_description,
json_extract(
connections.value,
'$.privateLinkServiceConnectionStateStatus'
) as connection_status,
json_extract(connections.value, '$.provisioningState') as connection_provisioning_state
from
azure_postgresql_server as s,
json_each(private_endpoint_connections) as connections;

Control examples

Schema for azure_postgresql_server

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
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.
cloud_environmenttextThe Azure Cloud Environment.
earliest_restore_datetimestamp with 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'.
nametext=The 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_grouptext=The resource group which holds this resource.
server_administratorsjsonbA list of server administrators.
server_configurationsjsonbA list of configurations for a server.
server_keysjsonbA list of server keys for a server.
server_security_alert_policyjsonbServer security alert policy associated with the PostgreSQL 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'.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
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_idtext=, !=, ~~, ~~*, !~~, !~~*The 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.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure

You can pass the configuration to the command with the --config argument:

steampipe_export_azure --config '<your_config>' azure_postgresql_server