This is a work in progress of an Ansible playbook that will audit and harden PostgreSQL 9 to the DoD STIG v1r6.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

237 lines
6.5 KiB

1 year ago
  1. ---
  2. title: STIG Postgresql 12
  3. date: March 23, 2020
  4. author: Micah Halter
  5. ---
  6. # Set Up Environment Variables
  7. Find the `postgresql` data folder and version
  8. ```
  9. $~ sudo su - postgres
  10. $~ psql -c "SHOW data_directory"
  11. $~ psql -c "SHOW server_version"
  12. ```
  13. As a DB administrator, add the following lines to `~/.bashrc` (Updated for your `postgresql` installation
  14. ```
  15. export PATH="/usr/pgsql-12/bin:$PATH"
  16. export PGDATA='/var/lib/pgsql/12/data'
  17. export PGVER=12
  18. ```
  19. # Set up pgaudit
  20. Install `pgaudit` extension with
  21. ```
  22. $~ sudo yum install pgaudit14_12
  23. ```
  24. Change `shared_preload_libraries` line in `${PGDATA}/postgresql.conf` to
  25. ```
  26. shared_preload_libraries = 'pgaudit'
  27. ```
  28. Add `pgaudit` configuration options to the bottom of `${PGDATA}/postgresql.conf`
  29. ```
  30. #------------------------------------------------------------------------------
  31. # PGAUDIT OPTIONS
  32. #------------------------------------------------------------------------------
  33. # Enable catalog logging - default is 'on'
  34. pgaudit.log_catalog='on'
  35. # Specify the verbosity of log information (INFO, NOTICE, LOG, WARNING, DEBUG)
  36. pgaudit.log_level='log'
  37. # Log the parameters being passed
  38. pgaudit.log_parameter='on'
  39. # Log each relation (TABLE, VIEW, etc.) mentioned in a SELECT or DML statement
  40. pgaudit.log_relation='off'
  41. # For every statement and substatement, log the statement and parameters
  42. pgaudit.log_statement_once='off'
  43. # Define the master role to use for object logging
  44. # pgaudit.role=''
  45. # Choose the statements to log:
  46. # READ - SELECT, COPY
  47. # WRITE - INSERT, UPDATE, DELETE, TRUNCATE, COPY
  48. # FUNCTION - Function Calls and DO Blocks
  49. # ROLE - GRANT, REVOKE, CREATE/ALTER/DROP ROLE
  50. # DDL - All DDL not included in ROLE
  51. # MISC - DISCARD, FETCH, CHECKPOINT, VACUUM
  52. pgaudit.log='ddl,role,write,read'
  53. ```
  54. # Set up Logging
  55. Verify the following logging settings in `${PGDATA}/postgresql.conf`
  56. ```
  57. log_destination = 'syslog'
  58. logging_collector = on
  59. log_directory = 'log'
  60. log_filename = 'postgresql-%a.log'
  61. log_file_mode = 0600
  62. log_truncate_on_rotation = on
  63. log_rotation_age = 1d
  64. log_rotation_size = 0
  65. syslog_facility = 'LOCAL0'
  66. syslog_ident = 'postgres'
  67. log_checkpoints = on
  68. log_connections = on
  69. log_disconnections = on
  70. log_duration = off
  71. log_error_verbosity = default
  72. log_hostname = on
  73. log_line_prefix = '%m %u %d: '
  74. log_lock_waits = on
  75. log_statement = 'none'
  76. log_timezone = 'America/New_York'
  77. client_min_messages = notice
  78. log_min_messages = warning
  79. log_min_error_statement = error
  80. log_min_duration_statement = -1
  81. ```
  82. Add the following rule to `/etc/rsyslog.conf`
  83. ```
  84. local0.* /var/log/postgresql
  85. ```
  86. # Install pgcrypto
  87. Run
  88. ```
  89. $~ sudo su - postgres
  90. $~ psql -c "CREATE EXTENSION pgcrypto"
  91. ```
  92. # Setup SSL
  93. ## Create SSL Certificates
  94. ```
  95. # Create Self-Signed certificate
  96. $~ openssl genrsa -aes256 -out ca.key 4096
  97. $~ openssl req -new -x509 -sha256 -days 1825 -key ca.key -out ca.crt \
  98. -subj "/C=US/ST=GA/L=Atlanta/O=GTRI/CN=root-ca"
  99. # Create Server Intermediate Certificate
  100. $~ openssl genrsa -aes256 -out server-intermediate.key 4096
  101. $~ openssl req -new -sha256 -days 1825 -key server-intermediate.key \
  102. -out server-intermediate.csr \
  103. -subj "/C=US/ST=GA/L=Atlanta/O=GTRI/CN=server-im-ca"
  104. $~ openssl x509 -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca \
  105. -req -days 1825 -CA ca.crt -CAkey ca.key -CAcreateserial \
  106. -in server-intermediate.csr -out server-intermediate.crt
  107. # Create Client Intermediate Certificate
  108. $~ openssl genrsa -aes256 -out client-intermediate.key 4096
  109. $~ openssl req -new -sha256 -days 1825 -key client-intermediate.key \
  110. -out client-intermediate.csr \
  111. -subj "/C=US/ST=GA/L=Atlanta/O=GTRI/CN=client-im-ca"
  112. $~ openssl x509 -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca \
  113. -req -days 1825 -CA ca.crt -CAkey ca.key -CAcreateserial \
  114. -in client-intermediate.csr -out client-intermediate.crt
  115. # Create Server Certificate
  116. # replace dbase01 with hostname:
  117. $~ openssl req -nodes -new -newkey rsa:4096 -sha256 -keyout server.key \
  118. -out server.csr -subj "/C=US/ST=GA/L=Atlanta/O=GTRI/CN=dbase01"
  119. $~ openssl x509 -extfile /etc/pki/tls/openssl.cnf -extensions usr_cert \
  120. -req -days 1825 -CA server-intermediate.crt -CAkey server-intermediate.key \
  121. -CAcreateserial -in server.csr -out server.crt
  122. # Create Client Certificate
  123. # client cert must be mapped to a postgres role
  124. $~ openssl req -nodes -new -newkey rsa:4096 -sha256 -keyout client.key \
  125. -out client.csr -subj "/C=US/ST=GA/L=Atlanta/O=GTRI/CN=ident_map"
  126. $~ openssl x509 -extfile /etc/pki/tls/openssl.cnf -extensions usr_cert \
  127. -req -days 1825 -CA client-intermediate.crt -CAkey client-intermediate.key \
  128. -CAcreateserial -in client.csr -out client.crt
  129. $~ cat server.crt server-intermediate.crt ca.crt > ./server-full.crt
  130. # place ca.crt, server.key, server-full.crt in the $PGDATA directory
  131. # chown them postgres:postgres
  132. # chmod them 600
  133. ```
  134. ## Configure Postgresql to use SSL
  135. ```
  136. # Create role named same as client certificate
  137. $~ psql -c "CREATE ROLE ident_map LOGIN"
  138. ```
  139. Configure `ssl` certificates in `$PGDATA/postgresql.conf` (update file paths as needed):
  140. ```
  141. ssl = true
  142. ssl_cert_file = 'server-full.crt'
  143. ssl_key_file = 'server.key'
  144. ssl_ca_file = 'ca.crt'
  145. ```
  146. Configure `$PGDATA/pg_ident.conf`:
  147. ```
  148. ssl-test ident_map identmap
  149. ```
  150. Configure `$PGDATA/pg_hba.conf` (replace `dbase01` with host name):
  151. ```
  152. hostssl all all dbase01/32 cert clientcert=1 map=ssl-test
  153. hostssl all all ::1/128 cert clientcert=1 map=ssl-test
  154. ```
  155. ## Configure Clients
  156. Copy the certificates on the client (update paths as needed)
  157. ```
  158. $~ mkdir $CLIENT_HOME/.postgresql
  159. $~ cp ca.crt $CLIENT_HOME/.postgresql/root.crt
  160. $~ cp client.key $CLIENT_HOME/.postgresql/postgresql.key
  161. $~ cat client.crt client-intermediate.crt ca.crt \
  162. > $CLIENT_HOME/.postgresql/postgresql.crt
  163. $~ chmod 600 $CLIENT_HOME/.postgresql/*
  164. ```
  165. Test the client (replace `dbase01` with host name):
  166. ```
  167. $~ psql "postgresql://dbase01:5432/postgres?sslmode=verify-full" -U ident_map
  168. ```
  169. Make sure all of the client roles inherit the `ident_map` role, and the ssl certificates should work.
  170. # Run the ansible
  171. Set the defaults in `roles/disa-v1r6/defaults/main.yml`
  172. ```
  173. $~ sudo ansible-playbook playbook.yml
  174. ```
  175. # TODO
  176. - `PGS9-00-002700`: real-time alerts on audit failures. If we set this up for
  177. `rsyslog`, then it should automatically work
  178. - `PGS9-00-008000`: FIPS enabled (other rules need this as well)
  179. - `PGS9-00-009900`: 75% audit capacity warning, still going to `rsyslog`. If
  180. we set that up with 75% audit capacity warning, we should be good.
  181. - `PGS9-00-011600`: cron script to terminate sessions after defined trigger events