cis postgresql 11 benchmark v1.0.0...this document, cis postgresql 11 benchmark, provides...

CISPostgreSQL11Benchmarkv1.0.0-06-28-2019

1|P a g e

TermsofUsePlease see the below link for our current terms of use: https://www.cisecurity.org/cis-securesuite/cis-securesuite-membership-terms-of-use/

2|P a g e

TableofContents

TermsofUse...................................................................................................................................................................1

Overview..........................................................................................................................................................................5

IntendedAudience..................................................................................................................................................5

ConsensusGuidance..............................................................................................................................................5

TypographicalConventions...............................................................................................................................6

ScoringInformation...............................................................................................................................................6

ProfileDefinitions...................................................................................................................................................7

Acknowledgements................................................................................................................................................8

Recommendations.......................................................................................................................................................9

1InstallationandPatches...................................................................................................................................9

1.1Ensurepackagesareobtainedfromauthorizedrepositories(NotScored)........9

1.2EnsureInstallationofBinaryPackages(NotScored)...................................................13

1.3EnsureInstallationofCommunityPackages(NotScored)........................................15

1.4EnsuresystemdServiceFilesAreEnabled(Scored).....................................................22

1.5EnsureDataClusterInitializedSuccessfully(Scored)..................................................24

2DirectoryandFilePermissions..................................................................................................................27

2.1Ensurethefilepermissionsmaskiscorrect(Scored)..................................................27

2.2EnsurethePostgreSQLpg_wheelgroupmembershipiscorrect(Scored).........29

3LoggingMonitoringAndAuditing(Centos6).....................................................................................32

3.1PostgreSQLLogging................................................................................................................................32

3.1.1LoggingRationale.......................................................................................................................32

3.1.2Ensurethelogdestinationsaresetcorrectly(Scored)............................................33

3.1.3Ensuretheloggingcollectorisenabled(Scored)........................................................35

3.1.4Ensurethelogfiledestinationdirectoryissetcorrectly(Scored).....................37

3.1.5Ensurethefilenamepatternforlogfilesissetcorrectly(Scored).....................39

3.1.6Ensurethelogfilepermissionsaresetcorrectly(Scored)....................................41

3.1.7Ensure'log_truncate_on_rotation'isenabled(Scored)............................................43

3.1.8Ensurethemaximumlogfilelifetimeissetcorrectly(Scored)...........................46

3.1.9Ensurethemaximumlogfilesizeissetcorrectly(Scored)...................................48

3|P a g e

3.1.10Ensurethecorrectsyslogfacilityisselected(Scored)..........................................50

3.1.11EnsuretheprogramnameforPostgreSQLsyslogmessagesiscorrect(Scored)......................................................................................................................................................52

3.1.12Ensurethecorrectmessagesarewrittentotheserverlog(NotScored)....54

3.1.13EnsurethecorrectSQLstatementsgeneratingerrorsarerecorded(NotScored)........................................................................................................................................................56

3.1.14Ensure'debug_print_parse'isdisabled(Scored).....................................................58

3.1.15Ensure'debug_print_rewritten'isdisabled(Scored).............................................60

3.1.16Ensure'debug_print_plan'isdisabled(Scored)........................................................62

3.1.17Ensure'debug_pretty_print'isenabled(Scored).....................................................64

3.1.18Ensure'log_connections'isenabled(Scored)............................................................66

3.1.19Ensure'log_disconnections'isenabled(Scored).....................................................68

3.1.20Ensure'log_error_verbosity'issetcorrectly(NotScored)..................................70

3.1.21Ensure'log_hostname'issetcorrectly(Scored).......................................................72

3.1.22Ensure'log_line_prefix'issetcorrectly(NotScored).............................................74

3.1.23Ensure'log_statement'issetcorrectly(Scored)......................................................77

3.1.24Ensure'log_timezone'issetcorrectly(Scored)........................................................79

3.2EnsurethePostgreSQLAuditExtension(pgAudit)isenabled(Scored).............81

4UserAccessandAuthorization...................................................................................................................85

4.1Ensuresudoisconfiguredcorrectly(Scored)..................................................................85

4.2Ensureexcessiveadministrativeprivilegesarerevoked(Scored)........................87

4.3Ensureexcessivefunctionprivilegesarerevoked(Scored)......................................90

4.4EnsureexcessiveDMLprivilegesarerevoked(Scored).............................................93

4.5Usepg_permissionextensiontoauditobjectpermissions(NotScored)............97

4.6EnsureRowLevelSecurity(RLS)isconfiguredcorrectly(NotScored)...........102

4.7Ensuretheset_userextensionisinstalled(NotScored)..........................................106

4.8Makeuseofdefaultroles(NotScored).............................................................................121

5ConnectionandLogin...................................................................................................................................123

5.1Ensureloginvia"local"UNIXDomainSocketisconfiguredcorrectly(NotScored).....................................................................................................................................................124

5.2Ensureloginvia"host"TCP/IPSocketisconfiguredcorrectly(Scored).........127

4|P a g e

6PostgreSQLSettings......................................................................................................................................131

6.1Ensure'AttackVectors'RuntimeParametersareConfigured(NotScored)..131

6.2Ensure'backend'runtimeparametersareconfiguredcorrectly(Scored).....133

6.3Ensure'Postmaster'RuntimeParametersareConfigured(NotScored).........135

6.4Ensure'SIGHUP'RuntimeParametersareConfigured(NotScored).................138

6.5Ensure'Superuser'RuntimeParametersareConfigured(NotScored)...........141

6.6Ensure'User'RuntimeParametersareConfigured(NotScored).......................144

6.7EnsureFIPS140-2OpenSSLCryptographyIsUsed(Scored)................................148

6.8EnsureSSLisenabledandconfiguredcorrectly(Scored).......................................152

6.9Ensurethepgcryptoextensionisinstalledandconfiguredcorrectly(NotScored).....................................................................................................................................................155

7Replication.........................................................................................................................................................158

7.1Ensureareplication-onlyuseriscreatedandusedforstreamingreplication(NotScored)...........................................................................................................................................159

7.2Ensurebasebackupsareconfiguredandfunctional(NotScored).....................161

7.3EnsureWALarchivingisconfiguredandfunctional(Scored)..............................163

7.4Ensurestreamingreplicationparametersareconfiguredcorrectly(NotScored).....................................................................................................................................................165

8SpecialConfigurationConsiderations..................................................................................................167

8.1EnsurePostgreSQLconfigurationfilesareoutsidethedatacluster(NotScored).....................................................................................................................................................167

8.2EnsurePostgreSQLsubdirectorylocationsareoutsidethedatacluster(NotScored).....................................................................................................................................................170

8.3Ensurethebackupandrestoretool,'pgBackRest',isinstalledandconfigured(NotScored)...........................................................................................................................................172

8.4Ensuremiscellaneousconfigurationsettingsarecorrect(NotScored)...........183

Appendix:SummaryTable.................................................................................................................................185

Appendix:ChangeHistory..................................................................................................................................188

5|P a g e

OverviewThisdocument,CISPostgreSQL11Benchmark,providesprescriptiveguidanceforestablishingasecureconfigurationpostureforPostgreSQL11.ThisguidewastestedagainstPostgreSQL11runningonCentOS7,butappliestootherLinuxdistributionsaswell.Toobtainthelatestversionofthisguide,pleasevisithttp://benchmarks.cisecurity.org.Ifyouhavequestions,comments,orhaveidentifiedwaystoimprovethisguide,[email protected].

Intended Audience

Thisdocumentisintendedforsystemandapplicationadministrators,securityspecialists,auditors,helpdesk,andplatformdeploymentpersonnelwhoplantodevelop,deploy,assess,orsecuresolutionsthatincorporatePostgreSQL11.

Consensus Guidance

Thisbenchmarkwascreatedusingaconsensusreviewprocesscomprisedofsubjectmatterexperts.Consensusparticipantsprovideperspectivefromadiversesetofbackgroundsincludingconsulting,softwaredevelopment,auditandcompliance,securityresearch,operations,government,andlegal.

EachCISbenchmarkundergoestwophasesofconsensusreview.Thefirstphaseoccursduringinitialbenchmarkdevelopment.Duringthisphase,subjectmatterexpertsconvenetodiscuss,create,andtestworkingdraftsofthebenchmark.Thisdiscussionoccursuntilconsensushasbeenreachedonbenchmarkrecommendations.Thesecondphasebeginsafterthebenchmarkhasbeenpublished.Duringthisphase,allfeedbackprovidedbytheInternetcommunityisreviewedbytheconsensusteamforincorporationinthebenchmark.Ifyouareinterestedinparticipatingintheconsensusprocess,pleasevisithttps://workbench.cisecurity.org/.

6|P a g e

Typographical Conventions

Thefollowingtypographicalconventionsareusedthroughoutthisguide:

Convention Meaning

Stylized Monospace font Usedforblocksofcode,command,andscriptexamples.Textshouldbeinterpretedexactlyaspresented.

Monospace font Usedforinlinecode,commands,orexamples.Textshouldbeinterpretedexactlyaspresented.

<italicfontinbrackets> Italictextssetinanglebracketsdenoteavariablerequiringsubstitutionforarealvalue.

Italicfont Usedtodenotethetitleofabook,article,orotherpublication.

Note Additionalinformationorcaveats

Scoring Information

Ascoringstatusindicateswhethercompliancewiththegivenrecommendationimpactstheassessedtarget'sbenchmarkscore.Thefollowingscoringstatusesareusedinthisbenchmark:

Scored

Failuretocomplywith"Scored"recommendationswilldecreasethefinalbenchmarkscore.Compliancewith"Scored"recommendationswillincreasethefinalbenchmarkscore.

NotScored

Failuretocomplywith"NotScored"recommendationswillnotdecreasethefinalbenchmarkscore.Compliancewith"NotScored"recommendationswillnotincreasethefinalbenchmarkscore.

7|P a g e

Profile Definitions

ThefollowingconfigurationprofilesaredefinedbythisBenchmark:

• Level1-PostgreSQL

ItemsinthisprofileapplytoPostgreSQL10andintendto:

o bepracticalandprudent;o provideaclearsecuritybenefit;ando notinhibittheutilityofthetechnologybeyondacceptablemeans.

Note:TheintentofthisprofileistoincludechecksthatcanbeassessedbyremotelyconnectingtoPostgreSQL.Therefore,filesystem-relatedchecksarenotcontainedinthisprofile.

• Level1-PostgreSQLonLinux

ItemsinthisprofileapplytoPostgreSQL10runningonLinuxandintendto:

o bepracticalandprudent;o provideaclearsecuritybenefit;ando notinhibittheutilityofthetechnologybeyondacceptablemeans.

8|P a g e

Acknowledgements

This benchmark exemplifies the great things a community of users, vendors, and subject matter experts can accomplish through consensus collaboration. The CIS community thanks the entire consensus team with special recognition to the following individuals who contributed greatly to the creation of this guide:

AuthorDougHunleyEditorTimHarrisonCISSP,ICP,CenterforInternetSecurity

9|P a g e

Recommendations1 Installation and Patches

OneofthebestwaystoensuresecurePostgreSQLsecurityistoimplementsecurityupdatesastheycomeout,alongwithanyapplicableOSpatchesthatwillnotinterferewithsystemoperations.Itisadditionallyprudenttoensuretheinstalledversionhasnotreachedend-of-life.

1.1 Ensure packages are obtained from authorized repositories (Not Scored)

ProfileApplicability:

•Level1-PostgreSQLonLinux

Description:

Whenobtainingandinstallingsoftwarepackages(typicallyviayum),it'simperativethatpackagesaresourcedonlyfromvalidandauthorizedrepositories.ForPostgreSQL,ashortlistofvalidrepositorieswouldincludeCentOS(www.centos.org)andtheofficialPostgreSQLwebsite(yum.postgresql.org).

Rationale:

Beingopensource,PostgreSQLpackagesarewidelyavailableacrosstheinternetthroughRPMaggregatorsandproviders.However,usinginvalidorunauthorizedsourcesforpackagescanleadtoimplementinguntested,defective,ormalicioussoftware.

Manyorganizationschoosetoimplementalocalyumrepositorywithintheirorganization.Caremustbetakentoensurethatonlyvalidandauthorizedpackagesaredownloadedandinstalledintosuchlocalrepositories.

Audit:

Identifyandinspectconfiguredrepositoriestoensuretheyareallvalidandauthorizedsourcesofpackages.ThefollowingisanexampleofasimpleCENTOS7installillustratingtheuseoftheyum repolist allcommand.

$ whoami root $ yum repolist all | grep enabled:

10|P a g e

base/7/x86_64 CentOS-7 - Base enabled: 10,019 extras/7/x86_64 CentOS-7 - Extras enabled: 409 updates/7/x86_64 CentOS-7 - Updates enabled: 1945

Ensurethelistofconfiguredrepositoriesonlyincludesorganization-approvedrepositories.Ifanyunapprovedrepositoriesarelisted,thisisafail.

Remediation:

Altertheconfiguredrepositoriessotheyonlyincludevalidandauthorizedsourcesofpackages.

Asanexampleofaddinganauthorizedrepository,wewillinstallthePGDGrepositoryRPMfrom'yum.postgresql.org':

[root@centos7 ~]# whoami root [root@centos7 ~]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm Loaded plugins: fastestmirror pgdg-redhat-repo-latest.noarch.rpm | 5.6 kB 00:00:00 Examining /var/tmp/yum-root-CubWbD/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-4.noarch Marking /var/tmp/yum-root-CubWbD/pgdg-redhat-repo-latest.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-redhat-repo.noarch 0:42.0-4 will be installed --> Finished Dependency Resolution Dependencies Resolved ==================================================================================================================== Package Arch Version Repository Size ==================================================================================================================== Installing: pgdg-redhat-repo noarch 42.0-4 /pgdg-redhat-repo-latest.noarch 6.8 k Transaction Summary ==================================================================================================================== Install 1 Package Total size: 6.8 k Installed size: 6.8 k Is this ok [y/d/N]: y Downloading packages:

11|P a g e

Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgdg-redhat-repo-42.0-4.noarch 1/1 Verifying : pgdg-redhat-repo-42.0-4.noarch 1/1 Installed: pgdg-redhat-repo.noarch 0:42.0-4 Complete!

Verifytherepositoryhasbeenaddedandisenabled:

[root@centos7 ~]# whoami root [root@centos7 ~]# yum repolist all | grep enabled: base/7/x86_64 CentOS-7 - Base enabled: 10,019 extras/7/x86_64 CentOS-7 - Extras enabled: 409 pgdg10/7/x86_64 PostgreSQL 10 7 - x86_64 enabled: 663 pgdg11/7/x86_64 PostgreSQL 11 7 - x86_64 enabled: 487 pgdg94/7/x86_64 PostgreSQL 9.4 7 - x86_64 enabled: 746 pgdg95/7/x86_64 PostgreSQL 9.5 7 - x86_64 enabled: 732 pgdg96/7/x86_64 PostgreSQL 9.6 7 - x86_64 enabled: 745 updates/7/x86_64 CentOS-7 - Updates enabled: 1,945

References:

1. https://wiki.centos.org/PackageManagement/Yum/2. https://www.centos.org/docs/5/html/5.2/Deployment_Guide/s1-yum-yumconf-

repository.html3. https://en.wikipedia.org/wiki/Yum_(software)4. https://www.howtoforge.com/creating_a_local_yum_repository_centos5. https://yum.postgresql.org

CISControls:

Version6

2InventoryofAuthorizedandUnauthorizedSoftware InventoryofAuthorizedandUnauthorizedSoftware

12|P a g e

Version7

2.1MaintainInventoryofAuthorizedSoftware Maintainanup-to-datelistofallauthorizedsoftwarethatisrequiredintheenterpriseforanybusinesspurposeonanybusinesssystem.

13|P a g e

1.2 Ensure Installation of Binary Packages (Not Scored)



Description:

ThePostgreSQLpackagesareinstalledontheOperatingSystemfromvalidsource.

Rationale:

StandardLinuxdistributions,althoughpossessingtherequisitepackages,oftendonothavePostgreSQLpre-installed.Theinstallationprocessincludesinstallingthebinariesandthemeanstogenerateadataclustertoo.Packageinstallationshouldincludeboththeserverandclientpackages.Contributionmodulesareoptionaldependinguponone'sarchitecturalrequirements(theyarerecommendedthough).

Fromasecurityperspective,it'simperativetoverifythePostgreSQLbinarypackagesaresourcedfromavalidLinuxyumrepository.ThemostcommonLinuxrepositoriesincludeCentOSbaseandPGDGbase;however,it'suptotheorganizationtovalidate.ForacompletelistingofallPostgreSQLbinariesavailableviaconfiguredrepositoriesinspecttheoutputfromyum provides libpq.so.

Audit:

ToinspectwhatversionsofPostgreSQLpackagesareinstalled,andwhichrepotheycamefrom,wecanqueryusingtheyumandrpmcommands.Asillustratedbelow,PostgreSQL11.3packagesareinstalled:

[root@centos7 ~]# whoami root [root@centos7 ~]# yum info $(rpm -qa|grep postgres) | egrep '^Name|^Version|^From' Name : postgresql11 Version : 11.3 From repo : pgdg11 Name : postgresql11-contrib Version : 11.3 From repo : pgdg11 Name : postgresql11-libs Version : 11.3 From repo : pgdg11 Name : postgresql11-server Version : 11.3 From repo : pgdg11

14|P a g e

Iftheexpectedbinarypackagesarenotinstalled,arenottheexpectedversions,ordidnotcomefromanappropriaterepo,thisisafail.

Remediation:

IftheversionofPostgreSQLinstalledisnot11.x,thepackagesmaybeuninstalledusingthiscommand:

$ whoami root $ yum remove $(rpm -qa|grep postgres)

Thenextrecommendation"1.3EnsureInstallationofCommunityPackages"describeshowtoexplicitlychoosewhichversionofPostgreSQLtoinstall,regardlessofLinuxdistributionassociation.

Impact:

IfthePostgreSQLversionshippedaspartofthedefaultbinaryinstallationassociatedwithyourLinuxdistributionsatisfiesyourrequirements,thismaybeadequatefordevelopmentandtestingpurposes.However,forproductioninstancesit'sgenerallyrecommendedtoinstallthelateststablereleaseofPostgreSQL.

CISControls:

Version6

2InventoryofAuthorizedandUnauthorizedSoftware InventoryofAuthorizedandUnauthorizedSoftware

Version7

2.1MaintainInventoryofAuthorizedSoftware Maintainanup-to-datelistofallauthorizedsoftwarethatisrequiredintheenterpriseforanybusinesspurposeonanybusinesssystem.

15|P a g e

1.3 Ensure Installation of Community Packages (Not Scored)



Description:

Adding,andinstalling,thePostgreSQLcommunitypackagestothehost'spackagerepository.

Rationale:

It'sanunfortunaterealitythatLinuxdistributionsdonotalwayshavethemostup-to-dateversionsofPostgreSQL.Disadvantagesofolderreleasesinclude:missingbugpatches,noaccesstohighlydesirablecontributionmodules,noaccessto3rdpartyprojectsthatarecomplimentarytoPostgreSQL,andnoupgradepathmigratingfromoneversionofPostgreSQLtothenext.TheworstsetofcircumstancesistobelimitedtoaversionoftheRDBMSthathasreacheditsend-of-life.

Fromasecurityperspective,it'simperativethatPostgresCommunityPackagesareonlyobtainedfromtheofficialwebsitehttps://yum.postgresql.org/.Beingopensource,thePostgrespackagesarewidelyavailableovertheinternetviamyriadpackageaggregatorsandproviders.Obtainingsoftwarefromtheseunofficialsitesrisksinstallingdefective,corrupt,ordownrightmaliciousversionsofPostgreSQL.

Audit:

FirstdeterminewhetherornotthePostgreSQLCommunityPackagesareinstalled.Forthisexample,weareusingahostthatdoesnothaveanyPostgreSQLpackagesinstalledandofferresolutionintheRemediationProcedurebelow.

$ whoami root $ yum info $(rpm -qa|grep postgres) | egrep '^Name|^Version|^From' $

Iftheexpectedcommunitypackagesarenotinstalled,arenottheexpectedversions,orarenotfromthePGDGrepo,thisisafail.

16|P a g e

Remediation:

ThefollowingexampleaddsthePGDGrepositoryRPMforPostgreSQL,configuresyumtopreferthePGDGpackagesforversion11,andinstallstheclient-server-contributionsrpmstothehostwhereyouwanttoinstalltheRDBMS.

Usingawebbrowser,gotohttp://yum.postgresql.organdnavigatetotherepodownloadlinkforyourOSandversion.CopytheURLtotherepofile,andthentellyumtoinstallit:

[root@centos7 ~]# whoami root [root@centos7 ~]# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm Loaded plugins: fastestmirror pgdg-redhat-repo-latest.noarch.rpm | 5.6 kB 00:00:00 Examining /var/tmp/yum-root-CubWbD/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-4.noarch Marking /var/tmp/yum-root-CubWbD/pgdg-redhat-repo-latest.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-redhat-repo.noarch 0:42.0-4 will be installed --> Finished Dependency Resolution Dependencies Resolved ==================================================================================================================== Package Arch Version Repository Size ==================================================================================================================== Installing: pgdg-redhat-repo noarch 42.0-4 /pgdg-redhat-repo-latest.noarch 6.8 k Transaction Summary ==================================================================================================================== Install 1 Package Total size: 6.8 k Installed size: 6.8 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgdg-redhat-repo-42.0-4.noarch 1/1 Verifying : pgdg-redhat-repo-42.0-4.noarch

17|P a g e

1/1 Installed: pgdg-redhat-repo.noarch 0:42.0-4 Complete!

Now,configureyumtopreferthePGDGpackagesforversion11:

[root@centos7 ~]# yum -y install yum-priorities Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirror.sfo12.us.leaseweb.net * extras: mirror.umd.edu * updates: ftp.usf.edu Resolving Dependencies --> Running transaction check ---> Package yum-plugin-priorities.noarch 0:1.1.31-50.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ==================================================================================================================== Package Arch Version Repository Size ==================================================================================================================== Installing: yum-plugin-priorities noarch 1.1.31-50.el7 base 29 k Transaction Summary ==================================================================================================================== Install 1 Package Total download size: 29 k Installed size: 28 k Downloading packages: yum-plugin-priorities-1.1.31-50.el7.noarch.rpm | 29 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : yum-plugin-priorities-1.1.31-50.el7.noarch 1/1 Verifying : yum-plugin-priorities-1.1.31-50.el7.noarch 1/1 Installed: yum-plugin-priorities.noarch 0:1.1.31-50.el7 Complete! [root@centos7 ~]# whoami

18|P a g e

root [root@centos7 ~]# vi /etc/yum.repos.d/pgdg-redhat-all.repo <snip> [pgdg11] name=PostgreSQL 11 $releasever - $basearch baseurl=https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-$releasever-$basearch enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG priority=1 <-- add this line <snip>

Finally,installthePostgreSQLpackages:

[root@centos7 ~]# whoami root [root@centos7 ~]# yum -y groupinstall 'PostgreSQL Database Server 11 PGDG' Loaded plugins: fastestmirror, priorities There is no installed groups file. Maybe run: yum groups mark convert (see man yum) Loading mirror speeds from cached hostfile * base: mirror.sfo12.us.leaseweb.net * extras: mirror.umd.edu * updates: ftp.usf.edu pgdg10 | 3.6 kB 00:00:00 pgdg11 | 3.6 kB 00:00:00 pgdg94 | 3.6 kB 00:00:00 pgdg95 | 3.6 kB 00:00:00 pgdg96 | 3.6 kB 00:00:00 1401 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package postgresql11.x86_64 0:11.3-1PGDG.rhel7 will be installed --> Processing Dependency: libicu for package: postgresql11-11.3-1PGDG.rhel7.x86_64 ---> Package postgresql11-contrib.x86_64 0:11.3-1PGDG.rhel7 will be installed --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.22)(64bit) for package: postgresql11-contrib-11.3-1PGDG.rhel7.x86_64 --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.18)(64bit) for package: postgresql11-contrib-11.3-1PGDG.rhel7.x86_64 --> Processing Dependency: libxslt.so.1(LIBXML2_1.0.11)(64bit) for package: postgresql11-contrib-11.3-1PGDG.rhel7.x86_64 --> Processing Dependency: libxslt.so.1()(64bit) for package: postgresql11-contrib-11.3-1PGDG.rhel7.x86_64 ---> Package postgresql11-libs.x86_64 0:11.3-1PGDG.rhel7 will be installed ---> Package postgresql11-server.x86_64 0:11.3-1PGDG.rhel7 will be installed --> Running transaction check ---> Package libicu.x86_64 0:50.1.2-17.el7 will be installed ---> Package libxslt.x86_64 0:1.1.28-5.el7 will be installed --> Finished Dependency Resolution

19|P a g e

Dependencies Resolved ==================================================================================================================== Package Arch Version Repository Size ==================================================================================================================== Installing for group install "PostgreSQL Database Server 11 PGDG": postgresql11 x86_64 11.3-1PGDG.rhel7 pgdg11 1.7 M postgresql11-contrib x86_64 11.3-1PGDG.rhel7 pgdg11 616 k postgresql11-libs x86_64 11.3-1PGDG.rhel7 pgdg11 360 k postgresql11-server x86_64 11.3-1PGDG.rhel7 pgdg11 4.7 M Installing for dependencies: libicu x86_64 50.1.2-17.el7 base 6.9 M libxslt x86_64 1.1.28-5.el7 base 242 k Transaction Summary ==================================================================================================================== Install 4 Packages (+2 Dependent packages) Total download size: 14 M Installed size: 55 M Downloading packages: (1/6): libxslt-1.1.28-5.el7.x86_64.rpm | 242 kB 00:00:00 warning: /var/cache/yum/x86_64/7/pgdg11/packages/postgresql11-11.3-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Public key for postgresql11-11.3-1PGDG.rhel7.x86_64.rpm is not installed (2/6): postgresql11-11.3-1PGDG.rhel7.x86_64.rpm | 1.7 MB 00:00:00 (3/6): postgresql11-libs-11.3-1PGDG.rhel7.x86_64.rpm | 360 kB 00:00:00 (4/6): libicu-50.1.2-17.el7.x86_64.rpm | 6.9 MB 00:00:01 (5/6): postgresql11-server-11.3-1PGDG.rhel7.x86_64.rpm | 4.7 MB 00:00:00 (6/6): postgresql11-contrib-11.3-1PGDG.rhel7.x86_64.rpm | 616 kB 00:00:02 -------------------------------------------------------------------------------------------------------------------- Total 4.7 MB/s | 14 MB 00:00:03 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <[email protected]>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-4.noarch (installed)

20|P a g e

From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql11-libs-11.3-1PGDG.rhel7.x86_64 1/6 Installing : libicu-50.1.2-17.el7.x86_64 2/6 Installing : postgresql11-11.3-1PGDG.rhel7.x86_64 3/6 Installing : libxslt-1.1.28-5.el7.x86_64 4/6 Installing : postgresql11-contrib-11.3-1PGDG.rhel7.x86_64 5/6 Installing : postgresql11-server-11.3-1PGDG.rhel7.x86_64 6/6 Verifying : postgresql11-libs-11.3-1PGDG.rhel7.x86_64 1/6 Verifying : postgresql11-server-11.3-1PGDG.rhel7.x86_64 2/6 Verifying : libicu-50.1.2-17.el7.x86_64 3/6 Verifying : libxslt-1.1.28-5.el7.x86_64 4/6 Verifying : postgresql11-11.3-1PGDG.rhel7.x86_64 5/6 Verifying : postgresql11-contrib-11.3-1PGDG.rhel7.x86_64 6/6 Installed: postgresql11.x86_64 0:11.3-1PGDG.rhel7 postgresql11-contrib.x86_64 0:11.3-1PGDG.rhel7 postgresql11-libs.x86_64 0:11.3-1PGDG.rhel7 postgresql11-server.x86_64 0:11.3-1PGDG.rhel7 Dependency Installed: libicu.x86_64 0:50.1.2-17.el7 libxslt.x86_64 0:1.1.28-5.el7 Complete!

Note:Theabove-mentionedexampleisreferencedasanillustrationonly.Packagenamesandversionsmaydiffer.

References:

1. https://www.postgresql.org/2. https://www.postgresql.org/support/versioning/3. https://www.postgresql.org/developer/roadmap/4. https://yum.postgresql.org/repopackages.php

21|P a g e

CISControls:

Version6

18.1UseOnlyVendor-supportedSoftware Forallacquiredapplicationsoftware,checkthattheversionyouareusingisstillsupportedbythevendor.Ifnot,updatetothemostcurrentversionandinstallallrelevantpatchesandvendorsecurityrecommendations.

Version7

18.3VerifyThatAcquiredSoftwareisStillSupported Verifythattheversionofallsoftwareacquiredfromoutsideyourorganizationisstillsupportedbythedeveloperorappropriatelyhardenedbasedondevelopersecurityrecommendations.

22|P a g e

1.4 Ensure systemd Service Files Are Enabled (Scored)



Description:

Confirm,andcorrectifnecessary,thePostgreSQLsystemdserviceisenabled.

Rationale:

EnablingthesystemdserviceontheOSensuresthedatabaseserviceisactivewhenachangeofstateoccursasinthecaseofasystemstartuporreboot.

Audit:

Thedefaultoperatingtargetonsystemd-poweredoperatingsystemsistypically"multi-user".Oneconfirmsthedefaulttargetbyexecutingthefollowing:

$ whoami root $ systemctl get-default multi-user.target $ systemctl list-dependencies multi-user.target | grep -i postgres $

IftheintendedPostgreSQLserviceisnotregisteredasadependency(or"want")ofthedefaulttarget(nooutputforthe3rdcommandabove),thisisafail.

Remediation:

Irrespectiveofpackagesource,PostgreSQLservicescanbeidentifiedbecauseittypicallyincludesthetextstring"postgresql".PGDGinstallsdonotautomaticallyregistertheserviceasa"want"ofthedefaultsystemdtarget.MultipleinstancesofPostgreSQLservicesoftendistinguishthemselvesusingaversionnumber.

[root@centos7 ~]# whoami root [root@centos7 ~]# systemctl enable postgresql-11 Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service. [root@centos7 ~]# systemctl list-dependencies multi-user.target | grep -i postgres ● ├─postgresql-11.service [root@centos7 ~]#

23|P a g e

References:

1. https://linuxcommand.org/man_pages/runlevel8.html2. https://linuxcommand.org/man_pages/chkconfig8.html3. https://www.tldp.org/LDP/sag/html/run-levels-intro.html

CISControls:

Version6

18ApplicationSoftwareSecurity ApplicationSoftwareSecurity

Version7

5.1EstablishSecureConfigurations Maintaindocumented,standardsecurityconfigurationstandardsforallauthorizedoperatingsystemsandsoftware.

24|P a g e

1.5 Ensure Data Cluster Initialized Successfully (Scored)



Description:

FirsttimeinstallsofPostgreSQLrequirestheinstantiationofthedatabasecluster.Adatabaseclusterisacollectionofdatabasesthataremanagedbyasingleserverinstance.

Rationale:

Forthepurposesofsecurity,PostgreSQLenforcesownershipandpermissionsofthedata-clustersuchthat:

• Aninitializeddata-clusterisownedbytheUNIXaccountthatcreatedit.• Thedata-clustercannotbeaccessedbyotherUNIXuser-accounts.• Thedata-clustercannotbecreatedorownedbyroot• ThePostgreSQLprocesscannotbeinvokedbyrootnoranyUNIXuseraccount

otherthantheownerofthedatacluster.

Incorrectlyinstantiatingthedata-clusterwillresultinafailedinstallation.

Audit:

AssuminginstallingthePostgreSQLbinarypackagefromeithertheCENTOS7,orCommunityrepository(rpm)installation;thestandardmethod,asroot,istoinstantiatetheclusterthusly:

[root@centos7 ~]# whoami root [root@centos7 ~]# PGSETUP_INITDB_OPTIONS="-k" /usr/pgsql-11/bin/postgresql-11-setup initdb Initializing database ... OK [root@centos7 ~]#

Acorrectlyinstalleddata-clusterpossessesdirectorypermissionssimilarlytothefollowingexample.Otherwise,theservicewillfailtostart:

[root@centos7 ~]# whoami root [root@centos7 ~]# ls -la ~postgres/11 total 8 drwx------. 4 postgres postgres 51 May 29 13:49 . drwx------. 3 postgres postgres 37 May 29 13:22 ..

25|P a g e

drwx------. 2 postgres postgres 6 May 8 05:37 backups drwx------. 20 postgres postgres 4096 May 29 13:49 data -rw-------. 1 postgres postgres 874 May 29 13:49 initdb.log [root@centos7 ~]#

YoucanverifythePGDATAhassanepermissionsandattributesbyrunning:

-bash-4.2$ whoami postgres -bash-4.2$ /usr/pgsql-11/bin/postgresql-11- postgresql-11-check-db-dir postgresql-11-setup -bash-4.2$ /usr/pgsql-11/bin/postgresql-11-check-db-dir ~postgres/11/data -bash-4.2$ echo $? 0

Aslongasthereturncodeiszero(0),asshown,everythingisfine.

Remediation:

Attemptingtoinstantiateadataclustertoanexistingnon-emptydirectorywillfail:

[root@centos7 ~]# whoami root [root@centos7 ~]# PGSETUP_INITDB_OPTIONS="-k" /usr/pgsql-11/bin/postgresql-11-setup initdb Data directory is not empty!

Inthecaseofaclusterinstantiationfailure,onemustdelete/removetheentiredataclusterdirectoryandrepeattheinitdbcommand:

[root@centos7 ~]# whoami root [root@centos7 ~]# rm -rf ~postgres/10 [root@centos7 ~]# PGSETUP_INITDB_OPTIONS="-k" /usr/pgsql-11/bin/postgresql-11-setup initdb Initializing database ... OK

CISControls:

Version6

14.4ProtectInformationWithAccessControlLists Allinformationstoredonsystemsshallbeprotectedwithfilesystem,networkshare,claims,application,ordatabasespecificaccesscontrollists.Thesecontrolswillenforcetheprinciplethatonlyauthorizedindividualsshouldhaveaccesstotheinformationbasedontheirneedtoaccesstheinformationasapartoftheirresponsibilities.

Version7

26|P a g e

14.6ProtectInformationthroughAccessControlLists Protectallinformationstoredonsystemswithfilesystem,networkshare,claims,application,ordatabasespecificaccesscontrollists.Thesecontrolswillenforcetheprinciplethatonlyauthorizedindividualsshouldhaveaccesstotheinformationbasedontheirneedtoaccesstheinformationasapartoftheirresponsibilities.

27|P a g e

2 Directory and File Permissions

ThissectionprovidesguidanceonsecuringalloperatingsystemspecificobjectsforPostgreSQL.

2.1 Ensure the file permissions mask is correct (Scored)



Description:

Filesarealwayscreatedusingadefaultsetofpermissions.Filepermissionscanberestrictedbyapplyingapermissionsmaskcalledtheumask.Thepostgresuseraccountshoulduseaumaskof077todenyfileaccesstoalluseraccountsexcepttheowner.

Rationale:

TheLinuxOSdefaultstheumaskto002,whichmeanstheownerandprimarygroupcanreadandwritethefile,andotheraccountsarepermittedtoreadthefile.Notexplicitlysettingtheumasktoavalueasrestrictiveas077allowsotheruserstoread,write,orevenexecutefilesandscriptscreatedbythepostgresuseraccount.Thealternativetousingaumaskisexplicitlyupdatingfilepermissionsafterfilecreationusingthecommandlineutilitychmod(amanualanderrorproneprocessthatisnotadvised).

Audit:

Toviewthemask'scurrentsetting,executethefollowingcommands:

$ whoami root $ su - postgres $ whoami postgres $ umask 0022

Theumaskmustbe077ormorerestrictiveforthepostgresuser,otherwisethisisafail.

Remediation:

Dependinguponthepostgresuser'senvironment,theumaskistypicallysetintheinitializationfile.bash_profile,butmayalsobesetin.profileor.bashrc.Tosettheumask,addthefollowingtotheappropriateprofilefile:

28|P a g e

$ whoami postgres $ cd ~ $ ls -ld .{bash_profile,profile,bashrc} ls: cannot access .profile: No such file or directory ls: cannot access .bashrc: No such file or directory -rwx------. 1 postgres postgres 267 Aug 14 12:59 .bash_profile $ echo "umask 077" >> .bash_profile $ source .bash_profile $ umask 0077

DefaultValue:

0022

CISControls:

Version6


Version7


29|P a g e

2.2 Ensure the PostgreSQL pg_wheel group membership is correct (Scored)



Description:

Thegrouppg_wheelisexplicitlycreatedonahostwherethePostgreSQLserverisinstalled.Membershipinthisgroupenablesanordinaryuseraccounttogain'superuser'accesstoadatabaseclusterbyusingthesudocommand(See'Ensuresudoisconfiguredcorrectly'laterinthisbenchmark).Onlyuseraccountsauthorizedtohavesuperuseraccessshouldbemembersofthepg_wheelgroup.

Rationale:

Userswithunauthorizedmembershipinthepg_wheelgroupcanassumetheprivilegesoftheownerofthePostgreSQLRDBMSandadministerthedatabase,aswellasaccessingscripts,files,andotherexecutablestheyshouldnotbeabletoaccess.

Audit:

Executethecommandgetenttoconfirmthatapg_wheelgroupexists.Ifnosuchgroupexists,thisisafail:

$ whoami root $ # no output (below) means the group does not exist $ getent group pg_wheel $

Ifsuchagroupdoesexist,viewitsmembershipandconfirmthateachuserisauthorizedtoactasanadministrator;

$ whoami root $ # when the group exists, the command shows the 'group id' (GID) $ getent group pg_wheel pg_wheel:x:502: $ # since the group exists, list its members thusly $ awk -F':' '/pg_wheel/{print $4}' /etc/group $ # empty output == no members

30|P a g e

Remediation:

Ifthepg_wheelgroupdoesnotexist,usethefollowingcommandtocreateit:

$ whoami root $ groupadd pg_wheel && getent group pg_wheel pg_wheel:x:502:

Note:thatyoursystem'sgroupnumbermaynotbe502.That'sOK.Addingthepostgresusertothenewlycreatedgroupisdonebyissuing:

$ whoami root $ gpasswd -a postgres pg_wheel Adding user postgres to group pg_wheel $ # verify membership $ awk -F':' '/pg_wheel/{print $4}' /etc/group postgres

Removingauseraccountfromthe'pg_wheel'groupisachievedbyexecutingthefollowingcommand:

$ whoami root $ gpasswd -d pg_wheel postgres Removing user postgres from group pg_wheel $ # verify the user was removed $ awk -F':' '/pg_wheel/{print $4}' /etc/group $

References:

1. https://man7.org/linux/man-pages/man1/groups.1.html2. https://man7.org/linux/man-pages/man8/getent.1.html3. https://man7.org/linux/man-pages/man8/gpasswd.1.html4. https://man7.org/linux/man-pages/man8/useradd.8.html5. https://en.wikipedia.org/wiki/Wheel_%28Unix_term%29

CISControls:

Version6


31|P a g e

Version7


32|P a g e

3 Logging Monitoring And Auditing (Centos 6)

ThissectionprovidesguidancewithrespecttoPostgreSQL'sauditingandloggingbehavior.

3.1 PostgreSQL Logging

ThissectionprovidesguidancewithrespecttoPostgreSQL'sloggingbehaviorasitappliestosecurityandauditing.PostgreSQLcontainssignificantlymoreloggingoptionsthatarenotauditand/orsecurityrelated(andassuch,arenotcoveredherein).

3.1.1 Logging Rationale

Havinganaudittrailisanimportantfeatureofanyrelationaldatabasesystem.Youwantenoughdetailtodescribewhenaneventofinteresthasstartedandstopped,whattheeventis/was,theevent'scause,andwhattheeventdid/isdoingtothesystem.

Ideally,theloggedinformationisinaformatpermittingfurtheranalysisgivingusnewperspectivesandinsight.

ThePostgreSQLconfigurationfilepostgresql.confiswherealladjustableparameterscanbeset.Aconfigurationfileiscreatedaspartofthedatacluster'screationi.e.initdb.Theconfigurationfileenumeratesalltunableparametersandeventhoughmostofthemarecommentedoutitisunderstoodthattheyareinfactactiveandatthoseverysamedocumentedvalues.Thereasonthattheyarecommentedoutistosignifytheirdefaultvalues.Uncommentingthemwillforcetheservertoreadthesevaluesinsteadofusingthedefaultvalues.

33|P a g e

3.1.2 Ensure the log destinations are set correctly (Scored)


•Level1-PostgreSQL•Level1-PostgreSQLonLinux

Description:

PostgreSQLsupportsseveralmethodsforloggingservermessages,includingstderr,csvlogandsyslog.OnWindows,eventlogisalsosupported.Oneormoreofthesedestinationsshouldbesetforserverlogoutput.

Rationale:

Iflog_destinationisnotset,thenanylogmessagesgeneratedbythecorePostgreSQLprocesseswillbelost.

Audit:

ExecutethefollowingSQLstatementtoviewthecurrentlyactivelogdestinations:

postgres=# show log_destination; log_destination ----------------- stderr (1 row)

Thelogdestinationsshouldcomplywithyourorganization'spoliciesonlogging.Ifalltheexpectedlogdestinationsarenotset,thisisafail.

Remediation:

ExecutethefollowingSQLstatementstoremediatethissetting(inthisexample,settingthelogdestinationtocsvlog):

postgres=# alter system set log_destination = 'csvlog'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

Note:Ifmorethanonelogdestinationistobeused,setthisparametertoalistofdesiredlogdestinationsseparatedbycommas(e.g.'csvlog,stderr').

34|P a g e

DefaultValue:

stderr

References:

1. https://www.postgresql.org/docs/11/static/runtime-config-logging.html

Notes:

logging_collector(detailedinthenextsection)mustbeenabledtogenerateCSV-formatlogoutput.

CISControls:

Version6

6.2EnsureAuditLogSettingsSupportAppropriateLogEntryFormatting Validateauditlogsettingsforeachhardwaredeviceandthesoftwareinstalledonit,ensuringthatlogsincludeadate,timestamp,sourceaddresses,destinationaddresses,andvariousotherusefulelementsofeachpacketand/ortransaction.SystemsshouldrecordlogsinastandardizedformatsuchassyslogentriesorthoseoutlinedbytheCommonEventExpressioninitiative.Ifsystemscannotgeneratelogsinastandardizedformat,lognormalizationtoolscanbedeployedtoconvertlogsintosuchaformat.

Version7

6.2Activateauditlogging Ensurethatlocallogginghasbeenenabledonallsystemsandnetworkingdevices.

6.3EnableDetailedLogging Enablesystemloggingtoincludedetailedinformationsuchasaneventsource,date,user,timestamp,sourceaddresses,destinationaddresses,andotherusefulelements.

35|P a g e

3.1.3 Ensure the logging collector is enabled (Scored)



Description:

Theloggingcollectorisabackgroundprocessthatcaptureslogmessagessenttostderrandredirectsthemintologfiles.Thelogging_collectorsettingmustbeenabledinorderforthisprocesstorun.Itcanonlybesetatserverstart.

Rationale:

Theloggingcollectorapproachisoftenmoreusefulthanloggingtosyslog,sincesometypesofmessagesmightnotappearinsyslogoutput.Onecommonexampleisdynamic-linkerfailuremessage;anothermaybeerrormessagesproducedbyscriptssuchasarchive_command.

Note:Thissettingmustbeenabledwhenlog_destinationiseither'stderr'or'csvlog'andforcertainotherloggingparameterstotakeeffect.

Audit:

ExecutethefollowingSQLstatementandconfirmthatthelogging_collectorisenabled(on):

postgres=# show logging_collector; logging_collector ------------------- on (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)toremediatethissetting:

postgres=# alter system set logging_collector = 'on'; ALTER SYSTEM

Unfortunately,thissettingcanonlybechangedatserver(re)start.Asroot,restartthePostgreSQLserviceforthischangetotakeeffect:

[root@centos7 ~]# whoami root

36|P a g e

[root@centos7 ~]# systemctl restart postgresql-11 [root@centos7 ~]# systemctl status postgresql-11|grep 'ago$' Active: active (running) since <date>; 1s ago

DefaultValue:

on

References:


CISControls:

Version6


Version7



37|P a g e

3.1.4 Ensure the log file destination directory is set correctly (Scored)



Description:

Thelog_directorysettingspecifiesthedestinationdirectoryforlogfileswhenlog_destinationisstderrorcsvlog.Itcanbespecifiedasrelativetotheclusterdatadirectory($PGDATA)orasanabsolutepath.log_directoryshouldbesetaccordingtoyourorganization'sloggingpolicy.

Rationale:

Iflog_directoryisnotset,itisinterpretedastheabsolutepath'/'andPostgreSQLwillattempttowriteitslogsthere(andtypicallyfailduetoalackofpermissionstothatdirectory).Thisparametershouldbesettodirectthelogsintotheappropriatedirectorylocationasdefinedbyyourorganization'sloggingpolicy.

Audit:

ExecutethefollowingSQLstatementtoconfirmthattheexpectedloggingdirectoryisspecified:

postgres=# show log_directory; log_directory --------------- log (1 row)

Note:Thisshowsapathrelativetocluster'sdatadirectory.Anabsolutepathwouldstartwitha/likethefollowing:/var/log/pg_log

Remediation:


postgres=# alter system set log_directory='/var/log/postgres/11'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show log_directory;

38|P a g e

log_directory --------------- /var/log/postgres/11 (1 row)

Note:Theuseof/var/log/postgres/11,above,isanexample.Thisshouldbesettoanappropriatepathasdefinedbyyourorganization'sloggingrequirements.Havingsaidthat,itisagoodideatohavethelogsoutsideofyourPGDATAdirsothattheyarenotincludedbythingslikepg_basebackuporpgBackRest.

DefaultValue:

logwhichisrelativetothecluster'sdatadirectory(e.g./var/lib/pgsql/11/data/log)

References:


CISControls:

Version6


Version7



39|P a g e

3.1.5 Ensure the filename pattern for log files is set correctly (Scored)



Description:

Thelog_filenamesettingspecifiesthefilenamepatternforlogfiles.Thevalueforlog_filenameshouldmatchyourorganization'sloggingpolicy.

Thevalueistreatedasastrftimepattern,so%-escapescanbeusedtospecifytime-varyingfilenames.Thesupported%-escapesaresimilartothoselistedintheOpenGroup'sstrftimespecification.Ifyouspecifyafilenamewithoutescapes,youshouldplantousealogrotationutilitytoavoideventuallyfillingthepartitionthatcontainslog_directory.Ifthereareanytime-zone-dependent%-escapes,thecomputationisdoneinthezonespecifiedbylog_timezone.Also,thesystem'sstrftimeisnotuseddirectly,soplatform-specific(nonstandard)extensionsdonotwork.

IfCSV-formatoutputisenabledinlog_destination,.csvwillbeappendedtothelogfilename.(Iflog_filenameendsin.log,thesuffixisreplacedinstead.)

Rationale:

Iflog_filenameisnotset,thenthevalueoflog_directoryisappendedtoanemptystringandPostgreSQLwillfailtostartasitwilltrytowritetoadirectoryinsteadofafile.

Audit:

ExecutethefollowingSQLstatementtoconfirmthatthedesiredpatternisset:

postgres=# show log_filename; log_filename ------------------- postgresql-%a.log (1 row)

Note:Thisexampleshowstheuseofthestrftime%aescape.Thiscreatessevenlogfiles,oneforeachdayoftheweek(e.g.postgresql-Mon.log,postgresql-Tue.log,etal)

Remediation:


40|P a g e

postgres=# alter system set log_filename='postgresql-%Y%m%d.log'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show log_filename; log_filename ------------------- postgresql-%Y%m%d.log (1 row)

Note:Inthisexample,anewlogfilewillbecreatedforeachday(e.g.postgresql-20180901.log)

DefaultValue:

Thedefaultispostgresql-%a.log,whichcreatesanewlogfileforeachdayoftheweek(e.g.postgresql-Mon.log,postgresql-Tue.log).

References:

1. https://man7.org/linux/man-pages/man3/strftime.3.html2. https://www.postgresql.org/docs/11/static/runtime-config-logging.html

CISControls:

Version6


Version7



41|P a g e

3.1.6 Ensure the log file permissions are set correctly (Scored)



Description:

Thelog_file_modesettingdeterminesthefilepermissionsforlogfileswhenlogging_collectorisenabled.Theparametervalueisexpectedtobeanumericmodespecificationintheformacceptedbythechmodandumasksystemcalls.(Tousethecustomaryoctalformat,thenumbermuststartwitha0(zero).)

Thepermissionsshouldbesettoallowonlythenecessaryaccesstoauthorizedpersonnel.Inmostcasesthebestsettingis0600,sothatonlytheserverownercanreadorwritethelogfiles.Theothercommonlyusefulsettingis0640,allowingmembersoftheowner'sgrouptoreadthefiles,althoughtomakeuseofthat,youwillneedtoalterthelog_directorysettingtostorethelogfilesoutsidetheclusterdatadirectory.

Rationale:

Logfilesoftencontainsensitivedata.Allowingunnecessaryaccesstologfilesmayinadvertentlyexposesensitivedatatounauthorizedpersonnel.

Audit:

ExecutethefollowingSQLstatementtoverifythatthesettingisconsistentwithorganizationalloggingpolicy:

postgres=# show log_file_mode; log_file_mode --------------- 0600 (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)toremediatethissetting(withtheexampleassumingadesiredvalueof0600):

postgres=# alter system set log_file_mode = '0600'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ----------------

42|P a g e

t (1 row) postgres=# show log_file_mode; log_file_mode --------------- 0600 (1 row)

DefaultValue:

0600

References:


CISControls:

Version6


Version7


43|P a g e

3.1.7 Ensure 'log_truncate_on_rotation' is enabled (Scored)



Description:

Enablingthelog_truncate_on_rotationsettingwhenlogging_collectorisenabledcausesPostgreSQLtotruncate(overwrite)existinglogfileswiththesamenameduringlogrotationinsteadofappendingtothem.Forexample,usingthissettingincombinationwithalog_filenamesettingvaluelikepostgresql-%H.logwouldresultingenerating24hourlylogfilesandthencyclicallyoverwritingthem:

postgresql-00.log

postgresql-01.log

[...]

postgresql-23.log

Note:Truncationwilloccuronlywhenanewfileisbeingopenedduetotime-basedrotation,notduringserverstartuporsize-basedrotation(seelaterinthisbenchmarkforsize-basedrotationdetails).

Rationale:

Ifthissettingisdisabled,pre-existinglogfileswillbeappendedtoiflog_filenameisconfiguredinsuchawaythatstaticnamesaregenerated.

Enablingordisablingthetruncationshouldonlybedecidedwhenalsoconsideringthevalueoflog_filenameandlog_rotation_age/log_rotation_size.Someexamplestoillustratetheinteractionbetweenthesesettings:

# truncation is moot, as each rotation gets a unique filename (postgresql-20180605.log) log_truncate_on_rotation = on log_filename = 'postgresql-%Y%m%d.log' log_rotation_age = '1d' log_rotation_size = 0 # truncation every hour, losing log data every hour until the date changes log_truncate_on_rotation = on log_filename = 'postgresql-%Y%m%d.log' log_rotation_age = '1h' log_rotation_size = 0

44|P a g e

# no truncation if the date changed while generating 100M of log data, truncation otherwise log_truncate_on_rotation = on log_filename = 'postgresql-%Y%m%d.log' log_rotation_age = '0' log_rotation_size = '100M'

Audit:

ExecutethefollowingSQLstatementtoverifyhowlog_truncate_on_rotationisset:

postgres=# show log_truncate_on_rotation; log_truncate_on_rotation -------------------------- off (1 row)

Remediation:


postgres=# alter system set log_truncate_on_rotation = 'on'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show log_truncate_on_rotation; log_truncate_on_rotation -------------------------- on (1 row)

DefaultValue:

on

References:


Notes:

Besuretoconsideryourorganization'sloggingretentionpoliciesandtheuseofanyexternallogconsumptiontoolsbeforedecidingiftruncationshouldbeenabledordisabled.

45|P a g e

CISControls:

Version6

6.3EnsureAuditLoggingSystemsAreNotSubjectToLoss(i.e.rotation/archive) Ensurethatallsystemsthatstorelogshaveadequatestoragespaceforthelogsgeneratedonaregularbasis,sothatlogfileswillnotfillupbetweenlogrotationintervals.Thelogsmustbearchivedanddigitallysignedonaperiodicbasis.

Version7

6.4Ensureadequatestorageforlogs Ensurethatallsystemsthatstorelogshaveadequatestoragespaceforthelogsgenerated.

46|P a g e

3.1.8 Ensure the maximum log file lifetime is set correctly (Scored)



Description:

Whenlogging_collectorisenabled,thelog_rotation_ageparameterdeterminesthemaximumlifetimeofanindividuallogfile(dependingonthevalueoflog_filename).Afterthismanyminuteshaveelapsed,anewlogfilewillbecreatedviaautomaticlogfilerotation.Currentbestpracticesadviselogrotationatleastdaily,butyourorganization'sloggingpolicyshoulddictateyourrotationschedule.

Rationale:

Logrotationisastandardbestpracticeforlogmanagement.

Audit:

ExecutethefollowingSQLstatementtoverifythelogrotationageissettoanacceptablevalue:

postgres=# show log_rotation_age; log_rotation_age ------------------ 1d

Remediation:

ExecutethefollowingSQLstatement(s)toremediatethissetting(inthisexample,settingittoonehour):

postgres=# alter system set log_rotation_age='1h'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

1d(oneday)

47|P a g e

References:


CISControls:

Version6


Version7


48|P a g e

3.1.9 Ensure the maximum log file size is set correctly (Scored)



Description:

Thelog_rotation_sizesettingdeterminesthemaximumsizeofanindividuallogfile.Oncethemaximumsizeisreached,automaticlogfilerotationwilloccur.

Rationale:

Ifthisissettozero,size-triggeredcreationofnewlogfilesisdisabled.Thiswillpreventautomaticlogfilerotationwhenfilesbecometoolarge,whichcouldputlogdataatincreasedriskofloss(unlessage-basedrotationisconfigured).

Audit:

ExecutethefollowingSQLstatementtoverifythatlog_rotation_sizeissetincompliancewiththeorganization'sloggingpolicy:

postgres=# show log_rotation_size; log_rotation_size ------------------- 1GB (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)toremediatethissetting(inthisexample,settingitto1GB):

postgres=# alter system set log_rotation_size = '1GB'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

0

49|P a g e

References:


CISControls:

Version6


Version7


50|P a g e

3.1.10 Ensure the correct syslog facility is selected (Scored)



Description:

Thesyslog_facilitysettingspecifiesthesyslog"facility"tobeusedwhenloggingtosyslogisenabled.Youcanchoosefromanyofthe'local'facilities:

• LOCAL0• LOCAL1• LOCAL2• LOCAL3• LOCAL4• LOCAL5• LOCAL6• LOCAL7

Yourorganization'sloggingpolicyshoulddictatewhichfacilitytousebasedonthesyslogdaemoninuse.

Rationale:

Ifnotsettotheappropriatefacility,thePostgreSQLlogmessagesmaybeintermingledwithotherapplications'logmessages,incorrectlyrouted,orpotentiallydropped(dependingonyoursyslogconfiguration).

Audit:

ExecutethefollowingSQLstatementandverifythatthecorrectfacilityisselected:

postgres=# show syslog_facility; syslog_facility ----------------- local0 (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)toremediatethissetting(inthisexample,settingittotheLOCAL1facility):

51|P a g e

postgres=# alter system set syslog_facility = 'LOCAL1'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

LOCAL0

References:

1. https://tools.ietf.org/html/rfc3164#section-4.1.12. https://www.postgresql.org/docs/11/static/runtime-config-logging.html

CISControls:

Version6

6Maintenance,Monitoring,andAnalysisofAuditLogs Maintenance,Monitoring,andAnalysisofAuditLogs

Version7


52|P a g e

3.1.11 Ensure the program name for PostgreSQL syslog messages is correct (Scored)



Description:

Thesyslog_identsettingspecifiestheprogramnameusedtoidentifyPostgreSQLmessagesinsysloglogs.Anexampleofapossibleprogramnameis"postgres".

Rationale:

Ifthisisnotsetcorrectly,itmaybedifficultorimpossibletodistinguishPostgreSQLmessagesfromothermessagesinsysloglogs.

Audit:

ExecutethefollowingSQLstatementtoverifytheprogramnameissetcorrectly:

postgres=# show syslog_ident; syslog_ident -------------- postgres (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)toremediatethissetting(inthisexample,assumingaprogramnameof"pg11"):

postgres=# alter system set syslog_ident = 'pg11'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# show syslog_ident; syslog_ident -------------- pg11 (1 row)

53|P a g e

DefaultValue:

postgres

References:

1. https://tools.ietf.org/html/rfc3164#section-4.1.32. https://www.postgresql.org/docs/11/static/runtime-config-logging.html

CISControls:

Version6


Version7


54|P a g e

3.1.12 Ensure the correct messages are written to the server log (Not Scored)



Description:

Thelog_min_messagessettingspecifiesthemessagelevelsthatarewrittentotheserverlog.Eachlevelincludesallthelevelsthatfollowit.Thelowerthelevel(vertically,below),thefewermessagesaresent.

Validvaluesare:

• DEBUG5<--exceedinglychatty• DEBUG4• DEBUG3• DEBUG2• DEBUG1• INFO• NOTICE• WARNING• ERROR• LOG• FATAL• PANIC<--practicallymute

WARNINGisconsideredthebestpracticeunlessindicatedotherwisebyyourorganization'sloggingpolicy.

Rationale:

Ifthisisnotsettothecorrectvalue,toomanymessagesortoofewmessagesmaybewrittentotheserverlog.

Audit:

ExecutethefollowingSQLstatementtoconfirmthesettingiscorrect:

postgres=# show log_min_messages; log_min_messages ------------------ warning (1 row)

55|P a g e

Remediation:

ExecutethefollowingSQLstatement(s)assuperusertoremediatethissetting(inthisexample,tosetittowarning):

postgres=# alter system set log_min_messages = 'warning'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

WARNING

References:


CISControls:

Version6


Version7


56|P a g e

3.1.13 Ensure the correct SQL statements generating errors are recorded (Not Scored)



Description:

Thelog_min_error_statementsettingcausesallSQLstatementsgeneratingerrorsatorabovethespecifiedseverityleveltoberecordedintheserverlog.Eachlevelincludesallthelevelsthatfollowit.Thelowerthelevel(vertically,below),thefewermessagesarerecorded.Validvaluesare:

• DEBUG5<--exceedinglychatty• DEBUG4• DEBUG3• DEBUG2• DEBUG1• INFO• NOTICE• WARNING• ERROR• LOG• FATAL• PANIC<--practicallymute

ERRORisconsideredthebestpracticesetting.Changesshouldonlybemadeinaccordancewithyourorganization'sloggingpolicy.

Note:Toeffectivelyturnoffloggingoffailingstatements,setthisparametertoPANIC.

Rationale:

Ifthisisnotsettothecorrectvalue,toomanyerringSQLstatementsortoofewerringSQLstatementsmaybewrittentotheserverlog.

Audit:

ExecutethefollowingSQLstatementtoverifythesettingiscorrect:

postgres=# show log_min_error_statement; log_min_error_statement -------------------------

57|P a g e

error (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)assuperusertoremediatethissetting(intheexample,toerror):

postgres=# alter system set log_min_error_statement = 'error'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

ERROR

References:


CISControls:

Version6


Version7


58|P a g e

3.1.14 Ensure 'debug_print_parse' is disabled (Scored)



Description:

Thedebug_print_parsesettingenablesprintingtheresultingparsetreeforeachexecutedquery.ThesemessagesareemittedattheLOGmessagelevel.Unlessdirectedotherwisebyyourorganization'sloggingpolicy,itisrecommendedthissettingbedisabledbysettingittooff.

Rationale:

EnablinganyoftheDEBUGprintingvariablesmaycausetheloggingofsensitiveinformationthatwouldotherwisebeomittedbasedontheconfigurationoftheotherloggingsettings.

Audit:

ExecutethefollowingSQLstatementtoconfirmthesettingiscorrect:

postgres=# show debug_print_parse; debug_print_parse ------------------- off (1 row)

Remediation:


postgres=# alter system set debug_print_parse='off'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

off

59|P a g e

References:


CISControls:

Version6


Version7


60|P a g e

3.1.15 Ensure 'debug_print_rewritten' is disabled (Scored)



Description:

Thedebug_print_rewrittensettingenablesprintingthequeryrewriteroutputforeachexecutedquery.ThesemessagesareemittedattheLOGmessagelevel.Unlessdirectedotherwisebyyourorganization'sloggingpolicy,itisrecommendedthissettingbedisabledbysettingittooff.

Rationale:


Audit:

ExecutethefollowingSQLstatementtoconfirmthesettingisdisabled:

postgres=# show debug_print_rewritten; debug_print_rewritten ----------------------- off (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)todisablethissetting:

postgres=# alter system set debug_print_rewritten = 'off'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

off

61|P a g e

References:


CISControls:

Version6


Version7


62|P a g e

3.1.16 Ensure 'debug_print_plan' is disabled (Scored)



Description:

Thedebug_print_plansettingenablesprintingtheexecutionplanforeachexecutedquery.ThesemessagesareemittedattheLOGmessagelevel.Unlessdirectedotherwisebyyourorganization'sloggingpolicy,itisrecommendedthissettingbedisabledbysettingittooff.

Rationale:


Audit:

ExecutethefollowingSQLstatementtoverifythesettingisdisabled:

postgres=# show debug_print_plan ; debug_print_plan ------------------ off (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)todisablethissetting:

postgres=# alter system set debug_print_plan = 'off'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

off

References:


63|P a g e

CISControls:

Version6


Version7


64|P a g e

3.1.17 Ensure 'debug_pretty_print' is enabled (Scored)



Description:

Enablingdebug_pretty_printindentsthemessagesproducedbydebug_print_parse,debug_print_rewritten,ordebug_print_planmakingthemsignificantlyeasiertoread.

Rationale:

Ifthissettingisdisabled,the"compact"formatisusedinstead,significantlyreducingreadabilityoftheDEBUGstatementlogmessages.

Audit:

ExecutethefollowingSQLstatementtoconfirmthesettingisenabled:

postgres=# show debug_pretty_print ; debug_pretty_print -------------------- on (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)toenablethissetting:

postgres=# alter system set debug_pretty_print = 'on'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

Impact:

BeadvisedthattheaforementionedDEBUGprintingoptionsaredisabled,butifyourorganizationalloggingpolicyrequiresthemtobeonthenthisoptioncomesintoplay.

DefaultValue:

on

65|P a g e

References:


CISControls:

Version6


Version7


66|P a g e

3.1.18 Ensure 'log_connections' is enabled (Scored)



Description:

Enablingthelog_connectionssettingcauseseachattemptedconnectiontotheservertobelogged,aswellassuccessfulcompletionofclientauthentication.Thisparametercannotbechangedaftersessionstart.

Rationale:

PostgreSQLdoesnotmaintainaninternalrecordofattemptedconnectionstothedatabaseforlaterauditing.Itisonlybyenablingtheloggingoftheseattemptsthatonecandetermineifunexpectedattemptsarebeingmade.

Notethatenablingthiswithoutalsoenablinglog_disconnectionsprovideslittlevalue.Generally,youwouldenable/disablethepairtogether.

Audit:

ExecutethefollowingSQLstatementtoverifythesettingisenabled:

postgres=# show log_connections; log_connections ----------------- on (1 row)

Remediation:


postgres=# alter system set log_connections = 'on'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

off

67|P a g e

References:


CISControls:

Version6


Version7


68|P a g e

3.1.19 Ensure 'log_disconnections' is enabled (Scored)



Description:

Enablingthelog_disconnectionssettinglogstheendofeachsession,includingsessionduration.Thisparametercannotbechangedaftersessionstart.

Rationale:

PostgreSQLdoesnotmaintainthebeginningorendingofaconnectioninternallyforlaterreview.Itisonlybyenablingtheloggingofthesethatonecanexamineconnectionsforfailedattempts,'overlong'duration,orotheranomalies.

Notethatenablingthiswithoutalsoenablinglog_connectionsprovideslittlevalue.Generally,youwouldenable/disablethepairtogether.

Audit:

ExecutethefollowingSQLstatementtoverifythesettingisenabled:

postgres=# show log_disconnections; log_disconnections -------------------- on (1 row)

Remediation:


postgres=# alter system set log_disconnections = 'on'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

off

69|P a g e

References:


CISControls:

Version6


Version7


70|P a g e

3.1.20 Ensure 'log_error_verbosity' is set correctly (Not Scored)



Description:

Thelog_error_verbositysettingspecifiestheverbosity(amountofdetail)ofloggedmessages.Validvaluesare:

• TERSE• DEFAULT• VERBOSE

witheachcontainingthefieldsofthelevelaboveitaswellasadditionalfields.

TERSEexcludestheloggingofDETAIL,HINT,QUERY,andCONTEXTerrorinformation.

VERBOSEoutputincludestheSQLSTATE,errorcode,andthesourcecodefilename,functionname,andlinenumberthatgeneratedtheerror.

Theappropriatevalueshouldbesetbasedonyourorganization'sloggingpolicy.

Rationale:

Ifthisisnotsettothecorrectvalue,toomanydetailsortoofewdetailsmaybelogged.

Audit:


postgres=# show log_error_verbosity ; log_error_verbosity --------------------- default (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)assuperusertoremediatethissetting(inthisexample,toverbose):

postgres=# alter system set log_error_verbosity = 'verbose'; ALTER SYSTEM postgres=# select pg_reload_conf();

71|P a g e

pg_reload_conf ---------------- t (1 row)

DefaultValue:

DEFAULT

References:


CISControls:

Version6


Version7


72|P a g e

3.1.21 Ensure 'log_hostname' is set correctly (Scored)



Description:

Enablingthelog_hostnamesettingcausesthehostnameoftheconnectinghosttobeloggedinadditiontothehost'sIPaddressforconnectionlogmessages.Disablingthesettingcausesonlytheconnectinghost'sIPaddresstobelogged,andnotthehostname.Unlessyourorganization'sloggingpolicyrequireshostnamelogging,itisbesttodisablethissettingsoasnottoincurtheoverheadofDNSresolutionforeachstatementthatislogged.

Rationale:

Dependingonyourhostnameresolutionsetup,enablingthissettingmightimposeanon-negligibleperformancepenalty.Additionally,theIPaddressesthatareloggedcanberesolvedtotheirDNSnameswhenreviewingthelogs(unlessdynamichostnamesarebeingusedaspartofyourDHCPsetup).

Audit:


postgres=# show log_hostname; log_hostname -------------- off (1 row)

Remediation:

ExecutethefollowingSQLstatement(s)toremediatethissetting(inthisexample,tooff):

postgres=# alter system set log_hostname='off'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

off

73|P a g e

References:


CISControls:

Version6


Version7


74|P a g e

3.1.22 Ensure 'log_line_prefix' is set correctly (Not Scored)



Description:

Thelog_line_prefixsettingspecifiesaprintf-stylestringthatisprefixedtoeachlogline.Ifblank,noprefixisused.YoushouldconfigurethisasrecommendedbythepgBadgerdevelopmentteamunlessdirectedotherwisebyyourorganization'sloggingpolicy.

%charactersbegin"escapesequences"thatarereplacedwithstatusinformationasoutlinedbelow.Unrecognizedescapesareignored.Othercharactersarecopiedstraighttothelogline.Someescapesareonlyrecognizedbysessionprocessesandwillbetreatedasemptybybackgroundprocessessuchasthemainserverprocess.Statusinformationmaybealignedeitherleftorrightbyspecifyinganumericliteralafterthe%andbeforetheoption.Anegativevaluewillcausethestatusinformationtobepaddedontherightwithspacestogiveitaminimumwidth,whereasapositivevaluewillpadontheleft.Paddingcanbeusefultoaidhumanreadabilityinlogfiles.

Anyofthefollowingescapesequencescanbeused:

Escape Effect Session only %a Application name yes %u User name yes %d Database name yes %r Remote host name or IP address, and remote port yes %h Remote host name or IP address yes %p Process ID no %t Time stamp without milliseconds no %m Time stamp with milliseconds no %i Command tag: type of session's current command yes %e SQLSTATE error code no %c Session ID: see below no %l Number of the log line for each session or process, starting at 1 no %s Process start time stamp no %v Virtual transaction ID (backendID/localXID) no %x Transaction ID (0 if none is assigned) no %q Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes no %% Literal %

75|P a g e

Rationale:

Properlysettinglog_line_prefixallowsforaddingadditionalinformationtoeachlogentry(suchastheuser,orthedatabase).Saidinformationmaythenbeofuseinauditingorsecurityreviews.

Audit:


postgres=# show log_line_prefix; log_line_prefix ----------------- < %m > (1 row)

Remediation:


postgres=# alter system set log_line_prefix = '%m [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h '; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

%m [%p]

References:

1. https://pgbadger.darold.net/2. https://www.postgresql.org/docs/11/static/runtime-config-logging.html

CISControls:

Version6


76|P a g e

Version7


77|P a g e

3.1.23 Ensure 'log_statement' is set correctly (Scored)



Description:

Thelog_statementsettingspecifiesthetypesofSQLstatementsthatarelogged.Validvaluesare:

• none(off)• ddl• mod• all(allstatements)

Itisrecommendedthisbesettoddlunlessotherwisedirectedbyyourorganization'sloggingpolicy.

ddllogsalldatadefinitionstatements:

• CREATE• ALTER• DROP

modlogsallddlstatements,plusdata-modifyingstatements:

• INSERT• UPDATE• DELETE• TRUNCATE• COPY FROM

(PREPARE,EXECUTE,andEXPLAIN ANALYZEstatementsarealsologgediftheircontainedcommandisofanappropriatetype.)

Forclientsusingextendedqueryprotocol,loggingoccurswhenanExecutemessageisreceived,andvaluesoftheBindparametersareincluded(withanyembeddedsingle-quotemarksdoubled).

Rationale:

Settinglog_statementtoalignwithyourorganization'ssecurityandloggingpoliciesfacilitateslaterauditingandreviewofdatabaseactivities.

78|P a g e

Audit:


postgres=# show log_statement; log_statement --------------- none (1 row)

Iflog_statementissettononethenthisisafail.

Remediation:

ExecutethefollowingSQLstatement(s)assuperusertoremediatethissetting:

postgres=# alter system set log_statement='ddl'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

DefaultValue:

none

References:


CISControls:

Version6


Version7


79|P a g e

3.1.24 Ensure 'log_timezone' is set correctly (Scored)



Description:

Thelog_timezonesettingspecifiesthetimezonetouseintimestampswithinlogmessages.Thisvalueiscluster-wide,sothatallsessionswillreporttimestampsconsistently.Unlessdirectedotherwisebyyourorganization'sloggingpolicy,setthistoeitherGMTorUTC.

Rationale:

Logentrytimestampsshouldbeconfiguredforanappropriatetimezoneasdefinedbyyourorganization'sloggingpolicytoensurealackofconfusionaroundwhenaloggedeventoccurred.

Notethatthissettingaffectsonlythetimestampspresentinthelogs.Itdoesnotaffectthetimezoneinusebythedatabaseitself(forexample,select now()),nordoesitaffectthehost'stimezone.

Audit:

ExecutethefollowingSQLstatement:

postgres=# show log_timezone ; log_timezone -------------- US/Eastern (1 row)

Iflog_timezoneisnotsettoGMT,UTC,orasdefinedbyyourorganization'sloggingpolicythisisafail.

Remediation:


postgres=# alter system set log_timezone = 'GMT'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)

80|P a g e

DefaultValue:

Bydefault,thePGDGpackageswillsetthistomatchtheserver'stimezoneintheOperatingSystem.

References:


CISControls:

Version6


Version7


81|P a g e

3.2 Ensure the PostgreSQL Audit Extension (pgAudit) is enabled (Scored)



Description:

ThePostgreSQLAuditExtension(pgAudit)providesdetailedsessionand/orobjectauditloggingviathestandardPostgreSQLloggingfacility.ThegoalofpgAuditistoprovidePostgreSQLuserswiththecapabilitytoproduceauditlogsoftenrequiredtocomplywithgovernment,financial,orISOcertifications.

Rationale:

Basicstatementloggingcanbeprovidedbythestandardloggingfacilitywithlog_statement = all.Thisisacceptableformonitoringandotherusesbutdoesnotprovidethelevelofdetailgenerallyrequiredforanaudit.Itisnotenoughtohavealistofalltheoperationsperformedagainstthedatabase,itmustalsobepossibletofindparticularstatementsthatareofinteresttoanauditor.Thestandardloggingfacilityshowswhattheuserrequested,whilepgAuditfocusesonthedetailsofwhathappenedwhilethedatabasewassatisfyingtherequest.

WhenloggingSELECTandDMLstatements,pgAuditcanbeconfiguredtologaseparateentryforeachrelationreferencedinastatement.Noparsingisrequiredtofindallstatementsthattouchaparticulartable.Infact,thegoalisthatthestatementtextisprovidedprimarilyfordeepforensicsandshouldnotberequiredforanaudit.

Audit:

First,asthedatabaseadministrator(shownhereas"postgres"),verifypgauditisenabledbyrunningthefollowingcommands:

postgres=# show shared_preload_libraries ; shared_preload_libraries -------------------------- pgaudit (1 row)

Iftheoutputdoesnotcontain"pgaudit",thisisafail.Next,verifythatdesiredauditingcomponentsareenabled:

postgres=# show pgaudit.log; ERROR: unrecognized configuration parameter "pgaudit.log"

82|P a g e

Iftheoutputdoesnotcontainthedesiredauditingcomponents,thisisafail.ThelistbelowsummarizespgAudit.logcomponents:

• READ:SELECTandCOPYwhenthesourceisarelationoraquery.• WRITE:INSERT,UPDATE,DELETE,TRUNCATE,andCOPYwhenthedestinationisa

relation.• FUNCTION:FunctioncallsandDOblocks.• ROLE:Statementsrelatedtorolesandprivileges:GRANT,REVOKE,CREATE/ALTER/DROP

ROLE.• DDL:AllDDLthatisnotincludedintheROLEclass.• MISC:Miscellaneouscommands,e.g.DISCARD,FETCH,CHECKPOINT,VACUUM.

Remediation:

ToinstallandenablepgAudit,simplyinstalltheappropriaterpmfromthePGDGrepo:

[root@centos7 ~]# whoami root [root@centos7 ~]# yum -y install pgaudit13_11 Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile * base: centos.mirrors.tds.net * extras: mirror.team-cymru.com * updates: mirror.sesp.northwestern.edu base | 3.6 kB 00:00:00 extras | 3.4 kB 00:00:00 pgdg10 | 3.6 kB 00:00:00 pgdg11 | 3.6 kB 00:00:00 pgdg94 | 3.6 kB 00:00:00 pgdg95 | 3.6 kB 00:00:00 pgdg96 | 3.6 kB 00:00:00 updates | 3.4 kB 00:00:00 (1/6): pgdg11/7/x86_64/primary_db | 184 kB 00:00:00 (2/6): pgdg95/7/x86_64/primary_db | 245 kB 00:00:00 (3/6): pgdg10/7/x86_64/primary_db | 229 kB 00:00:01 (4/6): pgdg96/7/x86_64/primary_db | 248 kB 00:00:01 (5/6): pgdg94/7/x86_64/primary_db | 254 kB 00:00:01 (6/6): updates/7/x86_64/primary_db | 5.0 MB 00:00:08 1425 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package pgaudit13_11.x86_64 0:1.3.0-1.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: pgaudit13_11 x86_64 1.3.0-1.rhel7 pgdg11 45 k

83|P a g e

Transaction Summary ============================================================================= Install 1 Package Total download size: 45 k Installed size: 85 k Downloading packages: pgaudit13_11-1.3.0-1.rhel7.x86_64.rpm | 45 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgaudit13_11-1.3.0-1.rhel7.x86_64 1/1 Verifying : pgaudit13_11-1.3.0-1.rhel7.x86_64 1/1 Installed: pgaudit13_11.x86_64 0:1.3.0-1.rhel7 Complete!

pgAuditisnowinstalledandreadytobeconfigured.Next,weneedtoalterthepostgresql.confconfigurationfileto:

• enablepgAuditasanextensionintheshared_preload_librariesparameter• indicatewhichclassesofstatementswewanttologviathepgaudit.logparameter

and,finally,restartthePostgreSQLservice:

$ vi ${PGDATA}/postgresql.conf

Findtheshared_preload_librariesentry,andadd'pgaudit'toit(preservinganyexistingentries):

shared_preload_libraries = 'pgaudit' OR shared_preload_libraries = 'pgaudit,somethingelse'

Now,addanewpgaudit-specificentry:

# for this example we are logging the ddl and write operations pgaudit.log='ddl,write'

RestartthePostgreSQLserverforchangestotakeaffect:

[root@centos7 ~]# whoami root [root@centos7 ~]# systemctl restart postgresql-11 [root@centos7 ~]# systemctl status postgresql-11|grep 'ago$'

84|P a g e

Active: active (running) since Thu 2019-05-30 13:33:37 EDT; 10s ago [root@centos7 ~]#

Impact:

Dependingonsettings,itispossibleforpgAudittogenerateanenormousvolumeoflogging.Becarefultodetermineexactlywhatneedstobeauditloggedinyourenvironmenttoavoidloggingtoomuch.

References:

1. https://www.pgaudit.org/

Notes:

pgAuditversionsrelatetoPostgreSQLmajorversions;ensureyouinstallthepgAuditpackagethatmatchesyourPostgreSQLversion.

CISControls:

Version6


Version7


85|P a g e

4 User Access and Authorization

Thecapabilitytousedatabaseresourcesatagivenlevel,oruserauthorizationrules,allowsforusermanipulationofthevariouspartsofthePostgreSQLdatabase.Theseauthorizationsmustbestructuredtoblockunauthorizeduseand/orcorruptionofvitaldataandservicesbysettingrestrictionsonusercapabilities.

4.1 Ensure sudo is configured correctly (Scored)



Description:

ItiscommontohavemorethanoneauthorizedindividualadministeringthePostgreSQLserviceattheOperatingSystemlevel.ItisalsoquitecommontopermitloginprivilegestoindividualsonaPostgreSQLhostwhootherwisearenotauthorizedtoaccesstheserver'sdataclusterandfiles.AdministeringthePostgreSQLdatacluster,asopposedtoitsdata,istobeaccomplishedviaalocalhostloginofaregularUNIXuseraccount.Accesstothepostgressuperuseraccountisrestrictedinsuchamannerastointerdictunauthorizedaccess.sudosatisfiestherequirementsbyescalatingordinaryuseraccountprivilegesasthePostgreSQLRDBMSsuperuser.

Rationale:

Withoutsudo,therewouldnotbecapabilitiestostrictlycontrolaccesstothesuperuseraccountandtosecurelyandauthoritativelyaudititsuse.

Audit:

LoginasanOperatingSystemuserauthorizedtoescalateprivilegesandtestthesudoinvocationbyexecutingthefollowing:

$ whoami user1 $ groups user1 $ sudo su - postgres [sudo] password for user1: user1 is not in the sudoers file. This incident will be reported.

Asshownabove,user1hasnotbeenaddedtothe/etc/sudoersfileormadeamemberofanygrouplistedinthe/etc/sudoersfile.Whereas:

86|P a g e

$ whoami user2 $ groups user2 pg_wheel $ sudo su - postgres [sudo] password for user2: $ whoami postgres

showstheuser2userisconfiguredproperlyforsudoaccess.

Remediation:

Assuperuserroot,executethecommandvisudotoeditthe/etc/sudoersfilesothefollowinglineispresent:

%pg_wheel ALL= /bin/su - postgres

ThisgrantsanyOperatingSystemuserthatisamemberofthepg_wheelgrouptousesudotobecomethepostgresuser.EnsurethatallOperatingSystemuser'sthatneedsuchaccessaremembersofthegroupasdetailedearlierinthisbenchmark.

References:

1. https://www.sudo.ws/man/1.8.15/sudo.man.html2. https://www.sudo.ws/man/1.8.17/visudo.man.html

CISControls:

Version6

5.8AdministratorsShouldNotDirectlyLogInToASystem(i.e.useRunAs/sudo) Administratorsshouldberequiredtoaccessasystemusingafullyloggedandnon-administrativeaccount.Then,onceloggedontothemachinewithoutadministrativeprivileges,theadministratorshouldtransitiontoadministrativeprivilegesusingtoolssuchasSudoonLinux/UNIX,RunAsonWindows,andothersimilarfacilitiesforothertypesofsystems.

Version7

4.3EnsuretheUseofDedicatedAdministrativeAccounts Ensurethatalluserswithadministrativeaccountaccessuseadedicatedorsecondaryaccountforelevatedactivities.Thisaccountshouldonlybeusedforadministrativeactivitiesandnotinternetbrowsing,email,orsimilaractivities.

87|P a g e

4.2 Ensure excessive administrative privileges are revoked (Scored)



Description:

WithrespecttoPostgreSQLadministrativeSQLcommands,onlysuperusersshouldhaveelevatedprivileges.PostgreSQLregular,orapplication,usersshouldnotpossesstheabilitytocreateroles,createnewdatabases,managereplication,orperformanyotheractiondeemedprivileged.Typically,regularusersshouldonlybegrantedtheminimalsetofprivilegescommensuratewithmanagingtheapplication:

• DDL(createtable,createview,createindex,etc.)• DML(select,insert,update,delete)

Further,ithasbecomebestpracticetocreateseparaterolesforDDLandDML.Givenanapplicationcalled'payroll',onewouldcreatethefollowingusers:

• payroll_owner• payroll_user

AnyDDLprivilegeswouldbegrantedtothe'payroll_owner'accountonly,whileDMLprivilegeswouldbegiventothe'payroll_user'accountonly.Thispreventsaccidentalcreation/altering/droppingofdatabaseobjectsbyapplicationcodethatrunasthe'payroll_user'account.

Rationale:

Bynotrestrictingglobaladministrativecommandstosuperusersonly,regularusersgrantedexcessiveprivilegesmayexecuteadministrativecommandswithunintendedandundesirableresults.

Audit:

First,inspecttheprivilegesgrantedtothedatabasesuperuser(identifiedhereaspostgres)usingthedisplaycommandpsql -c "\du postgres"toestablishabaselineforgrantedadministrativeprivileges.Basedontheoutputbelow,thepostgressuperusercancreateroles,createdatabases,managereplication,andbypassrowlevelsecurity(RLS):

88|P a g e

$ whoami postgres $ psql -c "\du postgres" List of roles Role name | Attributes | Member of ----------+-------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, | {} | Bypass RLS |

Now,let'sinspectthesameinformationforamockregularusercalledappuserusingthedisplaycommandpsql -c "\du appuser".Theoutputconfirmsthatregularuserappuserhasthesameelevatedprivilegesassystemadministratoruserpostgres.Thisisafail.

$ whoami postgres $ psql -c "\du appuser" List of roles Role name | Attributes | Member of ----------+-------------------------------------------------+----------- appuser | Superuser, Create role, Create DB, Replication, | {} | Bypass RLS |

Whilethisexampledemonstratedexcessiveadministrativeprivilegesgrantedtoasingleuser,acomprehensiveauditshouldbeconductedtoinspectalldatabaseusersforexcessiveadministrativeprivileges.Thiscanbeaccomplishedviaeitherofthecommandsbelow.

$ whoami postgres $ psql -c "\du *" $ psql -c "select * from pg_user order by usename"

NOTEUsing"\du*"willshowallthedefaultPostgreSQLroles(e.g.pg_monitor)aswellasany'normal'roles.Thisisexpected,andshouldnotbecauseforalarm.

Remediation:

Ifanyregularorapplicationusershavebeengrantedexcessiveadministrativerights,thoseprivilegesshouldberemovedimmediatelyviathePostgreSQLALTER ROLESQLcommand.Usingthesameexampleabove,thefollowingSQLstatementsrevokeallunnecessaryelevatedadministrativeprivilegesfromtheregularuserappuser:

$ whoami postgres $ psql -c "ALTER ROLE appuser NOSUPERUSER;" ALTER ROLE $ psql -c "ALTER ROLE appuser NOCREATEROLE;" ALTER ROLE $ psql -c "ALTER ROLE appuser NOCREATEDB;" ALTER ROLE $ psql -c "ALTER ROLE appuser NOREPLICATION;"

89|P a g e

ALTER ROLE $ psql -c "ALTER ROLE appuser NOBYPASSRLS;" ALTER ROLE $ psql -c "ALTER ROLE appuser NOINHERIT;" ALTER ROLE

VerifytheappusernowpassesyourcheckbyhavingnodefinedAttributes:

$ whoami postgres $ psql -c "\du appuser" List of roles Role name | Attributes | Member of ----------+------------+----------- appuser | | {}

References:

1. https://www.postgresql.org/docs/11/static/sql-revoke.html2. https://www.postgresql.org/docs/11/static/sql-createrole.html3. https://www.postgresql.org/docs/11/static/sql-alterrole.html

CISControls:

Version6

5.1MinimizeAndSparinglyUseAdministrativePrivileges Minimizeadministrativeprivilegesandonlyuseadministrativeaccountswhentheyarerequired.Implementfocusedauditingontheuseofadministrativeprivilegedfunctionsandmonitorforanomalousbehavior.

Version7


90|P a g e

4.3 Ensure excessive function privileges are revoked (Scored)



Description:

Incertainsituations,toproviderequiredfunctionality,PostgreSQLneedstoexecuteinternallogic(storedprocedures,functions,triggers,etc.)and/orexternalcodemoduleswithelevatedprivileges.However,iftheprivilegesrequiredforexecutionareatahigherlevelthantheprivilegesassignedtoorganizationalusersinvokingthefunctionalityapplications/programs,thoseusersareindirectlyprovidedwithgreaterprivilegesthanassignedbytheirorganization.Thisisknownasprivilegeelevation.Privilegeelevationmustbeutilizedonlywherenecessary.Executeprivilegesforapplicationfunctionsshouldberestrictedtoauthorizedusersonly.

Rationale:

Ideally,allapplicationsourcecodeshouldbevettedtovalidateinteractionsbetweentheapplicationandthelogicinthedatabase,butthisisusuallynotpossibleorfeasiblewithavailableresourcesevenifthesourcecodeisavailable.TheDBAshouldattempttoobtainassurancesfromthedevelopmentorganizationthatthisissuehasbeenaddressedandshoulddocumentwhathasbeendiscovered.TheDBAshouldalsoinspectallapplicationlogicstoredinthedatabase(intheformoffunctions,rules,andtriggers)forexcessiveprivileges.

Audit:

FunctionsinPostgreSQLcanbecreatedwiththeSECURITY DEFINERoption.WhenSECURITY DEFINERfunctionsareexecutedbyauser,saidfunctionisrunwiththeprivilegesoftheuserwhocreatedit,nottheuserwhoisrunningit.TolistallfunctionsthathaveSECURITY DEFINER,runthefollowingSQL:

$ whoami root $ sudo su - postgres $ psql -c "SELECT nspname, proname, proargtypes, prosecdef, rolname, proconfig FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid JOIN pg_authid a ON a.oid = p.proowner WHERE prosecdef OR NOT proconfig IS NULL;"

Inthequeryresults,aprosecdefvalueof't'onarowindicatesthatthatfunctionusesprivilegeelevation.IfelevationofPostgreSQLprivilegesisutilizedbutnotdocumented,thisisafail.

91|P a g e

IfelevationofPostgreSQLprivilegesisdocumented,butnotimplementedasdescribedinthedocumentation,thisisafail.Iftheprivilege-elevationlogiccanbeinvokedinwaysotherthanintended,orincontextsotherthanintended,orbysubjects/principalsotherthanintended,thisisafail.

Remediation:

Wherepossible,revokeSECURITY DEFINERonPostgreSQLfunctions.TochangeaSECURITY DEFINERfunctiontoSECURITY INVOKER,runthefollowingSQL:

$ whoami root $ sudo su - postgres $ psql -c "ALTER FUNCTION [functionname] SECURITY INVOKER;"

IfitisnotpossibletorevokeSECURITY DEFINER,ensurethefunctioncanbeexecutedbyonlytheaccountsthatabsolutelyneedsuchfunctionality:

REVOKE EXECUTE ON FUNCTION delete_customer(integer,boolean) FROM appreader; REVOKE

Confirmthattheappreaderusermaynolongerexecutethefunction:

SELECT proname, proacl FROM pg_proc WHERE proname = 'delete_customer'; proname | proacl -----------------+-------------------------------------------------------- delete_customer | {=X/postgres,postgres=X/postgres,appwriter=X/postgres} (1 row)

Basedonoutputabove,appreader=X/postgresnolongerexistsintheproaclcolumnresultsreturnedfromqueryandconfirmsappreaderisnolongergrantedexecuteprivilegeonthefunction.

References:

1. https://www.postgresql.org/docs/11/static/catalog-pg-proc.html2. https://www.postgresql.org/docs/11/static/sql-grant.html3. https://www.postgresql.org/docs/11/static/sql-revoke.html4. https://www.postgresql.org/docs/11/static/sql-createfunction.html

CISControls:

Version6

5.1MinimizeAndSparinglyUseAdministrativePrivileges Minimizeadministrativeprivilegesandonlyuseadministrativeaccountswhentheyare

92|P a g e

required.Implementfocusedauditingontheuseofadministrativeprivilegedfunctionsandmonitorforanomalousbehavior.

Version7


93|P a g e

4.4 Ensure excessive DML privileges are revoked (Scored)



Description:

DML(insert,update,delete)operationsatthetablelevelshouldberestrictedtoonlyauthorizedusers.PostgreSQLmanagestablelevelDMLpermissionsviatheGRANTstatement.

Rationale:

ExcessiveDMLgrantscanleadtounprivilegeduserschangingordeletinginformationwithoutproperauthorization.

Audit:

ToauditexcessiveDMLprivileges,takeaninventoryofallusersdefinedintheclusterusingthe\du+ *SQLcommand,aswellasalltablesdefinedinthedatabaseusingthe\dt *.*SQLcommand.Furthermore,theintersectionmatrixoftablesandusergrantscanbeobtainedbyqueryingsystemcatalogspg_tablesandpg_user.NotethatinPostgreSQL,usersaredefinedcluster-wideacrossalldatabases,whileschemasandtablesarespecifictoaparticulardatabase.Therefore,thecommandsbelowshouldbeexecutedforeachdefineddatabaseinthecluster.Withthisinformation,inspectdatabasetablegrantsanddetermineifanyareexcessivefordefineddatabaseusers.

postgres=# -- display all users defined in the cluster postgres=# \x Expanded display is on. postgres=# \du+ * List of roles -[ RECORD 1 ]----------------------------------------------------------- Role name | pg_signal_backend Attributes | Cannot login Member of | {} Description | -[ RECORD 2 ]----------------------------------------------------------- Role name | postgres Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS Member of | {} Description | postgres=# -- display all schema.tables created in current database

94|P a g e

postgres=# \x Expanded display is off. postgres=# \dt+ *.* List of relations Schema | Name | Type | Owner | Size | -------------------+-------------------------+-------+----------+-----------+ information_schema | sql_features | table | postgres | 96 kB | information_schema | sql_implementation_info | table | postgres | 48 kB | information_schema | sql_languages | table | postgres | 48 kB | information_schema | sql_packages | table | postgres | 48 kB | information_schema | sql_parts | table | postgres | 48 kB | information_schema | sql_sizing | table | postgres | 48 kB | information_schema | sql_sizing_profiles | table | postgres | 8192 bytes| (snip) postgres=# -- query all tables and user grants in current database postgres=# -- the system catalogs 'information_schema' and 'pg_catalog' are excluded postgres=# select t.schemaname, t.tablename, u.usename, has_table_privilege(u.usename, t.tablename, 'select') as select, has_table_privilege(u.usename, t.tablename, 'insert') as insert, has_table_privilege(u.usename, t.tablename, 'update') as update, has_table_privilege(u.usename, t.tablename, 'delete') as delete from pg_tables t, pg_user u where t.schemaname not in ('information_schema','pg_catalog'); schemaname | tablename | usename | select | insert | update | delete ------------+-----------+---------+--------+--------+--------+-------- (0 rows)

Fortheexamplebelow,weillustrateusingasingletablecustomerandtwoapplicationusersappwriterandappreader.Theintentionisforappwritertohavefullselect,insert,update,anddeleterightsandforappreadertoonlyhaveselectrights.Wecanquerytheseprivilegeswiththeexamplebelowusingthehas_table_privilegefunctionandfilteringforjustthetableandrolesinquestion.

postgres=# select t.tablename, u.usename, has_table_privilege(u.usename, t.tablename, 'select') as select, has_table_privilege(u.usename, t.tablename, 'insert') as insert, has_table_privilege(u.usename, t.tablename, 'update') as update, has_table_privilege(u.usename, t.tablename, 'delete') as delete from pg_tables t, pg_user u where t.tablename = 'customer' and u.usename in ('appwriter','appreader'); tablename | usename | select | insert | update | delete ----------+-----------+--------+--------+--------+-------- customer | appwriter | t | t | t | t customer | appreader | t | t | t | t (2 rows)

Asdepicted,bothusershavefullprivilegesforthecustomertable.Thisisafail.Wheninspectingdatabase-wideresultsforallusersandalltablegrants,employa

95|P a g e

comprehensiveapproach.CollaborationwithapplicationdevelopersisparamounttocollectivelydetermineonlythosedatabaseusersthatrequirespecificDMLprivilegesandonwhichtables.

Remediation:

IfagivendatabaseuserhasbeengrantedexcessiveDMLprivilegesforagivendatabasetable,thoseprivilegesshouldberevokedimmediatelyusingtheREVOKESQLcommand.Continuingwiththeexampleabove,removeunauthorizedgrantsforappreaderuserusingtheREVOKEstatementandverifytheBooleanvaluesarenowfalse.

postgres=# REVOKE INSERT, UPDATE, DELETE ON TABLE customer FROM appreader; REVOKE postgres=# select t.tablename, u.usename, has_table_privilege(u.usename, t.tablename, 'select') as select, has_table_privilege(u.usename, t.tablename, 'insert') as insert, has_table_privilege(u.usename, t.tablename, 'update') as update, has_table_privilege(u.usename, t.tablename, 'delete') as delete from pg_tables t, pg_user u where t.tablename = 'customer' and u.usename in ('appwriter','appreader'); tablename | usename | select | insert | update | delete ----------+-----------+--------+--------+--------+-------- customer | appwriter | t | t | t | t customer | appreader | t | f | f | f (2 rows)

WiththepublicationofCVE-2018-1058,itisalsorecommendedthatallprivilegesberevokedfromthepublicschemaforallusersonalldatabases:

postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE

DefaultValue:

Thetableowner/creatorhasfullprivileges;allotherusersmustbeexplicitlygrantedaccess.

References:

1. https://www.postgresql.org/docs/11/static/sql-grant.html2. https://www.postgresql.org/docs/11/static/sql-revoke.html3. https://www.postgresql.org/docs/11/static/functions-info.html#functions-info-

access-table4. https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-

1058:_Protect_Your_Search_Path

96|P a g e

5. https://nvd.nist.gov/vuln/detail/CVE-2018-1058

CISControls:

Version6


Version7


97|P a g e

4.5 Use pg_permission extension to audit object permissions (Not Scored)



Description:

UsingaPostgreSQLextensioncalledpg_permissionsitispossibletodeclarewhichDBusersshouldhavewhichpermissionsonagivenobjectandgenerateareportshowingcompliance/deviation.

Rationale:

AuditingpermissionsinaPostgreSQLdatabasecanbeintimidatinggiventhedefaultmannerinwhichpermissionsarepresented.Thepg_permissionsextensiongreatlysimplifiesthispresentationandallowstheusertodeclarewhatpermissionsshouldexistandthenreportondifferencesfromthatideal.

Audit:

Seeifthepg_permissionsextensionisavailableforuse:

postgres=# select * from pg_available_extensions where name='pg_permission'; name | default_version | installed_version | comment ------+-----------------+-------------------+--------- (0 rows)

Iftheextensionisn'tfound,thisisafail.

Remediation:

Atthistime,pg_permissionisnotpackagedbythePGDGpackagingteam.Assuch,downloadthelatestfromtheextension'ssite,compileit,andtheninstallit:

[root@centos7 ~]# whoami root [root@centos7 ~]# yum -y install postgresql11-devel Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile * base: centos.mirrors.tds.net * extras: mirror.team-cymru.com * updates: mirrors.gigenet.com 1425 packages excluded due to repository priority protections

98|P a g e

Resolving Dependencies --> Running transaction check ---> Package postgresql11-devel.x86_64 0:11.3-1PGDG.rhel7 will be installed --> Processing Dependency: libicu-devel for package: postgresql11-devel-11.3-1PGDG.rhel7.x86_64 --> Running transaction check ---> Package libicu-devel.x86_64 0:50.1.2-17.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: postgresql11-devel x86_64 11.3-1PGDG.rhel7 pgdg11 2.0 M Installing for dependencies: libicu-devel x86_64 50.1.2-17.el7 base 702 k Transaction Summary ============================================================================= Install 1 Package (+1 Dependent package) Total download size: 2.7 M Installed size: 13 M Downloading packages: (1/2): libicu-devel-50.1.2-17.el7.x86_64.rpm | 702 kB 00:00:00 (2/2): postgresql11-devel-11.3-1PGDG.rhel7.x86_64.rpm | 2.0 MB 00:00:01 ----------------------------------------------------------------------------- Total 1.9 MB/s | 2.7 MB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libicu-devel-50.1.2-17.el7.x86_64 1/2 Installing : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 2/2 Verifying : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 1/2 Verifying : libicu-devel-50.1.2-17.el7.x86_64 2/2 Installed: postgresql11-devel.x86_64 0:11.3-1PGDG.rhel7 Dependency Installed: libicu-devel.x86_64 0:50.1.2-17.el7 Complete! [root@instance-1 ~]# curl -L -o pg_permission_1.1.tgz https://github.com/cybertec-postgresql/pg_permission/archive/REL_1_1.tar.gz % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 142 0 142 0 0 581 0 --:--:-- --:--:-- --:--:-- 579 0 0 0 9437 0 0 24799 0 --:--:-- --:--:-- --:--:-- 24799 [root@instance-1 ~]# tar xf pg_permission_1.1.tgz [root@instance-1 ~]# cd pg_permission-REL_1_1/

99|P a g e

[root@instance-1 ~]# which pg_config /usr/bin/which: no pg_config in (/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin) [root@instance-1 ~]# export PATH=/usr/pgsql-11/bin:$PATH [root@instance-1 ~]# which pg_config /usr/pgsql-11/bin/pg_config [root@instance-1 ~]# make install /usr/bin/mkdir -p '/usr/pgsql-11/share/extension' /usr/bin/mkdir -p '/usr/pgsql-11/share/extension' /usr/bin/mkdir -p '/usr/pgsql-11/doc/extension' /usr/bin/install -c -m 644 .//pg_permissions.control '/usr/pgsql-11/share/extension/' /usr/bin/install -c -m 644 .//pg_permissions--*.sql '/usr/pgsql-11/share/extension/' /usr/bin/install -c -m 644 .//README.pg_permissions '/usr/pgsql-11/doc/extension/' [root@instance-1 ~]# su - postgres bash-4.2$ whoami postgres bash-4.2$ psql -c "create extension pg_permissions;" CREATE EXTENSION

Nowyouneedtoaddentriestopermission_targetthatcorrespondtoyourdesiredpermissions.

Let'sassumewehaveaschemaappschema,andappusershouldhaveSELECT,UPDATE,DELETE,andINSERTpermissionsonalltablesandviewsinthatschema:

postgres=# INSERT INTO public.permission_target postgres=# (id, role_name, permissions, postgres=# object_type, schema_name) postgres=# VALUES postgres=# (1, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}', postgres=# 'TABLE', 'appschema'); INSERT 0 1 postgres=# INSERT INTO public.permission_target postgres=# (id, role_name, permissions, postgres=# object_type, schema_name) postgres=# VALUES postgres=# (2, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}', postgres=# 'VIEW', 'appschema'); INSERT 0 1

Ofcourse,theuserwillneedtheUSAGEprivilegeontheschema:

postgres=# INSERT INTO public.permission_target postgres=# (id, role_name, permissions,i postgres=# object_type, schema_name) postgres=# VALUES postgres=# (3, 'appuser', '{USAGE}', postgres=# 'SCHEMA', 'appschema'); INSERT 0 1

100|P a g e

TheuseralsoneedsUSAGEprivilegesontheappseqsequenceinthatschema:

postgres=# INSERT INTO public.permission_target postgres=# (id, role_name, permissions, postgres=# object_type, schema_name, object_name) postgres=# VALUES postgres=# (4, 'appuser', '{USAGE}', postgres=# 'SEQUENCE', 'appschema', 'appseq'); INSERT 0 1

Nowwecanreviewwhichpermissionsaremissingandwhichadditionalpermissionsaregranted:

postgres=# SELECT * FROM public.permission_diffs(); missing | role_name | object_type | schema_name | object_name | column_name | permission ---------+-----------+-------------+-------------+-------------+-------------+------------ f | laurenz | VIEW | appschema | appview | | SELECT t | appuser | TABLE | appschema | apptable | | DELETE (2 rows)

Thatmeansthatappuserismissing(missingisTRUE)theDELETEprivilegeonappschema.apptablewhichshouldbeGRANTed,whileuserlaurenzhastheadditionalSELECTprivilegeonappschema.appview(missingisFALSE).

Toreviewtheactualpermissionsonanobject,wecanusethe_permissionsviews:

postgres=# SELECT * FROM schema_permissions postgres=# WHERE role_name = 'appuser' AND schema_name = 'appschema' AND granted IS TRUE; object_type | role_name | schema_name | object_name | column_name | permissions | granted -------------+-----------+-------------+-------------+-------------+-------------+--------- SCHEMA | appuser | appschema | | | USAGE | t (1 row)

Formoredetailsandexamples,visittheonlinedocumentation.

References:

1. https://github.com/cybertec-postgresql/pg_permission

101|P a g e

CISControls:

Version7


102|P a g e

4.6 Ensure Row Level Security (RLS) is configured correctly (Not Scored)



Description:

InadditiontotheSQL-standardprivilegesystemavailablethroughGRANT,tablescanhaverowsecuritypoliciesthatrestrict,onaper-userbasis,whichindividualrowscanbereturnedbynormalqueriesorinserted,updated,ordeletedbydatamodificationcommands.ThisfeatureisalsoknownasRowLevelSecurity(RLS).

Bydefault,tablesdonothaveanypolicies,soifauserhasaccessprivilegestoatableaccordingtotheSQLprivilegesystem,allrowswithinitareequallyavailableforqueryingorupdating.Rowsecuritypoliciescanbespecifictocommands,toroles,ortoboth.ApolicycanbespecifiedtoapplytoALLcommands,ortoanycombinationofSELECT,INSERT,UPDATE,orDELETE.Multiplerolescanbeassignedtoagivenpolicy,andnormalrolemembershipandinheritancerulesapply.

IfyouuseRLSandapplyrestrictivepoliciestocertainusers,itisimportantthattheBypass RLSprivilegenotbegrantedtoanyunauthorizedusers.ThisprivilegeoverridesRLS-enabledtablesandassociatedpolicies.Generally,onlysuperusersandelevatedusersshouldpossessthisprivilege.

Rationale:

IfRLSpoliciesandprivilegesarenotconfiguredcorrectly,userscouldperformactionsontablesthattheyarenotauthorizedtoperform,suchasinserting,updating,ordeletingrows.

Audit:

Thefirststepforanorganizationistodeterminewhich,ifany,databasetablesrequireRLS.Thisdecisionisamatterofbusinessprocessesandisuniquetoeachorganization.Todiscoverwhich,ifany,databasetableshaveRLSenabled,executethefollowingquery.Ifanytable(s)shouldhaveRLSpoliciesapplied,butdonotappearinqueryresults,thenthisisafinding.

postgres=# SELECT oid, relname, relrowsecurity FROM pg_class WHERE relrowsecurity IS TRUE;

103|P a g e

Forthepurposeofthisillustration,wewilldemonstratethestandardexamplefromthePostgreSQLdocumentationusingthepasswdtableandpolicyexample.AsofPostgreSQL9.5,thecatalogtablepg_classprovidescolumnrelrowsecuritytoqueryanddeterminewhetherarelationhasRLSenabled.BasedonresultsbelowwecanseeRLSisnotenabled.AssumingthistableshouldbeRLSenabledbutisnot,thisisafinding.

postgres=# SELECT oid, relname, relrowsecurity FROM pg_class WHERE relname = 'passwd'; oid | relname | relrowsecurity -------+---------+---------------- 24679 | passwd | f (1 row)

FurtherinspectionofRLSpoliciesareprovidedviathesystemcatalogpg_policy,whichrecordspolicydetailsincludingtableOID,policyname,applicablecommands,therolesassignedapolicy,andtheUSINGandWITH CHECKclauses.Finally,RLSandassociatedpolicies(ifimplemented)mayalsobeviewedusingthestandardpsqldisplaycommand\d+ schema.tablewhichlistsRLSinformationaspartofthetabledescription.

ShouldyouimplementRowLevelSecurityandapplyrestrictivepoliciestocertainusers,it'simperativethatyoucheckeachuser'sroledefinitionviathepsqldisplaycommand\duandensureunauthorizedusershavenotbeengrantedBypass RLSprivilegeasthiswouldoverrideanyRLSenabledtablesandassociatedpolicies.IfunauthorizedusersdohaveBypass RLSgrantedthenresolvethisusingtheALTER ROLE user NOBYPASSRLS;command.

Remediation:

Again,weareusingtheexamplefromthePostgreSQLdocumentationusingtheexamplepasswdtable.WewillcreatethreedatabaserolestoillustratetheworkingsofRLS:

postgres=# CREATE ROLE admin; CREATE ROLE postgres=# CREATE ROLE bob; CREATE ROLE postgres=# CREATE ROLE alice; CREATE ROLE

Now,wewillinsertknowndataintothepasswdtable:

postgres=# INSERT INTO passwd VALUES ('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash'); INSERT 0 1 postgres=# INSERT INTO passwd VALUES ('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh'); INSERT 0 1 postgres=# INSERT INTO passwd VALUES

104|P a g e

('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh'); INSERT 0 1

AndwewillenableRLSonthetable:

postgres=# ALTER TABLE passwd ENABLE ROW LEVEL SECURITY; ALTER TABLE

NowthatRLSisenabled,weneedtodefineoneormorepolicies.Createtheadministratorpolicyandallowitaccesstoallrows:

postgres=# CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true); CREATE POLICY

Createapolicyfornormaluserstoviewallrows:

postgres=# CREATE POLICY all_view ON passwd FOR SELECT USING (true); CREATE POLICY

Createapolicyfornormalusersthatallowsthemtoupdateonlytheirownrowsandtolimitwhatvaluescanbesetfortheirloginshell:

postgres=# CREATE POLICY user_mod ON passwd FOR UPDATE USING (current_user = user_name) WITH CHECK ( current_user = user_name AND shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh') ); CREATE POLICY

Grantallthenormalrightsonthetabletotheadminuser:

postgres=# GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin; GRANT

Grantonlyselectaccessonnon-sensitivecolumnstoeveryone:

postgres=# GRANT SELECT (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell) ON passwd TO public; GRANT

Grantupdatetoonlythesensitivecolumns:

postgres=# GRANT UPDATE (pwhash, real_name, home_phone, extra_info, shell) ON passwd TO public; GRANT

105|P a g e

EnsurethatnoonehasbeengrantedBypass RLSinadvertently,byrunningthepsqldisplaycommand\du+.IfunauthorizedusersdohaveBypass RLSgrantedthenresolvethisusingtheALTER ROLE <user> NOBYPASSRLS;command.

Youcannowverifythat'admin','bob',and'alice'areproperlyrestrictedbyqueryingthepasswdtableaseachoftheseroles.

References:

1. https://www.postgresql.org/docs/11/static/ddl-rowsecurity.html2. https://www.postgresql.org/docs/11/static/sql-alterrole.html

CISControls:

Version6


Version7


106|P a g e

4.7 Ensure the set_user extension is installed (Not Scored)



Description:

PostgreSQLaccesstothesuperuserdatabaserolemustbecontrolledandauditedtopreventunauthorizedaccess.

Rationale:

Evenwhenreducingandlimitingtheaccesstothesuperuserroleasdescribedearlierinthisbenchmark,itisstilldifficulttodeterminewhoaccessedthesuperuserroleandwhatactionsweretakenusingthatrole.Assuch,itisidealtopreventanyonefromlogginginasthesuperuserandforcingthemtoescalatetheirrole.ThismodelisusedattheOSlevelbytheuseofsudoandshouldbeemulatedinthedatabase.Theset_userextensionallowsforthissetup.

Audit:

Checkiftheextensionisavailablebyqueryingthepg_available_extensionstable:

postgres=# select * from pg_available_extensions where name = 'set_user'; name | default_version | installed_version | comment ------+-----------------+-------------------+--------- (0 rows)

Iftheextensionisnotlistedthisisafail.

Remediation:

Atthetimethisbenchmarkisbeingwritten,set_userisnotavailableasapackageinthePGDGrepository.Assuch,wewillbuilditfromsource:

[root@centos7 ~]# whoami root [root@centos7 ~]# yum -y install postgresql11-devel Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile * base: centos.mirrors.tds.net * extras: mirror.team-cymru.com * updates: mirrors.gigenet.com 1425 packages excluded due to repository priority protections Resolving Dependencies

107|P a g e

--> Running transaction check ---> Package postgresql11-devel.x86_64 0:11.3-1PGDG.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: postgresql11-devel x86_64 11.3-1PGDG.rhel7 pgdg11 2.0 M Transaction Summary ============================================================================= Install 1 Package Total download size: 2.0 M Installed size: 9.3 M Downloading packages: postgresql11-devel-11.3-1PGDG.rhel7.x86_64.rpm | 2.0 MB 00:00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 1/1 Verifying : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 1/1 Installed: postgresql11-devel.x86_64 0:11.3-1PGDG.rhel7 Complete!

[root@centos7 ~]# yum install centos-release-scl Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile * base: centos.mirrors.tds.net * extras: mirror.team-cymru.com * updates: mirrors.cmich.edu 1425 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package centos-release-scl.noarch 0:2-3.el7.centos will be installed --> Processing Dependency: centos-release-scl-rh for package: centos-release-scl-2-3.el7.centos.noarch --> Running transaction check ---> Package centos-release-scl-rh.noarch 0:2-3.el7.centos will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: centos-release-scl noarch 2-3.el7.centos extras 12 k

108|P a g e

Installing for dependencies: centos-release-scl-rh noarch 2-3.el7.centos extras 12 k Transaction Summary =============================================================================Install 1 Package (+1 Dependent package) Total download size: 24 k Installed size: 39 k Is this ok [y/d/N]: y Downloading packages: (1/2): centos-release-scl-rh-2-3.el7.centos.noarch.rpm | 12 kB 00:00:00 (2/2): centos-release-scl-2-3.el7.centos.noarch.rpm | 12 kB 00:00:00 -----------------------------------------------------------------------------Total 144 kB/s | 24 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : centos-release-scl-rh-2-3.el7.centos.noarch 1/2 Installing : centos-release-scl-2-3.el7.centos.noarch 2/2 Verifying : centos-release-scl-2-3.el7.centos.noarch 1/2 Verifying : centos-release-scl-rh-2-3.el7.centos.noarch 2/2 Installed: centos-release-scl.noarch 0:2-3.el7.centos Dependency Installed: centos-release-scl-rh.noarch 0:2-3.el7.centos Complete!

[root@centos7 ~]# yum -y install llvm-toolset-7-clang Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile * base: centos.mirrors.tds.net * centos-sclo-rh: mirror.genesisadaptive.com * centos-sclo-sclo: mirror.jaleco.com * extras: mirror.team-cymru.com * updates: mirror.jaleco.com 1425 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7 will be installed --> Processing Dependency: llvm-toolset-7-clang-libs(x86-64) = 5.0.1-4.el7 for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: llvm-toolset-7-runtime for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclang.so.5(LLVM_5.0)(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libLLVM-5.0.so(LLVM_5.0)(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangToolingCore.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangTooling.so.5()(64bit) for package: llvm-

109|P a g e

toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangStaticAnalyzerFrontend.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangSerialization.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangRewriteFrontend.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangRewrite.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangParse.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangLex.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangIndex.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangFrontendTool.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangFrontend.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangFormat.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangDriver.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangCodeGen.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangBasic.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclangAST.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libclang.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Processing Dependency: libLLVM-5.0.so()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64 --> Running transaction check ---> Package llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7 will be installed --> Processing Dependency: llvm-toolset-7-libomp(x86-64) >= 5.0.1 for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 --> Processing Dependency: llvm-toolset-7-compiler-rt(x86-64) >= 5.0.1 for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 --> Processing Dependency: devtoolset-7-libstdc++-devel for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 --> Processing Dependency: devtoolset-7-gcc-c++ for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 ---> Package llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7 will be installed ---> Package llvm-toolset-7-runtime.x86_64 0:5.0.1-4.el7 will be installed --> Processing Dependency: scl-utils for package: llvm-toolset-7-runtime-5.0.1-4.el7.x86_64 --> Running transaction check ---> Package devtoolset-7-gcc-c++.x86_64 0:7.3.1-5.15.el7 will be installed --> Processing Dependency: devtoolset-7-gcc = 7.3.1-5.15.el7 for package: devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64 --> Processing Dependency: devtoolset-7-runtime for package: devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64 ---> Package devtoolset-7-libstdc++-devel.x86_64 0:7.3.1-5.15.el7 will be installed ---> Package llvm-toolset-7-compiler-rt.x86_64 0:5.0.1-2.el7 will be installed

110|P a g e

---> Package llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7 will be installed ---> Package scl-utils.x86_64 0:20130529-19.el7 will be installed --> Running transaction check ---> Package devtoolset-7-gcc.x86_64 0:7.3.1-5.15.el7 will be installed --> Processing Dependency: devtoolset-7-binutils >= 2.22.52.0.1 for package: devtoolset-7-gcc-7.3.1-5.15.el7.x86_64 ---> Package devtoolset-7-runtime.x86_64 0:7.1-4.el7 will be installed --> Processing Dependency: /usr/sbin/semanage for package: devtoolset-7-runtime-7.1-4.el7.x86_64 --> Processing Dependency: /usr/sbin/semanage for package: devtoolset-7-runtime-7.1-4.el7.x86_64 --> Running transaction check ---> Package devtoolset-7-binutils.x86_64 0:2.28-11.el7 will be installed ---> Package policycoreutils-python.x86_64 0:2.5-29.el7_6.1 will be installed --> Processing Dependency: setools-libs >= 3.3.8-4 for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: libsemanage-python >= 2.5-14 for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: audit-libs-python >= 2.1.3-4 for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: python-IPy for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: libqpol.so.1(VERS_1.4)(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: libqpol.so.1(VERS_1.2)(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: libcgroup for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: libapol.so.4(VERS_4.0)(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: checkpolicy for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: libqpol.so.1()(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Processing Dependency: libapol.so.4()(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64 --> Running transaction check ---> Package audit-libs-python.x86_64 0:2.8.4-4.el7 will be installed ---> Package checkpolicy.x86_64 0:2.5-8.el7 will be installed ---> Package libcgroup.x86_64 0:0.41-20.el7 will be installed ---> Package libsemanage-python.x86_64 0:2.5-14.el7 will be installed ---> Package python-IPy.noarch 0:0.75-6.el7 will be installed ---> Package setools-libs.x86_64 0:3.3.8-4.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: llvm-toolset-7-clang x86_64 5.0.1-4.el7 centos-sclo-rh 545 k Installing for dependencies: audit-libs-python x86_64 2.8.4-4.el7 base 76 k checkpolicy x86_64 2.5-8.el7 base 295 k devtoolset-7-binutils x86_64 2.28-11.el7 centos-sclo-rh 5.3 M devtoolset-7-gcc x86_64 7.3.1-5.15.el7 centos-sclo-rh 29 M

111|P a g e

devtoolset-7-gcc-c++ x86_64 7.3.1-5.15.el7 centos-sclo-rh 11 M devtoolset-7-libstdc++-devel x86_64 7.3.1-5.15.el7 centos-sclo-rh 2.6 M devtoolset-7-runtime x86_64 7.1-4.el7 centos-sclo-rh 20 k libcgroup x86_64 0.41-20.el7 base 66 k libsemanage-python x86_64 2.5-14.el7 base 113 k llvm-toolset-7-clang-libs x86_64 5.0.1-4.el7 centos-sclo-rh 13 M llvm-toolset-7-compiler-rt x86_64 5.0.1-2.el7 centos-sclo-rh 1.7 M llvm-toolset-7-libomp x86_64 5.0.1-2.el7 centos-sclo-rh 246 k llvm-toolset-7-llvm-libs x86_64 5.0.1-8.el7 centos-sclo-rh 13 M llvm-toolset-7-runtime x86_64 5.0.1-4.el7 centos-sclo-rh 1.1 M policycoreutils-python x86_64 2.5-29.el7_6.1 updates 456 k python-IPy noarch 0.75-6.el7 base 32 k scl-utils x86_64 20130529-19.el7 base 24 k setools-libs x86_64 3.3.8-4.el7 base 620 k Transaction Summary =============================================================================Install 1 Package (+18 Dependent packages) Total download size: 80 M Installed size: 261 M Downloading packages: (1/19): audit-libs-python-2.8.4-4.el7.x86_64.rpm | 76 kB 00:00:00 (2/19): checkpolicy-2.5-8.el7.x86_64.rpm | 295 kB 00:00:00 warning: /var/cache/yum/x86_64/7/centos-sclo-rh/packages/devtoolset-7-binutils-2.28-11.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID f2ee9d55: NOKEY Public key for devtoolset-7-binutils-2.28-11.el7.x86_64.rpm is not installed (3/19): devtoolset-7-binutils-2.28-11.el7.x86_64.rpm | 5.3 MB 00:00:04 (4/19): devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64.rpm | 11 MB 00:00:04 (5/19): devtoolset-7-runtime-7.1-4.el7.x86_64.rpm | 20 kB 00:00:00 (6/19): llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm | 545 kB 00:00:00 (7/19): devtoolset-7-libstdc++-devel-7.3.1-5.15.el7.x86_64.rpm | 2.6 MB 00:00:01 (8/19): libsemanage-python-2.5-14.el7.x86_64.rpm | 113 kB 00:00:01 (9/19): libcgroup-0.41-20.el7.x86_64.rpm | 66 kB 00:00:01 (10/19): llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm | 1.7 MB 00:00:00 (11/19): llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm | 246 kB 00:00:00 (12/19): llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm | 13 MB 00:00:02 (13/19): llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm | 1.1 MB 00:00:00 (14/19): python-IPy-0.75-6.el7.noarch.rpm | 32 kB 00:00:00 (15/19): scl-utils-20130529-19.el7.x86_64.rpm | 24 kB 00:00:00 (16/19): policycoreutils-python-2.5-29.el7_6.1.x86_64.rpm | 456 kB 00:00:00 (17/19): devtoolset-7-gcc-7.3.1-5.15.el7.x86_64.rpm | 29 MB 00:00:10 (18/19): llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm | 13 MB 00:00:03 (19/19): setools-libs-3.3.8-4.el7.x86_64.rpm | 620 kB 00:00:02 ----------------------------------------------------------------------------- Total 7.4 MB/s | 80 MB 00:00:10 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo Importing GPG key 0xF2EE9D55: Userid : "CentOS SoftwareCollections SIG (https://wiki.centos.org/SpecialInterestGroup/SCLo) <[email protected]>" Fingerprint: c4db d535 b1fb ba14 f8ba 64a8 4eb8 4e71 f2ee 9d55 Package : centos-release-scl-rh-2-3.el7.centos.noarch (@extras) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo Running transaction check Running transaction test

112|P a g e

Transaction test succeeded Running transaction Installing : scl-utils-20130529-19.el7.x86_64 1/19 Installing : llvm-toolset-7-runtime-5.0.1-4.el7.x86_64 2/19 Installing : llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64 3/19 Installing : llvm-toolset-7-libomp-5.0.1-2.el7.x86_64 4/19 Installing : llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64 5/19 Installing : setools-libs-3.3.8-4.el7.x86_64 6/19 Installing : checkpolicy-2.5-8.el7.x86_64 7/19 Installing : python-IPy-0.75-6.el7.noarch 8/19 Installing : libsemanage-python-2.5-14.el7.x86_64 9/19 Installing : libcgroup-0.41-20.el7.x86_64 10/19 Installing : audit-libs-python-2.8.4-4.el7.x86_64 11/19 Installing : policycoreutils-python-2.5-29.el7_6.1.x86_64 12/19 Installing : devtoolset-7-runtime-7.1-4.el7.x86_64 13/19 Installing : devtoolset-7-libstdc++-devel-7.3.1-5.15.el7.x86_64 14/19 Installing : devtoolset-7-binutils-2.28-11.el7.x86_64 15/19 Installing : devtoolset-7-gcc-7.3.1-5.15.el7.x86_64 16/19 Installing : devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64 17/19 Installing : llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 18/19 Installing : llvm-toolset-7-clang-5.0.1-4.el7.x86_64 19/19 Verifying : policycoreutils-python-2.5-29.el7_6.1.x86_64 1/19 Verifying : audit-libs-python-2.8.4-4.el7.x86_64 2/19 Verifying : llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 3/19 Verifying : devtoolset-7-libstdc++-devel-7.3.1-5.15.el7.x86_64 4/19 Verifying : scl-utils-20130529-19.el7.x86_64 5/19 Verifying : llvm-toolset-7-runtime-5.0.1-4.el7.x86_64 6/19 Verifying : llvm-toolset-7-clang-5.0.1-4.el7.x86_64 7/19 Verifying : libcgroup-0.41-20.el7.x86_64 8/19 Verifying : llvm-toolset-7-libomp-5.0.1-2.el7.x86_64 9/19 Verifying : libsemanage-python-2.5-14.el7.x86_64 10/19 Verifying : devtoolset-7-gcc-7.3.1-5.15.el7.x86_64 11/19 Verifying : devtoolset-7-binutils-2.28-11.el7.x86_64 12/19 Verifying : llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64 13/19 Verifying : python-IPy-0.75-6.el7.noarch 14/19 Verifying : devtoolset-7-runtime-7.1-4.el7.x86_64 15/19 Verifying : checkpolicy-2.5-8.el7.x86_64 16/19 Verifying : llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64 17/19 Verifying : setools-libs-3.3.8-4.el7.x86_64 18/19 Verifying : devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64 19/19 Installed: llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7 Dependency Installed: audit-libs-python.x86_64 0:2.8.4-4.el7 checkpolicy.x86_64 0:2.5-8.el7 devtoolset-7-binutils.x86_64 0:2.28-11.el7 devtoolset-7-gcc.x86_64 0:7.3.1-5.15.el7 devtoolset-7-gcc-c++.x86_64 0:7.3.1-5.15.el7 devtoolset-7-libstdc++-devel.x86_64 0:7.3.1-5.15.el7 devtoolset-7-runtime.x86_64 0:7.1-4.el7 libcgroup.x86_64 0:0.41-20.el7 libsemanage-python.x86_64 0:2.5-14.el7 llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7 llvm-toolset-7-compiler-rt.x86_64 0:5.0.1-2.el7 llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7

113|P a g e

llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7 llvm-toolset-7-runtime.x86_64 0:5.0.1-4.el7 policycoreutils-python.x86_64 0:2.5-29.el7_6.1 python-IPy.noarch 0:0.75-6.el7 scl-utils.x86_64 0:20130529-19.el7 setools-libs.x86_64 0:3.3.8-4.el7 Complete!

[root@centos7 ~]# yum -y install epel-release Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile * base: centos.mirrors.tds.net * centos-sclo-rh: mirror.genesisadaptive.com * centos-sclo-sclo: mirror.atlanticmetro.net * extras: mirror.team-cymru.com * updates: mirror.sesp.northwestern.edu 1425 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package epel-release.noarch 0:7-11 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: epel-release noarch 7-11 extras 15 k Transaction Summary ============================================================================= Install 1 Package Total download size: 15 k Installed size: 24 k Downloading packages: epel-release-7-11.noarch.rpm | 15 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : epel-release-7-11.noarch 1/1 Verifying : epel-release-7-11.noarch 1/1 Installed: epel-release.noarch 0:7-11 Complete!

[root@centos7 ~]# yum -y install llvm5.0 Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile epel/x86_64/metalink | 12 kB 00:00:00

114|P a g e

* base: centos.mirrors.tds.net * centos-sclo-rh: mirror.trouble-free.net * centos-sclo-sclo: mirror.jaleco.com * epel: mirror.compevo.com * extras: mirror.team-cymru.com * updates: mirrors.gigenet.com epel | 5.3 kB 00:00:00 (1/3): epel/x86_64/group_gz | 88 kB 00:00:00 (2/3): epel/x86_64/updateinfo | 1.0 MB 00:00:00 (3/3): epel/x86_64/primary_db | 6.7 MB 00:00:01 1447 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package llvm5.0.x86_64 0:5.0.1-7.el7 will be installed --> Processing Dependency: llvm5.0-libs(x86-64) = 5.0.1-7.el7 for package: llvm5.0-5.0.1-7.el7.x86_64 --> Running transaction check ---> Package llvm5.0-libs.x86_64 0:5.0.1-7.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: llvm5.0 x86_64 5.0.1-7.el7 epel 2.6 M Installing for dependencies: llvm5.0-libs x86_64 5.0.1-7.el7 epel 13 M Transaction Summary ============================================================================= Install 1 Package (+1 Dependent package) Total download size: 16 M Installed size: 57 M Downloading packages: warning: /var/cache/yum/x86_64/7/epel/packages/llvm5.0-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY Public key for llvm5.0-5.0.1-7.el7.x86_64.rpm is not installed (1/2): llvm5.0-5.0.1-7.el7.x86_64.rpm | 2.6 MB 00:00:00 (2/2): llvm5.0-libs-5.0.1-7.el7.x86_64.rpm | 13 MB 00:00:03 ----------------------------------------------------------------------------- Total 4.4 MB/s | 16 MB 00:00:03 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Importing GPG key 0x352C64E5: Userid : "Fedora EPEL (7) <[email protected]>" Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5 Package : epel-release-7-11.noarch (@extras) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : llvm5.0-libs-5.0.1-7.el7.x86_64 1/2 Installing : llvm5.0-5.0.1-7.el7.x86_64 2/2 Verifying : llvm5.0-5.0.1-7.el7.x86_64 1/2

115|P a g e

Verifying : llvm5.0-libs-5.0.1-7.el7.x86_64 2/2 Installed: llvm5.0.x86_64 0:5.0.1-7.el7 Dependency Installed: llvm5.0-libs.x86_64 0:5.0.1-7.el7 Complete!

[root@centos7 ~]# yum -y install gcc Loaded plugins: fastestmirror, langpacks, priorities Loading mirror speeds from cached hostfile * base: repos-va.psychz.net * centos-sclo-rh: repos-va.psychz.net * centos-sclo-sclo: repos-va.psychz.net * epel: mirror.cogentco.com * extras: mirror.vcu.edu * updates: repos-va.psychz.net 1531 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package gcc.x86_64 0:4.8.5-36.el7_6.2 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: gcc x86_64 4.8.5-36.el7_6.2 updates 16 M Transaction Summary ============================================================================= Install 1 Package Total download size: 16 M Installed size: 37 M Downloading packages: gcc-4.8.5-36.el7_6.2.x86_64.rpm | 16 MB 00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : gcc-4.8.5-36.el7_6.2.x86_64 1/1 Verifying : gcc-4.8.5-36.el7_6.2.x86_64 1/1 Installed: gcc.x86_64 0:4.8.5-36.el7_6.2 Complete!

116|P a g e

$ curl -L https://codeload.github.com/pgaudit/set_user/tar.gz/REL1_6_2 > set_user-1.6.2.tgz % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 14916 0 14916 0 0 57215 0 --:--:-- --:--:-- --:--:-- 184k $ tar xf set_user-1.6.2.tgz $ cd set_user-REL1_6_2 $ export PATH=/usr/pgsql-11/bin:$PATH [root@centos7 set_user-REL1_6_2]# make USE_PGXS=1 install gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o set_user.o set_user.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -L/usr/pgsql-11/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags -lm -shared -o set_user.so set_user.o /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o set_user.bc set_user.c /usr/bin/mkdir -p '/usr/pgsql-11/share/extension' /usr/bin/mkdir -p '/usr/pgsql-11/share/extension' /usr/bin/mkdir -p '/usr/pgsql-11/lib' /usr/bin/install -c -m 644 "set_user.h" /usr/pgsql-11/include /usr/bin/install -c -m 644 .//set_user.control '/usr/pgsql-11/share/extension/' /usr/bin/install -c -m 644 .//set_user--1.6.sql .//set_user--1.5--1.6.sql .//set_user--1.4--1.5.sql .//set_user--1.1--1.4.sql .//set_user--1.0--1.1.sql '/usr/pgsql-11/share/extension/' /usr/bin/install -c -m 755 set_user.so '/usr/pgsql-11/lib/' /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode/set_user' /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode'/set_user/ /usr/bin/install -c -m 644 set_user.bc '/usr/pgsql-11/lib/bitcode'/set_user/./ cd '/usr/pgsql-11/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o set_user.index.bc set_user/set_user.bc

Nowthatset_userisinstalled,weneedtotellPostgreSQLtoloaditslibrary:

$ whoami root $ vi ~postgres/11/data/postgresql.conf # load set_user libs before anything else shared_preload_libraries = 'set_user, other_libs' [root@centos7 ~]# systemctl restart postgresql-11 [root@centos7 ~]# systemctl status postgresql-11|grep 'ago$' Active: active (running) since Thu 2019-05-30 15:13:02 EDT; 1s ago

117|P a g e

Andnow,wecaninstalltheextensionwithSQL:

postgres=# select * from pg_available_extensions where name = 'set_user'; name | default_version | installed_version | comment ---------+-----------------+-------------------+----------------------------- set_user | 1.6 | | similar to SET ROLE but with | | | added logging (1 row) postgres=# create extension set_user; CREATE EXTENSION postgres=# select * from pg_available_extensions where name = 'set_user'; name | default_version | installed_version | comment ---------+-----------------+-------------------+----------------------------- set_user | 1.6 | 1.6 | similar to SET ROLE but with | | | added logging (1 row)

Now,weuseGRANTtoconfigureeachDBAroletoallowittousetheset_userfunctions.Intheexamplebelow,wewillconfiguremydbuserdoug.(YouwoulddothisforeachDBA'snormaluserrole.)

postgres=# grant execute on function set_user(text) to doug; GRANT postgres=# grant execute on function set_user_u(text) to doug; GRANT

ConnecttoPostgreSQLasyourselfandverifyitworksasexpected:

$ whoami psql $ psql -U doug -d postgres postgres=> select set_user('postgres'); ERROR: switching to superuser not allowed HINT: Use 'set_user_u' to escalate. postgres=> select set_user_u('postgres'); set_user_u ------------ OK (1 row) postgres=# select current_user, session_user; current_user | session_user --------------+-------------- postgres | doug (1 row) postgres=# select reset_user(); reset_user ------------ OK (1 row) postgres=> select current_user, session_user; current_user | session_user

118|P a g e

--------------+-------------- doug | doug (1 row)

OnceallDBA'snormaluseraccountshavebeenGRANTedpermission,revoketheabilitytologinasthepostgres(superuser)user:

postgres=# alter user postgres NOLOGIN; ALTER ROLE

Whichresultsin:

$ psql psql: FATAL: role "postgres" is not permitted to log in $ psql -U doug -d postgres psql (11.3)

Makesuretherearenootherrolesthataresuperuser'sandcanstilllogin:

postgres=# SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin; rolname --------- (0 rows)

Verifytherearenounprivilegedrolesthatcanlogindirectlythataregrantedasuperuserroleevenifitismultiplelayersremoved:

postgres=# DROP VIEW IF EXISTS roletree; NOTICE: view "roletree" does not exist, skipping DROP VIEW postgres=# CREATE OR REPLACE VIEW roletree AS postgres-# WITH RECURSIVE postgres-# roltree AS ( postgres(# SELECT u.rolname AS rolname, postgres(# u.oid AS roloid, postgres(# u.rolcanlogin, postgres(# u.rolsuper, postgres(# '{}'::name[] AS rolparents, postgres(# NULL::oid AS parent_roloid, postgres(# NULL::name AS parent_rolname postgres(# FROM pg_catalog.pg_authid u postgres(# LEFT JOIN pg_catalog.pg_auth_members m on u.oid = m.member postgres(# LEFT JOIN pg_catalog.pg_authid g on m.roleid = g.oid postgres(# WHERE g.oid IS NULL postgres(# UNION ALL postgres(# SELECT u.rolname AS rolname, postgres(# u.oid AS roloid, postgres(# u.rolcanlogin, postgres(# u.rolsuper, postgres(# t.rolparents || g.rolname AS rolparents, postgres(# g.oid AS parent_roloid, postgres(# g.rolname AS parent_rolname

119|P a g e

postgres(# FROM pg_catalog.pg_authid u postgres(# JOIN pg_catalog.pg_auth_members m on u.oid = m.member postgres(# JOIN pg_catalog.pg_authid g on m.roleid = g.oid postgres(# JOIN roltree t on t.roloid = g.oid postgres(# ) postgres-# SELECT postgres-# r.rolname, postgres-# r.roloid, postgres-# r.rolcanlogin, postgres-# r.rolsuper, postgres-# r.rolparents postgres-# FROM roltree r postgres-# ORDER BY 1; CREATE VIEW postgres=# SELECT postgres-# ro.rolname, postgres-# ro.roloid, postgres-# ro.rolcanlogin, postgres-# ro.rolsuper, postgres-# ro.rolparents postgres-# FROM roletree ro postgres-# WHERE (ro.rolcanlogin AND ro.rolsuper) postgres-# OR postgres-# ( postgres(# ro.rolcanlogin AND EXISTS postgres(# ( postgres(# SELECT TRUE FROM roletree ri postgres(# WHERE ri.rolname = ANY (ro.rolparents) postgres(# AND ri.rolsuper postgres(# ) postgres(# ); rolname | roloid | rolcanlogin | rolsuper | rolparents ---------+--------+-------------+----------+------------ (0 rows)

Ifanyrolesareidentifiedbythisquery,useREVOKEtocorrect.

Impact:

MuchlikethevenerablesudodoesfortheOS,set_usermanagessuperuseraccessforPostgreSQL.Completeconfigurationofset_userisdocumentedattheextension'swebsiteandshouldbereviewedtoensuretheloggingentriesthatyourorganizationcaresaboutareproperlyconfigured.

Notethatsomeexternaltoolsassumetheycanconnectasthepostgresuserbydefaultandthisisnolongertrue.Youmayfindsometoolsneeddifferentoptions,reconfigured,orevenabandonedtocompensateforthis.

References:

1. https://github.com/pgaudit/set_user

120|P a g e

CISControls:

Version6


5.8AdministratorsShouldNotDirectlyLogInToASystem(i.e.useRunAs/sudo) Administratorsshouldberequiredtoaccessasystemusingafullyloggedandnon-administrativeaccount.Then,onceloggedontothemachinewithoutadministrativeprivileges,theadministratorshouldtransitiontoadministrativeprivilegesusingtoolssuchasSudoonLinux/UNIX,RunAsonWindows,andothersimilarfacilitiesforothertypesofsystems.

Version7


121|P a g e

4.8 Make use of default roles (Not Scored)



Description:

PostgreSQLprovidesasetofdefaultroleswhichprovideaccesstocertain,commonlyneeded,privilegedcapabilitiesandinformation.AdministratorscanGRANTtheserolestousersand/orotherrolesintheirenvironment,providingthoseuserswithaccesstothespecifiedcapabilitiesandinformation.

Rationale:

Inkeepingwiththeprincipleofleastprivilege,judicioususeofthePostgreSQLdefaultrolescangreatlylimittheaccesstoprivileged,orsuperuser,access.

Audit:

Reviewthelistofalldatabaserolesthathavesuperuseraccessanddetermineifoneormorethedefaultroleswouldsufficefortheneedsofthatrole:

$ whoami postgres $ psql psql (11.3) Type "help" for help. postgres=# select rolname from pg_roles where rolsuper is true; rolname ---------- postgres doug (2 rows)

Remediation:

Ifyou'vedeterminedthatoneormoreofthedefaultrolescanbeused,simplyGRANTit:

postgres=# GRANT pg_monitor TO doug; GRANT ROLE

Andthenremovesuperuserfromtheaccount:

postgres=# ALTER ROLE doug NOSUPERUSER; ALTER ROLE

122|P a g e

postgres=# select rolname from pg_roles where rolsuper is true; rolname ---------- postgres (1 row)

DefaultValue:

ThefollowingdefaultrolesexistinPostgreSQL11.x:

• pg_read_all_settingsReadallconfigurationvariables,eventhosenormallyvisibleonlytosuperusers.

• pg_read_all_statsReadallpg_stat_*viewsandusevariousstatisticsrelatedextensions,eventhosenormallyvisibleonlytosuperusers.

• pg_stat_scan_tablesExecutemonitoringfunctionsthatmaytakeACCESS SHARElocksontables,potentiallyforalongtime.

• pg_signal_backendSendsignalstootherbackends(eg:cancelquery,terminate).• pg_read_server_filesAllowreadingfilesfromanylocationthedatabasecan

accessontheserverwithCOPYandotherfile-accessfunctions.• pg_write_server_filesAllowwritingtofilesinanylocationthedatabasecan

accessontheserverwithCOPYandotherfile-accessfunctions.• pg_execute_server_programAllowexecutingprogramsonthedatabaseserveras

theuserthedatabaserunsaswithCOPYandotherfunctionswhichallowexecutingaserver-sideprogram.

• pg_monitorRead/executevariousmonitoringviewsandfunctions.Thisroleisamemberofpg_read_all_settings,pg_read_all_statsandpg_stat_scan_tables.

AdministratorscangrantaccesstotheserolestousersusingtheGRANTcommand.

References:

1. https://www.postgresql.org/docs/11/default-roles.html

CISControls:

Version7


123|P a g e

5 Connection and Login

Therestrictionsonclient/userconnectionstothePostgreSQLdatabaseblocksunauthorizedaccesstodataandservicesbysettingaccessrules.Thesesecuritymeasureshelptoensurethatsuccessfulloginscannotbeeasilymadethroughbrute-forcepasswordattacks,passthehash,orintuitedbycleversocialengineeringexploits.

Settingsaregenerallyrecommendedtobeappliedtoalldefinedprofiles.Thefollowingpresentsstandaloneexamplesofloginsforparticularusecases.TheauthenticationrulesarereadfromthePostgreSQLhost-basedauthenticationfile,pg_hba.conf,fromtoptobottom.ThefirstruleconformingtotheconditionoftherequestexecutestheMETHODandstopsfurtherprocessingofthefile.Incorrectlyappliedrules,asdefinedbyasinglelineinstruction,cansubstantiallyaltertheintendedbehaviorresultingineitherallowingordenyingloginattempts.

Itisstronglyrecommendedthatauthenticationconfigurationsbeconstructedincrementallywithrigidtestingforeachnewlyappliedrule.Becauseofthelargenumberofdifferentvariations,thisbenchmarklimitsitselftoasmallnumberofauthenticationmethodsthatcanbesuccessfullyappliedundermostcircumstances.Furtheranalysis,usingtheotherauthenticationmethodsavailableinPostgreSQL,isencouraged.

124|P a g e

5.1 Ensure login via "local" UNIX Domain Socket is configured correctly (Not Scored)



Description:

Aremotehostlogin,viassh,isarguablythemostsecuremeansofremotelyaccessingandadministeringthePostgreSQLserver.Connectingwiththepsqlclient,viaUNIXDOMAINSOCKETS,usingthepeerauthenticationmethodisthemostsecuremechanismavailableforlocalconnections.ProvidedadatabaseuseraccountofthesamenameoftheUNIXaccounthasalreadybeendefinedinthedatabase,evenordinaryuseraccountscanaccesstheclusterinasimilarlyhighlysecuremanner.

Audit:

Newlycreateddataclustersareemptyofdataandhaveonlyoneuseraccount,thesuperuser(postgres).Bydefault,thedataclustersuperuserisnamedaftertheUNIXaccount.LoginauthenticationistestedviaUNIXDOMAINSOCKETSbytheUNIXuseraccountpostgres,thedefaultaccount,andset_userhasnotyetbeenconfigured:

$ whoami postgres $ psql postgres psql (11.3) Type "help" for help. postgres=#

LoginattemptsbyanotherUNIXuseraccountasthesuperusershouldbedenied:

$ su - user1 $ whoami user1 $ psql -U postgres -d postgres psql: FATAL: Peer authentication failed for user "postgres" $ exit

Thistestdemonstratesthatnotonlyislogginginasthesuperuserblocked,butsoislogginginasanotheruser:

$ su - user2 $ whoami user2

125|P a g e

$ psql -U postgres -d postgres psql: FATAL: Peer authentication failed for user "postgres" $ psql -U user1 -d postgres psql: FATAL: Peer authentication failed for user "user1" $ psql -U user2 -d postgres psql (11.3) Type "help" for help. postgres=>

Remediation:

CreationofadatabaseaccountthatmatchesthelocalaccountallowsPEERauthentication:

$ psql -c "CREATE ROLE user1 WITH LOGIN;" CREATE ROLE

ExecutethefollowingastheUNIXuseraccount,thedefaultauthenticationrulesshouldnowpermitthelogin:

$ su - user1 $ whoami user1 $ psql -d postgres psql (11.3) Type "help" for help. postgres=>

Asperthehost-basedauthenticationrulesin$PGDATA/pg_hba.conf,allloginattemptsviaUNIXDOMAINSOCKETSareprocessedonthelinebeginningwithlocal.

ThisistheminimalrulethatmustbeinplaceallowingPEERconnections:

# TYPE DATABASE USER ADDRESS METHOD local all postgres peer

Moretraditionally,arulelikethefollowingwouldbeusedtoallowanylocalPEERconnection:

# TYPE DATABASE USER ADDRESS METHOD local all all peer

Onceedited,theserverprocessmustreloadtheauthenticationfilebeforeitcantakeeffect.Improperlyconfiguredrulescannotupdatei.e.theoldrulesremaininplace.ThePostgreSQLlogswillreporttheoutcomeoftheSIGHUP:

postgres=# select pg_reload_conf(); pg_reload_conf ----------------

126|P a g e

t (1 row)

Thefollowingexamplesillustrateotherpossibleconfigurations.Theresultant"rule"ofsuccess/failuredependsuponthefirstmatchingline:

# allow postgres user logins locally via UNIX socket # TYPE DATABASE USER ADDRESS METHOD local all postgres peer

# allow all local users via UNIX socket # TYPE DATABASE USER ADDRESS METHOD local all all peer

# allow all local users, via UNIX socket, only if they are connecting to a db named the same as their username # e.g. if user 'bob' is connecting to a db named 'bob' # TYPE DATABASE USER METHOD local samerole all peer

# allow only local users, via UNIX socket, who are members of the 'rw' role in the db # TYPE DATABASE USER ADDRESS METHOD local all +rw peer

References:

1. https://www.postgresql.org/docs/11/static/client-authentication.html2. https://www.postgresql.org/docs/11/static/auth-pg-hba-conf.html

CISControls:

Version6

3.4UseOnlySecureChannelsForRemoteSystemAdministration Performallremoteadministrationofservers,workstation,networkdevices,andsimilarequipmentoversecurechannels.Protocolssuchastelnet,VNC,RDP,orothersthatdonotactivelysupportstrongencryptionshouldonlybeusediftheyareperformedoverasecondaryencryptionchannel,suchasSSL,TLSorIPSEC.

Version7

4.5UseMultifactorAuthenticationForAllAdministrativeAccess Usemulti-factorauthenticationandencryptedchannelsforalladministrativeaccountaccess.

127|P a g e

5.2 Ensure login via "host" TCP/IP Socket is configured correctly (Scored)



Description:

AlargenumberofauthenticationMETHODsareavailableforhostsconnectingusingTCP/IPsockets,including:

• trust• reject• md5• scram-sha-256• password• gss• sspi• ident• pam• ldap• radius• cert

METHODstrust,password,andidentarenottobeusedforremotelogins.METHODmd5isthemostpopularandcanbeusedinbothencryptedandunencryptedsessions,however,itisvulnerabletopacketreplayattacks.Itisrecommendedthatscram-sha-256beusedinsteadofmd5.

Useofthegss,sspi,pam,ldap,radius,andcertMETHODs,whilemoresecurethanmd5,aredependentupontheavailabilityofexternalauthenticatingprocesses/servicesandthusarenotcoveredinthisbenchmark.

Rationale:

Audit:

Newlycreateddataclustersareemptyofdataandhaveoneonlyoneuseraccount,thesuperuser.Bydefault,thedataclustersuperuserisnamedaftertheUNIXaccountpostgres.LoginauthenticationcanbetestedviaTCP/IPSOCKETSbyanyUNIXuseraccountfromthelocalhost.ApasswordmustbeassignedtoeachloginROLE:

postgres=# ALTER ROLE postgres WITH PASSWORD 'secret_password'; ALTER ROLE

128|P a g e

Testanunencryptedsession:

$ psql 'host=localhost user=postgres sslmode=disable' Password:

Testanencryptedsession:

$ psql 'host=localhost user=postgres sslmode=require' Password:

Remoteloginsrepeatthepreviousinvocationsbut,ofcourse,fromtheremotehost:Testunencryptedsession:

$ psql 'host=server-name-or-IP user=postgres sslmode=disable' Password:

Testencryptedsessions:

$ psql 'host=server-name-or-IP user=postgres sslmode=require' Password:

Remediation:

Confirmaloginattempthasbeenmadebylookingforaloggederrormessagedetailingthenatureoftheauthenticatingfailure.Inthecaseoffailedloginattempts,whetherencryptedorunencrypted,checkthefollowing:

• Theservershouldbesittingonaportexposedtotheremoteconnectinghosti.e.NOTipaddress127.0.0.1

listen_addresses = '*'

• Anauthenticatingrulemustexistinthefilepg_hba.conf

Thisexamplepermitsonlyencryptedsessionsforthepostgresroleanddeniesallunencryptedsessionforthepostgresrole:

# TYPE DATABASE USER ADDRESS METHOD hostssl all postgres 0.0.0.0/0 scram-sha-256 hostnossl all postgres 0.0.0.0/0 reject

Thefollowingexamplesillustrateotherpossibleconfigurations.Theresultant"rule"ofsuccess/failuredependsuponthefirstmatchingline.

# allow 'postgres' user only from 'localhost/loopback' connections # and only if you know the password # TYPE DATABASE USER ADDRESS METHOD

129|P a g e

host all postgres 127.0.0.1/32 scram-sha-256 # allow users to connect remotely only to the database named after them, # with the correct user password: # (accepts both SSL and non-SSL connections) # TYPE DATABASE USER ADDRESS METHOD host samerole all 0.0.0.0/0 scram-sha-256 # allow only those users who are a member of the 'rw' role to connect # only to the database named after them, with the correct user password: # (accepts both SSL and non-SSL connections) # TYPE DATABASE USER ADDRESS METHOD host samerole +rw 0.0.0.0/0 scram-sha-256

DefaultValue:

Theavailabilityofthedifferentpassword-basedauthenticationmethodsdependsonhowauser'spasswordontheserverisencrypted(orhashed,moreaccurately).Thisiscontrolledbytheconfigurationparameterpassword_encryptionatthetimethepasswordisset.Ifapasswordwasencryptedusingthescram-sha-256setting,thenitcanbeusedfortheauthenticationmethodsscram-sha-256andpassword(butpasswordtransmissionwillbeinplaintextinthelattercase).Theauthenticationmethodspecificationmd5willautomaticallyswitchtousingthescram-sha-256methodinthiscase,asexplainedabove,soitwillalsowork.Ifapasswordwasencryptedusingthemd5setting,thenitcanbeusedonlyforthemd5andpasswordauthenticationmethodspecifications(again,withthepasswordtransmittedinplaintextinthelattercase).(PreviousPostgreSQLreleasessupportedstoringthepasswordontheserverinplaintext.Thisisnolongerpossible.)Tocheckthecurrentlystoredpasswordhashes,seethesystemcatalogpg_authid.

Toupgradeanexistinginstallationfrommd5toscram-sha-256,afterhavingensuredthatallclientlibrariesinusearenewenoughtosupportSCRAM,setpassword_encryption = 'scram-sha-256'inpostgresql.conf,reloadthepostmaster,makealluserssetnewpasswords,andchangetheauthenticationmethodspecificationsinpg_hba.conftoscram-sha-256.

References:

1. https://www.postgresql.org/docs/11/static/client-authentication.html2. https://www.postgresql.org/docs/11/static/auth-pg-hba-conf.html3. https://tools.ietf.org/html/rfc7677

Notes:

1. UseTYPEhostsslwhenadministratingthedatabaseclusterasasuperuser.2. UseTYPEhostnosslforperformancepurposesandwhenDMLoperationsare

deemedsafewithoutSSLconnections.

130|P a g e

3. NoexampleshavebeengivenforADDRESS,i.e.,CIDR,hostname,domainnames,etc.4. Onlythree(3)typesofMETHODhavebeendocumented;therearemanymore.

CISControls:

Version6

14.2EncryptAllSensitiveInformationOverLess-trustedNetworks Allcommunicationofsensitiveinformationoverless-trustednetworksshouldbeencrypted.Wheneverinformationflowsoveranetworkwithalowertrustlevel,theinformationshouldbeencrypted.

Version7

14.4EncryptAllSensitiveInformationinTransit Encryptallsensitiveinformationintransit.

131|P a g e

6 PostgreSQL Settings

AsPostgreSQLevolveswitheachnewiteration,configurationparametersareconstantlybeingadded,deprecated,orremoved.Theseconfigurationparametersdefinenotonlyserverfunctionbuthowwellitperforms.

Manyroutineactivities,combinedwithaspecificsetofconfigurationparametervalues,cansometimesresultindegradedperformanceand,underaspecificsetofconditions,evencomprisethesecurityoftheRDBMS.Thefactofthematteristhatanyparameterhasthepotentialtoaffecttheaccessibilityandperformanceofarunningserver.

Ratherthandescribingallthepossiblecombinationofevents,thisbenchmarkdescribeshowaparametercanbecompromised.Examplesreflectthemostcommon,andeasiesttounderstand,exploits.Althoughbynomeansexhaustive,itishopedthatyouwillbeabletounderstandtheattackvectorsinthecontextofyourenvironment.

6.1 Ensure 'Attack Vectors' Runtime Parameters are Configured (Not Scored)



Description:

UnderstandingthevulnerabilityofPostgreSQLruntimeparametersbytheparticulardeliverymethod,orattackvector.

Rationale:

Thereareasmanywaysofcompromisingaserverasthereareruntimeparameters.AcombinationofanyoneormoreofthemexecutedattherighttimeundertherightconditionshasthepotentialtocompromisetheRDBMS.Mitigatingriskisdependentuponone'sunderstandingoftheattackvectorsandincludes:

1. Viausersession:includesthoseruntimeparametersthatcanbesetbyaROLEthatpersistsforthelifeofaserver-clientsession.

2. Viaattribute:includesthoseruntimeparametersthatcanbesetbyaROLEduringaserver-clientsessionthatcanbeassignedasanattributeforanentitysuchasatable,index,database,orrole.

132|P a g e

3. Viaserverreload:includesthoseruntimeparametersthatcanbesetbythesuperuserusingaSIGHUPorconfigurationfilereloadcommandandaffectstheentirecluster.

4. Viaserverrestart:includesthoseruntimeparametersthatcanbesetandeffectedbyrestartingtheserverprocessandaffectstheentirecluster.

Audit:

Reviewallconfigurationsettings.ConfigurePostgreSQLloggingtorecordallmodificationsandchangestotheRDBMS.

Remediation:

Inthecaseofachangedparameter,thevalueisreturnedbacktoitsdefaultvalue.Inthecaseofasuccessfulexploitofanalreadysetruntimeparameterthenananalysismustbecarriedoutdeterminingthebestapproachmitigatingtherisk.

Impact:

Itcanbedifficulttototallyeliminaterisk.Oncechanged,detectingamiscreantparametercanbecomeproblematic.

References:

1. https://www.postgresql.org/docs/11/static/runtime-config.html

CISControls:

Version6

18.7UseStandardDatabaseHardeningTemplates Forapplicationsthatrelyonadatabase,usestandardhardeningconfigurationtemplates.Allsystemsthatarepartofcriticalbusinessprocessesshouldalsobetested.

Version7

18.11UseStandardHardeningConfigurationTemplatesforDatabases Forapplicationsthatrelyonadatabase,usestandardhardeningconfigurationtemplates.Allsystemsthatarepartofcriticalbusinessprocessesshouldalsobetested.

133|P a g e

6.2 Ensure 'backend' runtime parameters are configured correctly (Scored)



Description:

Inordertoservemultipleclientsefficiently,thePostgreSQLserverlaunchesanew"backend"processforeachclient.Theruntimeparametersinthisbenchmarksectionarecontrolledbythebackendprocess.Theserver'sperformance,intheformofslowqueriescausingadenialofservice,andtheRDBM'sauditingabilitiesfordeterminingrootcauseanalysiscanbecompromisedviatheseparameters.

Rationale:

Adenialofserviceispossiblebydenyingtheuseofindexesandbyslowingdownclientaccesstoanunreasonablelevel.Unsanctionedbehaviorcanbeintroducedbyintroducingroguelibrarieswhichcanthenbecalledinadatabasesession.Loggingcanbealteredandobfuscatedinhibitingrootcauseanalysis.

Audit:

Issuethefollowingcommandtoverifythebackendruntimeparametersareconfiguredcorrectly:

postgres=# SELECT name, setting FROM pg_settings WHERE context IN ('backend','superuser-backend') ORDER BY 1; name | setting -----------------------+--------- ignore_system_indexes | off jit_debugging_support | off jit_profiling_support | off log_connections | on log_disconnections | on post_auth_delay | 0 (6 rows)

Note:Effectingchangestotheseparameterscanonlybemadeatserverstart.Therefore,asuccessfulexploitmaynotbedetecteduntilafteraserverrestart,e.g.,duringamaintenancewindow.

134|P a g e

Remediation:

Oncedetected,theunauthorized/undesiredchangecanbecorrectedbyalteringtheconfigurationfileandexecutingaserverrestart.Inthecasewheretheparameterhasbeenonthecommandlineinvocationofpg_ctltherestartinvocationisinsufficientandanexplicitstopandstartmustinsteadbemade.

1. Querytheviewpg_settingsandcomparewithpreviousqueryoutputsforanychanges.

2. Reviewconfigurationfilespostgresql.confandpostgresql.auto.confandcomparethemwithpreviouslyarchivedfilecopiesforanychanges.

3. Examinetheprocessoutputandlookforparametersthatwereusedatserverstartup:

ps aux | grep -E '[p]ost' | grep -- '-[D]'

Impact:

Allchangesmadeonthislevelwillaffecttheoverallbehavioroftheserver.Thesechangescanonlybeaffectedbyaserverrestartaftertheparametershavebeenalteredintheconfigurationfiles.

References:

1. https://www.postgresql.org/docs/11/static/view-pg-settings.html2. https://www.postgresql.org/docs/11/static/runtime-config.html

CISControls:

Version6


Version7


135|P a g e

6.3 Ensure 'Postmaster' Runtime Parameters are Configured (Not Scored)



Description:

PostgreSQLruntimeparametersthatareexecutedbythepostmasterprocess.

Rationale:

Thepostmasterprocessisthesupervisoryprocessthatassignsabackendprocesstoanincomingclientconnection.Thepostmastermanageskeyruntimeparametersthatareeithersharedbyallbackendconnectionsorneededbythepostmasterprocessitselftorun.

Audit:

ThefollowingparameterscanonlybesetatserverstartbytheownerofthePostgreSQLserverprocessandcluster,typicallytheUNIXuseraccountpostgres.Therefore,allexploitsrequirethesuccessfulcompromiseofeitherthatUNIXaccountorthepostgressuperuseraccountitself.

postgres=# SELECT name, setting FROM pg_settings WHERE context = 'postmaster' ORDER BY 1; name | setting -------------------------------------+---------------------------------------- allow_system_table_mods | off archive_mode | off autovacuum_freeze_max_age | 200000000 autovacuum_max_workers | 3 autovacuum_multixact_freeze_max_age | 400000000 bonjour | off bonjour_name | cluster_name | config_file | /var/lib/pgsql/11/data/postgresql.conf data_directory | /var/lib/pgsql/11/data data_sync_retry | off dynamic_shared_memory_type | posix event_source | PostgreSQL external_pid_file | hba_file | /var/lib/pgsql/11/data/pg_hba.conf hot_standby | on huge_pages | try ident_file | /var/lib/pgsql/11/data/pg_ident.conf jit_provider | llvmjit listen_addresses | localhost

136|P a g e

logging_collector | on max_connections | 100 max_files_per_process | 1000 max_locks_per_transaction | 64 max_logical_replication_workers | 4 max_pred_locks_per_transaction | 64 max_prepared_transactions | 0 max_replication_slots | 10 max_wal_senders | 10 max_worker_processes | 8 old_snapshot_threshold | -1 port | 5432 shared_buffers | 16384 shared_preload_libraries | pgaudit, set_user superuser_reserved_connections | 3 track_activity_query_size | 1024 track_commit_timestamp | off unix_socket_directories | /var/run/postgresql, /tmp unix_socket_group | unix_socket_permissions | 0777 wal_buffers | 512 wal_level | replica wal_log_hints | off (43 rows)

Remediation:

Oncedetected,theunauthorized/undesiredchangecanbecorrectedbyeditingthealteredconfigurationfileandexecutingaserverrestart.Inthecasewheretheparameterhasbeenonthecommandlineinvocationofpg_ctltherestartinvocationisinsufficientandanexplicitstopandstartmustinsteadbemade.

Detectingachangeispossiblebyoneofthefollowingmethods:

1. Querytheviewpg_settingsandcomparewithpreviousqueryoutputsforanychanges

2. Reviewtheconfigurationfilespostgresql.confandpostgresql.auto.confandcomparewithpreviouslyarchivedfilecopiesforanychanges


ps aux | grep -E 'postgres' | grep -- '-[D]'

Impact:

Allchangesmadeonthislevelwillaffecttheoverallbehavioroftheserver.ThesechangescanbeeffectedbyeditingthePostgreSQLconfigurationfilesandbyeitherexecutingaserverSIGHUPfromthecommandlineor,assuperuserpostgres,executingtheSQLcommandselect pg_reload_conf().Adenialofserviceispossiblebytheover-allocatingoflimitedresources,suchasRAM.Datacanbecorruptedbyallowingdamagedpagesto

137|P a g e

loadorbychangingparameterstoreinterpretvaluesinanunexpectedfashion,e.g.changingthetimezone.Clientmessagescanbealteredinsuchawayastointerferewiththeapplicationlogic.Loggingcanbealteredandobfuscatedinhibitingrootcauseanalysis.

References:


CISControls:

Version6


Version7


138|P a g e

6.4 Ensure 'SIGHUP' Runtime Parameters are Configured (Not Scored)



Description:

PostgreSQLruntimeparametersthatareexecutedbytheSIGHUPsignal.

Rationale:

Inordertodefineserverbehaviorandoptimizeserverperformance,theserver'ssuperuserhastheprivilegeofsettingtheseparameterswhicharefoundintheconfigurationfilespostgresql.confandpg_hba.conf.Alternatively,thoseparametersfoundinpostgresql.confcanalsobechangedusingaserverloginsessionandexecutingtheSQLcommandALTER SYSTEMwhichwritesitschangesintheconfigurationfilepostgresql.auto.conf.

Audit:

Thefollowingparameterscanbesetatanytime,withoutinterruptingtheserver,bytheownerofthepostmasterserverprocessandcluster(typicallyUNIXuseraccountpostgres).

postgres=# SELECT name, setting FROM pg_settings WHERE context = 'sighup' ORDER BY 1; name | setting ----------------------------------------+--------------------------------------- archive_command | (disabled) archive_timeout | 0 authentication_timeout | 60 autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold | 50 autovacuum_naptime | 60 autovacuum_vacuum_cost_delay | 20 autovacuum_vacuum_cost_limit | -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 autovacuum_work_mem | -1 bgwriter_delay | 200 bgwriter_flush_after | 64 bgwriter_lru_maxpages | 100 bgwriter_lru_multiplier | 2 checkpoint_completion_target | 0.5 checkpoint_flush_after | 32

140|P a g e

wal_receiver_status_interval | 10 wal_receiver_timeout | 60000 wal_retrieve_retry_interval | 5000 wal_sender_timeout | 60000 wal_sync_method | fdatasync wal_writer_delay | 200 wal_writer_flush_after | 128 (80 rows)

Remediation:

RestoreallvaluesinthePostgreSQLconfigurationfilesandinvoketheservertoreloadtheconfigurationfiles.

Impact:

Allchangesmadeonthislevelwillaffecttheoverallbehavioroftheserver.ThesechangescanbeeffectedbyeditingthePostgreSQLconfigurationfilesandbyeitherexecutingaserverSIGHUPfromthecommandlineor,assuperuserpostgres,executingtheSQLcommandselect pg_reload_conf().Adenialofserviceispossiblebytheover-allocatingoflimitedresources,suchasRAM.Datacanbecorruptedbyallowingdamagedpagestoloadorbychangingparameterstoreinterpretvaluesinanunexpectedfashion,e.g.changingthetimezone.Clientmessagescanbealteredinsuchawayastointerferewiththeapplicationlogic.Loggingcanbealteredandobfuscatedinhibitingrootcauseanalysis.

References:


CISControls:

Version6


Version7


141|P a g e

6.5 Ensure 'Superuser' Runtime Parameters are Configured (Not Scored)



Description:

PostgreSQLruntimeparametersthatcanonlybeexecutedbytheserver'ssuperuser,whichistraditionallypostgres.

Rationale:

Inordertoimproveandoptimizeserverperformance,theserver'ssuperuserhastheprivilegeofsettingtheseparameterswhicharefoundintheconfigurationfilepostgresql.conf.Alternatively,theycanbechangedinaPostgreSQLloginsessionviatheSQLcommandALTER SYSTEMwhichwritesitschangesintheconfigurationfilepostgresql.auto.conf.

Audit:

ThefollowingparameterscanonlybesetatserverstartbytheownerofthePostgreSQLserverprocessandclusteri.e.typicallyUNIXuseraccountpostgres.Therefore,allexploitsrequirethesuccessfulcompromiseofeitherthatUNIXaccountorthepostgressuperuseraccountitself.

postgres=# SELECT name, setting FROM pg_settings WHERE context = 'superuser' ORDER BY 1; name | setting ----------------------------+------------- commit_delay | 0 deadlock_timeout | 1000 dynamic_library_path | $libdir ignore_checksum_failure | off jit_dump_bitcode | off lc_messages | en_US.UTF-8 lo_compat_privileges | off log_duration | off log_error_verbosity | verbose log_executor_stats | off log_lock_waits | off log_min_duration_statement | -1 log_min_error_statement | error log_min_messages | warning log_parser_stats | off log_planner_stats | off log_replication_commands | off

142|P a g e

log_statement | ddl log_statement_stats | off log_temp_files | -1 max_stack_depth | 2048 pgaudit.log | ddl,write pgaudit.log_catalog | on pgaudit.log_client | off pgaudit.log_level | log pgaudit.log_parameter | off pgaudit.log_relation | off pgaudit.log_statement_once | off pgaudit.role | session_preload_libraries | session_replication_role | origin temp_file_limit | -1 track_activities | on track_counts | on track_functions | none track_io_timing | off update_process_title | on wal_compression | off wal_consistency_checking | zero_damaged_pages | off (40 rows)

Remediation:

Theexploitismadeintheconfigurationfiles.Thesechangesareeffecteduponserverrestart.Oncedetected,theunauthorized/undesiredchangecanbemadebyeditingthealteredconfigurationfileandexecutingaserverrestart.Inthecasewheretheparameterhasbeensetonthecommandlineinvocationofpg_ctltherestartinvocationisinsufficientandanexplicitstopandstartmustinsteadbemade.Detectingachangeispossiblebyoneofthefollowingmethods:

1. Querytheviewpg_settingsandcomparewithpreviousqueryoutputsforanychanges.

2. Reviewtheconfigurationfilespostgreql.confandpostgreql.auto.confandcomparewithpreviouslyarchivedfilecopiesforanychanges


ps aux | grep -E 'post' | grep -- '-[D]'

Impact:

Allchangesmadeonthislevelwillaffecttheoverallbehavioroftheserver.Thesechangescanonlybeaffectedbyaserverrestartaftertheparametershavebeenalteredintheconfigurationfiles.Adenialofserviceispossiblebytheoverallocatingoflimitedresources,suchasRAM.Datacanbecorruptedbyallowingdamagedpagestoloadorbychanging

143|P a g e

parameterstoreinterpretvaluesinanunexpectedfashion,e.g.changingthetimezone.Clientmessagescanbealteredinsuchawayastointerferewiththeapplicationlogic.Loggingcanbealteredandobfuscatedinhibitingrootcauseanalysis.

References:


CISControls:

Version6


Version7


144|P a g e

6.6 Ensure 'User' Runtime Parameters are Configured (Not Scored)



Description:

ThesePostgreSQLruntimeparametersaremanagedattheuseraccount(ROLE)level.

Rationale:

Inordertoimproveperformanceandoptimizefeatures,aROLEhastheprivilegeofsettingnumerousparametersinatransaction,session,orasanentityattribute.AnyROLEcanalteranyoftheseparameters.

Audit:

ThemethodusedtoanalyzethestateofROLEruntimeparametersandtodetermineiftheyhavebeencompromisedistoinspectallcatalogsandlistattributesfordatabaseentitiessuchasROLEsanddatabases:

postgres=# SELECT name, setting FROM pg_settings WHERE context = 'user' ORDER BY 1; name | setting -------------------------------------+-------------------- application_name | psql array_nulls | on backend_flush_after | 0 backslash_quote | safe_encoding bytea_output | hex check_function_bodies | on client_encoding | UTF8 client_min_messages | notice commit_siblings | 5 constraint_exclusion | partition cpu_index_tuple_cost | 0.005 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 cursor_tuple_fraction | 0.1 DateStyle | ISO, MDY debug_pretty_print | on debug_print_parse | off debug_print_plan | off debug_print_rewritten | off default_statistics_target | 100 default_tablespace | default_text_search_config | pg_catalog.english default_transaction_deferrable | off

147|P a g e

changingparameterstoreinterpretvaluesinanunexpectedfashion,e.g.changingthetimezone.Loggingcanbealteredandobfuscatedtoinhibitrootcauseanalysis.

References:


CISControls:

Version6


Version7


148|P a g e

6.7 Ensure FIPS 140-2 OpenSSL Cryptography Is Used (Scored)



Description:

Install,configure,anduseOpenSSLonaplatformthathasaNISTcertifiedFIPS140-2installationofOpenSSL.ThisprovidesPostgreSQLinstancestheabilitytogenerateandvalidatecryptographichashestoprotectunclassifiedinformationrequiringconfidentialityandcryptographicprotection,inaccordancewiththedataowner'srequirements.

Rationale:

FederalInformationProcessingStandard(FIPS)Publication140-2isacomputersecuritystandarddevelopedbyaU.S.Governmentandindustryworkinggroupforvalidatingthequalityofcryptographicmodules.Useofweak,oruntested,encryptionalgorithmsunderminethepurposesofutilizingencryptiontoprotectdata.PostgreSQLusesOpenSSLfortheunderlyingencryptionlayer.

Thedatabaseandapplicationmustimplementcryptographicmodulesadheringtothehigherstandardsapprovedbythefederalgovernmentsincethisprovidesassurancetheyhavebeentestedandvalidated.Itistheresponsibilityofthedataownertoassessthecryptographyrequirementsinlightofapplicablefederallaws,ExecutiveOrders,directives,policies,regulations,andstandards.

Fordetailedinformation,refertoNISTFIPSPublication140-2,SecurityRequirementsforCryptographicModules.Notethattheproduct'scryptographicmodulesmustbevalidatedandcertifiedbyNISTasFIPS-compliant.ThesecurityfunctionsvalidatedaspartofFIPS140-2forcryptographicmodulesaredescribedinFIPS140-2AnnexA.CurrentlyonlyRedHatEnterpriseLinuxiscertifiedasaFIPS140-2distributionofOpenSSL.Forotheroperatingsystems,usersmustobtainorbuildtheirownFIPS140-2OpenSSLlibraries.

Audit:

IfPostgreSQLisnotinstalledonRedHatEnterpriseLinux(RHEL)orCentOSthenFIPScannotbeenablednatively.Otherwisethedeploymentmustincorporateacustombuildoftheoperatingsystem.Asthesystemadministrator:

1. RunthefollowingtoseeifFIPSisenabled:

149|P a g e

$ cat /proc/sys/crypto/fips_enabled 1

Iffips_enabledisnot1,thenthesystemisnotFIPSenabled.

2. Runthefollowing(yourresultsandversionmayvary):

$ openssl version OpenSSL 1.0.2k-fips 26 Jan 2017

Iffipsisnotincludedintheopensslversion,thenthesystemisnotFIPScapable.

Remediation:

ConfigureOpenSSLtobeFIPScompliant.PostgreSQLusesOpenSSLforcryptographicmodules.ToconfigureOpenSSLtobeFIPS140-2compliant,seetheofficialRHELDocumentation.Belowisageneralsummaryofthestepsrequired:

• Installthedracut-fipspackage

$ yum -y install dracut-fips Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile [snip] Resolving Dependencies --> Running transaction check ---> Package dracut-fips.x86_64 0:033-554.el7 will be installed --> Processing Dependency: hmaccalc for package: dracut-fips-033-554.el7.x86_64 --> Running transaction check ---> Package hmaccalc.x86_64 0:0.9.13-4.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved Package Arch Version Repository Size Installing: dracut-fips x86_64 033-554.el7 base 61 k Installing for dependencies: hmaccalc x86_64 0.9.13-4.el7 base 26 k Transaction Summary Install 1 Package (+1 Dependent package) Total download size: 87 k Installed size: 107 k Downloading packages: [snip]

150|P a g e

Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : hmaccalc-0.9.13-4.el7.x86_64 1/2 Installing : dracut-fips-033-554.el7.x86_64 2/2 Verifying : hmaccalc-0.9.13-4.el7.x86_64 1/2 Verifying : dracut-fips-033-554.el7.x86_64 2/2 Installed: dracut-fips.x86_64 0:033-554.el7 Dependency Installed: hmaccalc.x86_64 0:0.9.13-4.el7 Complete!

• Recreatetheinitramfsfile

$ dracut -f

• Modifythekernelcommandline,e.g.GRUB_CMDLINE_LINUX,ofthecurrentkernelinthe/etc/default/grubfilebyaddingthefollowingoption:fips=1

• Runone,orbothifunsurehowthemachineisbooting,ofthefollowingcommands:

# If booting from BIOS $ grub2-mkconfig -o /boot/grub2/grub.cfg # If booting from EFI $ grub2-mkconfig -o /boot/efi/EFI/centos/grub.cfg

• Ifyouhaveprelinkinstalledyouwillwanttoexecuteprelink -u -apriortothenextreboot.

• Rebootthesystemforchangestotakeeffect.• Verifyfips_enabledaccordingtoAuditProcedureabove.

References:

1. https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Security_Guide/sect-Security_Guide-Federal_Standards_And_Regulations-Federal_Information_Processing_Standard.html

2. https://csrc.nist.gov/CSRC/media/projects/cryptographic-module-validation-program/documents/security-policies/140sp1758.pdf

3. https://csrc.nist.gov/publications/fips

151|P a g e

CISControls:

Version6


Version7


152|P a g e

6.8 Ensure SSL is enabled and configured correctly (Scored)



Description:

SSLonaPostgreSQLservershouldbeenabled(settoon)andconfiguredtoencryptTCPtraffictoandfromtheserver.

Rationale:

IfSSLisnotenabledandconfiguredcorrectly,thisincreasestheriskofdatabeingcompromisedintransit.

Audit:

TodeterminewhetherSSLisenabled(settoon),simplyquerytheparametervaluewhileloggedintothedatabaseusingeithertheSHOW sslcommandorSELECTfromsystemcatalogviewpg_settingsasillustratedbelow.Inbothcases,sslisoff;thisisafail.

postgres=# SHOW ssl; ssl ----- off (1 row) postgres=# SELECT name, setting, source FROM pg_settings WHERE name = 'ssl'; name | setting | source -----+---------+-------------------- ssl | off | default (1 row)

Remediation:

Forthisexample,andeaseofillustration,wewillbeusingaself-signedcertificatefortheservergeneratedviaopenssl,andthePostgreSQLdefaultsforfilenamingandlocationinthePostgreSQL$PGDATAdirectory.

$ whoami postgres $ # create new certificate and enter details at prompts $ openssl req -new -text -out server.req Generating a 2048 bit RSA private key .....................+++ ..................................................................+++

153|P a g e

writing new private key to 'privkey.pem' Enter PEM pass phrase: Verifying - Enter PEM pass phrase: ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:US State or Province Name (full name) []:Ohio Locality Name (eg, city) [Default City]:Columbus Organization Name (eg, company) [Default Company Ltd]:Me Inc Organizational Unit Name (eg, section) []:IT Common Name (eg, your name or your server's hostname) []:my.me.inc Email Address []:[email protected] Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: $ # remove passphrase (required for automatic server start up) $ openssl rsa -in privkey.pem -out server.key && rm privkey.pem Enter pass phrase for privkey.pem: writing RSA key $ # modify certificate to self signed, generate .key and .crt files $ openssl req -x509 -in server.req -text -key server.key -out server.crt $ # copy .key and .crt files to appropriate location, here default $PGDATA $ cp server.key server.crt $PGDATA $ # restrict file mode for server.key $ chmod og-rwx server.key

EditthePostgreSQLconfigurationfilepostgresql.conftoensurethefollowingitemsareset.Again,weareusingdefaults.Notethatalteringtheseparameterswillrequirerestartingthecluster.

# (change requires restart) ssl = on # allowed SSL ciphers ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # (change requires restart) ssl_cert_file = 'server.crt' # (change requires restart) ssl_key_file = 'server.key' password_encryption = scram-sha-256

154|P a g e

Finally,restartPostgreSQLandconfirmsslusingcommandsoutlinedinAuditProcedures:

postgres=# show ssl; ssl ----- on (1 row)

Impact:

Aself-signedcertificatecanbeusedfortesting,butacertificatesignedbyacertificateauthority(CA)(eitheroneoftheglobalCAsoralocalone)shouldbeusedinproductionsothatclientscanverifytheserver'sidentity.Ifallthedatabaseclientsarelocaltotheorganization,usingalocalCAisrecommended.

Toultimatelyenableandenforcesslauthenticationfortheserver,appropriatehostsslrecordsmustbeaddedtothepg_hba.conffile.BesuretoreloadPostgreSQLafteranychanges(restartnotrequired).

Note:ThehostsslrecordmatchesconnectionattemptsmadeusingTCP/IP,butonlywhentheconnectionismadewithSSLencryption.ThehostrecordmatchesattemptsmadeusingTCP/IP,butallowsbothSSLandnon-SSLconnections.ThehostnosslrecordmatchesattemptsmadeusingTCP/IP,butonlythosewithoutSSL.CareshouldbetakentoenforceSSLasappropriate.

References:

1. https://www.postgresql.org/docs/11/static/ssl-tcp.html2. http://nvlpubs.nist.gov/nistpubs/SpecialPublications/NIST.SP.800-52r1.pdf3. https://www.postgresql.org/docs/11/static/libpq-ssl.html

CISControls:

Version6


Version7


155|P a g e

6.9 Ensure the pgcrypto extension is installed and configured correctly (Not Scored)



Description:

PostgreSQLmustimplementcryptographicmechanismstopreventunauthorizeddisclosureormodificationoforganization-definedinformationatrest(toinclude,ataminimum,PIIandclassifiedinformation)onorganization-definedinformationsystemcomponents.

Rationale:

PostgreSQLhandlingdatathatrequires"dataatrest"protectionsmustemploycryptographicmechanismstopreventunauthorizeddisclosureandmodificationoftheinformationatrest.ThesecryptographicmechanismsmaybenativetoPostgreSQLorimplementedviaadditionalsoftwareoroperatingsystem/filesystemsettings,asappropriatetothesituation.Informationatrestreferstothestateofinformationwhenitislocatedonasecondarystoragedevice(e.g.diskdrive,tapedrive)withinanorganizationalinformationsystem.

Selectionofacryptographicmechanismisbasedontheneedtoprotecttheintegrityoforganizationalinformation.Thestrengthofthemechanismiscommensuratewiththesecuritycategoryand/orclassificationoftheinformation.Organizationshavetheflexibilitytoeitherencryptallinformationonstoragedevices(i.e.fulldiskencryption)orencryptspecificdatastructures(e.g.files,records,orfields).Organizationsmayalsooptionallychoosetoimplementbothtoimplementlayeredsecurity.

Thedecisionwhether,andwhat,toencryptrestswiththedataownerandisalsoinfluencedbythephysicalmeasurestakentosecuretheequipmentandmediaonwhichtheinformationresides.Organizationsmaychoosetoemploydifferentmechanismstoachieveconfidentialityandintegrityprotections,asappropriate.Iftheconfidentialityandintegrityofapplicationdataisnotprotected,thedatawillbeopentocompromiseandunauthorizedmodification.

ThePostgreSQLpgcryptoextensionprovidescryptographicfunctionsforPostgreSQLandisintendedtoaddresstheconfidentialityandintegrityofuserandsysteminformationatrestinnon-mobiledevices.

156|P a g e

Audit:

OnepossiblewaytoencryptdatawithinPostgreSQListousethepgcryptoextension.TocheckifpgcryptoisinstalledonPostgreSQL,asadatabaseadministratorrunthefollowingcommands:

postgres=# SELECT * FROM pg_available_extensions WHERE name='pgcrypto'; name | default_version | installed_version | comment ----------+-----------------+-------------------+------------------------- pgcrypto | 1.3 | | cryptographic functions (1 row)

Ifdatainthedatabaserequiresencryptionandpgcryptoisnotavailable,thisisafail.Ifdiskorfilesystemrequiresencryption,askthesystemowner,DBA,andSAtodemonstratetheuseofdisk-levelencryption.Ifthisisrequiredandisnotfound,thisisafail.Ifcontrolsdonotexistorarenotenabled,thisisalsoafail.

Remediation:

ThepgcryptoextensionisincludedwiththePostgreSQL'contrib'package.Althoughincluded,itneedstobecreatedinthedatabase.

Asthedatabaseadministrator,runthefollowing:

postgres=# CREATE EXTENSION pgcrypto; CREATE EXTENSION

Verifypgcryptoisinstalled:

postgres=# SELECT * FROM pg_available_extensions WHERE name='pgcrypto'; name | default_version | installed_version | comment ----------+-----------------+-------------------+------------------------- pgcrypto | 1.3 | 1.3 | cryptographic functions (1 row)

Impact:

Whenconsideringorundertakinganyformofencryption,itiscriticaltounderstandthestateoftheencrypteddataatallstagesofthedatalifecycle.Theuseofpgcryptoensuresthatthedataatrestinthetables(andthereforeondisk)isencrypted,butforthedatatobeaccessedbyanyusersorapplications,saidusers/applicationswill,bynecessity,haveaccesstotheencryptanddecryptkeysandthedatainquestionwillbeencrypted/decryptedinmemoryandthentransferredto/fromtheuser/applicationinthatform.

157|P a g e

References:

1. http://www.postgresql.org/docs/11/static/pgcrypto.html

CISControls:

Version6

14.5EncryptAtRestSensitiveInformation Sensitiveinformationstoredonsystemsshallbeencryptedatrestandrequireasecondaryauthenticationmechanism,notintegratedintotheoperatingsystem,inordertoaccesstheinformation.

Version7

14.8EncryptSensitiveInformationatRest Encryptallsensitiveinformationatrestusingatoolthatrequiresasecondaryauthenticationmechanismnotintegratedintotheoperatingsystem,inordertoaccesstheinformation.

158|P a g e

7 Replication

Dataredundancyoftenplaysamajorroleaspartofanoveralldatabasestrategy.ReplicationisanexampleofdataredundancyandfulfillsbothHighAvailabilityandHighPerformancerequirements.However,althoughtheDBAmayhaveexpendedmuchtimeandeffortsecuringthePRIMARYhostandtakenthetimetohardenSTANDBYconfigurationparameters,onesometimesoverlooksthemediumtransmittingthedataitselfoverthenetwork.Consequently,replicationisanappealingattackvectorgiventhatallDDL,andDMLoperationsexecutedonthePRIMARY,ormaster,hostissentoverthewiretotheSECONDARY/STANDBY,orslave,hosts.Fortunately,whencorrectlyunderstood,defeatingsuchattackscanbeimplementedinastraightforwardmanner.Thisbenchmarkreviewsthoseissuessurroundingthemostcommonmechanismsofreplicatingdatabetweenhosts.ThereareseveralPostgreSQLreplicationmechanismsandincludes:

• WarmStandby(alsoknownasLOGShipping)o TransactionlogsarecopiedfromthePRIMARYtoSECONDARYhostthat

readsthelogsina"recovery"mode.ForallintentsandpurposesthehostingestingtheWALcannotbereadi.e.it'soff-line.

• HotStandbyo OperatesintheexactsamefashionastheWarmStandbyServerexceptthat,

inaddition,itoffersaread-onlyenvironmentforclientconnectionstoconnectandquery.

• PointInTimeRecovery(PITR)o Primarilyusedfordatabaseforensicsandrecoveryatparticularpointsin

timesuchasinthecasethatimportantdatamayhavebeenaccidentallyremoved.Onecanrestoretheclustertoapointintimebeforetheeventoccurred.

• StreamingReplicationo Usesanexplicitconnection,whichinamannerofspeakingissimilartothe

standardclientconnection,betweenthePRIMARYandSTANDBYhost.Ittooreadsthetransactionlogsandingestsintoaread-onlyserver.What'sdifferentisthattheconnectionusesaspecialreplicationprotocolwhichisfasterandmoreefficientthanlogshipping.Similartostandardclientconnections,italsohonorsthesameauthenticationrulesasexpressedinthePostgreSQLhost-basedauthenticationfile,pg_hba.conf.

159|P a g e

7.1 Ensure a replication-only user is created and used for streaming replication (Not Scored)



Description:

Createanewuserspecificallyforusebystreamingreplicationinsteadofusingthesuperuseraccount.

Rationale:

Asitisnotnecessarytobeasuperusertoinitiateareplicationconnection,itispropertocreateanaccountspecificallyforreplication.Thisallowsfurther'lockingdown'theusesofthesuperuseraccountandfollowsthegeneralprincipleofusingtheleastprivilegesnecessary.

Audit:

Checkwhichuserscurrentlyhavethereplicationpermission:

postgres=# select rolname from pg_roles where rolreplication is true; rolname ---------- postgres (1 row)

InadefaultPostgreSQLcluster,onlythepostgresuserwillhavethispermission.

Remediation:

Itwillbenecessarytocreateanewroleforreplicationpurposes:

postgres=# create user replication_user REPLICATION encrypted password 'XXX'; CREATE ROLE postgres=# select rolname from pg_roles where rolreplication is true; rolname ------------------ postgres replication_user (2 rows)

160|P a g e

Whenusingpg_basebackup(orotherreplicationtools)andwhenconfiguringrecovery.confonyourstandbyserver,youwouldusethereplication_user(anditspassword).

Ensureyouallowthenewuserviayourpg_hba.conffile:

# note that 'replication' in the 2nd column is required and is a special # keyword, not a real database hostssl replication replication_user 0.0.0.0/0 md5

References:

1. https://www.postgresql.org/docs/11/static/app-pgbasebackup.html2. https://www.postgresql.org/docs/11/static/standby-settings.html

CISControls:

Version6


Version7


161|P a g e

7.2 Ensure base backups are configured and functional (Not Scored)



Description:

A'basebackup'isacopyofthePRIMARYhost'sdatacluster($PGDATA)andisusedtocreateSTANDBYhostsandforPointInTimeRecovery(PITR)mechanisms.Basebackupsshouldbecopiedacrossnetworksinasecuremannerusinganencryptedtransportmechanism.ThePostgreSQLCLIpg_basebackupcanbeused,however,SSLencryptionshouldbeenabledontheserveraspersection6.8ofthisbenchmark.ThepgBackResttooldetailedinsection8.3ofthisbenchmarkcanalsobeusedtocreatea'basebackup'.

Remediation:

Executingbasebackupsusingpg_basebackuprequiresthefollowingstepsonthestandbyserver:

$ whoami postgres $ pg_basebackup -h name_or_IP_of_master \ -p 5432 \ -U replication_user \ -D ~postgres/11/data \ -P -v -R -Xs \

References:

1. https://www.postgresql.org/docs/11/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE

2. https://www.postgresql.org/docs/11/static/app-pgbasebackup.html

CISControls:

Version6

10.2TestBackupsRegularly Testdataonbackupmediaonaregularbasisbyperformingadatarestorationprocesstoensurethatthebackupisproperlyworking.

162|P a g e

Version7

10.3TestDataonBackupMedia Testdataintegrityonbackupmediaonaregularbasisbyperformingadatarestorationprocesstoensurethatthebackupisproperlyworking.

163|P a g e

7.3 Ensure WAL archiving is configured and functional (Scored)



Description:

WriteAheadLog(WAL)Archiving,orLogShipping,istheprocessofsendingtransactionlogfilesfromthePRIMARYhosteithertooneormoreSTANDBYhostsortobearchivedonaremotestoragedeviceforlateruse,e.g.PITR.ThereareseveralutilitiesthatcancopyWALsincluding,butnotlimitedto,cp,scp,sftp,andrynsc.Basically,theserverfollowsasetofruntimeparameterswhichdefineswhentheWALshouldbecopiedusingoneoftheaforementionedutilities.

Rationale:

Unlesstheserverhasbeencorrectlyconfigured,onerunstheriskofsendingWALsinanunsecured,unencryptedfashion.

Audit:

Reviewthefollowingruntimeparametersinpostgresql.conf.ThefollowingexampledemonstratesrsyncbutrequiresthatSSHasatransportmediumbeenabledonthesourcehost:

archive_mode = on archive_command = 'rsync -e ssh -a %p postgres@remotehost:/var/lib/pgsql/WAL/%f'

ConfirmSSHpublic/privatekeyshavebeengeneratedonboththesourceandtargethostsintheirrespectivesuperuserhomeaccounts.

Remediation:

Changeparametersandrestarttheserverasrequired.Note:SSHpublickeysmustbegeneratedandinstalledasperindustrystandards.

References:

1. https://www.postgresql.org/docs/11/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING

2. https://linux.die.net/man/1/ssh-keygen3. https://linux.die.net/man/1/rsync

164|P a g e

CISControls:

Version6


Version7


165|P a g e

7.4 Ensure streaming replication parameters are configured correctly (Not Scored)



Description:

StreamingreplicationfromaPRIMARYhosttransmitsDDL,DML,passwords,andotherpotentiallysensitiveactivitiesanddata.TheseconnectionsshouldbeprotectedwithSecureSocketsLayer(SSL).

Rationale:

Unencryptedtransmissionscouldrevealsensitiveinformationtounauthorizedparties.Unauthenticatedconnectionscouldenableman-in-the-middleattacks.

Audit:

Confirmadedicatedandnon-superuserrolewithreplicationpermissionexists:

postgres=> select rolname from pg_roles where rolreplication is true; rolname ------------------ postgres replication_user (2 rows)

Onthetarget/STANDBYhost,executeapsqlinvocationsimilartothefollowing,confirmingthatSSLcommunicationsarepossible:

$ whoami postgres $ psql 'host=mySrcHost dbname=postgres user=replication_user password=mypassword sslmode=require' -c 'select 1;'

Remediation:

ReviewpriorsectionsinthisbenchmarkregardingSSLcertificates,replicationuser,andWALarchiving.

Confirmthefilerecovery.confispresentontheSTANDBYhostandcontainslinessimilartothefollowing:

166|P a g e

standby_mode=on primary_conninfo = 'user=replication_user password=mypassword host=mySrcHost port=5432 sslmode=require sslcompression=1'

References:

1. https://www.postgresql.org/docs/11/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SECURITY

2. https://www.postgresql.org/docs/11/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE

3. https://www.postgresql.org/docs/11/static/app-pgbasebackup.html4. https://www.postgresql.org/docs/11/static/runtime-config-wal.html#RUNTIME-

CONFIG-WAL-ARCHIVING5. https://linux.die.net/man/1/openssl

CISControls:

Version6


Version7


167|P a g e

8 Special Configuration Considerations

Therecommendationsproposedherearetotryandaddresssomeofthelesscomeusecaseswhichmaywarrantadditionalconfigurationguidance/consideration.

8.1 Ensure PostgreSQL configuration files are outside the data cluster (Not Scored)



Description:

PostgreSQLconfigurationfileswithinthedatacluster'sdirectorytreecanbechangedbyanyoneloggingintothedataclusterasthesuperuser,i.e.postgres.Asamatterofdefaultpolicy,configurationfilessuchaspostgresql.conf,pg_hba.conf,andpg_ident,areplacedinthedatacluster'sdirectory,$PGDATA.PostgreSQLcanbeconfiguredtorelocatethesefilestolocationsoutsidethedataclusterwhichcannotthenbeaccessedbyanordinarysuperuserloginsession.

Considerationshouldalsobegivento"includedirectives";theseareclustersubdirectorieswhereonecanlocatefilescontainingadditionalconfigurationparameters.Includedirectivesaremeanttoaddmoreflexibilityforuniqueinstallsorlargenetworkenvironmentswhilemaintainingorderandconsistentarchitecturaldesign.

Rationale:

LeavingPostgreSQLconfigurationfileswithinthedatacluster'sdirectorytreeincreasesthechangesthattheywillbeinadvertentlyorintentionallyaltered.

Audit:

Executethefollowingcommandstoverifytheconfigurationiscorrect:

postgres=# select name, setting from pg_settings where name ~ '.*_file$'; name | setting --------------------+---------------------------------------- config_file | /var/lib/pgsql/11/data/postgresql.conf external_pid_file | hba_file | /var/lib/pgsql/11/data/pg_hba.conf ident_file | /var/lib/pgsql/11/data/pg_ident.conf ssl_ca_file | ssl_cert_file | server.crt

168|P a g e

ssl_crl_file | ssl_dh_params_file | ssl_key_file | server.key (9 rows)

Executethefollowingcommandtoseeanyactiveincludesettings:

$ grep ^include $PGDATA/postgresql.{auto.,}conf

Inspectthefiledirectoriesandpermissionsforallreturnedvalues.Onlysuperusersandauthorizedusersshouldhaveaccesscontrolrightsforthesefiles.Ifpermissionsarenothighlyrestricted,thisisafail.

Remediation:

Followthesestepstoremediatetheconfigurationfilelocationsandpermissions:

• Determineappropriatelocationsforrelocatableconfigurationfilesbasedonyourorganization'ssecuritypolicies.Ifnecessary,relocateand/orrenameconfigurationfilesoutsideofthedatacluster.

• Ensuretheirfilepermissionsarerestrictedasmuchaspossible,i.e.onlysuperuserreadaccess.

• Changethesettingsaccordinglyinthepostgresql.confconfigurationfile.• Restartthedatabaseclusterforthechangestotakeeffect.

DefaultValue:

ThedefaultsforPostgreSQLconfigurationfilesarelistedbelow.

name | setting --------------------+---------------------------------------- config_file | /var/lib/pgsql/11/data/postgresql.conf external_pid_file | hba_file | /var/lib/pgsql/11/data/pg_hba.conf ident_file | /var/lib/pgsql/11/data/pg_ident.conf ssl_ca_file | ssl_cert_file | server.crt ssl_crl_file | ssl_dh_params_file | ssl_key_file | server.key

References:

1. https://www.postgresql.org/docs/11/static/runtime-config-file-locations.html2. https://www.postgresql.org/docs/11/static/runtime-config-connection.html3. https://www.postgresql.org/docs/11/static/config-setting.html#CONFIG-

INCLUDES

169|P a g e

CISControls:

Version6


Version7


170|P a g e

8.2 Ensure PostgreSQL subdirectory locations are outside the data cluster (Not Scored)



Description:

ThePostgreSQLclusterisorganizedtocarryoutspecifictasksinsubdirectories.Forthepurposesofperformance,reliability,andsecuritythesesubdirectoriesshouldberelocatedoutsidethedatacluster.

Rationale:

Somesubdirectoriescontaininformation,suchaslogs,whichcanbeofvaluetootherssuchasdevelopers.Othersubdirectoriescangainaperformancebenefitwhenplacedonfaststoragedevices.Finally,relocatingasubdirectorytoaseparateanddistinctpartitionmitigatesdenialofserviceandinvoluntaryservershutdownwhenexcessivewritesfillthedatacluster'spartition,e.g.pg_xlogandpg_log.

Audit:

ExecutethefollowingSQLstatementtoverifytheconfigurationiscorrect.Alternatively,inspecttheparametersettingsinthepostgresql.confconfigurationfile.

postgres=# select name, setting from pg_settings where (name ~ '_directory$' or name ~ '_tablespace'); name | setting ----------------------+------------------------- data_directory | /var/lib/pgsql/11/data default_tablespace | log_directory | ../log stats_temp_directory | pg_stat_tmp temp_tablespaces | (5 rows)

Inspectthefileanddirectorypermissionsforallreturnedvalues.Onlysuperusersandauthorizedusersshouldhaveaccesscontrolrightsforthesefilesanddirectories.Ifpermissionsarenothighlyrestrictive,thisisafail.

Remediation:

Performthefollowingstepstoremediatethesubdirectorylocationsandpermissions:

171|P a g e

• Determineappropriatedata,log,andtablespacedirectoriesandlocationsbasedonyourorganization'ssecuritypolicies.Ifnecessary,relocatealllisteddirectoriesoutsidethedatacluster.

• Ensurefilepermissionsarerestrictedasmuchaspossible,i.e.onlysuperuserreadaccess.

• Whendirectoriesarerelocatedtootherpartitions,ensurethattheyareofsufficientsizetomitigateagainstexcessivespaceutilization.

• Lastly,changethesettingsaccordinglyinthepostgresql.confconfigurationfileandrestartthedatabaseclusterforchangestotakeeffect.

DefaultValue:

Thedefaultfordata_directoryisConfigDirandthedefaultforlog_directoryislog(basedonabsolutepathofdata_directory).Thedefaultsfortablespacesettingsarenull,ornotset,uponclustercreation.

References:

1. https://www.postgresql.org/docs/10/static/runtime-config-file-locations.html

CISControls:

Version6


Version7


172|P a g e

8.3 Ensure the backup and restore tool, 'pgBackRest', is installed and configured (Not Scored)



Description:

pgBackRestaimstobeasimple,reliablebackupandrestoresystemthatcanseamlesslyscaleuptothelargestdatabasesandworkloads.Insteadofrelyingontraditionalbackuptoolsliketarandrsync,pgBackRestimplementsallbackupfeaturesinternallyandusesacustomprotocolforcommunicatingwithremotesystems.Removingrelianceontarandrsyncallowsforbettersolutionstodatabase-specificbackupchallenges.Thecustomremoteprotocolallowsformoreflexibilityandlimitsthetypesofconnectionsthatarerequiredtoperformabackupwhichincreasessecurity.

Rationale:

ThenativePostgreSQLbackupfacilitypg_dumpprovidesadequatelogicalbackupoperationsbutdoesnotprovideforPointInTimeRecovery(PITR).ThePostgreSQLfacilitypg_basebackupperformsphysicalbackupofthedatabasefilesanddoesprovideforPITR,butitisconstrainedbysinglethreading.BothofthesemethodologiesarestandardinthePostgreSQLecosystemandappropriateforparticularbackup/recoveryneeds.pgBackRestoffersanotheroptionwithmuchmorerobustfeaturesandflexibility.

pgBackRestisopensourcesoftwaredevelopedtoperformefficientbackupsonPostgreSQLdatabasesthatmeasureintensofterabytesandgreater.Itsupportsperfilechecksums,compression,partial/failedbackupresume,high-performanceparalleltransfer,asynchronousarchiving,tablespaces,expiration,full/differential/incremental,local/remoteoperationviaSSH,hard-linking,restore,backupencryption,andmore.pgBackRestiswritteninCandPerlanddoesnotdependonrsyncortarbutinsteadperformsitsowndeltaswhichgivesitmaximumflexibility.Finally,pgBackRestprovidesaneasytouseinternalrepositorylistingbackupdetailsaccessibleviathepgbackrest infocommand,asillustratedbelow.

$ pgbackrest info stanza: proddb01 status: ok db (current) wal archive min/max (11.3-1): 000000010000000000000012 / 000000010000000000000017

173|P a g e

full backup: 20190603-153106F timestamp start/stop: 2019-06-03 15:31:06 / 2019-06-03 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: 20190603-153106F_20181002-173109D timestamp start/stop: 2019-06-03 17:31:09 / 2019-06-03 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: 20190603-153106F incr backup: 20190603-153106F_20181002-183114I timestamp start/stop: 2019-06-03 18:31:14 / 2019-06-03 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: 20190603-153106F, 20190603-153106F_20190603-173109D

Audit:

Ifinstalled,invokeitwithoutargumentstoseethehelp:

$ # not installed # pgbackrest -bash: pgbackrest: command not found $ # instlled $ pgbackrest pgBackRest 2.14 - General help Usage: pgbackrest [options] [command] Commands: archive-get Get a WAL segment from the archive. archive-push Push a WAL segment to the archive. backup Backup a database cluster. check Check the configuration. expire Expire backups that exceed retention. help Get help. info Retrieve information about backups. restore Restore a database cluster. stanza-create Create the required stanza data. stanza-delete Delete a stanza. stanza-upgrade Upgrade a stanza. start Allow pgBackRest processes to run. stop Stop pgBackRest processes from running. version Get version. Use 'pgbackrest help [command]' for more information.

174|P a g e

Remediation:

pgBackRestisnotinstallednorconfiguredforPostgreSQLbydefault,butinsteadismaintainedasaGitHubproject.Fortunately,itisapartofthePGDGrepositoryandcanbeeasilyinstalled:

$ whoami root $ yum -y install pgbackrest Loaded plugins: fastestmirror, priorities Loading mirror speeds from cached hostfile epel/x86_64/metalink | 18 kB 00:00:00 * base: mirror.cisp.com * centos-sclo-rh: mirror.cc.columbia.edu * centos-sclo-sclo: mirror.cisp.com * epel: mirror.umd.edu * extras: mirror.cisp.com * updates: mirror.cisp.com base | 3.6 kB 00:00:00 centos-sclo-rh | 3.0 kB 00:00:00 centos-sclo-sclo | 2.9 kB 00:00:00 epel | 5.3 kB 00:00:00 extras | 3.4 kB 00:00:00 pgdg10 | 3.6 kB 00:00:00 pgdg11 | 3.6 kB 00:00:00 pgdg94 | 3.6 kB 00:00:00 pgdg95 | 3.6 kB 00:00:00 pgdg96 | 3.6 kB 00:00:00 updates | 3.4 kB 00:00:00 (1/7): epel/x86_64/updateinfo | 977 kB 00:00:00 (2/7): pgdg11/7/x86_64/primary_db | 185 kB 00:00:00 (3/7): pgdg96/7/x86_64/primary_db | 248 kB 00:00:00 (4/7): pgdg94/7/x86_64/primary_db | 258 kB 00:00:00 (5/7): pgdg10/7/x86_64/primary_db | 230 kB 00:00:01 (6/7): pgdg95/7/x86_64/primary_db | 247 kB 00:00:01 (7/7): epel/x86_64/primary_db | 6.7 MB 00:00:02 1467 packages excluded due to repository priority protections Resolving Dependencies --> Running transaction check ---> Package pgbackrest.x86_64 0:2.14-1.rhel7 will be installed --> Processing Dependency: perl-XML-LibXML for package: pgbackrest-2.14-1.rhel7.x86_64 --> Processing Dependency: perl-JSON-PP for package: pgbackrest-2.14-1.rhel7.x86_64 --> Processing Dependency: perl-IO-Socket-SSL for package: pgbackrest-2.14-1.rhel7.x86_64 --> Processing Dependency: perl-Digest-SHA for package: pgbackrest-2.14-1.rhel7.x86_64 --> Processing Dependency: perl-DBD-Pg for package: pgbackrest-2.14-1.rhel7.x86_64 --> Running transaction check ---> Package perl-DBD-Pg.x86_64 0:2.19.3-4.el7 will be installed --> Processing Dependency: perl(DBI) >= 1.52 for package: perl-DBD-Pg-2.19.3-4.el7.x86_64 --> Processing Dependency: perl(version) for package: perl-DBD-Pg-2.19.3-

175|P a g e

4.el7.x86_64 ---> Package perl-Digest-SHA.x86_64 1:5.85-4.el7 will be installed --> Processing Dependency: perl(Digest::base) for package: 1:perl-Digest-SHA-5.85-4.el7.x86_64 ---> Package perl-IO-Socket-SSL.noarch 0:1.94-7.el7 will be installed --> Processing Dependency: perl-Net-SSLeay >= 1.55-5 for package: perl-IO-Socket-SSL-1.94-7.el7.noarch --> Processing Dependency: perl(Net::SSLeay) >= 1.21 for package: perl-IO-Socket-SSL-1.94-7.el7.noarch --> Processing Dependency: perl(IO::Socket::IP) >= 0.20 for package: perl-IO-Socket-SSL-1.94-7.el7.noarch --> Processing Dependency: perl(Net::SSLeay) for package: perl-IO-Socket-SSL-1.94-7.el7.noarch --> Processing Dependency: perl(Net::LibIDN) for package: perl-IO-Socket-SSL-1.94-7.el7.noarch --> Processing Dependency: perl(Mozilla::CA) for package: perl-IO-Socket-SSL-1.94-7.el7.noarch ---> Package perl-JSON-PP.noarch 0:2.27202-2.el7 will be installed --> Processing Dependency: perl(Data::Dumper) for package: perl-JSON-PP-2.27202-2.el7.noarch ---> Package perl-XML-LibXML.x86_64 1:2.0018-5.el7 will be installed --> Processing Dependency: perl(XML::SAX::Exception) for package: 1:perl-XML-LibXML-2.0018-5.el7.x86_64 --> Processing Dependency: perl(XML::SAX::DocumentLocator) for package: 1:perl-XML-LibXML-2.0018-5.el7.x86_64 --> Processing Dependency: perl(XML::SAX::Base) for package: 1:perl-XML-LibXML-2.0018-5.el7.x86_64 --> Processing Dependency: perl(XML::NamespaceSupport) for package: 1:perl-XML-LibXML-2.0018-5.el7.x86_64 --> Running transaction check ---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed --> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64 --> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64 ---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed ---> Package perl-Digest.noarch 0:1.17-245.el7 will be installed ---> Package perl-IO-Socket-IP.noarch 0:0.21-5.el7 will be installed ---> Package perl-Mozilla-CA.noarch 0:20130114-5.el7 will be installed ---> Package perl-Net-LibIDN.x86_64 0:0.12-15.el7 will be installed ---> Package perl-Net-SSLeay.x86_64 0:1.55-6.el7 will be installed ---> Package perl-XML-NamespaceSupport.noarch 0:1.11-10.el7 will be installed ---> Package perl-XML-SAX.noarch 0:0.99-9.el7 will be installed --> Processing Dependency: perl(LWP::UserAgent) for package: perl-XML-SAX-0.99-9.el7.noarch ---> Package perl-XML-SAX-Base.noarch 0:1.08-7.el7 will be installed ---> Package perl-version.x86_64 3:0.99.07-3.el7 will be installed --> Running transaction check ---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed --> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch

176|P a g e

---> Package perl-libwww-perl.noarch 0:6.05-2.el7 will be installed --> Processing Dependency: perl(WWW::RobotRules) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(URI) >= 1.10 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(Net::HTTP) >= 6.04 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(LWP::MediaTypes) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Status) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Response) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Request::Common) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Request) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Negotiate) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Date) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Daemon) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTTP::Cookies) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(File::Listing) >= 6 for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(URI::Heuristic) for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(URI::Escape) for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTML::HeadParser) for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(HTML::Entities) for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(Encode::Locale) for package: perl-libwww-perl-6.05-2.el7.noarch --> Processing Dependency: perl(Digest::MD5) for package: perl-libwww-perl-6.05-2.el7.noarch --> Running transaction check ---> Package perl-Digest-MD5.x86_64 0:2.52-3.el7 will be installed ---> Package perl-Encode-Locale.noarch 0:1.03-5.el7 will be installed ---> Package perl-File-Listing.noarch 0:6.04-7.el7 will be installed ---> Package perl-HTML-Parser.x86_64 0:3.71-4.el7 will be installed --> Processing Dependency: perl(HTML::Tagset) >= 3 for package: perl-HTML-Parser-3.71-4.el7.x86_64 ---> Package perl-HTTP-Cookies.noarch 0:6.01-5.el7 will be installed ---> Package perl-HTTP-Daemon.noarch 0:6.01-8.el7 will be installed ---> Package perl-HTTP-Date.noarch 0:6.02-8.el7 will be installed --> Processing Dependency: perl(Time::Zone) for package: perl-HTTP-Date-6.02-8.el7.noarch ---> Package perl-HTTP-Message.noarch 0:6.06-6.el7 will be installed --> Processing Dependency: perl(IO::HTML) for package: perl-HTTP-Message-6.06-6.el7.noarch --> Processing Dependency: perl(Compress::Raw::Zlib) for package: perl-HTTP-Message-6.06-6.el7.noarch ---> Package perl-HTTP-Negotiate.noarch 0:6.01-5.el7 will be installed

177|P a g e

---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed --> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch ---> Package perl-LWP-MediaTypes.noarch 0:6.02-2.el7 will be installed --> Processing Dependency: mailcap for package: perl-LWP-MediaTypes-6.02-2.el7.noarch ---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed ---> Package perl-Net-HTTP.noarch 0:6.06-2.el7 will be installed ---> Package perl-URI.noarch 0:1.60-9.el7 will be installed --> Processing Dependency: perl(Business::ISBN) for package: perl-URI-1.60-9.el7.noarch ---> Package perl-WWW-RobotRules.noarch 0:6.02-5.el7 will be installed --> Running transaction check ---> Package mailcap.noarch 0:2.1.41-2.el7 will be installed ---> Package perl-Business-ISBN.noarch 0:2.06-2.el7 will be installed --> Processing Dependency: perl(Business::ISBN::Data) >= 20120719.001 for package: perl-Business-ISBN-2.06-2.el7.noarch ---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed ---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed ---> Package perl-HTML-Tagset.noarch 0:3.20-15.el7 will be installed ---> Package perl-IO-HTML.noarch 0:1.00-2.el7 will be installed ---> Package perl-TimeDate.noarch 1:2.30-2.el7 will be installed --> Running transaction check ---> Package perl-Business-ISBN-Data.noarch 0:20120719.001-2.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================= Package Arch Version Repository Size ============================================================================= Installing: pgbackrest x86_64 2.14-1.rhel7 pgdg11 275 k Installing for dependencies: mailcap noarch 2.1.41-2.el7 base 31 k perl-Business-ISBN noarch 2.06-2.el7 base 25 k perl-Business-ISBN-Data noarch 20120719.001-2.el7 base 24 k perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k perl-DBD-Pg x86_64 2.19.3-4.el7 base 195 k perl-DBI x86_64 1.627-4.el7 base 802 k perl-Data-Dumper x86_64 2.145-3.el7 base 47 k perl-Digest noarch 1.17-245.el7 base 23 k perl-Digest-MD5 x86_64 2.52-3.el7 base 30 k perl-Digest-SHA x86_64 1:5.85-4.el7 base 58 k perl-Encode-Locale noarch 1.03-5.el7 base 16 k perl-File-Listing noarch 6.04-7.el7 base 13 k perl-HTML-Parser x86_64 3.71-4.el7 base 115 k perl-HTML-Tagset noarch 3.20-15.el7 base 18 k perl-HTTP-Cookies noarch 6.01-5.el7 base 26 k perl-HTTP-Daemon noarch 6.01-8.el7 base 21 k perl-HTTP-Date noarch 6.02-8.el7 base 14 k perl-HTTP-Message noarch 6.06-6.el7 base 82 k perl-HTTP-Negotiate noarch 6.01-5.el7 base 17 k perl-IO-Compress noarch 2.061-2.el7 base 260 k perl-IO-HTML noarch 1.00-2.el7 base 23 k

178|P a g e

perl-IO-Socket-IP noarch 0.21-5.el7 base 36 k perl-IO-Socket-SSL noarch 1.94-7.el7 base 115 k perl-JSON-PP noarch 2.27202-2.el7 base 55 k perl-LWP-MediaTypes noarch 6.02-2.el7 base 24 k perl-Mozilla-CA noarch 20130114-5.el7 base 11 k perl-Net-Daemon noarch 0.48-5.el7 base 51 k perl-Net-HTTP noarch 6.06-2.el7 base 29 k perl-Net-LibIDN x86_64 0.12-15.el7 base 28 k perl-Net-SSLeay x86_64 1.55-6.el7 base 285 k perl-PlRPC noarch 0.2020-14.el7 base 36 k perl-TimeDate noarch 1:2.30-2.el7 base 52 k perl-URI noarch 1.60-9.el7 base 106 k perl-WWW-RobotRules noarch 6.02-5.el7 base 18 k perl-XML-LibXML x86_64 1:2.0018-5.el7 base 373 k perl-XML-NamespaceSupport noarch 1.11-10.el7 base 18 k perl-XML-SAX noarch 0.99-9.el7 base 63 k perl-XML-SAX-Base noarch 1.08-7.el7 base 32 k perl-libwww-perl noarch 6.05-2.el7 base 205 k perl-version x86_64 3:0.99.07-3.el7 base 84 k Transaction Summary ============================================================================= Install 1 Package (+41 Dependent packages) Total download size: 3.7 M Installed size: 9.5 M Downloading packages: : perl-Net-LibIDN-0.12-15.el7.x86_64.rpm | 28 kB 00:00:00

179|P a g e

(30/42): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00 (31/42): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:00 (32/42): perl-URI-1.60-9.el7.noarch.rpm | 106 kB 00:00:00 (33/42): perl-XML-NamespaceSupport-1.11-10.el7.noarch.rpm | 18 kB 00:00:00 (34/42): perl-WWW-RobotRules-6.02-5.el7.noarch.rpm | 18 kB 00:00:00 (35/42): perl-XML-SAX-0.99-9.el7.noarch.rpm | 63 kB 00:00:00 (36/42): perl-TimeDate-2.30-2.el7.noarch.rpm | 52 kB 00:00:00 (37/42): mailcap-2.1.41-2.el7.noarch.rpm | 31 kB 00:00:01 (38/42): perl-XML-SAX-Base-1.08-7.el7.noarch.rpm | 32 kB 00:00:00 (39/42): perl-version-0.99.07-3.el7.x86_64.rpm | 84 kB 00:00:00 (40/42): perl-libwww-perl-6.05-2.el7.noarch.rpm | 205 kB 00:00:00 (41/42): perl-XML-LibXML-2.0018-5.el7.x86_64.rpm | 373 kB 00:00:00 (42/42): pgbackrest-2.14-1.rhel7.x86_64.rpm | 275 kB 00:00:01 ----------------------------------------------------------------------------- Total 1.3 MB/s | 3.7 MB 00:00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : perl-Data-Dumper-2.145-3.el7.x86_64 1/42 Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 2/42 Installing : perl-Digest-1.17-245.el7.noarch 3/42 Installing : perl-XML-SAX-Base-1.08-7.el7.noarch 4/42 Installing : perl-IO-Socket-IP-0.21-5.el7.noarch 5/42 Installing : perl-Encode-Locale-1.03-5.el7.noarch 6/42 Installing : perl-XML-NamespaceSupport-1.11-10.el7.noarch 7/42 Installing : perl-Digest-MD5-2.52-3.el7.x86_64 8/42 Installing : 1:perl-Digest-SHA-5.85-4.el7.x86_64 9/42 Installing : perl-JSON-PP-2.27202-2.el7.noarch 10/42 Installing : perl-HTML-Tagset-3.20-15.el7.noarch 11/42 Installing : perl-IO-HTML-1.00-2.el7.noarch 12/42 Installing : 1:perl-TimeDate-2.30-2.el7.noarch 13/42 Installing : perl-HTTP-Date-6.02-8.el7.noarch 14/42 Installing : perl-File-Listing-6.04-7.el7.noarch 15/42 Installing : perl-Business-ISBN-Data-20120719.001-2.el7.noarch 16/42 Installing : perl-Business-ISBN-2.06-2.el7.noarch 17/42 Installing : perl-URI-1.60-9.el7.noarch 18/42 Installing : perl-WWW-RobotRules-6.02-5.el7.noarch 19/42 Installing : perl-Net-SSLeay-1.55-6.el7.x86_64 20/42 Installing : 3:perl-version-0.99.07-3.el7.x86_64 21/42 Installing : perl-Mozilla-CA-20130114-5.el7.noarch 22/42 Installing : perl-Net-Daemon-0.48-5.el7.noarch 23/42 Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 24/42 Installing : perl-IO-Compress-2.061-2.el7.noarch 25/42 Installing : perl-PlRPC-0.2020-14.el7.noarch 26/42 Installing : perl-DBI-1.627-4.el7.x86_64 27/42 Installing : perl-DBD-Pg-2.19.3-4.el7.x86_64 28/42 Installing : perl-Net-LibIDN-0.12-15.el7.x86_64 29/42 Installing : perl-IO-Socket-SSL-1.94-7.el7.noarch 30/42 Installing : perl-Net-HTTP-6.06-2.el7.noarch 31/42 Installing : mailcap-2.1.41-2.el7.noarch 32/42 Installing : perl-LWP-MediaTypes-6.02-2.el7.noarch 33/42 Installing : perl-HTTP-Message-6.06-6.el7.noarch 34/42 Installing : perl-HTTP-Cookies-6.01-5.el7.noarch 35/42 Installing : perl-HTML-Parser-3.71-4.el7.x86_64 36/42 Installing : perl-HTTP-Negotiate-6.01-5.el7.noarch 37/42 Installing : perl-HTTP-Daemon-6.01-8.el7.noarch 38/42

180|P a g e

Installing : perl-libwww-perl-6.05-2.el7.noarch 39/42 Installing : perl-XML-SAX-0.99-9.el7.noarch 40/42 Installing : 1:perl-XML-LibXML-2.0018-5.el7.x86_64 41/42 Installing : pgbackrest-2.14-1.rhel7.x86_64 42/42 Verifying : perl-IO-Compress-2.061-2.el7.noarch 1/42 Verifying : perl-Business-ISBN-2.06-2.el7.noarch 2/42 Verifying : mailcap-2.1.41-2.el7.noarch 3/42 Verifying : perl-HTTP-Cookies-6.01-5.el7.noarch 4/42 Verifying : perl-JSON-PP-2.27202-2.el7.noarch 5/42 Verifying : perl-Net-LibIDN-0.12-15.el7.x86_64 6/42 Verifying : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 7/42 Verifying : perl-HTML-Parser-3.71-4.el7.x86_64 8/42 Verifying : perl-Net-Daemon-0.48-5.el7.noarch 9/42 Verifying : perl-HTTP-Message-6.06-6.el7.noarch 10/42 Verifying : perl-Digest-MD5-2.52-3.el7.x86_64 11/42 Verifying : perl-HTTP-Date-6.02-8.el7.noarch 12/42 Verifying : perl-WWW-RobotRules-6.02-5.el7.noarch 13/42 Verifying : perl-XML-NamespaceSupport-1.11-10.el7.noarch 14/42 Verifying : perl-IO-Socket-SSL-1.94-7.el7.noarch 15/42 Verifying : perl-Mozilla-CA-20130114-5.el7.noarch 16/42 Verifying : 3:perl-version-0.99.07-3.el7.x86_64 17/42 Verifying : perl-DBD-Pg-2.19.3-4.el7.x86_64 18/42 Verifying : perl-Encode-Locale-1.03-5.el7.noarch 19/42 Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 20/42 Verifying : 1:perl-XML-LibXML-2.0018-5.el7.x86_64 21/42 Verifying : perl-IO-Socket-IP-0.21-5.el7.noarch 22/42 Verifying : perl-Net-SSLeay-1.55-6.el7.x86_64 23/42 Verifying : perl-HTTP-Negotiate-6.01-5.el7.noarch 24/42 Verifying : perl-Business-ISBN-Data-20120719.001-2.el7.noarch 25/42 Verifying : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 26/42 Verifying : perl-libwww-perl-6.05-2.el7.noarch 27/42 Verifying : perl-HTTP-Daemon-6.01-8.el7.noarch 28/42 Verifying : perl-LWP-MediaTypes-6.02-2.el7.noarch 29/42 Verifying : perl-XML-SAX-0.99-9.el7.noarch 30/42 Verifying : perl-PlRPC-0.2020-14.el7.noarch 31/42 Verifying : perl-DBI-1.627-4.el7.x86_64 32/42 Verifying : perl-Net-HTTP-6.06-2.el7.noarch 33/42 Verifying : 1:perl-TimeDate-2.30-2.el7.noarch 34/42 Verifying : 1:perl-Digest-SHA-5.85-4.el7.x86_64 35/42 Verifying : perl-IO-HTML-1.00-2.el7.noarch 36/42 Verifying : perl-XML-SAX-Base-1.08-7.el7.noarch 37/42 Verifying : perl-Digest-1.17-245.el7.noarch 38/42 Verifying : perl-HTML-Tagset-3.20-15.el7.noarch 39/42 Verifying : pgbackrest-2.14-1.rhel7.x86_64 40/42 Verifying : perl-URI-1.60-9.el7.noarch 41/42 Verifying : perl-File-Listing-6.04-7.el7.noarch 42/42 Installed: pgbackrest.x86_64 0:2.14-1.rhel7 Dependency Installed: mailcap.noarch 0:2.1.41-2.el7 perl-Business-ISBN.noarch 0:2.06-2.el7 perl-Business-ISBN-Data.noarch 0:20120719.001-2.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-Pg.x86_64 0:2.19.3-4.el7

181|P a g e

perl-DBI.x86_64 0:1.627-4.el7 perl-Data-Dumper.x86_64 0:2.145-3.el7 perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 perl-Digest-SHA.x86_64 1:5.85-4.el7 perl-Encode-Locale.noarch 0:1.03-5.el7 perl-File-Listing.noarch 0:6.04-7.el7 perl-HTML-Parser.x86_64 0:3.71-4.el7 perl-HTML-Tagset.noarch 0:3.20-15.el7 perl-HTTP-Cookies.noarch 0:6.01-5.el7 perl-HTTP-Daemon.noarch 0:6.01-8.el7 perl-HTTP-Date.noarch 0:6.02-8.el7 perl-HTTP-Message.noarch 0:6.06-6.el7 perl-HTTP-Negotiate.noarch 0:6.01-5.el7 perl-IO-Compress.noarch 0:2.061-2.el7 perl-IO-HTML.noarch 0:1.00-2.el7 perl-IO-Socket-IP.noarch 0:0.21-5.el7 perl-IO-Socket-SSL.noarch 0:1.94-7.el7 perl-JSON-PP.noarch 0:2.27202-2.el7 perl-LWP-MediaTypes.noarch 0:6.02-2.el7 perl-Mozilla-CA.noarch 0:20130114-5.el7 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-Net-HTTP.noarch 0:6.06-2.el7 perl-Net-LibIDN.x86_64 0:0.12-15.el7 perl-Net-SSLeay.x86_64 0:1.55-6.el7 perl-PlRPC.noarch 0:0.2020-14.el7 perl-TimeDate.noarch 1:2.30-2.el7 perl-URI.noarch 0:1.60-9.el7 perl-WWW-RobotRules.noarch 0:6.02-5.el7 perl-XML-LibXML.x86_64 1:2.0018-5.el7 perl-XML-NamespaceSupport.noarch 0:1.11-10.el7 perl-XML-SAX.noarch 0:0.99-9.el7 perl-XML-SAX-Base.noarch 0:1.08-7.el7 perl-libwww-perl.noarch 0:6.05-2.el7 perl-version.x86_64 3:0.99.07-3.el7 Complete!

Onceinstalled,pgBackRestmustbeconfiguredforthingslikestanzaname,backuplocation,retentionpolicy,logging,etc.Pleaseconsulttheconfigurationguide.IfemployingpgBackRestforyourbackup/recoverysolution,ensuretherepository,basebackups,andWALarchivesarestoredonareliablefilesystemseparatefromthedatabaseserver.Further,theexternalstoragesystemwherebackupsresidedshouldhavelimitedaccesstoonlythosesystemadministratorsasnecessary.Finally,aswithanybackup/recoverysolution,stringenttestingmustbeconducted.Abackupisonlygoodifitcanberestoredsuccessfully.

References:

1. https://pgbackrest.org/2. https://github.com/pgbackrest/pgbackrest3. https://www.postgresql.org/docs/11/static/app-pgdump.html

182|P a g e

4. https://www.postgresql.org/docs/11/static/app-pgbasebackup.html

CISControls:

Version6

10DataRecoveryCapability DataRecoveryCapability

Version7

10.1EnsureRegularAutomatedBackUps Ensurethatallsystemdataisautomaticallybackeduponregularbasis.

10.2PerformCompleteSystemBackups Ensurethateachoftheorganization'skeysystemsarebackedupasacompletesystem,throughprocessessuchasimaging,toenablethequickrecoveryofanentiresystem.

183|P a g e

8.4 Ensure miscellaneous configuration settings are correct (Not Scored)



Description:

Thisrecommendationcoversnon-regular,specialfiles,anddynamiclibraries.

PostgreSQLpermitslocalloginsviatheUNIXDOMAINSOCKETand,forthemostpart,anyonewithalegitimateUnixloginaccountcanmaketheattempt.LimitingPostgreSQLloginattemptscanbemadebyrelocatingtheUNIXDOMAINSOCKETtoasubdirectorywithrestrictedpermissions.

Thecreationandimplementationofuser-defineddynamiclibrariesisanextraordinarypowerfulcapability.InthehandsofanexperiencedDBA/programmer,itcansignificantlyenhancethepowerandflexibilityoftheRDBMS.ButnewandunexpectedbehaviorcanalsobeassignedtotheRDBMS,resultinginaverydangerousenvironmentinwhatshouldotherwisebetrusted.

Rationale:

Audit:

ExecutethefollowingSQLstatementtoverifytheconfigurationiscorrect.Alternatively,inspecttheparametersettingsinthepostgresql.confconfigurationfile.

postgres=# select name, setting from pg_settings where name in ('external_pid_file', 'unix_socket_directories','shared_preload_libraries','dynamic_library_path','local_preload_libraries','session_preload_libraries'); name | setting ---------------------------+--------------------------- dynamic_library_path | $libdir external_pid_file | local_preload_libraries | session_preload_libraries | shared_preload_libraries | pgaudit, set_user unix_socket_directories | /var/run/postgresql, /tmp (6 rows)

Inspectthefileanddirectorypermissionsforallreturnedvalues.Onlysuperusersshouldhaveaccesscontrolrightsforthesefilesanddirectories.Ifpermissionsarenothighlyrestricted,thisisafail.

184|P a g e

Remediation:

Followthesestepstoremediatetheconfiguration:

• Determinepermissionsbasedonyourorganization'ssecuritypolicies.• Relocateallfilesandensuretheirpermissionsarerestrictedasmuchaspossible,i.e.

onlysuperuserreadaccess.• Ensurealldirectorieswherethesefilesarelocatedhaverestrictedpermissionssuch

thatthesuperusercanreadbutnotwrite.• Lastly,changethesettingsaccordinglyinthepostgresql.confconfigurationfile

andrestartthedatabaseclusterforchangestotakeeffect.

DefaultValue:

Thedynamic_library_pathdefaultis$libdirandunix_socket_directoriesdefaultis/var/run/postgresql, /tmp.Thedefaultforexternal_pid_fileandalllibraryparametersareinitiallynull,ornotset,uponclustercreation.

References:

1. https://www.postgresql.org/docs/11/static/runtime-config-file-locations.html2. https://www.postgresql.org/docs/11/static/runtime-config-connection.html3. https://www.postgresql.org/docs/11/static/runtime-config-client.html

CISControls:

Version6


Version7


185|P a g e

Appendix:SummaryTableControl Set

CorrectlyYes No

1 InstallationandPatches1.1 Ensurepackagesareobtainedfromauthorizedrepositories

(NotScored) o o

1.2 EnsureInstallationofBinaryPackages(NotScored) o o1.3 EnsureInstallationofCommunityPackages(NotScored) o o1.4 EnsuresystemdServiceFilesAreEnabled(Scored) o o1.5 EnsureDataClusterInitializedSuccessfully(Scored) o o2 DirectoryandFilePermissions2.1 Ensurethefilepermissionsmaskiscorrect(Scored) o o2.2 EnsurethePostgreSQLpg_wheelgroupmembershipis

correct(Scored) o o

3 LoggingMonitoringAndAuditing(Centos6)3.1 PostgreSQLLogging3.1.1 LoggingRationale3.1.2 Ensurethelogdestinationsaresetcorrectly(Scored) o o3.1.3 Ensuretheloggingcollectorisenabled(Scored) o o3.1.4 Ensurethelogfiledestinationdirectoryissetcorrectly

(Scored) o o

3.1.5 Ensurethefilenamepatternforlogfilesissetcorrectly(Scored) o o

3.1.6 Ensurethelogfilepermissionsaresetcorrectly(Scored) o o3.1.7 Ensure'log_truncate_on_rotation'isenabled(Scored) o o3.1.8 Ensurethemaximumlogfilelifetimeissetcorrectly(Scored) o o3.1.9 Ensurethemaximumlogfilesizeissetcorrectly(Scored) o o3.1.10 Ensurethecorrectsyslogfacilityisselected(Scored) o o3.1.11 EnsuretheprogramnameforPostgreSQLsyslogmessagesis

correct(Scored) o o

3.1.12 Ensurethecorrectmessagesarewrittentotheserverlog(NotScored) o o

3.1.13 EnsurethecorrectSQLstatementsgeneratingerrorsarerecorded(NotScored) o o

3.1.14 Ensure'debug_print_parse'isdisabled(Scored) o o3.1.15 Ensure'debug_print_rewritten'isdisabled(Scored) o o3.1.16 Ensure'debug_print_plan'isdisabled(Scored) o o3.1.17 Ensure'debug_pretty_print'isenabled(Scored) o o3.1.18 Ensure'log_connections'isenabled(Scored) o o3.1.19 Ensure'log_disconnections'isenabled(Scored) o o

186|P a g e

3.1.20 Ensure'log_error_verbosity'issetcorrectly(NotScored) o o3.1.21 Ensure'log_hostname'issetcorrectly(Scored) o o3.1.22 Ensure'log_line_prefix'issetcorrectly(NotScored) o o3.1.23 Ensure'log_statement'issetcorrectly(Scored) o o3.1.24 Ensure'log_timezone'issetcorrectly(Scored) o o3.2 EnsurethePostgreSQLAuditExtension(pgAudit)isenabled

(Scored) o o

4 UserAccessandAuthorization4.1 Ensuresudoisconfiguredcorrectly(Scored) o o4.2 Ensureexcessiveadministrativeprivilegesarerevoked

(Scored) o o

4.3 Ensureexcessivefunctionprivilegesarerevoked(Scored) o o4.4 EnsureexcessiveDMLprivilegesarerevoked(Scored) o o4.5 Usepg_permissionextensiontoauditobjectpermissions(Not

Scored) o o

4.6 EnsureRowLevelSecurity(RLS)isconfiguredcorrectly(NotScored) o o

4.7 Ensuretheset_userextensionisinstalled(NotScored) o o4.8 Makeuseofdefaultroles(NotScored) o o5 ConnectionandLogin5.1 Ensureloginvia"local"UNIXDomainSocketisconfigured

correctly(NotScored) o o

5.2 Ensureloginvia"host"TCP/IPSocketisconfiguredcorrectly(Scored) o o

6 PostgreSQLSettings6.1 Ensure'AttackVectors'RuntimeParametersareConfigured

(NotScored) o o

6.2 Ensure'backend'runtimeparametersareconfiguredcorrectly(Scored) o o

6.3 Ensure'Postmaster'RuntimeParametersareConfigured(NotScored) o o

6.4 Ensure'SIGHUP'RuntimeParametersareConfigured(NotScored) o o

6.5 Ensure'Superuser'RuntimeParametersareConfigured(NotScored) o o

6.6 Ensure'User'RuntimeParametersareConfigured(NotScored) o o

6.7 EnsureFIPS140-2OpenSSLCryptographyIsUsed(Scored) o o6.8 EnsureSSLisenabledandconfiguredcorrectly(Scored) o o6.9 Ensurethepgcryptoextensionisinstalledandconfigured


187|P a g e

7 Replication7.1 Ensureareplication-onlyuseriscreatedandusedfor

streamingreplication(NotScored) o o

7.2 Ensurebasebackupsareconfiguredandfunctional(NotScored) o o

7.3 EnsureWALarchivingisconfiguredandfunctional(Scored) o o7.4 Ensurestreamingreplicationparametersareconfigured


8 SpecialConfigurationConsiderations8.1 EnsurePostgreSQLconfigurationfilesareoutsidethedata

cluster(NotScored) o o

8.2 EnsurePostgreSQLsubdirectorylocationsareoutsidethedatacluster(NotScored) o o

8.3 Ensurethebackupandrestoretool,'pgBackRest',isinstalledandconfigured(NotScored) o o

8.4 Ensuremiscellaneousconfigurationsettingsarecorrect(NotScored) o o

188|P a g e

Appendix:ChangeHistoryDate Version Changesforthisversion

Jun28,2019 1.0.0 InitialRelease

cis postgresql 11 benchmark v1.0.0...this document, cis postgresql 11 benchmark, provides...

Documents