Database Assessment - PostgreSQL

Version 

psql -V 

List of Databases
psql -l

Connect to database "postgres"
psql -d postgres

Information Gathering

postgres=> select * from pg_user; 

postgres=> select * from pg_roles; 

postgres=> SHOW ALL; 

postgres=> SELECT * FROM pg_settings; 

postgres=> SELECT name,setting,unit,category,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart FROM pg_settings; 

postgres=> SHOW config_file;

PostgreSQL configuration file (postgresql.conf)
PostgreSQL Client Authentication Configuration File (pg_hba.conf)

---

A. Client Authentication *

• Disable all trust connections, use strong authentication (md5/kerberos etc)
• Trust authentication is only suitable for TCP/IP connections if you trust every user on every machine that is allowed to connect to the server by the pg_hba.conf lines that specify trust. It is seldom reasonable to use trust for any TCP/IP connections other than those from localhost (127.0.0.1)
• Audit Tips: No Argument "METHOD" of "trust" in pg_hba.conf
• Limit connections only from allowed IP
• Use SSL connection
• Audit Tips: 
• Ensure value of "ssl" in postgresql.conf is set to "on"
• Existence of "hostssl" in pg_hba.conf
• pg_settings
• ssl_ca_file is configured
• ssl_cert_file is configured

---

B. User Roles and Access Privileges

• Restrict role attribute of "Superuser", "Create role", "Create DB" to DBA only. 
• Audit Tips: Check role "Attributes" in List of  Role 
• postgres-> \du
• Set VALID UNTIL clause 
• Audit Tips: Check existance of value in column "rolvaliduntil"
• postgres=> select * from pg_roles;

---

C. Connection and Login Restriction

• Password
• password_encryption is set to "on"
• SELECT * FROM pg_shadow;
• SELECT * FROM pg_available_extensions where name='pgcrypto';
• Session Management
• postgresql.conf
• statement_timeout = 10000 #milliseconds 
• tcp_keepalives_idle = 10 # seconds 
• tcp_keepalives_interval = 10 # seconds 
• tcp_keepalives_count = 10

---

D. Logging

• Enable logging
• Audit Tips: In postgresql.conf
• Ensure "log_statement" is not set to "none"  
• log_file_mode = 0600
• Value is set to "log_destination"
• log_connections = on
• log_line_prefix = '< %m %u %d %s %p %r %a> '
• pg_settings
• log_connections is set to on
• log_disconnections is set to on
• SHOW shared_preload_libraries
• Check existance of pgaudit *

---

E. Unnecessary Database Components

• Check for multiple version of Postgres Installed
• rpm -qa | grep postgres
• Check extension installed
• postgres=> select * from pg_extension where extname != 'plpgsql';
• postgres=> SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;
• Review the permissions granted to users by the operating system/file system on the database files, database log files and database backup files
• ls -lR

---

References
1. PostgreSQL - Internals - System Catalogs *
2. OWASP Backend Security Project PostgreSQL Hardening *
3. Tuning Your PostgreSQL Server *
4. DISA STIG: PostgreSQL STIG *