turbot / steampipe-plugin-azure Goto Github PK

While executing this query, it runs in to loop. There are only 3 no of key-vaults in the subscription.

> select
  name,
  id,
  soft_delete_enabled,
  soft_delete_retention_in_days
from
  azure_key_vault

⠏ Loading results: 275
⠸ Loading results: 553
⠇ Loading results: 646
⠋ Loading results: 646
⠹ Loading results: 646
⠼ Loading results: 646
⠦ Loading results: 646
Error: pq: rpc error: code = Unavailable desc = transport is closing ( This comes after I stopped the service with steampipe service stop --force)
Connected again to execute the same query

abc % steampipe query
Welcome to Steampipe v0.2.2
For more information, type .help

select
name,
id,
soft_delete_enabled,
soft_delete_retention_in_days
from
azure_key_vault
⠙ Loading results: 275

References
https://docs.microsoft.com/en-us/rest/api/network-watcher/flowlogs

In this link https://hub.steampipe.io/plugins/turbot/azure/tables/azure_storage_queue

The provides query may be replaced to region instead of location ?

select
name as queue_name,
storage_account_name,
location
from
azure_storage_queue;
Error: pq: column "location" does not exist

Describe the new table

Need to query the Azure Monitor table to verify the CIS section 5 (Logging and Monitoring)

References

Section 5

We need to achieve some of the steps as per CIS recommendation

5.1 Configuring Diagnostic Settings
5.1.1 Ensure that a 'Diagnostics Setting' exists (Automated)
5.1.2 Ensure Diagnostic Setting captures appropriate categories (Automated)
5.1.3 Ensure the storage container storing the activity logs is not publicly accessible (Automated)
5.1.4 Ensure the storage account containing the container with activity logs is encrypted with BYOK (Use Your Own Key) (Automated)
5.1.5 Ensure that logging for Azure KeyVault is 'Enabled' (Automated)
5.2.1 Ensure that Activity Log Alert exists for Create Policy Assignment (Automated)
5.2.2 Ensure that Activity Log Alert exists for Delete Policy Assignment (Automated)
5.2.3 Ensure that Activity Log Alert exists for Create or Update Network Security Group (Automated)
5.2.4 Ensure that Activity Log Alert exists for Delete Network Security Group (Automated)
5.2.5 Ensure that Activity Log Alert exists for Create or Update Network Security Group Rule (Automated)
5.2.6 Ensure that activity log alert exists for the Delete Network Security Group Rule (Automated)
5.2.7 Ensure that Activity Log Alert exists for Create or Update Security Solution (Automated)
5.2.8 Ensure that Activity Log Alert exists for Delete Security Solution (Automated)
5.2.9 Ensure that Activity Log Alert exists for Create or Update or Delete SQL Server Firewall Rule (Automated)
5.3 Ensure that Diagnostic Logs are enabled for all services which support it. (Automated)

The following table azure_storage_table table stores the properties of a storage account’s Table service.
Changing the name to azure_storage_table_service will be more appropriate.

References
https://docs.microsoft.com/en-us/rest/api/mysql/servers/list#code-try-0

This will unblock the following CIS recommendations

Section 4

4.3.2 Ensure 'Enforce SSL connection' is set to 'ENABLED' for MySQL Database Server (Automated)

Describe the new table

Need to query the Kubernetes table to query if the instances are enabled with RBAC.

References

Section 8

We need to achieve some of the steps as per CIS recommendation
8.5 Enable role-based access control (RBAC) within Azure Kubernetes Services (Automated), steps 2 & 3

Is your feature request related to a problem? Please describe.
This is to achieve some of the CIS steps 7.6

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
At this point in time, there is no API mechanism available.

Describe the new table

Need to query the Security Center table to verify the CIS section 2 (Security Center)

References

Section 2

2.1 Ensure that Azure Defender is set to On for Servers (Manual)
2.2 Ensure that Azure Defender is set to On for App Service (Manual)
2.3 Ensure that Azure Defender is set to On for Azure SQL database servers (Manual)
2.4 Ensure that Azure Defender is set to On for SQL servers on machines (Manual)
2.5 Ensure that Azure Defender is set to On for Storage (Manual)
2.6 Ensure that Azure Defender is set to On for Kubernetes (Manual)
2.7 Ensure that Azure Defender is set to On for Container Registries (Manual)
2.8 Ensure that Azure Defender is set to On for Key Vault (Manual)
2.9 Ensure that Windows Defender ATP (WDATP) integration with Security Center is selected (Manual)
2.10 Ensure that Microsoft Cloud App Security (MCAS) integration with Security Center is selected (Manual)
2.11 Ensure that 'Automatic provisioning of monitoring agent' is set to 'On' (Automated)
2.12 Ensure any of the ASC Default policy setting is not set to "Disabled" (Manual)
2.13 Ensure 'Additional email addresses' is configured with a security contact email (Automated)
2.14 Ensure that 'Notify about alerts with the following severity' is set to 'High' (Automated)
2.15 Ensure that 'All users with the following roles' is set to 'Owner' (Automated)

Register with Azure Active Directory is enabled on App Service, this can an extension to azure_app_service_web_app table.

az webapp identity show --resource-group <RESOURCE_GROUP_NAME> --name
<APP_NAME> --query principalId
The output should return unique Principal ID.
If no output for the above command then Register with Azure Active Directory is not set.

Is your feature request related to a problem? Please describe.
This is to achieve some of the CIS steps 7.5

Describe the solution you'd like

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Please note that at this point of time, there is no API/CLI mechanism available to
programmatically conduct security assessment for this recommendation.

References
https://docs.microsoft.com/en-us/rest/api/keyvault/getkeys/getkeys
https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/key_vault_key

https://godoc.org/github.com/Azure/azure-sdk-for-go/storage#Container.ListBlobs

After chaing my azure password, steampipe fails with an unfriendly error:

select * from azure_ad_group
Error: pq: rpc error: code = Unknown desc = Invoking Azure CLI failed with the following error: UnexpectedError: The command failed with an unexpected error. Here is the traceback:
Get Token request returned http error: 400 and server response: {"error":"invalid_grant","error_description":"AADSTS50173: The provided grant has expired due to it being revoked, a fresh auth token is needed. The user might have changed or reset their password. The grant was issued on '2020-08-26T13:52:10.1898142Z' and the TokensValidFrom date (before which tokens are not valid) for this user is '2021-01-26T21:20:37.0000000Z'.\r\nTrace ID: 62ae314d-d6be-4854-ab2d-1e19ac971300\r\nCorrelation ID: 5d919732-f206-4ed5-b6f9-1d6798276b37\r\nTimestamp: 2021-01-26 23:28:38Z","error_codes":[50173],"timestamp":"2021-01-26 23:28:38Z","trace_id":"62ae314d-d6be-4854-ab2d-1e19ac971300","correlation_id":"5d919732-f206-4ed5-b6f9-1d6798276b37","error_uri":"https://login.microsoftonline.com/error?code=50173"}
Traceback (most recent call last):
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/knack/cli.py", line 215, in invoke
    cmd_result = self.invocation.execute(args)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/commands/__init__.py", line 654, in execute
    raise ex
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/commands/__init__.py", line 718, in _run_jobs_serially
    results.append(self._run_job(expanded_arg, cmd_copy))
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/commands/__init__.py", line 711, in _run_job
    six.reraise(*sys.exc_info())
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/six.py", line 703, in reraise
    raise value
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/commands/__init__.py", line 688, in _run_job
    result = cmd_copy(params)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/commands/__init__.py", line 325, in __call__
    return self.handler(*args, **kwargs)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/__init__.py", line 784, in default_command_handler
    return op(**command_args)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/command_modules/profile/custom.py", line 75, in get_access_token
    creds, subscription, tenant = profile.get_raw_token(subscription=subscription, resource=resource, tenant=tenant)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/_profile.py", line 649, in get_raw_token
    creds = self._creds_cache.retrieve_token_for_user(username_or_sp_id,
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/azure/cli/core/_profile.py", line 1019, in retrieve_token_for_user
    token_entry = context.acquire_token(resource, username, _CLIENT_ID)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/authentication_context.py", line 145, in acquire_token
    return self._acquire_token(token_func)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/authentication_context.py", line 128, in _acquire_token
    return token_func(self)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/authentication_context.py", line 143, in token_func
    return token_request.get_token_from_cache_with_refresh(user_id)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/token_request.py", line 347, in get_token_from_cache_with_refresh
    return self._find_token_from_cache()
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/token_request.py", line 127, in _find_token_from_cache
    return self._cache_driver.find(cache_query)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/cache_driver.py", line 198, in find
    return self._refresh_entry_if_necessary(entry,
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/cache_driver.py", line 177, in _refresh_entry_if_necessary
    return self._refresh_expired_entry(entry)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/cache_driver.py", line 153, in _refresh_expired_entry
    token_response = self._refresh_function(entry, None)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/token_request.py", line 137, in _get_token_with_token_response
    return self._get_token_with_refresh_token(refresh_token, resource, None)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/token_request.py", line 339, in _get_token_with_refresh_token
    return self._oauth_get_token(oauth_parameters)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/token_request.py", line 112, in _oauth_get_token
    return client.get_token(oauth_parameters)
  File "/usr/local/Cellar/azure-cli/2.12.1/libexec/lib/python3.8/site-packages/adal/oauth2_client.py", line 289, in get_token
    raise AdalError(return_error_string, error_response)
adal.adal_error.AdalError: Get Token request returned http error: 400 and server response: {"error":"invalid_grant","error_description":"AADSTS50173: The provided grant has expired due to it being revoked, a fresh auth token is needed. The user might have changed or reset their password. The grant was issued on '2020-08-26T13:52:10.1898142Z' and the TokensValidFrom date (before which tokens are not valid) for this user is '2021-01-26T21:20:37.0000000Z'.\r\nTrace ID: 62ae314d-d6be-4854-ab2d-1e19ac971300\r\nCorrelation ID: 5d919732-f206-4ed5-b6f9-1d6798276b37\r\nTimestamp: 2021-01-26 23:28:38Z","error_codes":[50173],"timestamp":"2021-01-26 23:28:38Z","trace_id":"62ae314d-d6be-4854-ab2d-1e19ac971300","correlation_id":"5d919732-f206-4ed5-b6f9-1d6798276b37","error_uri":"https://login.microsoftonline.com/error?code=50173"}
To open an issue, please run: 'az feedback'
> 

We should throw a more informative error, similar to the az cli:

$ az vm list
ValidationError: The credential data used by CLI has been expired because you might have changed or reset the password. Please clear browser's cookies and run 'az login'

References
https://docs.microsoft.com/en-us/rest/api/keyvault/getsecrets/getsecrets
https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/key_vault_secret

This is to add most of the Azure IAM CIS requirements.

E.x.

• CIS v 1.3.0 : 1.22 Ensure Security Defaults is enabled on Azure Active Directory (Automated) : This is to check the Directory Properties whether enabled with defaults or not.
• Whether a maximum of 3 owners should be designated for subscription or not.

Even if we have data available as mentioned in the screen the query below does not show any data.

select virtual_network_rules from azure_storage_account where name = 'anothertesting'

We can extend azure_storage_account to add additional data in virtual_network_rules field to include the data for Firewalls and virtual networks

The following table azure_storage_blob table stores the properties of a storage account’s Blob service.
Changing the name to azure_storage_blob_service will be more appropriate.

Currently this query returns only the id of the
select jsonb_pretty(flow_logs) from azure_network_security_group where name = 'myabc-nsg'
[ {
"id": "/subscriptions/xxxxxxxxxxxxxxxxxx1b097a0469dd/resourceGroups/NetworkWatcherRG/providers/Microsoft.Network/networkWatchers/NetworkWatcher_westus/flowLogs/myabc-nsg-flowlog
}
]
However
If we can pull more info from the Flow logs settings from Network Watcher will be good, this will also address the retention period.

API reference - https://docs.microsoft.com/en-us/rest/api/network-watcher/flowlogs/get

Ref Azure CIS 1.3.0 : 6.4

v0.3.0 [2021-03-11]

What's new?

• New tables added
  • azure_sql_server

Bug fixes

• Removed use of deprecated ItemFromKey function from all tables

Already there is logging support available for Storage Account, however enabling the logging in storage account level does not ensure the logging in Blob, Table & Queue service in it.

Requirement is whether, logging is enabled for read, write, and delete requests for above services.

Ref:
Azure CIS 1.3.0 > 3.3,3.10,3.11

This is to ensure FTP deployments are disabled for App Service. This will be available in App Service : [General Setting]

This will be helpful if we capture the Stack settings under general setting to validate the latest version of applications.

Ref : Azure CIS 1.3.0 : 9.9, 9.10

Is your feature request related to a problem? Please describe.
Would like to have following details associated with the vm table such as --

How to know of the Vm is associated with any endpoint protection e.g. az vm show -g MyResourceGroup -n MyVm -d
Also provide support for the vm extension such as : az vm extension list -g MyResourceGroup --vm-name MyVm
Describe the solution you'd like
A clear and concise description of what you want to happen.

This is to achieve some of the CIS steps ( such as 7.4, 7.5, 7.6)

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Is there a way to query the FRIENDLY names of azure policies? So far, I've only been able to obtain the definition IDs which are completely useless when given to our app teams for compliance reporting. Is there a better way? So far, I have this query:

policyresources
| where properties['policyDefinitionAction'] != "deny"
| where properties['complianceState'] == "NonCompliant"
| where properties['resourceGroup'] != ""
| project properties['resourceId'], resourceGroup, properties['policyDefinitionName'], properties['policyDefinitionReferenceId'], properties['complianceState']
| order by 'resourceId' asc

Its recommended that every Keys in KV must have expiry date. By default, keys never expire. It is thus recommended that keys be rotated in the key vault and set an explicit expiration time for all keys. This ensures that the keys cannot be used beyond their assigned lifetimes.

The existing table does not support to query the same.

Ref : Azure CIS 1.3.0 : 8.1. 8.2

One of the CIs requirement (9.11 Ensure Azure Keyvaults are used to store secrets (Manual)) need to ensure that App service App or Functions application using Azure Key Vault.

We may need to capture few information from this https://docs.microsoft.com/en-us/azure/app-service/app-service-key-vault-references in JSON Template for App Service Configuration.

https://docs.microsoft.com/en-us/rest/api/storagerp/table/list

Describe the new table

Need to query the PostgreSql table to query and verify the CIS section 4.3 (PostgreSQL Database Server)

References

Section 4

We need to achieve some of the steps as per CIS recommendation
4.3.1 Ensure 'Enforce SSL connection' is set to 'ENABLED' for PostgreSQL Database Server (Automated)
4.3.2 Ensure 'Enforce SSL connection' is set to 'ENABLED' for MySQL Database Server (Automated)
4.3.3 Ensure server parameter 'log_checkpoints' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.4 Ensure server parameter 'log_connections' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.5 Ensure server parameter 'log_disconnections' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.6 Ensure server parameter 'connection_throttling' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.7 Ensure server parameter 'log_retention_days' is greater than 3 days for PostgreSQL Database Server (Automated)
4.3.8 Ensure 'Allow access to Azure services' for PostgreSQL Database Server is disabled (Manual)
4.4 Ensure that Azure Active Directory Admin is configured (Automated)
4.5 Ensure SQL server's TDE protector is encrypted with Customer-managed key (Automated)

This registers Azure Active Directory features for the web app.

az webapp identity show --resource-group <RESOURCE_GROUP_NAME> --name
<APP_NAME> --query principalId
The output should return unique Principal ID.

Ref Azure CIS 1.3.0 : 9.5

References
https://docs.microsoft.com/en-us/rest/api/postgresql/servers

This will unblock the following CIS recommendations

Section 4

4.3.1 Ensure 'Enforce SSL connection' is set to 'ENABLED' for PostgreSQL Database Server (Automated)
4.3.3 Ensure server parameter 'log_checkpoints' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.4 Ensure server parameter 'log_connections' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.5 Ensure server parameter 'log_disconnections' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.6 Ensure server parameter 'connection_throttling' is set to 'ON' for PostgreSQL Database Server (Automated)
4.3.7 Ensure server parameter 'log_retention_days' is greater than 3 days for PostgreSQL Database Server (Automated)
4.3.8 Ensure 'Allow access to Azure services' for PostgreSQL Database Server is disabled (Manual)

References
https://docs.microsoft.com/en-us/rest/api/storageservices/list-containers2

This will unblock the following CIS recommendations-

5.1.3 Ensure the storage container storing the activity logs is not publicly accessible (Automated)
5.1.4 Ensure the storage account containing the container with activity logs is encrypted with BYOK (Use Your Own Key) (Automated)

List of tables

• azure_ad_service_principal
• azure_ad_user
• azure_ad_user
• azure_cosmosdb_account
• azure_cosmosdb_mongo_database
• azure_cosmosdb_sql_database
• azure_management_lock
• azure_provider
• azure_resource_group
• azure_role_assignment
• azure_role_definition
• azure_storage_account
• azure_storage_queue
• azure_subnet

https://docs.microsoft.com/en-us/rest/api/sql/servers/list

This is one of the CIS requirement to check if the old style VHDs are in use, if they can be detected for encryption check.

Ref: Azure CIS 1.3.0 : 7.7

Is your feature request related to a problem? Please describe.
Need to verify one CIS recommendations for which the table should contain the logging details for blob service.
https://docs.microsoft.com/en-us/rest/api/storageservices/get-blob-service-properties

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Azure tables that were using ItemFromKey forget API call in table schema are breaking.

> select * from azure_ad_group where object_id = '66e4618f-1eda-45d6-914c-6613a1ac2cfd';
Error: pq: rpc error: code = Internal desc = get hydrate function getAdGroup failed with panic interface conversion: interface {} is nil, not *graphrbac.ADGroup

> select * from azure_ad_user where object_id = '1cbf1ad0-993a-47f4-a927-64cf4a2ecb5f';
Error: pq: rpc error: code = Internal desc = get hydrate function getAdUser failed with panic interface conversion: interface {} is nil, not *graphrbac.User

Tables impacted

• azure_ad_user
• azure_ad_service_principal
• azure_ad_user
• azure_cosmosdb_account
• azure_cosmosdb_mongo_database
• azure_cosmosdb_sql_database
• azure_management_lock
• azure_provider
• azure_resource_group
• azure_role_assignment
• azure_role_definition
• azure_storage_account
• azure_storage_queue
• azure_subnet

1. Authentication / Authorization.
2. TLS encryption
3. FTP deployments are disabled

Remove managed_disk_storage_account_type, os_disk_size_gb from azure_compute_virtual_machine. These fields are unreliable as they are null if the vm is not running. The azure_compute_disk has this information.

Add power_state, private_ips, public_ips to azure_compute_virtual_machine. These are common fields related to a vm that are difficult to obtain in the current table structure.

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.