Automate implementing the DISA STIG for PostgreSQL

In this article I’ll show how to do a security audit of the configuration of a PostgreSQL server using , an open-source cross-platform multi-purpose security auditing program, with an audit rule-set that automates checking a server’s compliance with the published by the United States Defense Information Systems Agency (DISA). DevAudit PostgreSQL 9.x Security Technical Implementation Guide Introduction This is the 2nd in my series of articles on the open-source auditing project DevAudit. In the I covered auditing the library dependencies and application configuration and code of an ASP.NET web application. Here I’ll talk about auditing the configuration of an application server that your web or other application may depend on, against a well-known set of security and hardening rules. This step can be part of a continuous integration or deployment pipeline that verifies the configuration and security posture of your application’s operating environment or infrastructure. Specifically in this article I’ll cover: first article Remote auditing a Linux PostgreSQL server over SSH. Implementing PostgreSQL STIG rules as audit rules. Future DevAudit server auditing capabilities. Remote auditing a Windows PostgreSQL server over WinRM. Auditing a PostgreSQL server container. Docker About DevAudit DevAudit is a cross-platform multi-purpose auditing tool designed to audit all aspects of application development security. Applications usually run on and depend on servers deployed in the environment to provide needed services like authentication, web application hosting and serving, data storage and so on. Application servers can also be deployed as that may utilize containerization technology like Docker. Securing database servers like PostgreSQL is a critical part of application security as improperly configured database servers are a major vector for application vulnerabilities and the potential mass disclosure of sensitive data like user accounts. security micro-services DevAudit can audit the configuration of database servers like MySQL and PostgreSQL according to configurable audit rules defined in YAML text files that implement collections of security checks and hardening guidelines. One of the best collections of security guidance and configuration standards for securing application servers is the Security Technical Implementation Guide(STIG) from the U.S. DoD Defense Information Systems Agency (DISA). From Wikipedia: document collection A Security Technical Implementation Guide (STIG) is a cybersecurity methodology for standardizing security protocols within networks, servers, computers, and logical designs to enhance overall security. These guides, when implemented, enhance security for software, hardware, physical and logical architectures to further reduce vulnerabilities. STIGs are published for a wide range of servers, operating systems, and applications. DevAudit can audit servers and operating systems and applications based on the rules provided by STIGs and other published security guidelines. Currently DevAudit provides configuration auditing for the following servers: OpenSSH sshd Apache httpd MySQL and derivatives Nginx PostgreSQL You can run DevAudit server audits each time you deploy or test your application to ensure changes were not made to the server configuration that could affect the security posture of your application. Getting Started The easiest way to get started with DevAudit is to download a binary release for Windows or Linux from the project page. All you need are the .NET Framework 4.6+ on Windows or Mono 4.4+ on Linux. On Linux you can also pull the from Docker Hub. You can also build the project from source on Windows or Linux. See the section of the DevAudit wiki for the full set of installation options. releases ossindex/devaudit Docker image installation You don’t need a running instance of PostgreSQL installed for the audit as long as you have access to the PGSQL binary and configuration file you intend to use for the server instance. You can also audit a running PostgreSQL Docker container. From your DevAudit project folder you can kick off a audit on your local machine using: devaudit pgsql -c -b If you don’t specify the or parameters then DevAudit will attempt to auto-detect the PostgreSQL server configuration file and binary. For example on a Linux host we can do: -c -b allisterb@ubuntu:~/DevAudit$ ./devaudit pgsql -c /etc/postgresql/9.5/main/postgresql.conf _____ _______ __ __ __| \ .-----..--.--.| _ |.--.--..--| ||__|| |_| -- || -__|| | || || | || _ || || _||_____/ |_____| \___/ |___|___||_____||_____||__||____| v2.1.0.004:09:04 [AUDIT] [SUCCESS] Auto-detected PostgreSQL server binary at /usr/lib/postgresql/9.5/bin/postgres.04:09:04 [AUDIT] [INFO] Using PostgreSQL configuration file /etc/postgresql/9.5/main/postgresql.conf.04:09:04 [AUDIT] [SUCCESS] Got PostgreSQL server version 9.5.6.04:09:04 [AUDIT] [STATUS] Scanning PostgreSQL packages.Scanning PostgreSQL packages...04:09:05 [AUDIT] [SUCCESS] Parsed configuration from /etc/postgresql/9.5/main/postgresql.conf. Successfully included 0 out of 0 include files. First line parsed 1. Last line parsed: 623. Parsed 1 totalconfiguration nodes. Parsed 501 total comments.04:09:06 [AUDIT] [INFO] Found PostgreSQL server auto configuration file /var/lib/postgresql/9.5/main/postgresql.auto.conf.04:09:06 [AUDIT] [SUCCESS] Merged configuration from /var/lib/postgresql/9.5/main/postgresql.auto.conf. In this case DevAudit auto-detected the PostgreSQL server binary path and used the path specified to the configuration file to load the server configuration. With the DevAudit Docker image the command to do a PostgreSQL audit on the Linux host machine looks like: docker run -it -v /:/hostroot ossindex/devaudit pgsql -c /etc/postgresql/9.5/main/postgresql.conf We need to run the Docker container with the options to enable interactive output and mount our local root as a volume at in order for the DevAudit Docker container to get access to the files needed for the audit. Once you set the needed options for the Docker container you can use the same DevAudit program options as the regular command-line version. -it /hostroot Remote auditing a Linux PostgreSQL server over SSH It’s usually the case that the server your application will be talking to at deployment or test-time or run-time is not the machine you develop your application on. We can use the remote auditing capabilities of DevAudit to audit any machine over the network that runs PostgreSQL server. Here I’ll kick off the audit of a remote Linux PostgreSQL server using the following command-line options to create a SSH audit environment: Specify the IP or host-name of the remote computer to connect to via SSH. -s Specify the user to connect to the remote computer with. -u Specify the OpenSSH format private-key file to use to authenticate the user. -k Specify that we want to use an interactive prompt (with local echo disabled) to enter the pass-phrase for the key file. -p > devaudit pgsql -s 173.xxx.xxx.xxx -u richard.foo -k C:\bar.key -p -c /etc/postgresql/9.5/main/postgresql.confPassword:_____ _______ __ __ __| \ .-----..--.--.| _ |.--.--..--| ||__|| |_| -- || -__|| | || || | || _ || || _||_____/ |_____| \___/ |___|___||_____||_____||__||____| v2.1.0.007:44:17 [HOST] [INFO] Connecting to 173.xxx.xxx.xxx:22...07:44:18 [HOST] [INFO] Host key fingerprint is: ssh-rsa 0b:50:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx.07:44:19 [HOST] [SUCCESS] Connected to 173.xxx.xxx.xxx in 1954 ms.07:44:19 [HOST] [INFO] Using work directory: C:\Users\Allister\AppData\Local\Temp\devaudit-work\1495352659.80851.07:44:21 [AUDIT] [SUCCESS] Auto-detected PostgreSQL server binary at /usr/lib/postgresql/9.5/bin/postgres.07:44:21 [AUDIT] [INFO] Using PostgreSQL configuration file /etc/postgresql/9.5/main/postgresql.conf.07:44:21 [AUDIT] [SUCCESS] Got PostgreSQL server version 9.5.6.07:44:21 [AUDIT] [STATUS] Scanning PostgreSQL packages.Scanning PostgreSQL packages...07:44:22 [AUDIT] [SUCCESS] Parsed configuration from /etc/postgresql/9.5/main/postgresql.conf. Successfully included 0 out of 0 include files. First line parsed 1. Last line parsed: 623. Parsed 1 total configuration nodes. Parsed 501 total comments.07:44:23 [AUDIT] [INFO] Found PostgreSQL server auto configuration file /var/lib/postgresql/9.5/main/postgresql.auto.conf.07:44:24 [AUDIT] [SUCCESS] Merged configuration from /var/lib/postgresql/9.5/main/postgresql.auto.conf. DevAudit will connect to the remote machine and execute in parallel the required tasks for the PostgreSQL audit. The server binary path is auto-detected and the specified configuration file is read and parsed. Any additional files or configuration tasks required are read and executed to gather as much information on the server as possible for the audit tasks. 07:44:24 [AUDIT] [SUCCESS] Scanned 1 PostgreSQL packages.Searching OSS Index for vulnerabilities for 1 packages...07:44:24 [AUDIT] [INFO] Loading default configuration rules for PostgreSQL application.Searching OSS Index for vulnerabilities for 1 packages...07:44:24 [AUDIT] [INFO] Got 7 default configuration rule(s) for 1 module(s) from postgresql.yml in 77ms.Searching OSS Index for vulnerabilities for 1 packages...07:44:26 [AUDIT] [SUCCESS] Found 87 vulnerabilities for 1 package(s) on OSS Index in 2321 ms.Evaluating 7 configuration rule(s)...07:44:26 [AUDIT] [SUCCESS] Evaluated 7 configuration rule(s) in 21 ms.07:44:27 [AUDIT] [INFO] Evaluated 87 vulnerabilities with 0 matches to package version in 515 ms. DevAudit will audit the versions of the PostgreSQL ‘packages’ or application modules and components it detects against vulnerability data compiled by . Currently only the core server binary is registered as a ‘package’ or module/component by DevAudit. PostgreSQL are only reported against a particular PGSQL server version, rather than against a particular module like pgcrypto. But DevAudit does have the capability to scan for vulnerabilities for specific application modules, plugins, extensions etc. In this case we did not find any indexed vulnerabilities for version 9.5.6 of PGSQL. OSS Index vulnerabilities There are however 3 findings regarding the PostgreSQL server configuration: Application Configuration Audit Results=======================================3 total vulnerabilities found in PostgreSQL application configuration audit. Total time for audit: 9398 ms. [1/1] Module: postgresql. 7 rule(s). 3 rule(s) succeeded. [VULNERABLE]--[1/7] Rule: Server port is set to default (Port). Result: True.--Summary:--By default postgres runs on port 5432. Most attackers will check if a server is listening on port 5432.--If you change the port # to something different, others need to know exactly what port to use to access the server.--Tags:--STIG/V-72841/PostgreSQL must be configured to prohibit or restrict the use of organization defined functions, ports, protocols, and/or services--Severity: 1--Resolution:--Add a directive like the following to the PGSQL configuration file: port 9122.--Urls:-- https://www.crunchydata.com/postgres-stig/PGSQL-STIG-9.5+.pdf --[2/7] Rule: client_min_messages is set to log or debug. Result: False.--[3/7] Rule: max_connections is not set. Result: False.--[4/7] Rule: log_timezone is not set. Result: False.--[5/7] Rule: Server auditing is not enabled. Result: True.--Summary:--Information system auditing capability is critical for accurate forensic analysis.--Without information about the outcome of events, security personnel cannot make an accurate assessment as to whether an attack was successful or if changes were made to the security state of the system.--Event outcomes can include indicators of event success or failure and event-specific results (e.g.,the security state of the information system after the event occurred).--As such, they also provide a means to measure the impact of an event and help authorized personnel to determine the appropriate response.--Tags:--STIG/V-72843/PostgreSQL must produce audit records containing sufficient information to establish the outcome (success or failure) of the events.--Severity: 2--Resolution:--You must install and configure the pgaudit extension.--Urls:-- -- https://www.crunchydata.com/postgres-stig/PGSQL-STIG-9.5+.pdf http://pgaudit.org --[6/7] Rule: client_min_messages is not set to error. Result: True.--Summary:--Any PostgreSQL or associated application providing too much information in error messages on the screen or printout risks compromising the data and security of the system.--The structure and content of error messages need to be carefully considered by the organization anddevelopment team.--Tags:--STIG/V-72851/PostgreSQL must provide non-privileged users with error messages that provide information necessary for corrective actions without revealing information that could be exploited by adversaries.--Severity: 2--Resolution:--Set the client_min_messages directive in the PGSQL configuration file to error.--Urls:-- -- https://www.crunchydata.com/postgres-stig/PGSQL-STIG-9.5+.pdf https://www.postgresql.org/docs/9.1/static/runtime-config-logging.html --[7/7] Rule: Server log files are not mode 0600. Result: False. We found that: The PostgreSQL server being audited is configured to run on the default port. The pgaudit extension is not installed. The configuration file directive is not set to ‘error’. client_min_message Each of these findings corresponds to an audit rule in the YAML file, and each DevAudit audit rule implements a rule from the PostgreSQL STIG. For example the first rule from the PostgreSQL STIG document looks like: Rules/postgresql.yml Group ID (Vulid): V-72841Group Title: SRG-APP-000142-DB-000094Rule ID: SV-87493r1_ruleSeverity: CAT IIRule Version (STIG-ID): PGS9-00-000100Rule Title: PostgreSQL must be configured to prohibit or restrict the use of organization-defined functions, ports, protocols, and/or services, as definedin the PPSM CAL and vulnerability assessments. ... To support the requirements and principles of least functionality, the application must support the organizational requirements providing only essential capabilities and limiting the use of ports, protocols, and/or services to only those required, authorized, and approved to conduct official business or to address authorized quality of life issues. Database Management Systems using ports, protocols, and services deemed unsafe are open to attack through those ports, protocols, and services. This can allow unauthorized access to the database and through the database to other components of the information system. Check Content:As the database administrator, run the following SQL:$ psql -c "SHOW port"If the currently defined port configuration is deemed prohibited, this is a finding.Fix Text: Note: The following instructions use the PGDATA environment variable. See supplementary content APPENDIX-F for instructions onconfiguring PGDATA.To change the listening port of the database, as the database administrator, change the following setting in postgresql.conf:$ sudo su - postgres$ vi $PGDATA/postgresql.confChange the port parameter to the desired port. This is implemented as an audit rule in as: postgresql.yml - id: 1title: Server port is set to default (Port)tags:- STIG/V-72841/PostgreSQL must be configured to prohibit or restrict the use of organization defined functions, ports, protocols, and/or servicesseverity: 1xpathtest: not(boolean(/PGSQL/Values/port)) or boolean(/PGSQL/Values/port='5432')summary: |By default postgres runs on port 5432. Most attackers will check if a server is listening on port 5432.If you change the port # to something different, others need to know exactly what port to use to access the server.resolution: |Add a directive like the following to the PGSQL configuration file: port 9122.urls:- https://www.crunchydata.com/postgres-stig/PGSQL-STIG-9.5+.pdf This audit rule queries the PostgreSQL configuration file(s) to determine if the configuration directive is either unset or set to the default value. port Implementing PostgreSQL STIG rules as audit rules DevAudit uses the library to parse and query server configuration files. Alpheus creates a representation of the configuration file as an XML document and allows you to query this document using queries. In addition to understanding the syntax of configuration files Alphues tries to understand the semantics as well for elements like include files. A PostgreSQL configuration file that looks like this: Alpheus XPATH # -----------------------------# PostgreSQL configuration file# -----------------------------## This file consists of lines of the form: ... # The default values of these variables are driven from the -D command-line# option or PGDATA environment variable, represented here as ConfigDir. data_directory = '/var/lib/postgresql/9.5/main' # use data in another directory# (change requires restart)hba_file = '/etc/postgresql/9.5/main/pg_hba.conf' # host-based authentication file# (change requires restart)... would be represented as XML that looks like the following ----------------------------- ... /etc/postgresql/9.5/main/pg_hba.conf host-based authentication file (change requires restart) /etc/postgresql/9.5/main/pg_ident.conf Each comment and directive of the configuration file is represented as an XML element with attributes declaring the precise location of the directive or comment in the configuration text file. By representing the configuration file content and structure as XML, Alpheus can perform sophisticated queries on configuration files that go far beyond typical searches using regular expressions. Alpheus can also interpret the syntax elements for include files and insert the XML content parsed from any include files found into the root element of the parent document. grep In the case of the 1st audit rule, we use an XPATH query to check the complex condition: Either the port directive does not exist or The port directive is set to 5432. not(boolean(/PGSQL/Values/port)) or boolean(/PGSQL/Values/port='5432') If this condition is satisfied then DevAudit marks this as a finding. Each audit rule has additional attributes like title, summary, tags that are printed out during the application configuration audit results report. Currently only about 7 of the STIG rules for PostgreSQL are implemented by DevAudit. We’re currently building out the rule-sets for each supported server audit target and adding the additional auditing capabilities needed (like running SQL queries on the target database server) that will allow full implementation of the rules for each server STIG. You can also add any custom configuration audit rules you need for your PostgreSQL server deployment by appending them to the file. Rules/postgresql.yml Future DevAudit server auditing capabilities Currently DevAudit is limited to syntactic analysis of server configuration files. Implementing server auditing requires additional capabilities like: Execute SQL queries on the target database server to retrieve configuration settings and database metadata and schema information. Execute shell commands in the target server’s operating environment to retrieve environment variables, file-system permissions and ownership, and other system information. Eventually DevAudit audit rules will contain tests like the following %env(PGPORT)='5432' or %sql(SHOW port)='5432' that will allow full implementation of the server STIG rules. Remote auditing a Windows PostgreSQL server We can also the use the remote auditing capabilities of DevAudit to audit Windows machines via WinRM. Here I’ll kick off the audit of a remote Windows PostgreSQL server from another Windows machine using the following command-line options to create a WinRM audit environment: Specify that we want to use the WinRM protocol for auditing. -w Specify the IP of the Windows computer to connect to. -s Specify the user to connect to the Windows machine with. -u Specify the password for the user connecting to the Windows machine. -p > devaudit pgsql -w -s 192.168.56.103 -u Administrator -p -r C:\Apps\pgsql -c .confPassword:_____ _______ __ __ __| \ .-----..--.--.| _ |.--.--..--| ||__|| |_| -- || -__|| | || || | || _ || || _||_____/ |_____| \___/ |___|___||_____||_____||__||____| @postgresql v2.1.0.005:16:11 [HOST] [STATUS] Connecting to Windows host address 192.168.56.103Connecting to Windows host address 192.168.56.103..05:16:13 [HOST] [SUCCESS] Connected to Windows host address 192.168.56.103. Computer name: WIN-H1LOJM1A4GD. Windows version: Microsoft Windows Server 2008 R2 Standard .05:16:14 [AUDIT] [SUCCESS] Auto-detected PostgreSQL server binary at C:\Apps\pgsql\bin\postgres.exe.05:16:15 [AUDIT] [INFO] Using PostgreSQL configuration file C:\Apps\pgsql\postgresql.conf.05:16:15 [AUDIT] [SUCCESS] Got PostgreSQL server version 9.1.24.Scanning PostgreSQL packages...05:16:16 [AUDIT] [SUCCESS] Parsed configuration from C:\Apps\pgsql\postgresql.conf. Successfully included 0 out of 0 include files. First line parsed 1. Last line parsed: 557. Parsed 1 total configuration nodes. Parsed 453 total comments. ... Application Configuration Audit Results=======================================4 total vulnerabilities found in PostgreSQL application configuration audit. Total time for audit: 8370 ms. [1/1] Module: postgresql. 7 rule(s). 4 rule(s) succeeded. [VULNERABLE]--[1/7] Rule: Server port is set to default (Port). Result: True.--Summary:--By default postgres runs on port 5432. Most attackers will check if a server is listening on port 5432.--If you change the port # to something different, others need to know exactly what port to use to access the server.--Tags:--STIG/V-72841/PostgreSQL must be configured to prohibit or restrict the use of organization defined functions, ports, protocols, and/or services--Severity: 1--Resolution:--Add a directive like the following to the PGSQL configuration file: port 9122.--Urls:-- https://www.crunchydata.com/postgres-stig/PGSQL-STIG-9.5+.pdf --[2/7] Rule: log_timezone is not set. Result: True.--Summary:--Time stamps generated by PostgreSQL must include date and time. Time is commonly expressed in Coordinated Universal Time (UTC),--a modern continuation of Greenwich Mean Time (GMT), or local time with an offset from UTC.--Tags:--STIG/V-72887/PostgreSQL must record time stamps, in audit records and application data, that can be mapped to Coordinated Universal Time (UTC, formerly GMT)..--Severity: 2--Resolution:--Set the log_timezone directive in the PGSQL configuration file to the current timezone.--Urls:-- https://www.crunchydata.com/postgres-stig/PGSQL-STIG-9.5+.pdf --[3/7] Rule: Server auditing is not enabled. Result: True.--Summary:--Information system auditing capability is critical for accurate forensic analysis.--Without information about the outcome of events, security personnel cannot make an accurate assessment as to whether an attack was successful or if changes were made to the security state of the system.--Event outcomes can include indicators of event success or failure and event-specific results (e.g., the security state of the information system after the event occurred).--As such, they also provide a means to measure the impact of an event and help authorized personnel to determine the appropriate response.--Tags:--STIG/V-72843/PostgreSQL must produce audit records containing sufficient information to establish the outcome (success or failure) of the events.--Severity: 2--Resolution:--You must install and configure the pgaudit extension.--Urls:-- -- https://www.crunchydata.com/postgres-stig/PGSQL-STIG-9.5+.pdf http://pgaudit.org --[4/7] Rule: Server log files are not mode 0600. Result: False.--[5/7] Rule: max_connections is not set. Result: False.--[6/7] Rule: client_min_messages is not set to error. Result: True. ... Here we are auditing a Windows machine at 192.168.56.103 using a PostgreSQL root directory on the remote machine at C:\Apps\pgsql and a configuration file postgresql.conf in the specified root directory. DevAudit detected the PGSQL version as 9.1.24 and found 4 configuration issues that violated the STIG audit rules. Auditing a PostgreSQL server Docker container PostgreSQL 9.6 Docker container audit A common scenario for application deployment is to run as a Docker container. We can create a DevAudit Docker container audit environment by specifying the option with the name or id of the running Docker container. Here we connect to a running Docker container named pgsql1. All subsequent DevAudit program options remain the same and the server audit proceeds as usual: -i allisterb@ubuntu:~/DevAudit$ ./devaudit pgsql -i pgsql1 -c /var/lib/postgresql/data/postgresql.conf_____ _______ __ __ __| \ .-----..--.--.| _ |.--.--..--| ||__|| |_| -- || -__|| | || || | || _ || || _||_____/ |_____| \___/ |___|___||_____||_____||__||____| v2.1.0.008:20:33 [HOST] [SUCCESS] Found Docker container with id or name pgsql1.08:20:33 [AUDIT] [SUCCESS] Auto-detected PostgreSQL server binary at /usr/lib/postgresql/9.6/bin/postgres.08:20:33 [AUDIT] [INFO] Using PostgreSQL configuration file /var/lib/postgresql/data/postgresql.conf.08:20:33 [AUDIT] [SUCCESS] Got PostgreSQL server version 9.6.2.08:20:33 [AUDIT] [STATUS] Scanning PostgreSQL packages.Scanning PostgreSQL packages...08:20:35 [AUDIT] [SUCCESS] Parsed configuration from /var/lib/postgresql/data/postgresql.conf. Successfully included 0 out of 0 include files. First line parsed 1. Last line parsed: 524. Parsed 1 total configuration nodes. Parsed 519 total comments.08:20:35 [AUDIT] [INFO] Found PostgreSQL server auto configuration file /var/lib/postgresql/data/postgresql.auto.conf.08:20:35 [AUDIT] [SUCCESS] Merged configuration from /var/lib/postgresql/data/postgresql.auto.conf.08:20:35 [AUDIT] [SUCCESS] Scanned 1 PostgreSQL packages.Searching OSS Index for vulnerabilities for 1 packages... DevAudit can be a great complement to dedicated Docker image by providing scans for specific application server configuration issues before container deployment. Audit rules aren’t limited to security but can also encompass rules for performance, replication, clustering, server fail-over and so on. security scanning Conclusion DevAudit can audit the server configuration of PostgreSQL servers in multiple environments against security rules from the DISA PostgreSQL STIG as well as any other source of security and hardening guidelines. We are building out the capabilities of the project to audit an ever increasing number of servers (like MongoDB) as well as the kinds of auditing capabilities available for each server audit. For DBAs, developers, and teams adopting DevOps and DevSecOps, DevAudit can be a great platform for integrating automated security checks as a consistent and well-defined part of the development and deployment process. Links e GitHub project pag on DevAudit. Short presentation Index of open source packages and vulnerability data. OSS Index: : Contact about commercial support options. Vör Security