Story: Testing of PostgreSQL database and set up logging/add recommendations


  1. Pentest PostgreSQL database. See comment.

  2. Set up logging and audit logging on the database and server. See below:

Types of events to collect logs for: (Create new topic once ready then move this list to that new topic)

  • Authentication into PostgreSQL database logs (“Login Failed” message)
  • Connection attempts to the PostgreSQL database, access of the account
  • Log for queries made “ select usename, passwd from pg_shadow;“
  • Clearing the log files
  • Changes: to user privileges ie being able to INSERT, being able to UPDATE, etc
  • Change to ACL, changes to environment variables, other configuration settings
  • Creating/deleting table

Set up audit logging

  1. Look into Postgres with encryption

Postgres Transparent Data Encryption - The patch can store all the files making up a PostgreSQL cluster securely on disk in encrypted format (data-at-rest encryption) and then decrypt blocks as they are read from disk. However the data is unencrypted in memory.

About PostgreSQL Logging

Logging body is made up of the following:

  • MESSAGE: to set error message text
  • HINT: to provide the hint message so that the root cause of the error is easier to be discovered.
  • DETAIL: to give detailed information about the error.
  • ERRCODE: to identify the error code, which can be either by condition name or directly five-character SQLSTATE code. See table of error codes and condition names.

ERROR messages contain HINT:

ERROR: Duplicate email:
HINT: Check the email again

PostgreSQL Logging Documentation

All about Error Reporting and Logging -

Write Ahead Logging -

Related: Set Up Logging

1 Like

(Copy and paste of the various notes pentesting postgresql)

The HINT in an ERROR message leaks information

The ERROR message leaks information

DROP index statement

LIST index statement (which part is reconnaissance though

Being able to find the Postgres version

Being able to query the type of index

Being able to DELETE

An attempt was made to DELETE

A new role was CREATED
CREATE ROLE role_name;

Roles have been set
GRANT role_2 TO role_1;

Database has been deleted

A column has been dropped
ALTER TABLE table_name DROP COLUMN column_name;

A table has been dropped

How to prevent disaster recovery

No data at rest encryption

Blocks are decrypted as they are read from disk (side channel attack)

Is the database first nitialized with encryption in mind and that the key used for initializing the database is accessible to the server during startup.

The following are not encrypted:
Transport encryption (client / server) via SSL; Encrypted replication; Fully secured replicas

The Client connection to the database is not encrypted (ie SSL)

Postgres SQL injections

Being able to use WAL to shutdown PostgreSQL (filling up pg_xlog/ directory)

Can you enumerate the PostgreSQL details (use nmap against metasploitable)

Can you brute force into PostgreSQL database (use msfconsole and wordlists)



Admin Postgres Auxiliary Modules ON METASPLOIT