PostgreSQL's Security Compared to MySQL, etc

Question

Having been confronted with some bold claims about PostgreSQL's insecurity (while hailing MySQL's security) I'd like to get someone else's opinion:

• "PostgreSQL is insecure because of multiselects" - I'd assume `multiselects` are what I'd call `subselects`, but I might be wrong. Current MySQL versions support subselects, but according to [1] some libraries might not support or might have disabled them. Could that be the reason for the claim or am I overlooking something here?
• "SQL injections are the easiest to exploit with PostgreSQL" - IMHO SQL injections are an application / library problem and are simply valid SQL queries, so there is no real difference between databases, right?!
• "I love PostgreSQL for getting root permissions as it has so many security holes" - first I'd assume PostgreSQL's security track record is about as good as MySQL's (couldn't really find much on this)? Secondly running PostgreSQL as root is simply a stupid idea. Or is there anything valid in this?

I'd have said that PostgreSQL is more security aware than MySQL (supporting roles, more authentication methods,...), but that the database itself has generally a very limited impact on the security of an application. Or am I overlooking any arguments here?

[1] Is MySQL more resistant to SQL injection attack than PostgreSQL (under Perl/DBI)?

PS: Both MySQL and PostgreSQL are great products - no need for any non-security related discussions ;-)

Answer 1

"By default, PostgreSQL is probably the most security-aware database available ..."

Database Hacker's Handbook

PostgreSQL isn't unsecure because of multi-query-statements, that's normal functionality but it isn't available in older MySQL-drivers. The MySQLi-driver also supports multi-query-statements. SQL Server, Oracle, DB2 and almost all other databases have this option, MySQL was just very late to implement it. Being late, doesn't mean "safe".

SQL injection is a major error made the programmer, not by the database. The major problem sits behind the keyboard, that's the one to blame.

Use prepared statements and STOP trusting userinput, that's how you avoid SQL injection and other security problems. Stored procedures can also help to lower the impact of SQL injection, these are very ease to use in PostgreSQL. Check also quote_ident() when you have dynamic table- or columnnames in your SQL. MySQL lacks functions like this.

PostgreSQL has ROLES and inherited roles to set and maintain permissions. If you give away superuser permissions (root) to everybody, you create problems. If you don't, you're safe. There are no known bugs in superuser permissions, the claim about security holes in these permissions sounds like FUD because there is no proof.

Did you check SE PostgreSQL? That's security on a higher level. PostgreSQL version 9.1 (beta at this moment) has new options for SE as well. MySQL can only dream about this level of security.

Answer 2

It's my personal opinion that security debates with different database technologies are often entirely unfounded and in many cases, those who are quick to point the finger at one or the other likely fail to realize that the reason for a data spillage wasn't because of the database, but because they didn't properly secure their application, but can't admit fault, thus placing blame elsewhere. At least that's every security debate I've had thus far over any database technology.

A good example, SQL injection is not the database fault at all, ever. SQL is a standardized language, accepted by both MySQL and PostgreSQL (and Oracle, and others...). SQL injection is the manipulation of the Structured Query Language, not a server security flaw. The fact that the application did not properly sanitize input is the reason for it. You can't argue that one database that uses the same standardized language is any less secure against unintended query manipulation than another database that uses the same techonology, so whoever told you that SQL injection is more of a problem with one of these two databases clearly doesn't understand what exactly SQL injection is.

With regards to running PostgreSQL as root, you shouldn't run either as root. Running a service on a server as the root user is always a bad idea, again, nothing related to the servers.

I have very little experience with PostgreSQL, but I will say that MySQL has an outstanding permission system in place that allows users to be delegated a set of available commands, on a specific list of databases, on a select list client hosts. PostgreSQL may be done differently than that, but I'd be hard pressed to accept that one's security when related to authentication and user accounts is leaps and bounds over the other.

Answer 3

In complement to Scott answer (+1), I would add several things:

• SQL injections with subqueries is not possible with MySQL but you can do SQL Injection with UNION queries, a bit longer but in term of information disclosure UNION injections is a must. And SQL injection is most often use for information disclosure, more than for injection DROP table foo; instructions.
• PostgreSQL dissalow inter-base queries, unless you use dbi_connect special behaviours. So in terme of databases separation it's better.
• PostgreSQL have a big user/group privilege management, and you can have a very fine SQL policy for all objects and data for each user or group. I would say it's even better than what you have in MySQL (but I may be wrong). And in term of database connection user credentials management you've got a really bigger tool, you can map your database users on an external SSO system for example.
• Then and it's for me the biggets point. In term of security one way to have a secure and robust database is to limit the application entry points and manage data integrity and heavy data manipulations in the database. If you restrict your application entry points to some stored procedures and views, some stored statements, and then do the important stuff in some other stored procedures & triggers, or if you display only views to users, and use rules to do the real write jobs, etc. See what I mean? Well to build this sort of application PostgreSQl is really the best of the two.

Answer 4

Putting this here as a comment isn't a good place for it:

A multiselect is a query like:

mysql_query("SELECT x FROM y; SELECT p FROM q;");

Two seperate queries, one single query call. It's the classic SQL injection scenario, where user-provided data executes a completely different query than the coder had intented, e.g. The Bobby Tables attack.

MySQL/PHP are immune to this only by virtue of MySQL's driver not allowing such constructs. It's still totally vulnerable to sub-query injections, but it won't allow two totally independent queries in the same statement.

Answer 5

The default PostgreSQL installation prevents accessing the database in very rigorous ways via pg_hba.conf file. It is up to the system administrator how the database will be exposed to the outside world. Once it is configured, it is up to the application to prevent DB "attacks".