Top PostgreSQL Security Threats

Modern databases store all kinds of data. From trivial to highly sensitive. The restaurants we frequent, our map locations, our identity credentials, (e.g., Social Security Numbers, Addresses, Medical Records, Banking info, etc…), and everything in between is more than likely stored in a database somewhere. No wonder data is so valuable.

Database technologies advance at a breakneck pace. Innovation, progression, integrity, and enhancements abound are at the forefront as a direct result of the labors of intelligent and devoted engineers, developers, and robust communities supporting those vendors.

Yet there is another side to the coin. That, unfortunately, co-exists within this data-driven world in the form of malware, viruses, and exploits on a massive, all-time high scale.

Data is valuable to the parties on that side of the operation as well. But for different reasons. Any of them could be but are not limited to power, blackmail, financial gain and access, control, fun, pranks, malice, theft, revenge… You get the idea. The list is endless.

Alas, we have to operate with a security mindset. Without this mindset, we leave our systems vulnerable to these types of attacks. PostgreSQL is just as susceptible to compromise, misuse, theft, unauthorized access/control as other forms of software.

So What Measures Can We Take to Mitigate the Number of Risks to Our PostgreSQL Installs?

I strongly feel that promoting awareness of what known threats are out there, is as good a place to start as any. Knowledge is power and we should use all available at our disposal. Besides, how can we police for that we are not even aware of in order to tighten up security on those PostgreSQL instances and protect the data residing there?

I recently searched out known security ‘concerns’ and ‘threats’, targeting the PostgreSQL environment. My search encompassed recent reports, articles, and blog posts within the first quarter of 2018. In addition to that specific time frame, I explored well-known long-standing concerns that are still viable threats today (namely SQL Injection), while not polished or brandished as ‘recently discovered‘.

A Photo Opportunity

A Deep Dive into Database Attacks [Part III]: Why Scarlett Johansson’s Picture Got My Postgres Database to Start Mining Monero

Word of this crafty malware attack returned the most ‘hits’ out of my objective search results.

We’ll visit one of several great blog posts and a rundown of its content. I’ve also included additional blog posts towards the end of this section so be sure and visit those as well detailing this intrusion.

Observations

Information from Imperva, reports their honeypot database (StickyDB) discovered a malware attack on one of their PostgreSQL servers. The honeypot net, as Imperva names the system, is designed to trick attackers into attacking the database so they (Imperva) can learn about it and become more secure. In this particular instance, the payload is a malware that cryptomines Monero, embedded in a photo of Scarlett Johansson.

The payload is dumped to disk at runtime with the lo_export function. But apparently, this happens because lo_export is an entry in pg_proc versus normally direct calling (lo_export).

Interesting details directly from the blog post here for extreme clarity (see cited article),

Now the attacker is able to execute local system commands using one simple function – fun6440002537. This SQL function is a wrapper for calling a C-language function, “sys_eval”, a small exported function in “tmp406001440” (a binary based on sqlmapproject), which basically acts as proxy to invoke shell commands from SQL client.

So what will be next steps of the attack? Some reconnaissance. So it started with getting the details of the GPU by executing lshw -c video and continued to cat /proc/cpuinfo in order to get the CPU details (Figures 3-4). While this feels odd at first, it makes complete sense when your end goal is to mine more of your favorite cryptocurrency, right?

With a combination of database access and the ability to execute code remotely, all while ‘flying under the radar‘ of monitoring solutions, the trespasser then downloads the payload via a photo of Scarlett Johansson.

(Note: The photo has since been removed from its hosted location. See linking article for the mention.)

According to the report, the payload is in binary format. That binary code was appended into the photo in order to pass for an actual photo during upload, allowing for a viewable picture.

See Figure 6 of the post for the SQL responsible for utilizing wget, dd, and executing chmod for permissions on the downloaded file. That downloaded file then creates another executable which is responsible for actually mining the Monero. Of course, housekeeping and cleanup are needed after all this nefarious work.

Figure 7 depicts the performing SQL.

Imperva recommends monitoring this list of potential breach areas in the closing section:

• Watch out of direct PostgreSQL calls to lo_export or indirect calls through entries in pg_proc.
• Beware of PostgreSQL functions calling to C-language binaries.
• Use a firewall to block outgoing network traffic from your database to the Internet.
• Make sure your database is not assigned with public IP address. If it is, restrict access only to the hosts that interact with it (application server or clients owned by DBAs).

Imperva also performed various antivirus tests along with details of how attackers can potentially locate vulnerable PostgreSQL servers. Although I did not include them here for brevity, consult the article for full details of their findings.

Recommended Reading

• Imperva has 2 preceding articles that accompany Part 3 (the one discussed here). While those do not target PostgreSQL heavily as what we’ve just glossed over, they are highly informative reads:
  • Part 1
  • Part 2
• The Scarlett Johansson PostgreSQL malware attack
• Hackers Target PostgreSQL DBs With Coinminer Hidden in Scarlett Johannsson Image
• A Hacker News thread on the exploit.

CVE Details, Report, and Vulnerabilities

I visited this site, which posts latest security threats on a per vendor basis and discovered 4 vulnerabilities in Q1 of 2018. The PostgreSQL Security Information page also has them listed so feel free to consult that resource.

Although most all of them have been addressed, I felt it important to include these in this post to bring awareness to readers who may not have known about them. I feel we can learn from all of them. Especially in the different ways of discovered vulnerabilities.

They are listed below in the order of date published:

I. CVE-2018-1052 date published 2018-02-09 : Update Date 3/10/2018

Overview:

Memory disclosure vulnerability in table partitioning was found in PostgreSQL 10.x before 10.2, allowing an authenticated attacker to read arbitrary bytes of server memory via purpose-crafted insert to a partitioned table.

This vulnerability was fixed with the release of PostgreSQL 10.2 confirmed here. Older 9.x version also fixed are mentioned as well so visit that link to check your specific version.

II. CVE-2018-1053 date published 2018-02-09 : Update Date 3/15/2018

Overview:

In PostgreSQL 9.3.x before 9.3.21, 9.4.x before 9.4.16, 9.5.x before 9.5.11, 9.6.x before 9.6.7 and 10.x before 10.2, pg_upgrade creates file in current working directory containing the output of `pg_dumpall -g` under umask which was in effect when the user invoked pg_upgrade, and not under 0077 which is normally used for other temporary files. This can allow an authenticated attacker to read or modify the one file, which may contain encrypted or unencrypted database passwords. The attack is infeasible if a directory mode blocks the attacker searching the current working directory or if the prevailing umask blocks the attacker opening the file.

As with the previous CVE-2018-1052, PostgreSQL 10.2 fixed this portion of the vulnerability:

Ensure that all temporary files made with “pg_upgrade” are non-world-readable

Many older versions of PostgreSQL are affected by this vulnerability. Be sure and visit the provided link for all those listed versions.

III. CVE-2017-14798 date published 2018-03-01 : Update Date 3/26/2018

Overview:

A race condition in the PostgreSQL init script could be used by attackers able to access the PostgreSQL account to escalate their privileges to root.

Although I could not find anywhere on the linking page that PostgreSQL version 10 was mentioned, many older versions are, so visit that link if running older versions.

Suse Linux Enterprise Server users may be interested in 2 linked articles here and here where this vulnerability was fixed for version 9.4 init script.

IV. CVE-2018-1058 date published 2018-03-02 : Update Date 3/22/2018

Overview:

A flaw was found in the way PostgreSQL allowed a user to modify the behavior of a query for other users. An attacker with a user account could use this flaw to execute code with the permissions of superuser in the database. Versions 9.3 through 10 are affected.

This update release mentions this vulnerability with an interesting linked document all users should visit.

The article provides a fantastic guide from the community titled A Guide to CVE-2018-1058: Protect Your Search Path that has an incredible amount of information concerning the vulnerability, risks, and best practices for combating it.

I’ll do my best to summarize, but visit the guide for your own benefit, comprehension, and understanding.

Overview:

With the advent of PostgreSQL version 7.3, schemas were introduced into the ecosystem. This enhancement allows users to create objects in separate namespaces. By default, when a user creates a database, PostgreSQL also creates a public schema in which all new objects are created. Users who can connect to a database can also create objects in that databases public schema.

This section directly from the guide is highly important (see cited article):

Schemas allow users to namespace objects, so objects of the same name can exist in different schemas in the same database. If there are objects with the same name in different schemas and the specific schema/object pair is not specified (i.e. schema.object), PostgreSQL decides which object to use based on the search_path setting. The search_path setting specifies the order the schemas are searched when looking for an object. The default value for search_path is $user,public where $user refers to the name of the user connected (which can be determined by executing SELECT SESSION_USER;).

Another key point is here:

The problem described in CVE-2018-1058 centers around the default “public” schema and how PostgreSQL uses the search_path setting. The ability to create objects with the same names in different schemas, combined with how PostgreSQL searches for objects within schemas, presents an opportunity for a user to modify the behavior of a query for other users.

Below is a high-level list the guide recommends application of these practices as stipulated to reduce the risk of this vulnerability:

• Do not allow users to create new objects in the public schema
• Set the default search_path for database users
• Set the default search_path in the PostgreSQL configuration file (postgresql.conf)

SQL Injection

Any ‘security-themed‘ SQL blog post or article cannot label itself as such without mention of SQL injection. While this method of attack is by no stretch of the imagination ‘the new kid on the block‘, it has to be included.

SQL Injection is always a threat and perhaps even more so in the web application space. Any SQL database -including PostgreSQL- is potentially vulnerable to it.

While I don’t have a deep knowledge base on SQL Injection -also known as SQLi- I’ll do my best to provide a brief summary, how it can potentially affect your PostgreSQL server, and ultimately how to reduce the risks of falling prey to it.

Refer to the links provided towards the end of this section, all of which contain a wealth of information, explanation, and examples in those areas I am unable to adequately communicate.

Unfortunately, several types of SQL injections exist and they all share the common goal of inserting offensive SQL into queries for execution in the database, perhaps not originally intended nor designed by the developer.

Unsanitized user input, poorly designed or non-existent type checking (AKA validation), along with unescaped user input all can potentially leave the door wide open for would-be attackers. Many web programming API’s provide some protection against SQLi: e.g., ORM’s(Object Relational Mapper), parameterized queries, type checking, etc…. However, it is the developer’s responsibility to make every effort and reduce prime scenarios for SQL injection by implementing those diversions and mechanisms at their disposal.

Here are notable suggestions to reduce the risk of SQL injection from the OWASP SQL Injection Prevention Cheat Sheet. Be sure and visit it for complete detailing example uses in practice (see cited article).

Primary Defenses:

• Option 1: Use of Prepared Statements (with Parameterized Queries)
• Option 2: Use of Stored Procedures
• Option 3: White List Input Validation
• Option 4: Escaping All User Supplied Input

Additional Defenses:

• Also: Enforcing Least Privilege
• Also: Performing White List Input Validation as a Secondary Defense

Recommended Reading:

I’ve included additional articles with a load of information for further study and awareness:

• What is SQL injection? This oldie but goodie can make your web applications hurt
• SQL injection (Wikipedia)
• SQL Injection (CLOUDFLARE)
• SQL Injection (w3schools.com)
• SQL Injection Prevention Cheat Sheet
• Testing For SQL Injection
• SQL Injection Vulnerabilities and How to Prevent Them
• SQL Injection Cheat Sheet

Postgres Role Privileges

We have a saying for something along the lines of “We are our own worst enemy.”

We can definitely apply it to working within the PostgreSQL environment. Neglect, misunderstanding, or lack of diligence are just as much an opportunity for attacks and unauthorized use as those purposely launched.

Perhaps even more so, inadvertently allowing easier access, routes, and channels for offending parties to tap into.

I’ll mention an area that always needs revaluation or reassessment from time to time.

Unwarranted or extraneous role privileges.

• SUPERUSER
• CREATROLE
• CREATEDB
• GRANT

This amalgamation of privileges is definitely worth a look. SUPERUSER and CREATROLE are extremely powerful commands and would be better served in the hands of a DBA as opposed to an analyst or developer wouldn’t you think?

Does the role really need the CREATEDB attribute? What about GRANT? That attribute has the potential for misuse in the wrong hands.

Heavily weigh all options prior to allowing roles these attributes in your environment.

Strategies, Best Practices, and Hardening

Below is a list of useful blog posts, articles, checklists, and guides that returned for a ‘year back’ (at the time of this writing) of search results. They are not listed in any order of importance and each offer noteworthy suggestions.

• Simple Ways to Protect Your Postgres Servers from an Unlikely Attack Vector: A Rogue Picture of Scarlett Johansson
• Helping to Secure your PostgreSQL Database
• Don’t be hard-headed… Harden your PostgreSQL database to ensure security
• How to Secure your PostgreSQL Database – 10 Tips
• Securing PostgreSQL From External Attack
• PostgreSQL Privileges and Security – Locking Down the Public Schema

Conclusion

In this blog, we have gone through the security issues that concern PostgreSQL administrators all across the globe: those include SQL injection which is the holy grail of all security incidents, slip-ups in basic approaches to handling data securely such as failing to properly tighten permissions across your infrastructure, and also some lesser-known security problems that might be overlooked. When the security of our data is concerned, it’s critical that we take and apply advice from trusted parties such as Imperva and the like which provide us with ways to protect ourselves both from basic attacks and from 0-days (exploits that have not yet been known before) alike, because reputable advice means a good future for our databases and infrastructure as a whole.

Do keep in mind that the security measures we need to take would heavily depend on the vulnerabilities we want to fend off, but in general, knowing all of the necessary defenses to fend off SQL injection, properly using privileges, and always trying to reduce our risk level pertaining to vulnerabilities is crucial. Staying up-to-date with what’s happening in the PostgreSQL security space will do us wonders as well: we can only defend our servers (and, consequently, our data) well if we know what the attackers might be after.

Should you be searching for more best practices on improving the security or performance posture of your PostgreSQL installations, turn to ClusterControl: as the tool is developed by top-notch database experts from all over the world, it will make your databases sing in no time. The product also comes with a 30-day free trial, so make sure you don’t miss out on trying all of the features that ClusterControl can offer for your business and give it a shot today.

For more content on how you should go about securing your PostgreSQL database instances, turn to the Severalnines blog for advice: learning how to automate security audits for PostgreSQL will certainly be a step in the right direction. Make sure to follow us on Twitter, LinkedIn, and subscribe to our RSS feed for more updates, and we will see you in the next one.