Protecting Postgres: Key Security Takeaways from a Postgres Honeypot

By Yonatan Khanashvili and Shelly Raban, Threat Hunting Experts at Team Axon

Executive Summary

During 2023, Team Axon set up a honeypot to uncover public Postgres attacks. The analysis of the attacks revealed a significant escalation in threats against publicly exposed Postgres databases, highlighted by a widespread ransomware operation and a large-scale coin miner intrusion that, according to public records, has been running for years. The ransomware campaign that Team Axon uncovered, targeted publicly accessible Postgres databases, involving the deletion of all tables followed by a ransom note demanding cryptocurrency. Remarkably, databases were compromised within seven minutes of becoming publicly accessible, showcasing the operation's vast reach.

As Postgres is widely used by web servers and other publicly accessible applications, we are publishing this paper to emphasize the importance of robust security measures and best practices for defending Postgres databases against relevant threats.

Background: The Prevalence of Publicly Exposed Postgres Instances

By default, Postgresql databases listen on port 5432. If this port is already in use, Postgresql will use the next free port, usually 5433.

In our recent exploration of Shodan's database, we stumbled upon 1,310,544 instances of Postgres databases with their default ports open to the internet. Over a million instances, each potentially serving as an entry point for threat actors wishing to exploit security gaps in Postgres deployments, increasing the risk for ransomware attacks that could disrupt business operations and compromise sensitive data.

Shodan exposure prevalence query

Technical Analysis

In our ongoing commitment to proactively address emerging threats and stay ahead of the evolving threat landscape, Team Axon consistently researches new and trending attack methods. As a part of this initiative, we have set up an exposed PostgreSQL server honeypot designed to lure potential attackers. 

Through the use of a honeypot, we've recently identified two interesting attack methods targeting PostgreSQL servers that have remained relevant over the past few years. In our analysis, we'll delve into two primary attack vectors: ransomware targeting data hosted in PostgreSQL and coin miners specifically targeting PostgreSQL environments.

To attract attacks, we intentionally used the default username 'Postgres' Super Admin  and a weak password, to leave a security gap that would look appealing for malicious actors. In both of the above mentioned attack scenarios, threat actors were able to execute successful brute-force attacks, gaining initial access to the PostgreSQL service. 

Attack Flow #1 - PostgreSQL Ransomware

The attack was carried out using an automated loop targeting the same instance repeatedly, iteratively deleting all tables and databases to maximize the impact and profit. To our surprise, the databases were breached just seven minutes after they became accessible to the public, highlighting the extensive scope of the operation.

Data Exfiltration and Deletion
The following statements repeat for each database name. Let's take the ‘postgres’ database as an example. First, the threat actor retrieves the size of the database and the table names. Then, it iterates through the tables to obtain sample data from each table and drops it. Finally, it deletes the whole database.

-- Get the 'postgres' database size.
SELECT pg_database_size('postgres')

-- Retrieve the names of tables in the 'public' schema of the 'postgres' database.
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_catalog = 'postgres';

The following statements repeat for on each table name in the database. Let's take the 'users' table as an example.

-- Retrieve the names of columns in the 'users' table within the 'public' schema the 'postgres' database.
SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'users';

-- Get the first 50 rows of the 'users' table
SELECT * FROM users LIMIT 50;

-- Permanently delete the 'users' table from the database
DROP TABLE IF EXISTS users CASCADE;

-- Save the changes made during the current transaction
COMMIT

-- Terminate backends (connections) to the database in order to delete it (except for the "postgres" database)
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname <> 'postgres' AND pid <> pg_backend_pid();

-- Another iteration over database names is initiated
BEGIN

-- Drop each database
DROP DATABASE postgres;

Data exfiltration and deletion statements 

As we can see in line 9, the threat actor only dumps a small portion of each table and does not obtain most of the data. This means, that even if the victim pays, the data is already gone and can not be recovered. As explained by Imperva, the size and sample data could be used as proof by the threat actor if the victim asked for it.

Ransom Note Creation

A unique email address and a corresponding database code are autogenerated for each infection. A new database, ‘read_me_to_recover’, is created, holding the ransom note and payment instructions.

-- Create the ransom note database
CREATE DATABASE readme_to_recover TEMPLATE template0;
BEGIN

-- Create a table and insert the ransom note
CREATE TABLE readme (text_field VARCHAR(255));
INSERT INTO readme (text_field) VALUES ('After paying send mail to us: rambler+3ysom@onionmail.org and we will provide a link for you to download your data. Your DBCODE is: 3YSOM');
COMMIT

Ransom note creation statements

Actor Characteristics

Upon a successful attack on a Postgres database, the intruder creates a new 'readme' table. This table contains a ransom demand, along with contact details and an extensive 'information' page. The message in the 'readme' table states:

“All your data is backed up. You must pay 0.0125 BTC to 18224LViuRGEhqrUzeRLE9Y9ggogcdkNn5 In 48 hours, your data will be publicly disclosed and deleted. (more information: go to http://iplis[.]ru/data3”

“After paying send mail to us: rambler+3ysom@onionmail.org and we will provide a link for you to download your data. Your DBCODE is: 3YSOM”

The comprehensive note demands clear payment without any discounts or free data release. It includes instructions for buying cryptocurrencies and offers a 'live chat' contact through the 'getsession' program, requiring the 'DECODE' code to identify the victim. Our analysis, however, shows no evidence of the attacker actually extracting the database content. As such, paying the ransom most likely does not guarantee data recovery. While paying ransomware is generally not advised, sometimes businesses make such decisions based on risk assessment. 

Interestingly, this actor's methods and characteristics closely resemble a significant ransomware campaign from 2020 targeting MongoDB databases, indicating a continued trend of attacks on exposed databases and an expansion in the types of databases targeted by the attacker.

A ransomware note from the 'readme' table

We have also spotted coin-mining activity on the exposed server. The threat actor leveraged fileless payloads using "copy from program" statements to achieve OS command execution using the PostgreSQL user context. According to PostgreSQL Global Development Group, this intentional design allows those with superuser or pg_execute_server_program privileges to act as the operating system user (typically 'postgres'). There is no inherent security boundary between the database superuser and the underlying OS user, and it doesn't grant OS superuser (root) privileges. 

Nonetheless, the convergence of database superuser capabilities with the underlying OS user raises significant security concerns. We've noticed the use of base64 encoded payloads that encompass ELFs and bash scripts. This includes downloading extra payloads from remote servers via curl/wget, or using /dev/tcp if these aren't installed, as well as terminating other coin-mining processes. All these actions are closely related to the behaviors that were reported by Unit42 in 2020.

For each command, the threat actor used the following flow:

1. Deleting the temporary table in case one with the same name already exists (in this example- "public"."temp_bmquaorqvc")
2. Creating the table
3. Forming a "copy from program" statement with the wanted command or base64 payload (in this example- 'ifconfig 2>&1 || exit 0' WITH DELIMITER '~';’), which inserts the command into the newly created table
4. Executing the command by running ‘SELECT * FROM <temp_table_name>’
5. Dropping the table

Statements used for OS command execution

Team Axon Recommendations

Let’s speak about best practices to stay on top of threats targeting Postgres servers. 

Postgres Default User & Password Practices
Our research and honeypot analysis indicates that the initial breach in this campaign was mainly due to poor password practices, making it easier to conduct a successful brute force attack. It is imperative to use a strong password and refrain from utilizing the 'Postgres' username, which is set as the default in Postgres applications. The default username 'Postgres' greatly increases the chances of a successful brute-force attack if used. Furthermore, many Postgres Docker installation guides and tutorials incorporate passwords directly in their command lines. You should avoid this practice and ensure the creation of unique and robust passwords instead.

Network Security Practices
If it's not essential, refrain from exposing your instance to the internet. Instead, employ IP whitelisting to permit connections exclusively from trusted IP addresses. In cases where internet exposure is necessary, avoid using the default PostgreSQL port and place it behind a firewall.

Log Auditing 
Activating the log auditing feature is instrumental in identifying unusual activities executed on the databases and can offer extra context for analysis during an incident. By default, Postgres instances do not have query log auditing enabled for tracking the operations performed on databases.  Enabling the auditing can be done with the following guide:

• Edit the Postgres config file /var/lib/pgsql/<version>/data/postgresql.conf OR 
  var/lib/postgresql/data/postgresql.conf
• Change the log_statement setting to 'all'
  • #log_statement = 'none' to log_statement = 'all'
• Change the logging_collector setting to 'on'
  • #logging_collector = off to logging_collector = on
• Create a new directory under data directory (NOTE: the directory has to exist, Postgres isn’t creating it for you)
  • Change the log_directory to the name of the directory you created
  • #log_directory = 'log' to log_directory = 'log'
• Uncomment log_filename
  • #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' to log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
• Set log_destination to stderr
  • log_destination = 'stderr'
• Optional: Set up defined logging schema with modifying log_line_prefix
  • log_line_prefix = '%m, %p, %d, %h '
  • %m = timestamp with milliseconds
  • %p = process ID
  • %d = database name
  • %h = remote host
• Restart the service, or stop and start the container
  • sudo service postgresql restart
  • docker stop <container name> docker start <container name>

For enhanced security, it is advisable to forward logs to an external server or cloud storage to minimize the risk of log data being altered by adversaries during incidents. While we observed no evidence of log tampering in this ransomware campaign, implementing such a practice is generally recommended for improved audit integrity.

Avoid Using Postgres “Trust Authentication” Configuration
Another highly common security practice gap in PostgreSQL is the “trust authentication” feature, which allows any user or IP address to connect to the database without requiring a password. Such lax security settings make PostgreSQL databases prime targets for ransomware attacks, as attackers can easily gain unauthorized access. 

This setup can be done by editing the PostgreSQL configuration file and allowing a “trust” method on all incoming connections or by setting the environment variable POSTGRES_HOST_AUTH_METHOD=trust. 

Similar to the weak user passwords, we saw this security gap configuration common in some docker installation frameworks, make sure to check this configuration as following a good posture practice. 

Follow the Principle of Least Privilege
Avoid granting superuser privileges or the default role pg_execute_server_program to untrusted users, as these permissions enable the "copy from program" feature, potentially leading to OS command execution in the context of the PostgreSQL service underlying OS user.

Move to Managed Database Solutions
Moving to a managed database solution rather than managing your database on your own can be a quick and smart move to keep your Postgres instances secure. Managed database solutions will help you with following the best practices described above, and provide additional monitoring, and another level of defense behind the identity and access management (IAM). Good options could be Cloud SQL by Google Cloud Platform (GCP), or Amazon Relational Database Service (RDS by AWS).

Implement Backup
PostgreSQL provides tools like pg_dump to create backups. Regularly creating backups of your databases can be an effective countermeasure for ransomware attacks. Ensure that the directory containing the backups has restrictive permissions. You can use tools like pg_restore to restore data from a backup.

Conclusion

Our recent honeypot analysis has revealed a significant surge in threats targeting publicly exposed PostgreSQL databases in 2023. Currently, over a million PostgreSQL instances are accessible on the internet, broadening the attack surface. The prevalent method for initial access involves successful brute-force attacks on the default Postgres user with weak credentials. These findings emphasize the critical need for organizations to enhance their security postures to effectively counteract this escalating threat landscape.

Appendix: Indicators of Compromise (IOCs) 

Full list of Postgres Ransomware campaign IPs can be found here.

Statement patterns in postgres logs (Ransomware):

• ‘%CREATE DATABASE readme_to_recover TEMPLATE template0;’
• ‘%INSERT INTO readme (text_field) VALUES ('After paying send mail to us: rambler+<5_characters>@onionmail.org and we will provide a link for you to download your data. Your DBCODE is: <5_characters>');’
  

Statement patterns in postgres logs (PGminer):

• ‘%COPY "public"."temp_<10_letters>" TO '/var/lib/postgresql/data/temp_<10_letters>;’
• ‘%COPY "public"."temp_<10_letters>" FROM PROGRAM E'(%) 2>&1 || exit 0' WITH DELIMITER '~';’’
  

To stay updated on threat hunting research, activities, and queries, follow Team Axon’s Twitter account (@team__axon). 

HUNTERS © 2024 All rights reserved
The Hunters and Axon Team trademarks, service marks and logos used herein are owned by Cyber Hunters Ltd. All other trademarks used herein are the property of their respective owners.