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

[hcs0]

TODO

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

• GitHub - EnterpriseDB/pgaudit: PostgreSQL Audit Extension
• GitHub - 2ndQuadrant/audit-trigger: Simple, easily customised trigger-based auditing for PostgreSQL (Postgres). See also pgaudit.

3. 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 - PostgreSQL: Documentation: 13: 19.8. Error Reporting and Logging

Write Ahead Logging - PostgreSQL: Documentation: 13: 19.5. Write Ahead Log

---

Related: Set Up Logging

Story: Set up log collection, centralization and monitoring on Kosmos servers

Defining this as a ‘story’ ticket because other topics will need to be made for below. I proposed this to basti to set up log collection. How to get started (since this is a big task) we start with 1 or 2 servers. Log Collection Define the sources to log - servers, databases, any network rules set up (ie snort), authentication events, proxy logs, mail server logs (if relevant), dns servers, etc. This requires obtaining a list of what the assets are, then drill down. This is the coverage sc…

[hcs0]

(Copy and paste of the various notes pentesting postgresql)

The HINT in an ERROR message leaks information PL/pgSQL Errors and Messages

The ERROR message leaks information PL/pgSQL Errors and Messages

DROP index statement PostgreSQL DROP INDEX Statement

LIST index statement (which part is reconnaissance though 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 username NOINHERIT LOGIN PASSWORD password;
CREATE ROLE role_name;

Roles have been set
GRANT role_2 TO role_1;

Database has been deleted
DROP DATABASE [IF EXISTS] db_name;

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

A table has been dropped
DROP TABLE [IF EXISTS] table_name CASCADE;

How to prevent disaster recovery Schedule - PGConf.EU 2019 - PostgreSQL Conference Europe 2019

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 Postgres SQL Injection Cheat Sheet | pentestmonkey

Being able to use WAL to shutdown PostgreSQL PostgreSQL: Documentation: 9.3: Continuous Archiving and Point-in-Time Recovery (PITR) (filling up pg_xlog/ directory)

Can you enumerate the PostgreSQL details Penetration Testing: Brute Forcing Postgres (use nmap against metasploitable)

Can you brute force into PostgreSQL database Penetration Testing: Brute Forcing Postgres (use msfconsole and wordlists)

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

METASPLOIT POSTGRESQL SETUP Metasploit Postgres Setup - Fedora Project Wiki

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