{"id":5368,"date":"2021-01-19T16:11:13","date_gmt":"2021-01-19T10:41:13","guid":{"rendered":"https:\/\/opstree.com\/blog\/\/?p=5368"},"modified":"2025-11-21T14:55:59","modified_gmt":"2025-11-21T09:25:59","slug":"postgres-cis-benchmark","status":"publish","type":"post","link":"https:\/\/opstree.com\/blog\/2021\/01\/19\/postgres-cis-benchmark\/","title":{"rendered":"Postgres &#8211; CIS Benchmark"},"content":{"rendered":"\r\n\r\n\r\n<p>We have seen many security incidents. Any breach in security cause concern among enterprises. To be honest it not only concern them, it also gives birth to their nightmare, distrust and scepticism as organisation. The root cause of this distrust is improper implementation and configuration.<\/p>\r\n\r\n\r\n\r\n<p><strong>Opstree Security <\/strong>has started a new initiative where we rigorously analyse and implement CIS Benchmark of every tools being used today.<\/p>\r\n\r\n\r\n\r\n<p>In this CIS series, we will discuss the CIS Benchmarks of <a href=\"https:\/\/opstree.com\/blog\/2025\/10\/07\/postgresql-performance-with-pgbouncer\/\">PostgreSQL<\/a>.<!--more--><\/p>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\">PostgreSQL<\/h2>\r\n\r\n\r\n\r\n<p>For those who are new to PostgreSQL . Let us give you a quick summary of it.<\/p>\r\n\r\n\r\n\r\n<p>PostgreSQL is an open-source and free relational database management system (RDBMS). It was initially launched in July 1986 and so far in three decades, it has incorporated various key features that distinguish it from other ruling RDBMS.<\/p>\r\n\r\n\r\n\r\n<p>It provides us various key benefits.<\/p>\r\n\r\n\r\n\r\n<ul>\r\n<li>Table inheritance<\/li>\r\n<li>User-defined types<\/li>\r\n<li>Nested transactions (savepoints)<\/li>\r\n<li>Multi-version concurrency control (MVCC)<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\">FACTS<\/h2>\r\n\r\n\r\n\r\n<figure class=\"wp-block-table\">\r\n<table>\r\n<tbody>\r\n<tr>\r\n<td><strong>Instagram uses two back-end database systems mainly. These are: PostgreSQL and Cassandra.<\/strong><br \/><br \/>Many organisations are using it.:-<br \/>Apple<br \/>Skype<br \/>Spotify<br \/>IMDB<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/figure>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\">Objective<\/h2>\r\n\r\n\r\n\r\n<p>If you are using it in production , then this blog is specially written for you.<\/p>\r\n\r\n\r\n\r\n<p>As PostgreSQL CIS Benchmark is vast topic to cover so we have segregated topics. In this blog we will endorse following PostgreSQL CIS Benchmarks.<\/p>\r\n\r\n\r\n\r\n<ul>\r\n<li>PostgreSQL Logging<\/li>\r\n<li>Directory and File Permission<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\">PostgreSQL Logging<\/h2>\r\n\r\n\r\n\r\n<p>Most of the Postgres CIS checks are from logging. So most of our discussion will focus on important checks from it.<\/p>\r\n\r\n\r\n\r\n<h4 class=\"wp-block-heading\">Ensure the log file destination directory is set correctly<\/h4>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show logging_collector;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5376\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-1.png?w=459\" alt=\"\" width=\"500\" height=\"127\" \/>\r\n<figcaption>Figure 1: Execution of psql statement show log_directory<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>If it yields something like log then it is not set and syslog is mainly handling logs and creating a directory \/var\/log\/postgresql.<\/p>\r\n\r\n\r\n\r\n<p>It is required for logging_collector to be enabled.<\/p>\r\n\r\n\r\n\r\n<p>Let&#8217;s see logs.<\/p>\r\n\r\n\r\n\r\n<p>We know how much crucial a log is as it tells us various information that has happened in our PostgreSQL.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5379\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-3.png?w=1024\" alt=\"\" width=\"500\" height=\"51\" \/>\r\n<figcaption>Figure 2: Check Postgres log. What it shows?<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>Here as you can see it shows us the Start and Stop time of PostgreSQL.<\/p>\r\n\r\n\r\n\r\n<p>Let&#8217;s execute an error statement.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5381\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-4.png?w=1024\" alt=\"\" width=\"500\" height=\"71\" \/>\r\n<figcaption>Figure 3: Now it appended the error at end of Postgres log.<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>Now we are at least familiar with Postgres logging. Now let&#8217;s add some other checks.<\/p>\r\n<p><strong>[Also Read: <a href=\"https:\/\/opstree.com\/blog\/2025\/09\/23\/postgres-mcp-for-claude-desktop\/\">The Ultimate Guide to Postgres MCP for Claude Desktop<\/a> ]<\/strong><\/p>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure &#8216;log_connections&#8217; is enabled<\/h3>\r\n\r\n\r\n\r\n<p>By default, log_connections is not enabled.<\/p>\r\n\r\n\r\n\r\n<p>When we enable it, it causes each attempted connection to the Postgres server to be logged, as well as successful completion of client authentication.<\/p>\r\n\r\n\r\n\r\n<p>It helps us in auditing as well as when any breach has happened it quickly helps us to find source user and navigate us to quickly confine the breach to limited extent.<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_connections ;<\/pre>\r\n\r\n\r\n\r\n\r\n\r\n<figure><\/figure>\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5386\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-5.png?w=443\" alt=\"\" width=\"500\" height=\"133\" \/>\r\n<figcaption>Figure 4: Output of psql statement<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>See what actually get logs.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5392\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-9.png?w=1024\" alt=\"\" width=\"500\" height=\"59\" \/>\r\n<figcaption>Figure 5: Logging of Connections<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>Here as you can see, I have remotely tried to connect my Postgres server. My Public IP has been captured. And you can see &#8216;opstree@postgres&#8217; says command executed by opstree user in Postgres database. My public IP is also captured 139.5.242.31.<\/p>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure &#8216;log_disconnections&#8217; is enabled<\/h3>\r\n\r\n\r\n\r\n<p>By default, log_disconnections is not enabled.<\/p>\r\n\r\n\r\n\r\n<p>By enabling log_disconnections, it logs the end of each session, including session duration.\u00a0 By enabling the log_disconnections, one can view and examine connections for failed attempts and the duration of the logged-in session.<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_disconnections ;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5389\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-7.png?w=555\" alt=\"\" width=\"500\" height=\"111\" \/>\r\n<figcaption>Figure 6: Output of psql statement<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5393\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-10.png?w=1024\" alt=\"\" width=\"500\" height=\"58\" \/>\r\n<figcaption>Figure 7: Logging for log_disconnections<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>Look closely at the last line of Figure 7. You will see session time being recorded which in my case is 05 minutes 56 seconds. My Public IP, Database I have accessed, and user name.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5395\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-11.png?w=1024\" alt=\"\" width=\"500\" height=\"27\" \/>\r\n<figcaption>Figure 8:<br \/><br \/>Here you will show it also logs Authentication Failed for user opstree. These logs are very crucial.<\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>We can collect these metrics and send to elasticsearch or any other monitoring tool too for internal purpose.<\/p>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure the log file permissions are set correctly<\/h3>\r\n\r\n\r\n\r\n<p>Log File Permission is mandatory to be set to 0600. Any loose permission will allow other users to read it. As shown earlier, logs contain crucial information. So its file permission must be restricted.<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">Execute Following psql statement to check:\r\nshow log_file_mode;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5398\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-12.png?w=365\" alt=\"\" width=\"500\" height=\"159\" \/>\r\n<figcaption>Figure 9: Execution of psql statement.<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5400\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-13.png?w=757\" alt=\"\" width=\"500\" height=\"50\" \/>\r\n<figcaption>Figure 10: Permission of log files.<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure &#8216;log_statement&#8217; is set correctly<\/h3>\r\n\r\n\r\n\r\n<p>log_statement setting specifies the types of SQL statements that are logged.<\/p>\r\n\r\n\r\n\r\n<p><strong>By default, it is set to none.<\/strong> <strong>And if so this is a fail.<\/strong><\/p>\r\n\r\n\r\n\r\n<p><strong>If someone created a table, altered it, or dropped it, these actions will not be logged. As per CIS, this should be set to ddl.<\/strong><\/p>\r\n\r\n\r\n\r\n<p>Other Values are:<\/p>\r\n\r\n\r\n\r\n<ul>\r\n<li><strong>none (off) <\/strong><\/li>\r\n<li><strong>ddl (logs all data definition statements: )<\/strong><\/li>\r\n<li><strong>mod (logs all ddl statements, plus data-modifying statements like insert, update )<\/strong><\/li>\r\n<li><strong>all (logs all statement) can be noisy<\/strong><\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_statement;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5409\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-15.png?w=480\" alt=\"\" width=\"500\" height=\"131\" \/>\r\n<figcaption>Figure 11: Output of psql statement executed.<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>Let&#8217;s create a table and then delete it and see if it is logged or not.<\/p>\r\n\r\n\r\n\r\n<div class=\"wp-block-image\">\r\n<figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5411\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-16.png?w=1024\" alt=\"\" width=\"500\" height=\"49\" \/>\r\n<figcaption>Figure 12: Here we have created a table opstree_employee and then dropped it. ( postgres@postgres) You can see user opstree performed this action on database Postgres.<\/figcaption>\r\n<\/figure>\r\n<\/div>\r\n\r\n\r\n\r\n<p>Here we have important statements that are being logged along with user-details.<\/p>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure &#8216;log_duration&#8217; is enabled<\/h3>\r\n\r\n\r\n\r\n<p>Enabling the log_duration setting causes the duration of each completed SQL statement to be logged.<\/p>\r\n\r\n\r\n\r\n<p><strong>By logging the duration of statements, it becomes easier to identify both non-performant queries as well as possible DoS attempts (excessively long-running queries)<\/strong><\/p>\r\n\r\n\r\n\r\n<p><strong>By default, it is set to off<\/strong>.<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_duration;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5416\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-18.png?w=606\" alt=\"\" width=\"500\" height=\"89\" \/>\r\n<figcaption>Figure 13: Output of psql statement<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5414\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-17.png?w=926\" alt=\"\" width=\"500\" height=\"50\" \/>\r\n<figcaption>Figure 14: Here you can see we have got the duration of the completed statement.<\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>Now it prints user and database the duration belongs to. But doesn&#8217;t print statement.<\/p>\r\n\r\n\r\n\r\n<p>Because logging of SQL statements may include sensitive information of your customer or organization that should not be recorded in logs. Doing so will also create an overhead or great noise level.<\/p>\r\n\r\n\r\n\r\n<p>Example:- All statements that I have executed are working well and the responses in ms (millisecond ) are good too. It is just done to check the performance of our queries when required. These parameters are generally exported to elasticsearch or any monitoring tool and there we analyze the performance.<\/p>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure &#8216;log_min_duration_statement&#8217; is disabled<\/h3>\r\n\r\n\r\n\r\n<p>It specifies the minimum execution time for a statement at which the statement will be logged. For example, if you set it to 750 ms, then all SQL statements that execute in 750 ms or longer will be logged along with the statement. Setting it to -1 disables this feature, which is recommended.<\/p>\r\n\r\n\r\n\r\n<p><strong>By default, it is set to -1. i.e disabled<\/strong>.<\/p>\r\n\r\n\r\n\r\n<p>We also want to say that you can enable this feature (<strong>while testing your queries in the development and staging environment<\/strong>) or in prod too if it is suitable for your organization, as scenarios are always different from one organization to another.<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_min_duration_statement;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5425\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-22.png?w=400\" alt=\"\" width=\"500\" height=\"154\" \/>\r\n<figcaption>Figure 15: Output of psql statement.<br \/><br \/>This is deliberately done to show you it&#8217;s use-case. By default, it is -1 (disabled) as recommended.<\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>To show you an example of what it records, we are enabling this to 1 ms. So I will execute two statements one with less time and another with more time than 1 ms. This is just done to show you the outcome.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5421\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-20.png?w=1024\" alt=\"\" width=\"500\" height=\"38\" \/>\r\n<figcaption>Figure 16: <br \/><br \/>Here you can see statement taking 1 ms are recorded along with statement executed and others are exempted and their duration is logged only.<br \/><br \/><strong>[ Also Read: <a href=\"https:\/\/opstree.com\/blog\/2025\/11\/20\/ai-for-data-integration-transformation\/\">AI for Smarter Data Integration<\/a> ]<\/strong><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure the filename pattern for log files is set correctly<\/h3>\r\n\r\n\r\n\r\n<p>Log rotation depends upon log_rotation_age or log_rotation_size and when they met, It creates a new file whose name depends upon the file name pattern<\/p>\r\n\r\n\r\n\r\n<p>The default in many versions of Postgres is <strong>postgresql-%a.log<\/strong> , which creates a new log file for each day of the week (e.g. <strong>postgresql-Mon.log , postgresql-Tue.log<\/strong> ).<\/p>\r\n\r\n\r\n\r\n<p>Thus it should be set to <strong>postgresql-%Y%m%d.log<\/strong> (recommend).<\/p>\r\n\r\n\r\n\r\n<p>OR<\/p>\r\n\r\n\r\n\r\n<p><strong>postgresql-%Y-%m-%d_%H%M%S.log<\/strong><\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_min_duration_statement;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5423\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-21.png?w=307\" alt=\"\" width=\"500\" height=\"187\" \/>\r\n<figcaption>Figure 17: Output of psql statement<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5499\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-40.png\" alt=\"\" width=\"550\" height=\"148\" \/><br \/>\r\n<figcaption>Figure 18: Output of ls-ls in \/var\/log\/pg_log<\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure the maximum log file lifetime is set correctly<\/h3>\r\n\r\n\r\n\r\n<p>When logging_collector is enabled, the log_rotation_age parameter determines the maximum lifetime of an individual log file. Once maximum log file lifetime is met, automatic log file rotation will occur.<\/p>\r\n\r\n\r\n\r\n<p><strong>Default:- It is set to 1d<\/strong><\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_rotation_age;<\/pre>\r\n\r\n\r\n\r\n<p>It should be set to 1 hour.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5432\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-25.png?w=359\" alt=\"\" width=\"500\" height=\"169\" \/>\r\n<figcaption>Figure 19: Output of psql statement<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p><strong>Let&#8217;s say Opstree bank is using Postgres. It generates a hefty amount of statements or queries by customer. If the rotation age is 1 day. Then we have a single log file for the entire day. It will be difficult for them to look for specific queries when required. Instead, what they will do they will want to rotate file hourly. <\/strong><\/p>\r\n\r\n\r\n\r\n<p><strong>Instead of having one single file for the entire day. Now we have 12 hourly log files for the entire day.<\/strong><\/p>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure the maximum log file size is set correctly<\/h3>\r\n\r\n\r\n\r\n<p>The log_rotation_size setting determines the maximum size of an individual log file. Once the maximum size of the log file is reached, automatic log file rotation will occur.<\/p>\r\n\r\n\r\n\r\n<p><strong>Default: Based on Postgres versions it can vary differently<\/strong>.<\/p>\r\n\r\n\r\n\r\n<p>Make sure it is not set to 0 which means disabled. This will prevent automatic log file rotation when files become too large.<\/p>\r\n\r\n\r\n\r\n<p><strong>It should be set to 1 GB as per CIS.<\/strong><\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_rotation_age;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5436\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-27.png?w=349\" alt=\"\" width=\"500\" height=\"168\" \/>\r\n<figcaption>Figure 20: Output of psql statement<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>So if the file size is greater than the specified value it will create a new file.<\/p>\r\n\r\n\r\n\r\n<p><strong>To show you an example we have deliberately set log_rotation_size to 50 MB.<\/strong><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5444\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-32.png?w=329\" alt=\"\" width=\"500\" height=\"167\" \/>\r\n<figcaption>Figure 21: Set 50 Mb to show an example.<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5443\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-31.png?w=1024\" alt=\"\" width=\"500\" height=\"54\" \/>\r\n<figcaption>Figure 22: <br \/><br \/>New logs are added into a new file as the previous file size has reached 50MB. And log rotation is triggered.<\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure &#8216;log_checkpoints&#8217; is enabled<\/h3>\r\n\r\n\r\n\r\n<p>A checkpoint is a point in the transaction log sequence wherein all the data files have been updated to reflect the information in the log.\u00a0<\/p>\r\n\r\n\r\n\r\n<p>When a crash happens, the latest checkpoint record is determined in the log from where it should start the REDO operation.<\/p>\r\n\r\n\r\n\r\n<p><strong>Default:- It is disabled or off<\/strong>.<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_checkpoints ;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5448\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-34.png?w=368\" alt=\"\" width=\"500\" height=\"152\" \/>\r\n<figcaption>Figure 23: Output of log_checkpoint<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5447\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-33.png?w=1024\" alt=\"\" width=\"500\" height=\"51\" \/>\r\n<figcaption>Figure 24: <br \/><br \/>To show you I have executed a forced checkpoint to show you how checkpoint get logs<\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure &#8216;log_error_verbosity&#8217; is set correctly<\/h3>\r\n\r\n\r\n\r\n<p>It specifies the verbosity (amount of detail) to be logged of an error message.<\/p>\r\n\r\n\r\n\r\n<p>When a statement is executed which eventually results in an error. This parameter specifies Postgres to log level of information along with error.<\/p>\r\n\r\n\r\n\r\n<p><strong>By default, it is set to default<\/strong> (log error statement).<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">show log_error_verbosity ;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5456\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-37.png?w=352\" alt=\"\" width=\"500\" height=\"163\" \/>\r\n<figcaption>Figure 25: Output of psql statement<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>Other values:<\/p>\r\n\r\n\r\n\r\n<ul>\r\n<li><strong>TERSE<\/strong>:- It displays limited information like sql statement which generated error.<\/li>\r\n<li><strong>VERBOSE<\/strong>:- It shows us error code, source file name, function name where it failed, and line number<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n<p>It should be set to <strong>verbose<\/strong> as per CIS but organizations should discuss and set what will be better for their case.<\/p>\r\n\r\n\r\n\r\n<pre class=\"wp-block-syntaxhighlighter-code\">I have executed an error statement.\r\nshow log_error_verbosityasd ;<\/pre>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5455\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-36.png?w=1024\" alt=\"\" width=\"500\" height=\"21\" \/>\r\n<figcaption>Figure 26: Output of default. It logs only statement.<br \/><br \/><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p>What verbose level logs.<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5459\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-38.png?w=1024\" alt=\"\" width=\"500\" height=\"40\" \/>\r\n<figcaption><em>Figure 27: Here you can see it logs various info from ERROR Type, STATEMENT to LOCATION<\/em><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<p><strong>PostgreSQL Logging Quick Summary:-<\/strong><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-table\">\r\n<table>\r\n<tbody>\r\n<tr>\r\n<td>Check Name<\/td>\r\n<td>Default Value<\/td>\r\n<td>Recommended Value<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>ENSURE THE LOG FILE DESTINATION DIRECTORY IS SET CORRECTLY<\/td>\r\n<td>Depend on version<\/td>\r\n<td>Log Directory should be set.<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure &#8216;log_connections&#8217; is enabled<\/td>\r\n<td>off<\/td>\r\n<td>on<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure \u2018log_disconnections\u2019 is enabled<\/td>\r\n<td>off<\/td>\r\n<td>on<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure the log file permissions are set correctly<\/td>\r\n<td>600<\/td>\r\n<td>600<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure \u2018log_statement\u2019 is set correctly<\/td>\r\n<td>none<\/td>\r\n<td>ddl<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure \u2018log_duration\u2019 is enabled<\/td>\r\n<td>off<\/td>\r\n<td>on<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure \u2018log_min_duration_statement\u2019 is disabled<\/td>\r\n<td>-1<\/td>\r\n<td>-1<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure the filename pattern for log files is set correctly<\/td>\r\n<td>Depend<br \/>upon Version<\/td>\r\n<td>postgresql-%Y%m%d.log <br \/>or<br \/>postgresql-%Y-%m-%d_%H%M%S.log<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure the maximum log file lifetime is set correctly<\/td>\r\n<td>1d<\/td>\r\n<td>1h<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure the maximum log file size is set correctly<\/td>\r\n<td>Depend upon version<\/td>\r\n<td>1GB<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure \u2018log_checkpoints\u2019 is enabled<\/td>\r\n<td>off<\/td>\r\n<td>on<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure \u2018log_error_verbosity\u2019 is set correctly<\/td>\r\n<td>default<\/td>\r\n<td>verbose<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<figcaption>Table 1: <em>Quick Summary of PostgreSQL Logging<\/em><\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\">Directory and File Permission<\/h2>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Ensure the file permissions mask is correct<\/h3>\r\n\r\n\r\n\r\n<p>As we know when a file or directory is created, its permission depends upon the umask value. Therefore this umask value should be set to restrict group and other permission.<\/p>\r\n\r\n\r\n\r\n<p><strong>By default, it is 002.<\/strong><\/p>\r\n\r\n\r\n\r\n<p><strong>It should be set to 077. Which will restrict groups and other users from read and write permissions.<\/strong><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-5464\" src=\"https:\/\/opstree.com\/blog\/\/wp-content\/uploads\/2021\/01\/image-39.png?w=576\" alt=\"\" width=\"500\" height=\"127\" \/>\r\n<figcaption>Figure 28: <br \/><br \/>Here as you can see Postgres dir has umask of 002. So when I created a file it allowed read permission to other users. Umask should be set to 077<\/figcaption>\r\n<\/figure>\r\n\r\n\r\n\r\n<h3 class=\"wp-block-heading\">Following checks are not included in the official CIS Benchmark (CIS PostgreSQL 9.5 Benchmark v1.1.0) but we want you to check it too.<\/h3>\r\n\r\n\r\n\r\n<ul>\r\n<li>Restricted Permission of <a href=\"https:\/\/opstree.com\/blog\/2023\/07\/11\/unlocking-debezium-exploring-the-fundamentals-of-real-time-change-data-capture-with-debezium-and-harnessing-its-power-in-docker-containers\/\">PostgreSQL Data Directory<\/a><\/li>\r\n<li>Restricted Permission of PostgreSQL Config Directory<\/li>\r\n<li>Restricted Permission of PostgreSQL Log Directory<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n<p><strong>Directory Permission Quick Summary:-<\/strong><\/p>\r\n\r\n\r\n\r\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-1 wp-block-columns-is-layout-flex\">\r\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis: 100%;\">\r\n<figure class=\"wp-block-table\">\r\n<table>\r\n<tbody>\r\n<tr>\r\n<td>Check Name<\/td>\r\n<td>Default Value<\/td>\r\n<td>Recommended<\/td>\r\n<\/tr>\r\n<tr>\r\n<td>Ensure the file permissions mask is correct<\/td>\r\n<td>022<\/td>\r\n<td>077<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<figcaption>Table 2: Quick Summary of Directory Permission<\/figcaption>\r\n<\/figure>\r\n<\/div>\r\n<\/div>\r\n\r\n\r\n\r\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\r\n\r\n\r\n\r\n<p>PostgreSQL is one of the top elite RDBMS and it is required to ensure that we are following the best security practices.<\/p>\r\n\r\n\r\n\r\n<p>Here we have discussed PostgreSQL logging and directory permission.<\/p>\r\n\r\n\r\n\r\n<p>We covered important checks, psql command to check it, its default value, and what the is recommendation.<\/p>\r\n\r\n\r\n\r\n<p>We will have another blog in which we will cover<\/p>\r\n\r\n\r\n\r\n<ul>\r\n<li><strong>Replication<\/strong><\/li>\r\n<li><strong>Network &amp; Connectivity<\/strong><\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n<p>Thanks for reading this blog. As we are still evaluating PostgreSQL security<\/p>\r\n<p><strong>Related Searches &#8211; <a href=\"https:\/\/opstree.com\/services\/database-and-data-engineering\/\">Data engineering services provider<\/a> | <a href=\"https:\/\/opstree.com\/aws-consulting-services\/\" target=\"_blank\" rel=\"noopener\">AWS Service Provider<\/a>\u00a0<\/strong><\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>We have seen many security incidents. Any breach in security cause concern among enterprises. To be honest it not only concern them, it also gives birth to their nightmare, distrust and scepticism as organisation. The root cause of this distrust is improper implementation and configuration. Opstree Security has started a new initiative where we rigorously &hellip; <a href=\"https:\/\/opstree.com\/blog\/2021\/01\/19\/postgres-cis-benchmark\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Postgres &#8211; CIS Benchmark&#8221;<\/span><\/a><\/p>\n","protected":false},"author":194452061,"featured_media":29900,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[28070474],"tags":[401916,475,768739308,304054034,434917,282303,768739286],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/11\/DevSecOps-1.jpg","jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pfDBOm-1oA","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/5368"}],"collection":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/users\/194452061"}],"replies":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/comments?post=5368"}],"version-history":[{"count":26,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/5368\/revisions"}],"predecessor-version":[{"id":29995,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/5368\/revisions\/29995"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media\/29900"}],"wp:attachment":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media?parent=5368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/categories?post=5368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/tags?post=5368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}