There are a lot of languages available for authoring Postgres functions, but there’s nothing quite like the the classic PL/pgSQL. It’s SQL! It’s not SQL! It’s a kind of horrifying mutant crossbreed suitable only for terrifying small children and generating complex reports from a cavalcade of dubious sources! And deep within its twisted entrails is an often overlooked feature usually only available in far more mature entities.
That’s right, it’s obvious we’re referring to the ASSERT statement.
Connecting to a Postgres database can be a headache for end users and DBAs alike. Not because of the work involved, but the general irritation of managing passwords—an issue common to any system that requires authentication controls.
The user wants to say, “Who am I? None of your business!”
While the database is more comfortable with, “Go away, before I taunt you a second time!”
Well, there’s some middle ground everyone can enjoy, and a few Postgres-specific caveats which add a bit of flavor to the whole interaction.
A few days ago, a developer came to me with that inevitable scenario that every DBA secretly dreads: a need for a dynamic table structure. After I’d finished dying inside, I explained the various architectures that could give him what he needed, and then I excused myself to another room so I could weep silently without disturbing my coworkers. But was it really that bad? Databases have come a long way since the Bad Old Days when there were really only two viable approaches to table polymorphism.
In a heterogeneous database environment, it’s not uncommon for object creation and modification to occur haphazardly. Unless permissions are locked down to prevent it, users and applications will create tables, modify views, or otherwise invoke DDL without the DBA’s knowledge. Or perhaps permissions are exceptionally draconian, yet they’ve been circumvented or a superuser account has gone rogue. Maybe we just need to audit database modifications to fulfill oversight obligations. Whatever the reason, Postgres has it covered with event triggers.
Ah, users. They log in, query things, accidentally delete critical data, and drop tables for giggles. Bane or boon, user accounts are a necessary evil in all databases for obvious reasons. And what would any stash of data be if nobody had access? Someone needs to own the objects, at the very least. So how can we be responsible with access vectors while hosting a Postgres database? We already covered automating grants, so let’s progress to the next step: building a “best practice” access stack.
