About Azure Database for PostgreSQL backup

Azure Backup and Azure Database Services have come together to build an enterprise-class backup solution for Azure Database for PostgreSQL servers that retains backups for up to 10 years. Besides long-term retention, the solution offers the following capabilities:

• Customer controlled scheduled and on-demand backups at the individual database level.
• Database-level restores to any PostgreSQL server or to any blob storage.
• Central monitoring of all operations and jobs.
• Backups are stored in separate security and fault domains. If the source server or subscription is compromised in any circumstances, the backups remain safe in the Backup vault (in Azure Backup managed storage accounts).
• Use of pg_dump allows a greater flexibility in restores. This helps you restore across database versions

You can use this solution independently or in addition to the native backup solution offered by Azure PostgreSQL that offers retention up to 35 days. The native solution is suited for operational recoveries, such as when you want to recover from the latest backups. The Azure Backup solution helps you with your compliance needs and more granular and flexible backup/restore.

Note

Azure Database for PostgreSQL - Single Server is on the retirement path and is scheduled for retirement by March 28, 2025.

If you currently have an Azure Database for PostgreSQL - Single Server service hosting production servers, we're glad to inform you that you can migrate your Azure Database for PostgreSQL - Single Server to the Azure Database for PostgreSQL - Flexible Server.

Azure Database for PostgreSQL - Flexible Server is a fully managed production-ready> database service designed for more granular control and flexibility over database management functions and configuration settings with the enterprise grade backup solution by Azure Backup. For more information about Azure Database for PostgreSQL - Flexible Server, visit Azure Database for PostgreSQL - Flexible Server.

Backup process

1. As a backup admin, you can specify the Azure PostgreSQL databases that you intend to back up. Additionally, you can also specify the details of the Azure key vault that stores the credentials needed to connect to the specified database(s). These credentials are securely seeded by the database admin in the Azure key vault.
2. The backup service then validates if it has appropriate permissions to authenticate with the specified PostgreSQL server and to back up its databases.
3. Azure Backup spins up a worker role (VM) with a backup extension installed in it to communicate with the protected PostgreSQL server. This extension consists of a coordinator and a PostgreSQL plugin. The coordinator triggers workflows for various operations, such as backup and restore, and the plugin manages the actual data flow.
4. At the scheduled time, the coordinator communicates with the plugin, for it to start streaming the backup data from the PostgreSQL server using pg_dump (custom).
5. The plugin sends the data directly to the Azure Backup managed storage accounts (masked by the Backup vault), eliminating the need for a staging location. The data is encrypted using Microsoft-managed keys and stored by the Azure Backup service in storage accounts.

Azure Backup authentication with the PostgreSQL server

Azure Backup follows strict security guidelines laid down by Azure; permissions on the resource to be backed up aren't assumed and need to be explicitly given by the user.

Key-vault based authentication model

The Azure Backup service needs to connect to the Azure PostgreSQL while taking each backup. While ‘username + password’ (or connection string), corresponding to the database, are used to make this connection, these credentials aren’t stored with Azure Backup. Instead, these credentials need to be securely seeded by the database admin in the Azure key vault as a secret. The workload admin is responsible to manage and rotate credentials; Azure Backup calls for the most recent secret details from the key vault to take the backup.

Set of permissions needed for Azure PostgreSQL database backup

1. Grant the following access permissions to the Backup vault’s MSI:

   • Reader access on the Azure PostgreSQL server.
   • Key Vault Secrets User (or get, list secrets) access on the Azure key vault.

2. Network line of sight access on:

   • The Azure PostgreSQL server – Allow access to Azure services flag to be set to Yes.
   • The key vault – Allow trusted Microsoft services flag to be set to Yes.

3. Database user’s backup privileges on the database

Note

You can grant these permissions within the configure backup flow with a single click if you (the backup admin) have ‘write’ access on the intended resources, or use an ARM template if you don’t have the required permissions (when multiple personas are involved).

Set of permissions needed for Azure PostgreSQL database restore

Permissions for restore are similar to the ones needed for backup and you need to grant the permissions on the target PostgreSQL server and its corresponding key vault. Unlike in configure backup flow, the experience to grant these permissions inline is currently not available. Therefore, you need to manually grant the access on the Postgres server and the corresponding key vault.

Additionally, ensure that the database user (corresponding to the credentials stored in the key vault) has the following restore privileges on the database:

• ALTER USER username CREATEDB;
• Assign the role azure_pg_admin to the database user.

Microsoft Entra ID based authentication model

We had earlier launched a different authentication model that was entirely based on Microsoft Entra ID. However, we now provide the new key vault-based authentication model (as explained above) as an alternative option, which eases the configuration process.

Download this document to get an automated script and related instructions to use this authentication model. It’ll grant an appropriate set of permissions to an Azure PostgreSQL server, for backup and restore.

Note

All the new configure protection will take place with the new key vault authentication model only. However, all the existing backup instances configured protection with the Microsoft Entra ID based authentication will continue to exist and have regular backups taken. To restore these backups, you need to follow the Microsoft Entra ID based authentication.

Grant access on the Azure PostgreSQL server and Key vault manually

To grant all the access permissions needed by Azure Backup, refer to the following sections:

Access permissions on the Azure PostgreSQL server

1. Set Backup vault’s MSI Reader access on the Azure PostgreSQL server.

   

2. Network line of sight access on the Azure PostgreSQL server: Set ‘Allow access to Azure services’ flag to ‘Yes’.

   

Access permissions on the Azure Key vault (associated with the PostgreSQL server)

1. Set Backup vault’s MSI Key Vault Secrets User (or get, list secrets) access on the Azure key vault. To assign permissions, you can use role assignments or access policies. It’s not required to add the permission using both the options as it doesn’t help.

   • Using Azure role-based access control (Azure RBAC) authorization (that is, Permission model is set to Azure role-based access control):

     • Under Access control, grant the backup vault’s MSI Key Vault Secrets User access on the key vault. Bearers of that role will be able to read secrets.
     • Grant permission to applications to access an Azure key vault using Azure RBAC.

   

   

   • Using access policies (that is, Permission model is set to Vault access policy):

     • Set Get and List permissions on secrets.
     • Learn about Assign an Azure Key Vault access policy

     

2. Network line of sight access on the key vault: Set the Allow trusted Microsoft services flag to Yes.

   

Database user’s backup privileges on the database

Run the following query in PG admin tool (replace username with the database user ID):

DO $do$
DECLARE
sch text;
BEGIN
EXECUTE format('grant connect on database %I to %I', current_database(), 'username');
FOR sch IN select nspname from pg_catalog.pg_namespace
LOOP
EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO username $$, sch);
EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO username $$, sch);
EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO username $$, sch);
EXECUTE format($$ GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO username $$, sch);
EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON SEQUENCES TO username $$, sch);
END LOOP;
END;
$do$

Note

If a database for which backup was already configured is failing with UserErrorMissingDBPermissions Please refer to this troubleshooting guide for assistance in resolving the issue.

Use the PG admin tool

Download PG admin tool if you don’t have it already. You can connect to the Azure PostgreSQL server through this tool. Also, you can add databases and new users to this server.

Create new server with a name of your choice. Enter the Host name/address name same as the Server name displayed in the Azure PostgreSQL resource view in the Azure portal.

Ensure that you add the current client ID address to the Firewall rules for the connection to go through.

You can add new databases and database users to the server. For database users, add a new Login/Group Roles’. Ensure Can login? is set to Yes.

Next steps

Azure Database for PostgreSQL backup