{"id":14691,"date":"2020-09-07T14:15:49","date_gmt":"2020-09-07T12:15:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/"},"modified":"2020-09-07T14:15:49","modified_gmt":"2020-09-07T12:15:49","slug":"increase-your-postgresql-databases-security-by-checking-a-few-settings","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/","title":{"rendered":"Increase your PostgreSQL databases security by checking a few settings"},"content":{"rendered":"

This blog provides you with a short overview of easy to control settings that enhance your PostgreSQL security.
\nMostly by creating a separate group to assign privileged database members.
\nAnd checking different logging and log rotations settings for better and more secure logging management. <\/p>\n

In this blog I will go over a few PostgreSQL security settings that help to secure your databases.<\/h2>\n

The following settings and recommendations are based on the CIS PostgreSQL 12 Benchmark.<\/p>\n

Privilege group pg_wheel<\/h3>\n

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.<\/p>\n

If you have not created a postgres user yet do so with:<\/p>\n

sudo useradd postgres<\/pre>\n
sudo passwd postgres<\/pre>\n
Create the group, as root, with:<\/p>\n
groupadd pg_wheel && getent group pg_wheel<\/pre>\n
This will create the group and show its properties (group ID may vary):<\/p>\n
pg_wheel:x:505:<\/p>\n
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.<\/p>\n
Then, as root, add your user to it:<\/p>\n
gpasswd -a username pg_wheel<\/pre>\n
To verify the change execute:<\/p>\n
awk -F':' '\/pg_wheel\/{print $4}' \/etc\/group<\/pre>\n
This will return your username.<\/p>\n
To enable sudo for all users in that group execute as root:<\/p>\n
echo '%pg_wheel ALL= \/bin\/su - postgres' > \/etc\/sudoers.d\/postgres<\/pre>\n
chmod 600 \/etc\/sudoers.d\/postgres<\/pre>\n
With this the pg_wheel group members can login as the postgres user.<\/p>\n
PostgreSQL logging<\/h3>\n
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.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Command<\/td>\nDescription<\/td>\nRemediation<\/td>\n<\/tr>\n
show log_destination;<\/em><\/td>\nThis will return the\u00a0 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.<\/td>\n\n
alter system set log_destination = ‘csvlog’; <\/em><\/p>\n
Or if you want multiple, simply separate them with a comma:<\/p>\n
alter system set log_destination = ‘csvlog, stderr, syslog’ ;<\/em><\/p>\n<\/td>\n<\/tr>\n
show logging_collector;<\/em><\/td>\nThe logging collector captures stderr messages and writes them as logs in the desired directory.<\/td>\nIf it doesn’t return ‘on’ change it with:<\/p>\n
alter system set logging_collector = ‘on’;<\/em><\/p>\n
For this setting to take effect you will have to restart your PostgreSQL service:<\/p>\n
systemctl restart postgresql-12<\/em><\/td>\n<\/tr>\n
show log_directory;<\/em><\/td>\nSpecifies the directory when log_destinations is sterr or csvlog. Can be set as absolute path or cluster data directory.<\/td>\nYou can change it with:<\/p>\n
alter system set log_directory=’\/path\/to\/directory’;<\/em><\/td>\n<\/tr>\n
show log_filename;<\/em><\/td>\nSpecifies filename pattern of the log files.<\/p>\n
Default value is:<\/p>\n
postgresql-%a.log<\/p>\n
which will create a logfile for each day of the week. With the days short form in the name. For example:<\/p>\n
postgresql-Mon.log<\/td>\n
Different settings are possible. If log_directory has CSV output enabled it will append to that log file.<\/p>\n
To change the setting:<\/p>\n
alter system set log_filename=’postgresql-%Y%m%d.log’;<\/em><\/p>\n
Which will make the logs have its date in the name.<\/td>\n<\/tr>\n
show log_file_mode;<\/em><\/td>\nThis setting specifies the file permissions. The recommended value is:<\/p>\n
0600 or 0640<\/p>\n
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.<\/td>\n
Change it with:<\/p>\n
alter system set log_file_mode = ‘0600’;<\/em><\/td>\n<\/tr>\n
show log_truncate_on_rotation;<\/em><\/td>\nWill overwrite the logfiles depending on their log_filename setting. Example:<\/p>\n
postgresql-%H.log would result in generating 24 hourly
\nlog files and then cyclically overwriting them.<\/td>\n
The log_filename setting should be set fitting to the style of truncation.<\/p>\n
To change its value use this:<\/p>\n
alter system set log_truncate_on_rotation = ‘on’;<\/em><\/td>\n<\/tr>\n
show log_rotation_age;<\/em><\/td>\nSpecifies the maximum amount of time a log file can be written to.<\/td>\nThis setting should be in harmony with the log_filename setting.<\/p>\n
Change its value with:<\/p>\n
alter system set log_rotation_age=’1d’;<\/p>\n
Other time spans are possible.<\/td>\n<\/tr>\n
show log_rotation_size;<\/em><\/td>\nSets 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.<\/td>\nSet this according to your organizations policies:<\/p>\n
alter system set log_rotation_size = ‘1GB’;<\/em><\/td>\n<\/tr>\n
show log_connections;<\/em><\/td>\nWill log any login attempts.<\/td>\nChange its value with:<\/p>\n
alter system set log_connections = ‘on’;<\/em><\/td>\n<\/tr>\n
show log_disconnections;<\/em><\/td>\nWill log disconnections.<\/td>\nShould definitely be enabled when log_connections is enabled.<\/p>\n
alter system set log_disconnections = ‘on’;<\/em><\/td>\n<\/tr>\n
show log_hostname;<\/em><\/td>\nWill also log the hostname additionally to its IP.<\/td>\nSet this to ‘off’ to reduce DNS overhead if your organizations policies doe not require the hostname to be logged.<\/p>\n
Alter this setting with:<\/p>\n
alter system set log_hostname=’off’;<\/em><\/td>\n<\/tr>\n
show log_statement;<\/em><\/td>\nWill log different SQL statements depending on its settings.<\/p>\n
You can either log ‘none’, ‘ddl’, ‘mod’ or ‘all’.<\/td>\n
Recommendation is to use ‘ddl’ or else your organizations policies.<\/p>\n
The change the settings execute:<\/p>\n
alter system set log_statement=’ddl’;<\/em><\/td>\n<\/tr>\n
show log_timezone ;<\/em><\/td>\nWill set the timezone and is needed for timestamps in log messages.<\/td>\nChange this according to your timezone:<\/p>\n
alter system set log_timezone = ‘GMT’;<\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Conclusion<\/h3>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":53,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,83,149],"tags":[2104,77,25],"type_dbi":[],"class_list":["post-14691","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-postgresql","category-security","tag-cis","tag-postgresql","tag-security"],"acf":[],"yoast_head":"\nIncrease your PostgreSQL databases security by checking a few settings - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Increase your PostgreSQL databases security by checking a few settings\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-09-07T12:15:49+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Burgert\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Burgert\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/\"},\"author\":{\"name\":\"Daniel Burgert\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2b718a1932b49a1889d67f9a86a89049\"},\"headline\":\"Increase your PostgreSQL databases security by checking a few settings\",\"datePublished\":\"2020-09-07T12:15:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/\"},\"wordCount\":916,\"commentCount\":0,\"keywords\":[\"CIS\",\"PostgreSQL\",\"Security\"],\"articleSection\":[\"Database Administration & Monitoring\",\"PostgreSQL\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/\",\"name\":\"Increase your PostgreSQL databases security by checking a few settings - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-09-07T12:15:49+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2b718a1932b49a1889d67f9a86a89049\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/increase-your-postgresql-databases-security-by-checking-a-few-settings\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Increase your PostgreSQL databases security by checking a few settings\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2b718a1932b49a1889d67f9a86a89049\",\"name\":\"Daniel Burgert\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/588e7739ac2dce782acd95efdd53318b7fd00590c5bfadf19520a9ab57003e31?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/588e7739ac2dce782acd95efdd53318b7fd00590c5bfadf19520a9ab57003e31?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/588e7739ac2dce782acd95efdd53318b7fd00590c5bfadf19520a9ab57003e31?s=96&d=mm&r=g\",\"caption\":\"Daniel Burgert\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-burgert\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Increase your PostgreSQL databases security by checking a few settings - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/","og_locale":"en_US","og_type":"article","og_title":"Increase your PostgreSQL databases security by checking a few settings","og_description":"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.","og_url":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/","og_site_name":"dbi Blog","article_published_time":"2020-09-07T12:15:49+00:00","author":"Daniel Burgert","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Daniel Burgert","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/"},"author":{"name":"Daniel Burgert","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2b718a1932b49a1889d67f9a86a89049"},"headline":"Increase your PostgreSQL databases security by checking a few settings","datePublished":"2020-09-07T12:15:49+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/"},"wordCount":916,"commentCount":0,"keywords":["CIS","PostgreSQL","Security"],"articleSection":["Database Administration & Monitoring","PostgreSQL","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/","url":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/","name":"Increase your PostgreSQL databases security by checking a few settings - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-09-07T12:15:49+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2b718a1932b49a1889d67f9a86a89049"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/increase-your-postgresql-databases-security-by-checking-a-few-settings\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Increase your PostgreSQL databases security by checking a few settings"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2b718a1932b49a1889d67f9a86a89049","name":"Daniel Burgert","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/588e7739ac2dce782acd95efdd53318b7fd00590c5bfadf19520a9ab57003e31?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/588e7739ac2dce782acd95efdd53318b7fd00590c5bfadf19520a9ab57003e31?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/588e7739ac2dce782acd95efdd53318b7fd00590c5bfadf19520a9ab57003e31?s=96&d=mm&r=g","caption":"Daniel Burgert"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-burgert\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14691","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/53"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14691"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14691\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14691"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14691"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14691"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14691"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}