Enhancing PostgreSQL Security: How to Encrypt the pgBackRest Repository
source link: https://www.percona.com/blog/enhancing-postgresql-security-how-to-encrypt-the-pgbackrest-repository/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Enhancing PostgreSQL Security: How to Encrypt the pgBackRest Repository
November 30, 2023
Encryption is the process of turning data into an unrecognizable format unless the necessary password (also known as passphrase) or decryption key is provided.
This blog describes how to encrypt the pgBackRest repository. pgBackRest is the backup tool used to perform Postgres database backup, restoration, and point-in-time recovery (PITR). The repository is where pgBackRest stores backups and archives WAL segments.
pgBackRest will encrypt the repository based on a user-provided password, thereby preventing unauthorized access to data stored within the repository.
In this demonstration, it is assumed that the pgBackRest is already installed and configured on the dedicated backup node and configured to take backups from the remote database node. The repository will be configured with a cipher type and key to demonstrate encryption.
Follow the below steps to encrypt the pgBackRest repository:
Backup node 172.20.20.20 (Dummy IP)
Remote DB node 172.15.15.15 (Dummy IP)
1) First, generate the cipher key. pgBackRest will use this cipher key to encrypt the pgBackRest repository.
It is important to use a long, random passphrase for the cipher key. A good way to generate one is to run: openssl rand -base64 48. (on the backup node):
[email protected]:~$ openssl rand -base64 48 PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co |
2) On the backup node, add the cipher type and key parameters in the pgBackRest configuration file. /etc/pgbackrest.conf:
vi /etc/pgbackrest.conf repo1-cipher-pass=PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co repo1-cipher-type=aes-256-cbc |
If you have an existing pgbackrest setup, then the existing stanza cannot be used after configuring the encryption for the repository. A new stanza needs to be created for taking the backup in the encryption-enabled repository. Using the existing stanza will result in the following error:
In the below example, the existing stanza dbtest has been used.
[email protected]:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest --log-level-console=info backup --type=full 2023-09-08 14:22:06.178 P00 INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=5971-2fe78c47 --log-level-console=info --log-level-file=debug--pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest --start-fast --stop-auto --type=full ERROR: [095]: unable to load info file '/var/lib/pgbackrest/backup/dbtest/backup.info' or '/var/lib/pgbackrest/backup/dbtest/ backup.info.copy': CryptoError: cipher header invalid HINT: is or was the repo encrypted? CryptoError: cipher header invalid HINT: is or was the repo encrypted? HINT: backup.info cannot be opened and is required to perform a backup. HINT: has a stanza-create been performed? 2023-09-08 14:22:06.180 P00 INFO: backup command end: aborted with exception [095] |
The pgBackRest configuration files will look like this after adding the cypher pass (key) and type.
Backup node
cat /etc/pgbackrest.conf: [global] repo1-path=/var/lib/pgbackrest repo1-retention-full=2 process-max=2 log-level-console=info log-level-file=debug start-fast=y stop-auto=y repo1-cipher-pass=PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co repo1-cipher-type=aes-256-cbc [dbtest_new] pg1-path=/var/lib/postgresql/15/main pg1-host=172.15.15.15 pg1-host-user=postgres |
DB node
cat /etc/pgbackrest.conf: [global] repo1-path=/var/lib/pgbackrest repo1-host=172.20.20.20 repo1-host-user=postgres process-max=2 log-level-console=info log-level-file=debug [dbtest_new] pg1-path=/var/lib/postgresql/15/main |
3) Create a new stanza (on the backup node):
[email protected]:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new stanza-create 2023-09-08 14:24:55.779 P00 INFO: stanza-create command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=5980-f29c6484 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=dbtest_new 2023-09-08 14:24:56.927 P00 INFO: stanza-create for stanza 'dbtest_new' on repo1 2023-09-08 14:24:57.045 P00 INFO: stanza-create command end: completed successfully (1269ms) |
4) Update the archive_command with the new stanza details on the DB node:
postgres=# ALTER SYSTEM SET archive_command = '/bin/pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new archive-push %p'; ALTER SYSTEM |
5) Reload the Postgres cluster (on the DB node):
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- (1 row) |
6) Execute the check command. The check command validates that pgBackRest and the archive_command setting are configured correctly for archiving and backups for the specified stanza:
[email protected]:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info check 2023-09-08 15:26:34.349 P00 INFO: check command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=7993-5acde7b9 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=dbtest_new 2023-09-08 15:26:35.585 P00 INFO: check repo1 configuration (primary) 2023-09-08 15:26:35.788 P00 INFO: check repo1 archive for WAL (primary) 2023-09-08 15:26:36.990 P00 INFO: WAL segment 000000010000000000000018 successfully archived to '/var/lib/pgbackrest/archive/dbtest_new/15-1/0000000100000000/000000010000000000000018-7cef04977b8b50f102a3d74ace8ab1cc4a035c8d.gz' on repo1 2023-09-08 15:26:37.092 P00 INFO: check command end: completed successfully (2745ms) |
7) Perform a FULL backup:
[email protected]:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info backup --type=full 2023-09-08 15:26:49.028 P00 INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8060-e6fa0627 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new --start-fast --stop-auto --type=full 2023-09-08 15:26:50.016 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2023-09-08 15:26:50.622 P00 INFO: backup start archive = 00000001000000000000001A, lsn = 0/1A000028 2023-09-08 15:26:50.622 P00 INFO: check archive for prior segment 000000010000000000000019 2023-09-08 15:26:54.242 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2023-09-08 15:26:54.447 P00 INFO: backup stop archive = 00000001000000000000001A, lsn = 0/1A000100 2023-09-08 15:26:54.454 P00 INFO: check archive for segment(s) 00000001000000000000001A:00000001000000000000001A 2023-09-08 15:26:54.970 P00 INFO: new backup label = 20230908-152649F 2023-09-08 15:26:55.024 P00 INFO: full backup size = 22.0MB, file total = 961 2023-09-08 15:26:55.024 P00 INFO: backup command end: completed successfully (5999ms) 2023-09-08 15:26:55.025 P00 INFO: expire command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8060-e6fa0627 --log-level-console=info --log-level-file=debug --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new 2023-09-08 15:26:55.026 P00 INFO: repo1: expire full backup 20230908-145538F 2023-09-08 15:26:55.035 P00 INFO: repo1: remove expired backup 20230908-145538F 2023-09-08 15:26:55.068 P00 INFO: repo1: 15-1 remove archive, start = 000000010000000000000015, stop = 000000010000000000000016 2023-09-08 15:26:55.068 P00 INFO: expire command end: completed successfully (43ms) |
8) Perform a DIFFERENTIAL backup (optional step):
|
9) To check out the backup status and its details, use the info command:
[email protected]:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new info stanza: dbtest_new status: ok cipher: aes-256-cbc db (current) wal archive min/max (15): 000000010000000000000017/00000001000000000000001C full backup: 20230908-151854F timestamp start/stop: 2023-09-08 15:18:54+00 / 2023-09-08 15:18:59+00 wal start/stop: 000000010000000000000017 / 000000010000000000000017 database size: 22.0MB, database backup size: 22.0MB repo1: backup set size: 2.9MB, backup size: 2.9MB full backup: 20230908-152649F timestamp start/stop: 2023-09-08 15:26:49+00 / 2023-09-08 15:26:54+00 wal start/stop: 00000001000000000000001A / 00000001000000000000001A database size: 22.0MB, database backup size: 22.0MB repo1: backup set size: 2.9MB, backup size: 2.9MB diff backup: 20230908-152649F_20230908-152702D timestamp start/stop: 2023-09-08 15:27:02+00 / 2023-09-08 15:27:04+00 wal start/stop: 00000001000000000000001C / 00000001000000000000001C database size: 22.0MB, database backup size: 8.3KB repo1: backup set size: 2.9MB, backup size: 512B backup reference list: 20230908-152649F |
Conclusion
pgBackRest is a completely free and open source backup tool available for PostgreSQL, and here we have seen the steps to encrypt the pgBackRest repository. You may use it for personal or commercial purposes without any restrictions whatsoever. Its advanced backup repository encryption feature is very easy to implement.
To learn more about the pgBackRest backup tool and encryption, click the links below:
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
Share This Post!
Subscribe
Label
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK