3 ms·

Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell

Ironically, the blog post shoots itself in the foot by starting with Postgres security. Postgres has very poor security compared to MySQL, and in fact, I tell companies implementing compliance policies to shift to MySQL. https://www.cvedetails.com/metasploit-modules/vendor-336/Postgresql.html https://www.cvedetails.com/metasploit-modules/vendor-336/Pos... The reasons are: - Postgres' grant model is overly complex. I haven't seen anybody maintain the grants correctly in production for non-admin read-only users. By contrast, MySQL's are grants are simple to use and simple to understand. - Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things. - Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid. - Currently Postgres is subject to around a dozen metasploit vulnerabilities that any script-kiddy can execute. The simple fact is, if you use Postgres, you almost certainly have a security compliance problem. I could make the same arguments about replication, or online schema changes, multi-master writes, or any enterprise database feature. Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO, and look at the metasploit options and see what can be done ASAP. I'd appreciate if you're itching to write a hasty response that you actually check your facts first. If you're thinking, "How is it possible that everybody else is wrong about Postgres being the best?", just remember the decade of Mongo fanboism on HN. I cringed during that era, too. Source: MySQL and Postgres DBA.

halayli 7y ago
your statements are nothing but opinions. It's also dishonest to make them sound like facts. If you care this much about security, you should have taken a look at CVE reports of each db. You'll find that historically MySql has almost double the number of vulnerabilities.
ilogik 7y ago
does running Postgres on AWS RDS mitigate some of these problems?
- DeathArrow 7y ago
  I don't know about AWS but our Postgres instances are accessible only through internal network connections. I think we are safe.
DeathArrow 7y ago
Wouldn't a competent DBA take care of those issues? It is a honest question since I don't know much about database administration.
danmaz74 7y ago
Using inflammatory language like "The simple fact is, if you use Postgres, you almost certainly have a security compliance problem" or "If you're thinking, "How is it possible that everybody else is wrong about Postgres being the best?", just remember the decade of Mongo fanboism on HN. I cringed during that era, too" really doesn't help getting the message through.
pritambaral 7y ago
> I haven't seen anybody maintain the grants correctly in production for non-admin read-only users. Your opinion against mine, but in my last project I successfully did this. I didn't even have to think about it; the client asked me if I could introduce a role for pure-read-only access and it was done in a few minutes. It helped that I'd already set up all objects, roles, and grants for non-admin access. OTOH, the privileges system in postgres has always worked when and how I've wanted it to work, unlike my experience with MySQL. ---- > Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things. > Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO From the docs: 7.1 to 9.2: "COPY naming a file is only allowed to database superusers, since it allows writing on any file that the backend has privileges to write on." (Note that there was no support for PROGRAM in these versions, which was introduced in 9.3, and thus the docs changed to ...) 9.3 to 10: "COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access." (and after this your request for grantable control was, well, granted ...) 11, 12: "COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access." And, of course, there's the classic combo of functions with SECURITY DEFINER and EXECUTE grants. For which, again, the doc has always advised: "For security reasons, it is best to use a fixed command string, or at least avoid passing any user input in it." ---- > Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid. I'm afraid I couldn't find any info about this thing you mention; and frankly, it's a pretty bold claim. Could you elaborate? Again, your opinion against mine (and many others'), but the "vaunted" engineering (and _design_) behind Postgres is solid on many fronts, from our experience of running it and using it in many contexts, including security.
xenator 7y ago
Grant model is little outdated in some aspects, but with right tools it is doable. On some level you just accept that you need to read documentation and follow it. It is just another thing that you need to care about. In case if you need it. I can little imagine case when you have access to the system and install dumps from untrusted sources. Probably it is world of untrusted PHP scripts from russian forums with stollen software. It is the world full of whole specter of pains. But I’m not agree that it should be considered as weakness. You never should restore dumps from untrusted sources ever. Such dumps can contains stored procedures that can contain code in pl/python that can do a lot of shady things. Is it a weakness or advantage of having freedom of using of python? In the world of script kiddies it is, but it is not problem of the Postgres.
yrro 7y ago
If you grant untrusted users administrative access then you're going to get owned, regardless of your chosen DB engine.
sterwill 7y ago
I've deployed, administered, and developed with PostgreSQL in a regulated, audited environment with external and internal compliance requirements (US healthcare; database contains protected health information). None of the security model issues you mention is anything like a realistic concern in that environment. And I'd love to see your sources for all your "exploits".
Flowible 7y ago
> Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things MySQL LOAD_FILE() and SELECT ... INTO OUTFILE have been used to compromise the database by copying SSH keys to the server, amongst other things. > Currently Postgres is subject to around a dozen metasploit vulnerabilities that any script-kiddy can execute Such as?
jstewartmobile 7y ago
shhhhhhh! Be quiet, and let natural selection run its course.
anarazel 7y ago
> - Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid. That never was the case (see evidence in initial commit [1]). Are you talking about CVE-2017-15099? Obviously annoying that we had that bug, but thats very far from what you claim. Since you write "I'd appreciate if you're itching to write a hasty response that you actually check your facts first." you actually follow up your own advice? > - Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things. If, and only if, the user is a superuser. It's possible to do the same in just about any other client/server rdbms. > Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO You mean, like it has been the case for ~19 years? https://www.postgresql.org/docs/7.1/sql-copy.html https://www.postgresql.org/docs/7.1/sql-copy.html "COPY naming a file is only allowed to database superusers, since it allows writing on any file that the backend has privileges to write on.". > - Currently Postgres is subject to around a dozen metasploit vulnerabilities that any script-kiddy can execute. That does not actually seem to be the case (what a surprise). And the modules that do exist are one for a (serious!) vulnerability from 2013, and others that aren't vulnerabilities because they require superuser rights. [1] https://github.com/postgres/postgres/commit/168d5805e4c08bed7b95d351bf097cff7c07dd65#diff-552e7a346d85054654b4be84c6ac5211 https://github.com/postgres/postgres/commit/168d5805e4c08bed...

halayli 7y ago

your statements are nothing but opinions. It's also dishonest to make them sound like facts. If you care this much about security, you should have taken a look at CVE reports of each db. You'll find that historically MySql has almost double the number of vulnerabilities.

ilogik 7y ago

does running Postgres on AWS RDS mitigate some of these problems?

DeathArrow 7y ago
I don't know about AWS but our Postgres instances are accessible only through internal network connections. I think we are safe.

DeathArrow 7y ago

Wouldn't a competent DBA take care of those issues? It is a honest question since I don't know much about database administration.

danmaz74 7y ago

Using inflammatory language like "The simple fact is, if you use Postgres, you almost certainly have a security compliance problem" or "If you're thinking, "How is it possible that everybody else is wrong about Postgres being the best?", just remember the decade of Mongo fanboism on HN. I cringed during that era, too" really doesn't help getting the message through.

pritambaral 7y ago

> I haven't seen anybody maintain the grants correctly in production for non-admin read-only users. Your opinion against mine, but in my last project I successfully did this. I didn't even have to think about it; the client asked me if I could introduce a role for pure-read-only access and it was done in a few minutes. It helped that I'd already set up all objects, roles, and grants for non-admin access. OTOH, the privileges system in postgres has always worked when and how I've wanted it to work, unlike my experience with MySQL. ---- > Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things. > Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO From the docs: 7.1 to 9.2: "COPY naming a file is only allowed to database superusers, since it allows writing on any file that the backend has privileges to write on." (Note that there was no support for PROGRAM in these versions, which was introduced in 9.3, and thus the docs changed to ...) 9.3 to 10: "COPY naming a file or command is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access." (and after this your request for grantable control was, well, granted ...) 11, 12: "COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access." And, of course, there's the classic combo of functions with SECURITY DEFINER and EXECUTE grants. For which, again, the doc has always advised: "For security reasons, it is best to use a fixed command string, or at least avoid passing any user input in it." ---- > Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid. I'm afraid I couldn't find any info about this thing you mention; and frankly, it's a pretty bold claim. Could you elaborate? Again, your opinion against mine (and many others'), but the "vaunted" engineering (and _design_) behind Postgres is solid on many fronts, from our experience of running it and using it in many contexts, including security.

xenator 7y ago

Grant model is little outdated in some aspects, but with right tools it is doable. On some level you just accept that you need to read documentation and follow it. It is just another thing that you need to care about. In case if you need it. I can little imagine case when you have access to the system and install dumps from untrusted sources. Probably it is world of untrusted PHP scripts from russian forums with stollen software. It is the world full of whole specter of pains. But I’m not agree that it should be considered as weakness. You never should restore dumps from untrusted sources ever. Such dumps can contains stored procedures that can contain code in pl/python that can do a lot of shady things. Is it a weakness or advantage of having freedom of using of python? In the world of script kiddies it is, but it is not problem of the Postgres.

yrro 7y ago

If you grant untrusted users administrative access then you're going to get owned, regardless of your chosen DB engine.

sterwill 7y ago

I've deployed, administered, and developed with PostgreSQL in a regulated, audited environment with external and internal compliance requirements (US healthcare; database contains protected health information). None of the security model issues you mention is anything like a realistic concern in that environment. And I'd love to see your sources for all your "exploits".

Flowible 7y ago

> Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things MySQL LOAD_FILE() and SELECT ... INTO OUTFILE have been used to compromise the database by copying SSH keys to the server, amongst other things. > Currently Postgres is subject to around a dozen metasploit vulnerabilities that any script-kiddy can execute Such as?

jstewartmobile 7y ago

shhhhhhh! Be quiet, and let natural selection run its course.

anarazel 7y ago

> - Postgres' version of upsert allowed any command to be run without checking the permissions. So the vaunted "software engineering" behind Postgres is not that solid. That never was the case (see evidence in initial commit [1]). Are you talking about CVE-2017-15099? Obviously annoying that we had that bug, but thats very far from what you claim. Since you write "I'd appreciate if you're itching to write a hasty response that you actually check your facts first." you actually follow up your own advice? > - Postgres' COPY FROM and COPY TO have been used to compromise the database by copying ssh keys to the server, amongst other things. If, and only if, the user is a superuser. It's possible to do the same in just about any other client/server rdbms. > Some constructive advice to the Postgres developers is to take a week and add grant commands to limit COPY FROM and COPY TO You mean, like it has been the case for ~19 years? https://www.postgresql.org/docs/7.1/sql-copy.html https://www.postgresql.org/docs/7.1/sql-copy.html "COPY naming a file is only allowed to database superusers, since it allows writing on any file that the backend has privileges to write on.". > - Currently Postgres is subject to around a dozen metasploit vulnerabilities that any script-kiddy can execute. That does not actually seem to be the case (what a surprise). And the modules that do exist are one for a (serious!) vulnerability from 2013, and others that aren't vulnerabilities because they require superuser rights. [1] https://github.com/postgres/postgres/commit/168d5805e4c08bed7b95d351bf097cff7c07dd65#diff-552e7a346d85054654b4be84c6ac5211 https://github.com/postgres/postgres/commit/168d5805e4c08bed...