Kosmos Community

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: info@postgresqltutorial.com
HINT: Check the email again

PostgreSQL Logging Documentation

All about Error Reporting and Logging - https://www.postgresql.org/docs/13/runtime-config-logging.html

Write Ahead Logging - https://www.postgresql.org/docs/13/runtime-config-wal.html

Related: Set Up Logging

1 Like

(Copy and paste of the various notes pentesting postgresql)

The HINT in an ERROR message leaks information http://www.postgresqltutorial.com/plpgsql-errors-messages/

The ERROR message leaks information http://www.postgresqltutorial.com/plpgsql-errors-messages/

DROP index statement http://www.postgresqltutorial.com/postgresql-indexes/postgresql-drop-index/

LIST index statement (which part is reconnaissance though http://www.postgresqltutorial.com/postgresql-indexes/postgresql-list-indexes/)

Being able to find the Postgres version

Being able to query the type of index

Being able to DELETE http://www.postgresqltutorial.com/postgresql-delete/

An attempt was made to DELETE http://www.postgresqltutorial.com/postgresql-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 https://www.postgresql.eu/events/pgconfeu2019/schedule/session/2754-you-forgot-to-put-the-where-in-the-delete/

No data at rest encryption https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/

Blocks are decrypted as they are read from disk (side channel attack) https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/

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: https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/
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 http://pentestmonkey.net/cheat-sheet/sql-injection/postgres-sql-injection-cheat-sheet

Being able to use WAL to shutdown PostgreSQL https://www.postgresql.org/docs/9.3/continuous-archiving.html (filling up pg_xlog/ directory)

Can you enumerate the PostgreSQL details https://pen-testing-lab.blogspot.com/2012/01/brute-forcing-postgres.html (use nmap against metasploitable)

Can you brute force into PostgreSQL database https://pen-testing-lab.blogspot.com/2012/01/brute-forcing-postgres.html (use msfconsole and wordlists)

PENTESTER’S GUIDE TO POSTGRES https://hakin9.org/a-penetration-testers-guide-to-postgresql/

METASPLOIT POSTGRESQL SETUP https://fedoraproject.org/wiki/Metasploit_Postgres_Setup

Admin Postgres Auxiliary Modules ON METASPLOIT https://www.offensive-security.com/metasploit-unleashed/admin-postgres-auxiliary-modules/