Today, Planet PostgreSQL was switched over from http to https. Previously, https was only used for the logged in portions for blog owners, but now the whole site uses it. If you access the page with the http protocol, you will automatically be redirected to https.
As part of this, the RSS feeds have also changed address from http to https (the path part of the URLs remain unchanged). If your feed reader does not automatically follow redirects, this will unfortunately make it stop updating until you have changed the URL.
In a couple of days we will enable HTTP Strict Transport Security on the site as well.
We apologize for the inconvenience for those of you who have to reconfigure your feeds, but we hope you agree the change is for the better.
We've just flipped the switch on www.postgresql.org to be served on https only. This has been done for a number of reasons:
We have not yet enabled HTTP Strict Transport Security, but will do so in a couple of days once we have verified all functionality. We have also not enabled HTTP/2 yet, this will probably come at a future date.
Please help us out with testing this, and let us know if you find something that's not working, by emailing the pgsql-www mailinglist.
There are still some other postgresql.org websites that are not available over https, and we will be working on those as well over the coming weeks or months.
The POODLE attack on https (the attack is about https, the vulnerability in SSL, an important distinction) has received a lot of media attention lately, so I figured a (very) short writeup was necessary.
The TL;DR; version is, you don't have to worry about POODLE for your PostgreSQL connections when using SSL.
The slightly longer version can be summarized by:
PostgreSQL can, as many other products, use SSL to secure client/server communications. It can be configured to be optional or required, to require a client certificate, or even to use the SSL client certificate to log in. However, the DBA tools are currently a bit lacking in this regard. In particular, there is no way for a DBA to see what SSL parameters are in force (or indeed if SSL is enabled at all) for an already existing connection.
There are multiple ways to see the status of a specific connection (such as the libpq PQgetssl() function, the psql startup message or the sslinfo module. Unfortunately all these methods have one thing in common - they are only available to the process making the connection to the database, not to a DBA inspecting the system from the outside.
9.4 will make it a little bit better, because log_connections now include SSL information when the user connects, similar to:
LOG: connection authorized: user=mha database=postgres SSL enabled (protocol=TLSv1.1, cipher=ECDHE-RSA-AES256-SHA)
But this can still be a bit of a pain to figure out for existing connectioons of course.
To deal with this problem, I've written a small PostgreSQL extension called pg_sslstatus. When loaded using shared_preload_libraries it will keep track of the SSL status for each connection, and expose it through a system view named pg_sslstatus like this:
postgres=# SELECT * FROM pg_sslstatus; pid | ssl | bits | compression | version | cipher | clientdn -------+-----+------+-------------+---------+----------------------+---------------------------------------------------------- 27286 | t | 256 | f | TLSv1 | ECDHE-RSA-AES256-SHA | 26682 | t | 256 | t | TLSv1 | ECDHE-RSA-AES256-SHA | /C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=magnus 26693 | f | | | | | (3 rows)
It will include one row for each connection, and can then be joined with either pg_stat_activity or pg_stat_replication to view the SSL data in a bigger context:
postgres=# SELECT datname, usename, client_addr, ssl, cipher FROM pg_stat_activity INNER JOIN pg_sslstatus ON pg_stat_activity.pid=pg_sslstatus.pid; datname | usename | client_addr | ssl | cipher ----------+---------+-------------+-----+---------------------- postgres | mha | 127.0.0.1 | t | ECDHE-RSA-AES256-SHA postgres | mha | | f | pgweb | pgweb | | f | (2 rows)
The extension is available from my github account today for existing version of PostgreSQL, under the standard PostgreSQL license. My plan is to work on having a similar view included in PostgreSQL 9.5 by default, but it's unfortunately too late to include it in 9.4 at this point.
Is your PostgreSQL installation vulnerable to the Heartbleed bug in OpenSSL? The TL;DR; version is "maybe, it depends, you should read this whole thing to find out". If you are vulnerable, it is a high risk vulnerability!
The slightly longer version is that it will be vulnerable if you are using SSL, and not vulnerable if you are not. But the situation is not quite that easy, as you may be using SSL even without planning to. PostgreSQL also not provide any extra protection against the bug - if you are using SSL, you are vulnerable to the bug just as with any other service.
As the bug is in OpenSSL, however, what you need to get patched is your OpenSSL installation and not PostgreSQL itself. And of course, remember to restart your services (this includes both PostgreSQL and any other services using SSL on your system). You will then have to consider in your scenario if you have to replace your SSL keys or not - the same rules apply as to any other service.
PostgreSQL by default ships with SSL turned off on most platforms. The most notable exception is Debian and derivatives (such as Ubuntu), which enable SSL by default.
If SSL is disabled globally, your installation is not vulnerable.
The easiest way to check this is to just use a simple SQL query:
postgres=# show ssl; ssl ----- off (1 row)
If this parameter returns off, you are not vulnerable. If it returns on, you are.
If you do not need SSL, the easiest fix is to turn this off and restart PostgreSQL. This also brings additional benefits of not paying the overhead of encryption if you don't need it. If you actually use SSL, this is of course not an option.
If you have installed PostgreSQL using a package based system, such as yum (from redhat/fedora or from yum.postgresql.org), apt (from debian/ubuntu/etc or from apt.postgresql.org), FreeBSD ports etc, it is up to your operating system to provide a patch. Most major distributions have already done this - you just need to to install it (and restart your services!). If your distribution has not yet updated, you need to convince them to do so ASAP.
If you are using a PostgreSQL installation package that bundles OpenSSL, you need an updated version of this package. The most common example of this is the EnterpriseDB Graphical Installers primarily used on Windows and Mac. We expect a new version of these installers to be released within a day or a few.
Postgres.app is also vulnerable and needs an update, but is normally not used for servers.
The OpenSCG separate download packages are also vulnerable.
For each of these you will have to wait for an updated package to show up in the next couple of days. All package maintainers have been notified, so it's only a matter of time.
Per the www.postgresql.org download pages we do recommend that you always use the "package manager" system for any platform where this is supported, which means most modern Linux or BSD distributions. If you are currently using one of the above installers on these platforms, a quick fix before the packages are out would be to switch to one of the "package manager" platforms that rely on the operating system update process. This may or may not be an option of course, depending on the complexity of the installation.
If you are using a platform where this is not available (such as Windows), your only option is to wait.
In PostgreSQL, the SSL negotiation happens before pg_hba.conf is matched. And the vulnerability in OpenSSL is in the negotiation phase. For this reason, even if you have restricted access to your server using pg_hba.conf IP filter rules, or your pg_hba.conf specifies only hostnossl records, this does not protect you.
Obviously, if you have an IP level firewall, either at the host or on the network, that will protect you. But pg_hba does not.
The pgcrypto module in PostgreSQL uses OpenSSL to provide encryption functions when available. Since the vulnerability is specifically in the protocol negotiation, use in pgcrypto is not vulnerable to this issue.
I have received a lot of questions since the announcement that we are temporarily shutting down the anonymous git mirror and commit messages. And we're also seeing quite a lot of media coverage.
Let me start by clarifying exactly what we're doing:
There has been some speculation in that we are going to shut down all list traffic for a few days - that is completely wrong. All other channels in the project will operate just as usual. This of course also includes all developers working on separate git repositories (such as a personal fork on github).
We are also not shutting down the repositories themselves. They will remain open, with the same content as today (including patches applied between now and Monday), they will just be frozen in time for a few days.
So why are we doing this? It's pretty simple - it takes a few days to prepare packages for all our supported platforms, to do testing on these, and get them ready for release. If we just committed the security fixes and then proceeded with the packaging, that would mean that anybody who was following our repository would be able to see those fixes a few days before the fixes were available to the majority of the users. That also means that anybody looking for the flaw would get a few days of time when the full details of the bug was in the open (since the fix was applied in public), but yet all the installations around the world would be unpatched and left wide open for exploit.
By restricting access to view the patches until release time, we close this window. Yes, the vulnerability is still in the code that is out there today. But it has been in there for a few years, and nobody (that we know of) found it in that time. Hopefully, nobody will between now and release time. But by not explicitly showing the bug, we're at least keeping that risk as low as possible while still being able to warn our users that they will need to apply the patch as soon as it's out.
We do realize that this will make some people look harder at the PostgreSQL code over the next couple of days trying to find this bug, and write an exploit for it.
I've seen a couple of comments along the line of "isn't this where you should be using a DVCS like git you're using, letting the people building the security fixes do that in a separate repository and merge it once ready, not needing to shut down the central one".
Turns out that is actually exactly what we are doing. The security fixes are mostly already developed, and as such are sitting somewhere else from the main repository. But we need at some point to merge these into the main repository, in order to let people build the packages. We only close down the repository mirroring right before this merge is done, and until the packages are ready to be released. It's not the work to develop the patch that requires the shutdown of the mirroring, it's the work to build and release packages.
The other advantage of the fact that we are using a DVCS, is that development does not stop during this time. Anybody working on a patch can keep working on it in their local copy of the repository. It's only the merge ("apply") of the patch to the upstream master branch that's going to be delayed. And that affects a much smaller group of people. Of course, it is a bit of an extra annoyance since we are currently trying to close out the open patches for the next release, but it's not a huge difference for most developers.
Yes, absolutely! We are not going to permanently hide any information, or try to obfuscate the contents of security patches (coughunlike some other players in the field).
Once the new versions are released, the git mirroring will resume. This will immediately mirror all the individual commits, including detailed commit messages showing what the bugs were (and of course including the fix itself). And we are assigning public CVE numbers to all security related bugs. At this point, the commit messages held in the queue will also be released, and appear on the pgsql-committers list for anybody who wants to read up on them. And of course, complete tarballs with the full release will be made available alongside the binary packages.
It's a difficult balance between keeping things open so that everybody can verify what's going on, and keep exploit information out of the hands of the bad guys. Our goal with what we did this time is to minimize exposure to our users for a potentially very bad exploit (depends on the scenario for each individual install, of course), while we work with downstream distributions to make sure our fixes can reach the users as quickly as possible.
Is it the right way? We don't know. It's the first time we do this, and it's not something we plan to do as a general process. We'll of course have to evaluate whether it was successful once it's all done.
Finally, for those of you who are our users, a short repeat. A new release is planned next week, current schedule is release on April 4th. We advise all users to review the security announcement and apply the fix as quickly as possible if the vulnerability is targetable in your environment. The patch will require installation of new binaries and a restart of the database, but no further migration work than that.
We take the security of our users seriously, and try our best to protect them as much as possible. It's out belief that the tradoffs we've done here are in their best interest. The future will tell, of course, if that belief is correct.
If you are running on any of the 8.3 versions, you should upgrade your system to 8.4 or newer immediately. If this cannot be done immediately, you should at least upgrade to 8.3.23 in the meantime. When upgrading, it's likely worth upgrading to 9.2 or at least 9.1, and not just to 8.4 which only has a bit over a year before it also goes end of life.
Any users of PostgreSQL 9.2, 9.1, 9.0 or 8.4 should still look at upgrading their systems to the latest minor release as of todays updates, since they contain both security and stability fixes. Minor version upgrades are, as always, just a matter of replacing your binaries and restarting the database. Automatic updates should also be out on the yum and apt repositories shortly.
If you've been to any of my pgcrypto talks, you know I'm a fan of exposing a login interface in the database. It's a great way to allow multiple systems to use the same authentication system, without being limited to a single framework. However, it can often be nice and easy to be able to use the account management system in said framework, because it's already there.
When I go to frameworks, django is my current favorite, and I do like the ability to use it's integrated admin system to edit users. For this reason, I wanted to add the ability to use a database function to authenticate other systems using the django user tables. Thanks to the fact that django choose a nicely readable text format for their hashes, and that PostgreSQL has the awesome pgcrypto library, this is pretty simple. Here's a function that will simply return true if authentication was successful, and false if not.
CREATE OR REPLACE FUNCTION django_login (_username text, _password text) RETURNS boolean AS $$ BEGIN IF EXISTS (SELECT * FROM auth_user WHERE username=_username) THEN RETURN encode(pgcrypto.digest(split_part(password, '$', 2) || _password, 'sha1'), 'hex') = split_part(password, '$', 3) FROM auth_user WHERE username=_username; ELSE RETURN 'f'; END IF; END; $$ LANGUAGE 'plpgsql';
This assumes you have installed pgcrypto in it's own schema, something I always recommend. If you haven't, just remove the schema specifier in the query.
Finally, if you use this type of authentication, remember to use SSL. And don't enable query logging...
As you can see, PostgreSQL has just released new updated versions, which include security fixes. They also contain other critical bug fixes, so even if you are not directly affected by the security issues, plan an upgrade as soon as possible.
One of the security issues that have been patched deal with NULL prefixes in SSL certificate names, a vulnerability that is basically the same one that have surfaced in a lot of different products this autumn, for example in the Mozilla suite of products. There is not really space enough to properly discuss the implications this has in a PostgreSQL environment in the release notes, so I'll try to elaborate some here - given that I wrote the fix for it.
First of all, a quick explanation of what the problem is. PostgreSQL uses OpenSSL to deal with certificates. Prior to the fixed version, we just asked OpenSSL for the name of the certificate, got back a string, and used this one. Now, if you know C coding, you know that a string is terminated by a NULL character. The bug in PostgreSQL is that we did not check the return value from this function, and make sure it returned the same value as the length of the returned string. This means that somebody could embed a NULL value in the certificate, and we would incorrectly parse and validate only the part that was before the NULL value. For example, if someone managed to get a certificate with the common name set to "postgresql.bank.com\0attacker.com", PostgreSQL would match this certificate against "postgresql.bank.com" (or "*.bank.com"), which is not correct. With the fix, the certificate will be rejected completely.
It is important to know that in order to make use of this vulnerability, the attacker needs to convince a trusted CA to sign such a certificate - which is quite obviously malicious. If the attacker cannot get the CA to hand this out, PostgreSQL will reject the certificate before we even get this far. It is arguably also a bug in the CA handling (technical or procedural) to even hand out such a certificate, and that bug need to be exploited before the one in PostgreSQL can be.
In the vast majority of cases, if not all, where PostgreSQL is deployed and actually using certificate validation, the certificates will be handed out by a trusted local CA. In which case, exploiting this vulnerability becomes much harder. This scenario is significantly different from the original scenario this bug was discovered in, which is the web browser. In the web browser case, the browser already trusts a large number of external CAs by default. PostgreSQL will trust no CAs by default (unless you are doing a debian install, in which case they put some default CAs in there - this is another reason why this is a really bad idea from a security perspective). PostgreSQL also does not prompt the user with a potentially incorrect name field on the certificate asking if this is ok or not - it will just reject the certificate if it doesn't match (correctly or incorrectly), closing another attack venue. So the bug is really only significant if you can't trust your CA - but the whole point of the CA is that it is a trusted entity...
PostgreSQL 8.4 is the first version to properly support certificate name validation, and also the first version to support client certificate authentication, both of which are vulnerable to this bug, neither of which is enabled by default. However, previous versions are also indirectly vulnerable, because they exposed the CN field of the certificate to the application for further validation. So you could have a stored procedure checking the client certificate, or just the libpq application checking the server certificate, even in earlier versions. And given the API structure, there was no way for these outside processes to know if they were being fooled or not. So if you are using an application that makes use of this on previous versions of PostgreSQL, you still need the patch - there is no way to fix the bug from the application.
The summary of this post is that this vulnerability is a lot less serious in PostgreSQL than in many other systems that had the issue. That doesn't mean it's not there, and that it should be (and have been) fixed. But it means that this vulnerability alone is likely not reason enough to rush an upgrade on your production systems - most likely you're not affected by it. On the PostgreSQL security page it is tagged with classification A, which is the highest. This is more an indication that the system we're using for classification really doesn't take these things into consideration - something we will look into for the future.
We're now up to the third day of pgcon, the first one of the actual conference - the previous ones being dedicated to tutorials. The day started with Selena, me and Dave doing a semi-improvised keynote. Well, it started with Dan saying welcome and going through some details, but he doesn't count... I doubt we actually spread any knowledge with that talk, but at least we got to plug some interesting talks at the conference, and show pictures of elephants.
Missed the start of the Aster talk on Petabyte databases using standard PostgreSQL, but the parts I caught sounded very interesting. I'm especially excited to hear they are planning to contribute a whole set of very interesting features back to core PostgreSQL. This makes a lot of sense since they're building their scaling on standard PostgreSQL and not a heavily modified one like some other players in the area, and it's very nice to see that they are realizing this.
After this talk, it was time for my own talk on PostgreSQL Encryption. I had a hard time deciding the split between pgcrypto and SSL when I made the talk, but I think it came out fairly well. Had a number of very good questions at the end, so clearly some people were interested. Perhaps even Bruce managed to learn something...
After this we had lunch, and I'm now sitting in Greg Smiths talk about benchmarking hardware. This is some very low level stuff compared to what you usually see around database benchmarking, but since this is what sits underneath the database, it's important stuff. And very interesting.
The rest of the day has a lineup of some very nice talks, I think. So there'll be no sitting around in the hallway! And in the evening there is the EnterpriseDB party, of course!
Yesterday had the developer meeting, where a bunch (~20) of the most active developers that are here in Ottawa sat down together for the whole day to discuss topics around the next version of PostgreSQL, and how our development model works. Got some very important discussions started, and actually managed to get agreement on a couple of issues that have previously been going in circles. All in all, a very useful day.