I’ve been a Postgres DBA since 2005. After all that time, I’ve come to a conclusion that I’m embarrassed I didn’t reach much earlier: Postgres is awful. This isn’t a “straw that broke the camel’s back” kind of situation; there is a litany of ridiculous idiocy in the project that’s, frankly, more than enough to stave off any DBA, end user, or developer. But I’ll limit my list to five, because clickbait.
1. Security Schmecurity
Look, every good Postgres DBA knows that the
pg_hba.conf file that handles connection security should have these lines:
local all all trust host all all all trust
And then the Postgres developers wonder why it’s so easy to exploit. Here I am, Joe T. Hacker, and I can just do this:
psql -U postgres -h production_server secure_database
And boom! I’m in as the superuser, and I can start dropping tables, replacing tables with whatever I want, screw with data, whatever. Sloppy. Just sloppy.
2. Super Users? Super Losers
Oh yeah, and after I connect to steal sensitive data and then clean up after myself, I can’t even do this:
secure_database=# DROP DATABASE secure_database; ERROR: cannot DROP the currently OPEN DATABASE secure_database=# DROP USER postgres; ERROR: CURRENT USER cannot be dropped
Ugh! Are you kidding me? This is basic stuff, people. As a superuser, I should be able to circumvent all security, durability, and even internal consistency. That the backend code doesn’t acknowledge what a superuser really is, merely proves that the database owner is just another user. Useless.
Sure, I could use the provided tool and do it from the command line like this:
dropdb -U postgres -h production_server secure_database
But I’m already connected! Oh, or I could switch databases and then do it like this:
secure_database=# \c postgres secure_database=# DROP DATABASE secure_database;
But if I’ve hacked in, every keystroke counts. It’s not even necessary! They could just queue the action and apply it after I disconnect, right? This kind of amateur work is why I can’t justify being a Postgres DBA anymore.
3. Awkward JOIN Syntax is Awkward
I cut my teeth as a DBA starting in 1999 with Oracle. Now that is a real database engine! One of the really cool things Oracle does is overload the
OUTER JOIN syntax with a neat shortcut. Not only is the
OUTER decorator optional, but so are
RIGHT! Let’s compare the only syntax Postgres supports with the snazzy method Oracle provides, and see which one is obviously way better:
-- Postgres (bleh) SELECT a.col1, b.col2 FROM a LEFT JOIN b ON (b.a_id = a.id); -- Oracle (Nice!) SELECT a.col1, b.col2 FROM a, b WHERE a.id = b.a_id (+);
(+) bit? Just slap that on any columns that should join two tables, and it will automatically control how the join works. If it’s on the right, it’s a
LEFT JOIN, and if it’s on the left, it’s a
RIGHT JOIN. See? Totally intuitive. Sure, Oracle can use the same syntax Postgres childishly demands, but who would purposefully do that? Gross.
4. JSON and the Kitchen Sink
Everyone keeps touting JSON support as some huge benefit to Postgres. Even I did it once or twice. But upon pondering it more seriously, where does it end? What’s the next interface method du jour that we’ll be cobbling onto Postgres next?
5. Extensions are the Worst Idea Ever
This is easily the worst of the bunch, and follows naturally from the previous point. Ever since Postgres 9.1, it’s been possible to easily write extensions that are effectively bolted directly to the database engine. Any random schmo can waltz up, whip up some code in basically any language, and it will be able to do any of this. Suddenly Postgres has some “new functionality.”
Are you kidding me!? But it gets even worse! There’s a site called PGXN with a related utility for making extensions easy to find and install. I don’t even want to imagine the shenanigans! Any random idiot, including me, could just put an extension there. Just convince a DBA to install it like this:
pgxn install awesome_tool_notanexploitorbotnetiswear
Suddenly they’re pwn3d. Do you want to be pwn3d? I didn’t think so. That’s the kind of risk you run by using Postgres as your primary database engine.
I for one, have seen the light. I can’t believe I was so misled by Postgres for over a decade. How am I even employable at all, having advocated a hacker’s playground for most of my professional career? How can any of us in the Postgres community live with ourselves, knowing we’ve effectively enabled effortless system compromises in the name of functionality?
I guess I’ll go back to using Oracle like the whipped dog I am. It’s what inspired me to be a DBA, and I abandoned it in favor of this travesty. I just pray Larry Ellison will forgive me for my transgressions.
I apologize for everything, and I sincerely hope the rest of the Postgres community joins me in supplication. If our end-users and developers are magnanimous, there might yet be a future for us.