# PostgreSQL Enumeration

- **URL:** https://hackindex.io/services/postgresql/enumeration/postgresql-enumeration
- **Path:** PostgreSQL > Enumeration
- **Up:** [Enumeration](https://hackindex.io/services/postgresql/enumeration.md)
- **Published:** 2026-01-18
- **Updated:** 2026-03-26

PostgreSQL enumeration covers confirming the service, pulling the version, and mapping database structure, roles, and extensions once you have access. Version feeds CVE matching. Role and extension state feeds privilege escalation decisions. This page assumes either no credentials yet or valid credentials already in hand: credential testing belongs elsewhere.

## Service and Version Detection

- Service and Version Detection
- Connecting with psql
- Session Context
- Database Enumeration
- Table Enumeration
- Role and Privilege Enumeration
- Extension Enumeration
- Configuration Exposure

```bash
nmap -sV -p 5432,5433 $TARGET_IP
# 5432/tcp open  postgresql PostgreSQL DB 14.5
```

`-sV` extracts the version string directly from the PostgreSQL startup message. Run default scripts alongside for any additional metadata the service exposes:

```bash
nmap -sV -sC -p 5432 $TARGET_IP
```

Confirm the service responds to the PostgreSQL wire protocol without credentials by attempting a connection and reading the error:

```bash
psql -h $TARGET_IP -p $PORT -U postgres 2>&1 | head -3
```

`password authentication failed` and `no pg_hba.conf entry` both confirm the service is live and reachable. A hang or `connection refused` means the port is filtered or closed.

## Connecting with psql

If you have credentials, connect directly:

```bash
psql -h $TARGET_IP -p $PORT -U $USER -d postgres
```

Force SSL when the server requires it:

```bash
psql "host=$TARGET_IP port=$PORT user=$USER dbname=postgres sslmode=require"
```

Disable SSL when a proxy breaks TLS:

```bash
psql "host=$TARGET_IP port=$PORT user=$USER dbname=postgres sslmode=disable"
```

If the server uses `trust` authentication for the connecting IP, the prompt is skipped entirely and you land in a session without a password. Note the source address restriction: this is scoped to whatever IP the server trusts.

## Session Context

Run these first to establish what the current session can see and do:

```sql
SELECT version();
SELECT current_user;
SELECT session_user;
SELECT current_database();
SHOW listen_addresses;
SHOW ssl;
SHOW password_encryption;
```

`listen_addresses='*'` confirms the instance is bound to all interfaces. `password_encryption` of `md5` versus `scram-sha-256` indicates the credential storage method. `current_user` versus `session_user` differing means you are operating under a SET ROLE context.

## Database Enumeration

List all databases on the instance:

```sql
\l
```

Switch into a database to enumerate its contents:

```sql
\c DBNAME
```

List schemas and the current search path:

```sql
SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;
SHOW search_path;
```

The `search_path` shows which schemas the application queries by default. Non-default schemas often contain the application's actual tables.

## Table Enumeration

List all tables across all schemas in the current database:

```sql
\dt *.*
```

For a specific schema:

```sql
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;
```

Prioritise tables that suggest credential or secret storage: `users`, `accounts`, `auth`, `sessions`, `tokens`, `api_keys`, `config`, `settings`, `oauth_tokens`. Table names alone do not prove a vulnerability: they identify where to look next.

Inspect column structure before reading data:

```sql
\d TABLENAME
\d+ TABLENAME
```

Pull a minimal sample to confirm content and types:

```sql
SELECT * FROM TABLENAME LIMIT 5;
```

## Role and Privilege Enumeration

List all roles with their attributes:

```sql
\du
```

Full role attribute view from the catalog:

```sql
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication
FROM pg_roles
ORDER BY rolname;
```

`rolsuper=true` means the role has full database control. From superuser, file read via `pg_read_file`, arbitrary file write via `COPY TO`, and extension loading are all available. `rolreplication=true` grants access to the replication protocol.

Role membership: who inherits from whom:

```sql
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
ORDER BY role, member;
```

If the current user is not a superuser, check whether any accessible role grants elevated permissions through inheritance.

## Extension Enumeration

Extensions installed on the instance determine what additional capabilities exist. Some are direct privilege escalation paths:

```sql
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;
# name      | default_version | installed_version
# ---------------+-----------------+------------------
#  plpgsql       | 1.0             | 1.0
#  plpython3u    | 1.0             | 1.0
#  file_fdw      | 1.0             | 1.0
```

`plpython3u`, `plperlu`, or `plsh` installed means untrusted procedural language functions can be created. With superuser rights, these allow OS command execution via `CREATE FUNCTION`. `file_fdw` allows querying files on the server filesystem as foreign tables. Both are covered in PostgreSQL privilege escalation pages.

## Configuration Exposure

Some configuration values visible from a regular session reveal network exposure and authentication setup:

```sql
SHOW listen_addresses;
SHOW port;
SHOW max_connections;
SHOW log_connections;
SHOW log_disconnections;
```

`log_connections=on` means every login attempt is written to the server log: relevant when testing credentials. `listen_addresses='*'` combined with network reach from a broad segment confirms the instance is broadly exposed.

## References

- [pgsql-brute NSE Script](https://nmap.org/nsedoc/scripts/pgsql-brute.html) - Tests credentials against PostgreSQL: useful for confirming service behaviour during version detection
- [psql: PostgreSQL documentation](https://www.postgresql.org/docs/current/app-psql.html) - Meta-commands, connection string format, and catalog query reference