5mins of Postgres E31: Postgres security patch release, spotting vulnerable extensions and securing the public schema
Today, we walk through the latest Postgres patch releases and a security bug that got fixed. We'll also talk about extension security in Postgres, as well as the public schema and a change in Postgres 15 to make it more secure.
Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel.
- Latest PostgreSQL patches
- Extension scripts replace objects not belonging to the extension: bug fix
- Use pgspot to detect problematic vulnerabilities in PostgreSQL extension scripts
- Use pghostile for PostgreSQL pen testing
- Postgres 15 default: Revoke PUBLIC CREATE from public schema
- What we have discussed in this episode of 5mins of Postgres
Transcript
Let's jump in.
Latest PostgreSQL patches
We'll start with this announcement for the new Postgres patch releases that came out last week. First of all, an important note that was mentioned in the beginning of this announcement is that the Postgres 10 end of life is coming up. If you're still running Postgres 10 in a production environment on November 10, 2022, which is roughly three months from now, you will actually be end of life, as in out of support. This means you should really start upgrading to newer Postgres versions now, if you haven't already.
If you're still running Postgres 10 in a production environment on November 10, 2022, you will actually be end of life, as in out of support.
Extension scripts replace objects not belonging to the extension: bug fix
This security vulnerability relates to an extension script being able to replace objects or affect objects that don't belong to the extension. This is a problem that has existed in Postgres for a while, but it was only discovered recently by Sven Klemm who reported this to the Postgres project. It relates to an extension script calling CREATE OR REPLACE or CREATE IF NOT EXISTS commands. These commands can cause the extension to take ownership of an object that wasn't supposed to be owned by the extension. This will then potentially cause arbitrary code execution of attacker controlled code.
It requires a few prerequisites to be effective.
- You have to have somebody who has permission to create non temporary objects in at least one schema. We'll come back to this, but usually this is a problem with the public schema.
- We'll also need an administrator to create or update an affected extension that has one of these commands,
- Lastly, we also need to get somebody to actually use one of these objects targeted by these commands.
It requires quite a few steps to be a problem, but I would be extra careful and make sure to update to this new patch release as fast as you can.
Use pgspot to detect problematic vulnerabilities in PostgreSQL extension scripts
Sven Klemm who reported this bug to the Postgres project also wrote a very useful tool called pgspot. Amongst other things pgspot actually detects problematic extension scripts that have an issue like this. pgspot is something you could run on extensions that you write yourself or maybe extensions that you are considering to use in your own system. There is a very useful list of 17 different vulnerabilities that pgspot detects.
To give you an example, the top vulnerability on the list is about unqualified operators. This is a typical Postgres schema security issue. Let's say you have a statement that does SELECT, and then it's selecting from one column, another column and it uses the + operator to combine these two. The + operator ultimately calls a function. What may not be obvious here is that operators can also be schema qualified. If you control the search path, you're able to get somebody to actually execute an operator that's in a different schema than you would expect.
Use pghostile for PostgreSQL pen testing
On the notion of schemas, I also want to point out pghostile. You can use this to battle test your Postgres system against a common problem, where you can create functions in the public schema. The public schema is in a search path and then you can trick a super-user to run code from public instead of pg_catalog.
Imagine a built-in function, let's say unnest for example. If you create an unnest function that's slightly more specific than a pg_catalog function, then Postgres would actually prefer to run that function instead of the built-in function and so you could trick an administrative user into executing these functions under their account, but with code of your choice.
pghostile creates those functions that will then cause you to accidentally run the wrong code. This is something that you could do as part of a pen test. For example, you could use pghostile to exercise if your code that runs as an administrative user is subject to these problems.
Postgres 15 default: Revoke PUBLIC CREATE from public schema
Postgres 15 actually has a very helpful change. A lot of what was reported here today is related to the fact that the public schema before Postgres 15 used to be writeable by anybody who can connect to a database. You may not know this, but if you have public schema active, as you might have today on a standard Postgres installation, as long as somebody can connect to the database, they can just go ahead and create objects like a table or function in that public schema.
You can already remove this privilege from users before Postgres 15. But Postgres 15 makes it a default where this public creation privilege is removed from the public schema. The idea is: in the public schema, if I'm a database owner, I can still keep creating objects there. But if I'm an unrelated user, I need to be explicitly granted that permission. This really improves the default security and will be a systematic improvement from the mentioned vulnerabilities today.
Thanks for listening in today. This was 5mins of Postgres E31. Subscribe to our YouTube channel and follow us on Twitter to get notified about next week's episode.