This blog provides you with a short overview of easy to control settings that enhance your PostgreSQL security.
Mostly by creating a separate group to assign privileged database members.
And checking different logging and log rotations settings for better and more secure logging management.

In this blog I will go over a few PostgreSQL security settings that help to secure your databases.

The following settings and recommendations are based on the CIS PostgreSQL 12 Benchmark.

Privilege group pg_wheel

Similar to the OS group wheel it should contain members that are authorized to elevate to super user and access the database. Providing more differentiation the pg_wheel group should be created and populated with members who should be able to become the postgres operating system user.

If you have not created a postgres user yet do so with:

sudo useradd postgres
sudo passwd postgres

Create the group, as root, with:

groupadd pg_wheel && getent group pg_wheel

This will create the group and show its properties (group ID may vary):

pg_wheel:x:505:

What does it mean? First part is the group name. The ‘x’ part is for the password. After that follows the group ID. And at last the members.

Then, as root, add your user to it:

gpasswd -a username pg_wheel

To verify the change execute:

awk -F':' '/pg_wheel/{print $4}' /etc/group

This will return your username.

To enable sudo for all users in that group execute as root:

echo '%pg_wheel ALL= /bin/su - postgres' > /etc/sudoers.d/postgres
chmod 600 /etc/sudoers.d/postgres

With this the pg_wheel group members can login as the postgres user.

PostgreSQL logging

Next are PostgreSQl logging settings that help keeping track of various action performed on the database. These also help identifying suspicious actions or errors. A running PostgreSQL database is needed to execute the sql commands.

Command Description Remediation
show log_destination; This will return the  methods in which PostgreSQL will write its logs. The destination should comply with your organizations logging policies. Not having this set will result in a loss of all logging information. It is also possible to have multiple logging destinations.

alter system set log_destination = ‘csvlog’;

Or if you want multiple, simply separate them with a comma:

alter system set log_destination = ‘csvlog, stderr, syslog’ ;
show logging_collector; The logging collector captures stderr messages and writes them as logs in the desired directory. If it doesn’t return ‘on’ change it with:

alter system set logging_collector = ‘on’;

For this setting to take effect you will have to restart your PostgreSQL service:

systemctl restart postgresql-12
show log_directory; Specifies the directory when log_destinations is sterr or csvlog. Can be set as absolute path or cluster data directory. You can change it with:

alter system set log_directory=’/path/to/directory’;
show log_filename; Specifies filename pattern of the log files.

Default value is:

postgresql-%a.log

which will create a logfile for each day of the week. With the days short form in the name. For example:

postgresql-Mon.log

 Different settings are possible. If log_directory has CSV output enabled it will append to that log file.

To change the setting:

alter system set log_filename=’postgresql-%Y%m%d.log’;

Which will make the logs have its date in the name.
show log_file_mode; This setting specifies the file permissions. The recommended value is:

0600 or 0640

The first only allows the server owner to read and write the log files. The other also allows members of the owner group to read the files. But that also requires the log_directory to be outside of the data cluster directory.

 Change it with:

alter system set log_file_mode = ‘0600’;
show log_truncate_on_rotation; Will overwrite the logfiles depending on their log_filename setting. Example:

postgresql-%H.log would result in generating 24 hourly
log files and then cyclically overwriting them.

 The log_filename setting should be set fitting to the style of truncation.

To change its value use this:

alter system set log_truncate_on_rotation = ‘on’;
show log_rotation_age; Specifies the maximum amount of time a log file can be written to. This setting should be in harmony with the log_filename setting.

Change its value with:

alter system set log_rotation_age=’1d’;

Other time spans are possible.
show log_rotation_size; Sets the maximum file size. If it is reached a new file will be created. If its value is 0 there will be no size triggered creation of log files. Set this according to your organizations policies:

alter system set log_rotation_size = ‘1GB’;
show log_connections; Will log any login attempts. Change its value with:

alter system set log_connections = ‘on’;
show log_disconnections; Will log disconnections. Should definitely be enabled when log_connections is enabled.

alter system set log_disconnections = ‘on’;
show log_hostname; Will also log the hostname additionally to its IP. Set this to ‘off’ to reduce DNS overhead if your organizations policies doe not require the hostname to be logged.

Alter this setting with:

alter system set log_hostname=’off’;
show log_statement; Will log different SQL statements depending on its settings.

You can either log ‘none’, ‘ddl’, ‘mod’ or ‘all’.

 Recommendation is to use ‘ddl’ or else your organizations policies.

The change the settings execute:

alter system set log_statement=’ddl’;
show log_timezone ; Will set the timezone and is needed for timestamps in log messages. Change this according to your timezone:

alter system set log_timezone = ‘GMT’;

 

Conclusion

These setting are easy to check and change, but provide quite a lot of information and control over your PostgreSQL database. Further settings will be showcased in my next blog.

Post Views: 1,293
Thumbnail [60x60]
by
Daniel Burgert