Detections
Analytics

8.3 Ensure the backup and restore tool, 'pgBackRest', is installed and configured

Information

pgBackRest aims to be a simple, reliable backup and restore system that can seamlessly scale up to the largest databases and workloads. Instead of relying on traditional backup tools like tar and rsync, pgBackRest implements all backup features internally and uses a custom protocol for communicating with remote systems. Removing reliance on tar and rsync allows for better solutions to database-specific backup challenges. The custom remote protocol allows for more flexibility and limits the types of connections that are required to perform a backup which increases security.
Rationale:
The native PostgreSQL backup facility pg_dump provides adequate logical backup operations but does not provide for Point In Time Recovery (PITR). The PostgreSQL facility pg_basebackup performs physical backup of the database files and does provide for PITR, but it is constrained by single threading. Both of these methodologies are standard in the PostgreSQL ecosystem and appropriate for particular backup/recovery needs. pgBackRest offers another option with much more robust features and flexibility.
pgBackRest is open source software developed to perform efficient backups on PostgreSQL databases that measure in tens of terabytes and greater. It supports per file checksums, compression, partial/failed backup resume, high-performance parallel transfer, asynchronous archiving, tablespaces, expiration, full/differential/incremental, local/remote operation via SSH, hard-linking, restore, backup encryption, and more. pgBackRest is written in C and Perl and does not depend on rsync or tar but instead performs its own deltas which gives it maximum flexibility. Finally, pgBackRest provides an easy to use internal repository listing backup details accessible via the pgbackrest info command, as illustrated below.
$ pgbackrest info
stanza: proddb01
status: ok
db (current)
 wal archive min/max (9.6-1): 000000010000000000000012 / 000000010000000000000017
 full backup: 20181002-153106F
 timestamp start/stop: 2018-10-02 15:31:06 / 2018-10-02 15:31:49
 wal start/stop: 000000010000000000000012 / 000000010000000000000012
 database size: 29.4MB, backup size: 29.4MB
 repository size: 3.4MB, repository backup size: 3.4MB
 diff backup: 20181002-153106F_20181002-173109D
 timestamp start/stop: 2018-10-02 17:31:09 / 2018-10-02 17:31:19
 wal start/stop: 000000010000000000000015 / 000000010000000000000015
 database size: 29.4MB, backup size: 2.6MB
 repository size: 3.4MB, repository backup size: 346.8KB
 backup reference list: 20181002-153106F
 incr backup: 20181002-153106F_20181002-183114I
 timestamp start/stop: 2018-10-02 18:31:14 / 2018-10-02 18:31:22
 wal start/stop: 000000010000000000000017 / 000000010000000000000017
 database size: 29.4MB, backup size: 8.2KB
 repository size: 3.4MB, repository backup size: 519B
 backup reference list: 20181002-153106F, 20181002-153106F_20181002-173109D
 NOTE: Nessus has provided the target output to assist in reviewing the benchmark to ensure target compliance.

Solution

pgBackRest is not installed nor configured for PostgreSQL by default, but instead is maintained as a GitHub project. Fortunately, it is a part of the PGDG repository and can be easily installed:
$ whoami
root
$ yum -y install pgbackrest.x86_64
Loaded plugins: fastestmirror
Setting up Install Process
Loading mirror speeds from cached hostfile
 * base: centos.mirrors.tds.net
 * extras: mirror.cs.vt.edu
 * updates: mirror.cs.pitt.edu
Resolving Dependencies
--> Running transaction check
---> Package pgbackrest.x86_64 0:2.05-1.rhel6 will be installed
--> Processing Dependency: perl-parent for package: pgbackrest-2.05-1.rhel6.x86_64
--> Processing Dependency: perl-XML-LibXML for package: pgbackrest-2.05-1.rhel6.x86_64
--> Processing Dependency: perl-Time-HiRes for package: pgbackrest-2.05-1.rhel6.x86_64
--> Processing Dependency: perl-JSON for package: pgbackrest-2.05-1.rhel6.x86_64
--> Processing Dependency: perl-IO-Socket-SSL for package: pgbackrest-2.05-1.rhel6.x86_64
--> Processing Dependency: perl-Digest-SHA for package: pgbackrest-2.05-1.rhel6.x86_64
--> Processing Dependency: perl-DBD-Pg for package: pgbackrest-2.05-1.rhel6.x86_64
--> Running transaction check
---> Package perl-DBD-Pg.x86_64 0:2.15.1-4.el6_3 will be installed
--> Processing Dependency: perl(DBI) for package: perl-DBD-Pg-2.15.1-4.el6_3.x86_64
---> Package perl-Digest-SHA.x86_64 1:5.47-144.el6 will be installed
---> Package perl-IO-Socket-SSL.noarch 0:1.31-3.el6_8.2 will be installed
--> Processing Dependency: perl-Net-SSLeay >= 1.35-10.el6_8.1 for package: perl-IO-Socket-SSL-1.31-3.el6_8.2.noarch
--> Processing Dependency: perl(Net::LibIDN) for package: perl-IO-Socket-SSL-1.31-3.el6_8.2.noarch
---> Package perl-JSON.noarch 0:2.15-5.el6 will be installed
--> Processing Dependency: perl(LWP::UserAgent) for package: perl-JSON-2.15-5.el6.noarch
--> Processing Dependency: perl(HTTP::Daemon) for package: perl-JSON-2.15-5.el6.noarch
---> Package perl-Time-HiRes.x86_64 4:1.9721-144.el6 will be installed
---> Package perl-XML-LibXML.x86_64 1:1.70-5.el6 will be installed
--> Processing Dependency: perl(XML::SAX::Exception) for package: 1:perl-XML-LibXML-1.70-5.el6.x86_64
--> Processing Dependency: perl(XML::SAX::DocumentLocator) for package: 1:perl-XML-LibXML-1.70-5.el6.x86_64
--> Processing Dependency: perl(XML::SAX::Base) for package: 1:perl-XML-LibXML-1.70-5.el6.x86_64
--> Processing Dependency: perl(XML::NamespaceSupport) for package: 1:perl-XML-LibXML-1.70-5.el6.x86_64
---> Package perl-parent.x86_64 1:0.221-144.el6 will be installed
--> Running transaction check
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
---> Package perl-Net-LibIDN.x86_64 0:0.12-3.el6 will be installed
---> Package perl-Net-SSLeay.x86_64 0:1.35-10.el6_8.1 will be installed
---> Package perl-XML-NamespaceSupport.noarch 0:1.10-3.el6 will be installed
---> Package perl-XML-SAX.noarch 0:0.96-7.el6 will be installed
---> Package perl-libwww-perl.noarch 0:5.833-5.el6 will be installed
--> Processing Dependency: perl-HTML-Parser >= 3.33 for package: perl-libwww-perl-5.833-5.el6.noarch
--> Processing Dependency: perl(URI::URL) for package: perl-libwww-perl-5.833-5.el6.noarch
--> Processing Dependency: perl(URI::Heuristic) for package: perl-libwww-perl-5.833-5.el6.noarch
--> Processing Dependency: perl(URI) for package: perl-libwww-perl-5.833-5.el6.noarch
--> Processing Dependency: perl(HTML::Entities) for package: perl-libwww-perl-5.833-5.el6.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-libwww-perl-5.833-5.el6.noarch
--> Processing Dependency: mailcap for package: perl-libwww-perl-5.833-5.el6.noarch
--> Running transaction check
---> Package mailcap.noarch 0:2.1.31-2.el6 will be installed
---> Package perl-Compress-Zlib.x86_64 0:2.021-144.el6 will be installed
--> Processing Dependency: perl(IO::Uncompress::Gunzip) >= 2.021 for package: perl-Compress-Zlib-2.021-144.el6.x86_64
--> Processing Dependency: perl(IO::Compress::Gzip::Constants) >= 2.021 for package: perl-Compress-Zlib-2.021-144.el6.x86_64
--> Processing Dependency: perl(IO::Compress::Gzip) >= 2.021 for package: perl-Compress-Zlib-2.021-144.el6.x86_64
--> Processing Dependency: perl(IO::Compress::Base::Common) >= 2.021 for package: perl-Compress-Zlib-2.021-144.el6.x86_64
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.021 for package: perl-Compress-Zlib-2.021-144.el6.x86_64
---> Package perl-HTML-Parser.x86_64 0:3.64-2.el6 will be installed
--> Processing Dependency: perl(HTML::Tagset) >= 3.03 for package: perl-HTML-Parser-3.64-2.el6.x86_64
--> Processing Dependency: perl(HTML::Tagset) for package: perl-HTML-Parser-3.64-2.el6.x86_64
---> Package perl-URI.noarch 0:1.40-2.el6 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.021-144.el6 will be installed
---> Package perl-HTML-Tagset.noarch 0:3.20-4.el6 will be installed
---> Package perl-IO-Compress-Base.x86_64 0:2.021-144.el6 will be installed
---> Package perl-IO-Compress-Zlib.x86_64 0:2.021-144.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===========================================================================================================
 Package Arch Version Repository Size
===========================================================================================================
Installing:
 pgbackrest x86_64 2.05-1.rhel6 pgdg96 203 k
Installing for dependencies:
 mailcap noarch 2.1.31-2.el6 base 27 k
 perl-Compress-Raw-Zlib x86_64 1:2.021-144.el6 base 70 k
 perl-Compress-Zlib x86_64 2.021-144.el6 base 46 k
 perl-DBD-Pg x86_64 2.15.1-4.el6_3 base 196 k
 perl-DBI x86_64 1.609-4.el6 base 705 k
 perl-Digest-SHA x86_64 1:5.47-144.el6 base 65 k
 perl-HTML-Parser x86_64 3.64-2.el6 base 109 k
 perl-HTML-Tagset noarch 3.20-4.el6 base 17 k
 perl-IO-Compress-Base x86_64 2.021-144.el6 base 70 k
 perl-IO-Compress-Zlib x86_64 2.021-144.el6 base 136 k
 perl-IO-Socket-SSL noarch 1.31-3.el6_8.2 base 70 k
 perl-JSON noarch 2.15-5.el6 base 97 k
 perl-Net-LibIDN x86_64 0.12-3.el6 base 35 k
 perl-Net-SSLeay x86_64 1.35-10.el6_8.1 base 174 k
 perl-Time-HiRes x86_64 4:1.9721-144.el6 base 49 k
 perl-URI noarch 1.40-2.el6 base 117 k
 perl-XML-LibXML x86_64 1:1.70-5.el6 base 364 k
 perl-XML-NamespaceSupport noarch 1.10-3.el6 base 17 k
 perl-XML-SAX noarch 0.96-7.el6 base 78 k
 perl-libwww-perl noarch 5.833-5.el6 base 390 k
 perl-parent x86_64 1:0.221-144.el6 base 28 k
Transaction Summary
===========================================================================================================
Install 22 Package(s)
Total download size: 3.0 M
Installed size: 7.5 M
Downloading Packages:
(1/22): mailcap-2.1.31-2.el6.noarch.rpm | 27 kB 00:00
(2/22): perl-Compress-Raw-Zlib-2.021-144.el6.x86_64.rpm | 70 kB 00:00
(3/22): perl-Compress-Zlib-2.021-144.el6.x86_64.rpm | 46 kB 00:00
(4/22): perl-DBD-Pg-2.15.1-4.el6_3.x86_64.rpm | 196 kB 00:00
(5/22): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00
(6/22): perl-Digest-SHA-5.47-144.el6.x86_64.rpm | 65 kB 00:00
(7/22): perl-HTML-Parser-3.64-2.el6.x86_64.rpm | 109 kB 00:00
(8/22): perl-HTML-Tagset-3.20-4.el6.noarch.rpm | 17 kB 00:00
(9/22): perl-IO-Compress-Base-2.021-144.el6.x86_64.rpm | 70 kB 00:00
(10/22): perl-IO-Compress-Zlib-2.021-144.el6.x86_64.rpm | 136 kB 00:00
(11/22): perl-IO-Socket-SSL-1.31-3.el6_8.2.noarch.rpm | 70 kB 00:00
(12/22): perl-JSON-2.15-5.el6.noarch.rpm | 97 kB 00:00
(13/22): perl-Net-LibIDN-0.12-3.el6.x86_64.rpm | 35 kB 00:00
(14/22): perl-Net-SSLeay-1.35-10.el6_8.1.x86_64.rpm | 174 kB 00:00
(15/22): perl-Time-HiRes-1.9721-144.el6.x86_64.rpm | 49 kB 00:00
(16/22): perl-URI-1.40-2.el6.noarch.rpm | 117 kB 00:00
(17/22): perl-XML-LibXML-1.70-5.el6.x86_64.rpm | 364 kB 00:00
(18/22): perl-XML-NamespaceSupport-1.10-3.el6.noarch.rpm | 17 kB 00:00
(19/22): perl-XML-SAX-0.96-7.el6.noarch.rpm | 78 kB 00:00
(20/22): perl-libwww-perl-5.833-5.el6.noarch.rpm | 390 kB 00:00
(21/22): perl-parent-0.221-144.el6.x86_64.rpm | 28 kB 00:00
(22/22): pgbackrest-2.05-1.rhel6.x86_64.rpm | 203 kB 00:00
-----------------------------------------------------------------------------------------------------------
Total 1.1 MB/s | 3.0 MB 00:02
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
 Installing : perl-IO-Compress-Base-2.021-144.el6.x86_64 1/22
 Installing : 1:perl-Compress-Raw-Zlib-2.021-144.el6.x86_64 2/22
 Installing : perl-XML-NamespaceSupport-1.10-3.el6.noarch 3/22
 Installing : 1:perl-XML-LibXML-1.70-5.el6.x86_64 4/22
 Installing : perl-XML-SAX-0.96-7.el6.noarch 5/22
 Installing : perl-IO-Compress-Zlib-2.021-144.el6.x86_64 6/22
 Installing : perl-Compress-Zlib-2.021-144.el6.x86_64 7/22
 Installing : 1:perl-Digest-SHA-5.47-144.el6.x86_64 8/22
 Installing : 1:perl-parent-0.221-144.el6.x86_64 9/22
 Installing : perl-DBI-1.609-4.el6.x86_64 10/22
 Installing : perl-DBD-Pg-2.15.1-4.el6_3.x86_64 11/22
 Installing : perl-HTML-Tagset-3.20-4.el6.noarch 12/22
 Installing : perl-HTML-Parser-3.64-2.el6.x86_64 13/22
 Installing : perl-URI-1.40-2.el6.noarch 14/22
 Installing : perl-Net-LibIDN-0.12-3.el6.x86_64 15/22
 Installing : mailcap-2.1.31-2.el6.noarch 16/22
 Installing : perl-libwww-perl-5.833-5.el6.noarch 17/22
 Installing : perl-JSON-2.15-5.el6.noarch 18/22
 Installing : 4:perl-Time-HiRes-1.9721-144.el6.x86_64 19/22
 Installing : perl-Net-SSLeay-1.35-10.el6_8.1.x86_64 20/22
 Installing : perl-IO-Socket-SSL-1.31-3.el6_8.2.noarch 21/22
 Installing : pgbackrest-2.05-1.rhel6.x86_64 22/22
 Verifying : perl-Compress-Zlib-2.021-144.el6.x86_64 1/22
 Verifying : perl-Net-SSLeay-1.35-10.el6_8.1.x86_64 2/22
 Verifying : pgbackrest-2.05-1.rhel6.x86_64 3/22
 Verifying : 4:perl-Time-HiRes-1.9721-144.el6.x86_64 4/22
 Verifying : mailcap-2.1.31-2.el6.noarch 5/22
 Verifying : perl-Net-LibIDN-0.12-3.el6.x86_64 6/22
 Verifying : perl-libwww-perl-5.833-5.el6.noarch 7/22
 Verifying : perl-XML-NamespaceSupport-1.10-3.el6.noarch 8/22
 Verifying : perl-DBD-Pg-2.15.1-4.el6_3.x86_64 9/22
 Verifying : perl-HTML-Parser-3.64-2.el6.x86_64 10/22
 Verifying : 1:perl-XML-LibXML-1.70-5.el6.x86_64 11/22
 Verifying : 1:perl-Compress-Raw-Zlib-2.021-144.el6.x86_64 12/22
 Verifying : perl-IO-Socket-SSL-1.31-3.el6_8.2.noarch 13/22
 Verifying : perl-URI-1.40-2.el6.noarch 14/22
 Verifying : perl-IO-Compress-Base-2.021-144.el6.x86_64 15/22
 Verifying : perl-HTML-Tagset-3.20-4.el6.noarch 16/22
 Verifying : perl-DBI-1.609-4.el6.x86_64 17/22
 Verifying : perl-XML-SAX-0.96-7.el6.noarch 18/22
 Verifying : 1:perl-parent-0.221-144.el6.x86_64 19/22
 Verifying : 1:perl-Digest-SHA-5.47-144.el6.x86_64 20/22
 Verifying : perl-IO-Compress-Zlib-2.021-144.el6.x86_64 21/22
 Verifying : perl-JSON-2.15-5.el6.noarch 22/22
Installed:
 pgbackrest.x86_64 0:2.05-1.rhel6
Dependency Installed:
 mailcap.noarch 0:2.1.31-2.el6 perl-Compress-Raw-Zlib.x86_64 1:2.021-144.el6
 perl-Compress-Zlib.x86_64 0:2.021-144.el6 perl-DBD-Pg.x86_64 0:2.15.1-4.el6_3
 perl-DBI.x86_64 0:1.609-4.el6 perl-Digest-SHA.x86_64 1:5.47-144.el6
 perl-HTML-Parser.x86_64 0:3.64-2.el6 perl-HTML-Tagset.noarch 0:3.20-4.el6
 perl-IO-Compress-Base.x86_64 0:2.021-144.el6 perl-IO-Compress-Zlib.x86_64 0:2.021-144.el6
 perl-IO-Socket-SSL.noarch 0:1.31-3.el6_8.2 perl-JSON.noarch 0:2.15-5.el6
 perl-Net-LibIDN.x86_64 0:0.12-3.el6 perl-Net-SSLeay.x86_64 0:1.35-10.el6_8.1
 perl-Time-HiRes.x86_64 4:1.9721-144.el6 perl-URI.noarch 0:1.40-2.el6
 perl-XML-LibXML.x86_64 1:1.70-5.el6 perl-XML-NamespaceSupport.noarch 0:1.10-3.el6
 perl-XML-SAX.noarch 0:0.96-7.el6 perl-libwww-perl.noarch 0:5.833-5.el6
 perl-parent.x86_64 1:0.221-144.el6
Complete!
Once installed, pgBackRest must be configured for things like stanza name, backup location, retention policy, logging, etc. Please consult the configuration guide.
If employing pgBackRest for your backup/recovery solution, ensure the repository, base backups, and WAL archives are stored on a reliable file system separate from the database server. Further, the external storage system where backups resided should have limited access to only those system administrators as necessary. Finally, as with any backup/recovery solution, stringent testing must be conducted. A backup is only good if it can be restored successfully.

See Also

https://workbench.cisecurity.org/files/2234

Item Details

Category: CONTINGENCY PLANNING

References: 800-53|CP-9, CSCv6|10, CSCv7|10.1, CSCv7|10.2

Plugin: Unix

Control ID: ac2f9bdcf558940247f6477c6c7a6b6177d292b97568029a04a5c97e87d3d163