Configure the Azure Storage extension in Azure Database for PostgreSQL flexible server

To use the Azure Storage extension, complete the following steps:

  1. Identify Azure Storage accounts
  2. Choose type of authorization
  3. Load the extension's library
  4. Allow list the extension
  5. Create the extension
  6. Initialize encryption key to encrypt sensitive credentials
  7. Use the extension to import and export data

Identify the Azure Storage accounts

Identify the Azure Storage accounts that you want users of the extension to interact with, to import data from or export data to.

Choose type of authorization

Decide which type of authorization you want to use for the requests made against the blob service of each of those Azure Storage accounts. The azure_storage extension supports authorization by using Shared Key, and authorization by using Microsoft Entra ID.

Of these two types of authorization, Microsoft Entra ID provides superior security and ease of use over Shared Key, and is the one Microsoft recommends.

To meet the prerequisites needed in each case, follow the instructions in the corresponding sections:

To use authorization with Microsoft Entra ID

  1. Enable Firewall rules in Azure Database for PostgreSQL on your Azure Database for PostgreSQL flexible server.
  2. Restart PostgreSQL engine, after enabling a system assigned managed identity on it.
  3. Assign role-based access control (RBAC) permissions for access to blob data on the Azure Storage account to the system assigned managed identity of your Azure Database for PostgreSQL flexible server.

Enable System Assigned Managed Identity

Screenshot of enabling System Assigned Managed Identity.

To use authorization with Shared Key

  1. Confirm that storage account allows access to its key
  2. Fetch one of the two access keys of the storage account

Confirm that storage account allows access to its key

Your Azure Storage account must have Allow storage account key access enabled (that is, it can't have its AllowSharedKeyAccess property set to false).

Screenshot of confirming that Allow storage account key access is enabled.

Fetch one of the two access keys of the storage account

To pass it to the azure_storage.account_add function, fetch either of the two access keys of the Azure Storage account.

Screenshot of copying storage account access key.

Load the extension's library

Configure your server to load the azure_storage binary module when it starts.

Screenshot of selecting azure_storage in shared_preload_libraries in parameters. Because the shared_preload_libraries parameter is static, you must restart the server for a change to take effect: Screenshot of dialog that pops up when changing shared_preload_libraries, to save and restart.

Allow list the extension

Add the extension to the allow list so that users can run CREATE EXTENSION, DROP EXTENSION, ALTER EXTENSION, and COMMENT ON EXTENSION.

Screenshot of selecting azure_storage in azure.extensions in parameters.

Create the extension

Use the client of your preference, such as PostgreSQL for Visual Studio Code (Preview), psql, or PgAdmin, to connect to the database where you want to use the Azure Storage extension.

To create all SQL objects (tables, types, functions, views, and so on) with which you can use the azure_storage extension to interact with instances of Azure Storage accounts, execute the following statement:

CREATE EXTENSION azure_storage;

Initialize encryption key to encrypt sensitive credentials

Use the client of your preference, such as PostgreSQL for Visual Studio Code (Preview), psql, or PgAdmin, to connect to the database where you want to use the Azure Storage extension.

To initialize the encryption key for all sensitive credentials used to authenticate with the different Azure storage accounts, run the following statement:

Note

Make sure you change <strong passphrase> to your own strong secret.

ALTER DATABASE <database_with_created_extension> SET azure_storage.credential_encryption_key = '<strong_passphrase>';

If you create the extension in multiple databases, you must initialize the value of azure_storage.credential_encryption_key at the database level, so all sensitive credentials kept in that database are encrypted by using the same key.

To set the value of azure_storage.credential_encryption_key, you must be member of the azure_storage_admin role. Then connect to the server, in the context of the database where you created the extension. In that context, execute ALTER DATABASE <database_with_created_extension> SET azure_storage.credential_encryption_key = '<strong passphrase>'; to initialize the encryption key that's used to encrypt all Azure storage account credentials that the extension keeps in the catalog of that database. After running this command, you must disconnect and reconnect to the database again, so that the override value takes effect. You should also invoke the azure_storage.account_encrypt_existing_credentials() function so that the credentials of existing accounts that were never encrypted before by using any other key are encrypted by using this key. To do so, execute SELECT azure_storage.account_encrypt_existing_credentials();.

Although possible, don't try to use other statements like ALTER ROLE or ALTER ROLE IN DATABASE to set the value of azure_storage.credential_encryption_key.

If you change the value of azure_storage.credential_encryption_key, you must manually add again, by using azure_storage.account_add, all storage accounts for which you provided a sensitive credential (an access key or a SAS token) that was encrypted by using the previous value. Currently, the extension doesn't support automatic rollover of encryption key.

Use the extension to import and export data

Now you're ready to add the storage accounts with which you want to interact (using the azure_storage.account_add function). Then you can import data stored in files in Azure Storage accounts, by using the azure_storage.blob_get function or the COPY FROM statement, or you can export data from PostgreSQL into files in an Azure Storage account, by using the azure_storage.blob_put function or the COPY TO statement.

Check out the list of quickstart examples:

In case you need to review all functions offered by the extension and all the details about each of them, review the full reference:

And, if you need to do some troubleshooting, review the list of errors that the extension can produce, and the context in which they can be raised.

Important

For authentication types that require an Azure Storage account access key, remember that your Azure Storage access keys are similar to a root password for your storage account. Always protect them. Use Azure Key Vault to manage and rotate your keys securely. The azure_storage extension stores those keys in a table azure_storage.accounts, which members of the pg_read_all_data role can read.

Related content

  • Last updated on