Ensuring CIS compliance for your Postgres database

In this playground, we will show you how you can use Puppet to ensure CIS compliancy for your Postgres database.

The playground system

On the playground system, you will find a Postgres database. We have created it using the pg_profile module. The playground will guide you in your security customizations.

Working in the playground

Under this text, you see the working area. You can inspect the system and issue any command you like in the terminal. In the editor window, you can see the Puppet production environment. You can edit anything you wish. The documentation tab shows the documentation for the pg_secured module.

Beware

This system will self-destruct in about one hour. So please don’t use it to build or create anything you wish to keep!

Subjects in this playground

The playground contains the following sub paragraphs:

Applying CIS controls
Selecting version of CIS benchmark
Excluding controls
Enable some controls
Reading the output
Enable all controls
Re-run Puppet and check idempotency

Happy exploring!!

Applying CIS controls

In this section, we’ll show you how you can use Puppet to apply CIS controls to your Postgres server.

In the editor tab, go to the directory hierdata\nodes and open the file pg14.playground.enterprisemodules.com. This file contains all the node-specific data.

In the top file you see this:

role: role::postgres::secured_db

This is different than the other Postgres playgrounds that use the role role::postgres::simple_db. The role secured_db adds the Puppet code to your node that ensures that ALL of the CIS controls for Postgres databases are applied to this database.

Selecting version of CIS benchmark

But there are many versions of Postgres and also many versions of the documents describing the CIS controls. How do we tell Puppet what to use?

The next section in the yaml files does.

pg_profile::database::cis_controls::dbname:             testdb
pg_profile::database::cis_controls::product_version:    pg14
pg_profile::database::cis_controls::doc_version:        'V1.0.0'

Here you see that we want to use version V1.0.0 for pg14 of the controls on this system and apply them to testdb.

Excluding controls

If you don’t specify anything else, Puppet will apply ALL controls to the selected Postgres database. Most of the times however, organisations need to customize the set of controls. Security people prefer working with exclusion lists. So everything is applied except the ones we explicitly exclude. If we scroll down in the yaml, we see the hiera key pg_profile::database::cis_controls::skip_list. This is an array of the controls that we want to skip, e.g. exclude.

Right now for teaching purposes, the list contains all available controls. So when we run Puppet, nothing will be secured.

Running Puppet.

Let do this:

puppet apply site.pp
Notice: Compiled catalog for pg14.playground.enterprisemodules.com in environment production in 0.65 seconds
Notice: Ensure Postgres software version 14
Notice: Ensure Postgres initial setup
Notice: Ensure Postgres database start
Notice: Ensure Postgres database(s) testdb@localhost
Notice: Making sure database testdb is secured.
Notice: Apply pg_secured CIS controls from pg14 V1.0.0 on testdb.
Notice: Applied catalog in 0.50 second

And you see no security changes..

Enable some controls

Now let’s enable some of the controls. Let’s take the first five controls and comment them out. Meaning they are no longer on the skip list and will be applied.

  # - systemd_service_files_are_enabled
  # - data_cluster_initialized_successfully
  # - file_permissions_mask_is_correct
  # - postgresql_pg_wheel_group_membership_is_correct
  # - log_destinations_are_set_correctly

Run Puppet

Now run Puppet again:

puppet apply site.pp

The output is now:

Notice: Compiled catalog for pg14.playground.enterprisemodules.com in environment production in 0.87 seconds
Notice: Ensure Postgres software version 14
Notice: Ensure Postgres initial setup
Notice: Ensure Postgres database start
Notice: Ensure Postgres database(s) testdb@localhost
Notice: Making sure database testdb is secured.
Notice: Apply pg_secured CIS controls from pg14 V1.0.0 on testdb.
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P2_1::Testdb/Pg_secured::Controls::File_permissions_mask_is_correct[testdb]/File_line[postgres_umask_set]/ensure: created
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P2_2::Testdb/Pg_secured::Controls::Postgresql_pg_wheel_group_membership_is_correct[testdb]/Group[pg_wheel]/ensure: created
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_2::Testdb/Pg_secured::Controls::Log_destinations_are_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_destination@testdb]/Pg_parameter[log_destination@testdb]/value: value changed 'stderr' to 'csvlog'
Notice: Applied catalog in 2.07 seconds

Reading the output

As you can see, Puppet applied some changes to the database. Let’s inspect one of these messages.

Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_2::Testdb/Pg_secured::Controls::Log_destinations_are_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_destination@testdb]/Pg_parameter[log_destination@testdb]/value: value changed 'stderr' to 'csvlog'

The Puppet notice message very explicitly tells you what is changed and why. The pg14::V1_0_0::P3_1_2::Testdb/ tells you what version of Postgres and CIS document where used to determine the baseline. The Log_destinations_are_set_correctly is a description of the CIS control. And Pg_parameter[log_destination@testdb]/value: value changed 'stderr' to 'csvlog' tells us that the parameter log_destination on the database testdb has been changed from stderr to csvlog

Enable all controls

Let’s see what happens when we apply all CIS controls to the database.

Remove all of the data for the key pg_profile::database::cis_controls::skip_list either by commenting out or removing the lines.

Now run Puppet:

puppet apply site.pp

Here is some example output:

Notice: Compiled catalog for pg14.playground.enterprisemodules.com in environment production in 0.94 seconds
Notice: Ensure Postgres software version 14
Notice: Ensure Postgres initial setup
Notice: Ensure Postgres database start
Notice: Ensure Postgres database(s) testdb@localhost
Notice: Making sure database testdb is secured.
Notice: Apply pg_secured CIS controls from pg14 V1.0.0 on testdb.
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P4_1::Testdb/Pg_secured::Controls::Sudo_is_configured_correctly[testdb]/File[/etc/sudoers.d/postgres]/ensure: defined content as '{md5}1f4f05909972b4c93d55e2beebe774bf'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P5_2::Testdb/Pg_secured::Controls::Login_via_host_tcp_ip_socket_is_configured_correctly[testdb]/File[/opt/puppetlabs/puppet/share/augeas/lenses/pg_hba.aug]/ensure: defined content as '{md5}7d74721b3ee07d33f388f30deafcada0'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P5_2::Testdb/Pg_secured::Controls::Login_via_host_tcp_ip_socket_is_configured_correctly[testdb]/Pg_hba[host to all on all from 127.0.0.1/32]/method: method changed 'trust' to 'scram-sha-256'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P5_2::Testdb/Pg_secured::Controls::Login_via_host_tcp_ip_socket_is_configured_correctly[testdb]/Pg_hba[host to all on all from ::1/128]/method: method changed 'trust' to 'scram-sha-256'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P5_2::Testdb/Pg_secured::Controls::Login_via_host_tcp_ip_socket_is_configured_correctly[testdb]/Pg_hba[host to all on replication from 127.0.0.1/32]/method: method changed 'trust' to 'scram-sha-256'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P5_2::Testdb/Pg_secured::Controls::Login_via_host_tcp_ip_socket_is_configured_correctly[testdb]/Pg_hba[host to all on replication from ::1/128]/method: method changed 'trust' to 'scram-sha-256'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_4::Testdb/Pg_secured::Controls::Log_file_destination_directory_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_directory@testdb]/Pg_parameter[log_directory@testdb]/value: value changed 'log' to '/var/log/postgres'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_5::Testdb/Pg_secured::Controls::Filename_pattern_for_log_files_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_filename@testdb]/Pg_parameter[log_filename@testdb]/value: value changed 'postgresql-%a.log' to 'postgresql-%Y%m%d.log'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_8::Testdb/Pg_secured::Controls::Maximum_log_file_lifetime_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_rotation_age@testdb]/Pg_parameter[log_rotation_age@testdb]/value: value changed '1440' to '60'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_9::Testdb/Pg_secured::Controls::Maximum_log_file_size_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_rotation_size@testdb]/Pg_parameter[log_rotation_size@testdb]/value: value changed '0' to 1048576
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_10::Testdb/Pg_secured::Controls::Correct_syslog_facility_is_selected[testdb]/Pg_secured::Internal::Set_parameter[syslog_facility@testdb]/Pg_parameter[syslog_facility@testdb]/value: value changed 'local0' to 'local1'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_11::Testdb/Pg_secured::Controls::Program_name_for_postgresql_syslog_messages_is_correct[testdb]/Pg_secured::Internal::Set_parameter[syslog_ident@testdb]/Pg_parameter[syslog_ident@testdb]/value: value changed 'postgres' to 'proddb'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_18::Testdb/Pg_secured::Controls::Log_connections_is_enabled[testdb]/Pg_secured::Internal::Set_parameter[log_connections@testdb]/Pg_parameter[log_connections@testdb]/value: value changed 'off' to 'on'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_19::Testdb/Pg_secured::Controls::Log_disconnections_is_enabled[testdb]/Pg_secured::Internal::Set_parameter[log_disconnections@testdb]/Pg_parameter[log_disconnections@testdb]/value: value changed 'off' to 'on'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_20::Testdb/Pg_secured::Controls::Log_error_verbosity_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_error_verbosity@testdb]/Pg_parameter[log_error_verbosity@testdb]/value: value changed 'default' to 'verbose'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_22::Testdb/Pg_secured::Controls::Log_line_prefix_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_line_prefix@testdb]/Pg_parameter[log_line_prefix@testdb]/value: value changed '%m [%p] ' to '%m [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_23::Testdb/Pg_secured::Controls::Log_statement_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_statement@testdb]/Pg_parameter[log_statement@testdb]/value: value changed 'none' to 'ddl'
Notice: /Stage[main]/Pg_secured::pg14::V1_0_0::P3_1_24::Testdb/Pg_secured::Controls::Log_timezone_is_set_correctly[testdb]/Pg_secured::Internal::Set_parameter[log_timezone@testdb]/Pg_parameter[log_timezone@testdb]/value: value changed 'UTC' to 'GMT'
Notice: Applied catalog in 4.97 seconds

Re-run Puppet and check idempotency

What happens now if we run Puppet again:

puppet apply site.pp

The output should look following:

Notice: Compiled catalog for pg14.playground.enterprisemodules.com in environment production in 0.67 seconds
Notice: Ensure Postgres software version 14
Notice: Ensure Postgres initial setup
Notice: Ensure Postgres database start
Notice: Ensure Postgres database(s) testdb@localhost
Notice: Making sure database testdb is secured.
Notice: Apply pg_secured CIS controls from pg14 V1.0.0 on testdb.
Notice: Applied catalog in 1.70 seconds

As you can see, no changes are (re)applied. The database is already secured.

You like it?

Do you like what you see here and want to test this on your own infrastructure? No problem. You can sign up for a free trial.

If you have any questions, don’t hesitate to contact us.