Introduction to Postgres Extension Development
Possibly the most notorious benefit to running a Postgres cluster is access to the vast ecosystem of extensions, including procedural languages, foreign data wrappers, index types and storage systems, handy utility functions and much more. Extensions run the gamut from columnar table storage, efficient vector functions, or BM25 fulltext search analytics, to more trivial augmentations like UUIDv7.
But what’s involved in actually developing an extension? What arcane Postgres APIs need to be invoked? How does installation work? Does it take a genius? This article will attempt to answer all of those questions and a few more besides, and hopefully by the end, you too can achieve immortality by contributing a useful feature to Postgres.
Choosing a Topic
I’ve written two relatively simple SQL-only extensions in the past. This isn’t uncommon, as packaging up a few PL/pgSQL functions into a utility library is a convenient way to distribute them. Since the possible subject matter is so vast, what might be an interesting piece of functionality to add using an extension? Some DBAs might want to prevent users from executing DDL for instance, so would that be something we could do?
More astute readers probably already know that it’s possible to reject DDL using a Postgres Event Trigger like this:
CREATE OR REPLACE FUNCTION f_block_ddl()
RETURNS event_trigger AS $$
BEGIN
RAISE EXCEPTION 'DDL is not allowed on this server.';
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER t_block_all_ddl
ON ddl_command_start
EXECUTE FUNCTION f_block_ddl();
Ironically however, the DROP EVENT TRIGGER
command itself will not invoke this trigger. What if we wanted a more tightly coupled method, directly embedded in Postgres itself through an extension written in C? How might that change things?
Getting Things Under Control
According to the Postgres extension documentation, all extensions require a control file to define various bits of metadata about the extension itself.
So let’s create a folder for the extension:
mkdir noddl
cd noddl
And create a noddl.control
file with these contents:
comment = 'The Infamous DDL Blocker'
default_version = '1.0'
module_pathname = '$libdir/noddl'
This is essentially the bare minimum necessary for a control file. It provides the name of the extension, its version, and the path where the library itself may be found. In this case, the library path is just the default $libdir
location for a library named “noddl”.
Now Postgres knows what to do with our fancy new extension when it gets installed.
Preparing the Environment
Since this will be a C extension, we need to prepare the development environment a bit more. A Postgres build environment requires a minimum of gcc, make, and the Postgres development headers. This is all completely operating system and distribution dependent, so doing this is beyond the scope of this guide.
However, the Postgres Download repository usually includes development packages for all supported operating systems. If this were a Debian, Ubuntu, or Mint system for example, adding Postgres development packages is one convenient command away:
sudo apt install postgresql-server-dev-17
Red Hat, Fedora, or Rocky systems may do this instead:
sudo dnf install postgresql17-devel
Once the postgres development headers are installed, double-check the availability of the pg_config
command, as this will guide the build and install process for our extension.
pg_config --pgxs
The pgxs
flag should display the location of the extension Makefile that will do most of the hard work of setting header paths, installing to the correct library directory, and so on.
It’s also a good idea to have a local copy of the Postgres source itself. Postgres C extension development often depends on invoking otherwise undocumented internal functions, referencing multiple structs, and calling several interface macros. The best way to do this is to examine the source code where these things are defined, as there is often an incredibly helpful comment heading or even a README which explains things in greater detail.
So go to GitHub and obtain a copy:
git clone git://git.postgresql.org/git/postgresql.git
Trust me, you’ll be very glad you did.
Making Everything Possible
Speaking of makefiles, it’s time to create one for the noddl extension. The PGXS documentation provides a sample Makefile along with an explanation of all available makefile parameters. Using that as a guide, our Makefile
should look something like this:
MODULES = noddl
PGFILEDESC = "Extension to block DDL for no reason."
EXTENSION = noddl
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
Similarly to the control file, we’re really just describing the extension. Note that the last three lines in the file are there to include the Postgres extension makefile which does all of the real work. There’s nothing better than delegating responsibilities when you don’t really know what you’re doing.
Start Your Engines
There are two critical components to a Postgres C extension.
The
PG_MODULE_MAGIC
macro. Don’t worry about this; it’s magic!A
PGinit
function to act as an entrypoint into the extension for setting hooks, creating GUCs, launching worker processes, and so on.
Given this module will be blocking DDL, there should be a convenient way to enable and disable it so superusers can execute DDL when necessary. That means we need at least one GUC. If we take this into account, an early skeleton for the extension will look something like this:
// Standard set of includes for an extension.
#include "postgres.h"
#include "utils/builtins.h"
#include "utils/elog.h"
#include "utils/guc.h"
#include "tcop/utility.h" // We'll need this later
// The module code
PG_MODULE_MAGIC;
static bool deny_ddl = false;
void
_PG_init(void)
{
DefineCustomBoolVariable(
"noddl.enable",
"Deny All DDL statements",
NULL,
&deny_ddl,
False, // Disabled by default.
PGC_SUSET, // Only allow superusers to modify
0, // No flags for this GUC
NULL, NULL, NULL // No variable hooks necessary
);
MarkGUCPrefixReserved("noddl");
}
This is actually a functional extension already. The only thing it does right now is set the noddl.enable
GUC, but this will successfully compile and install as a functional Postgres extension.
The next thing we should do is make our extension useful.
Intercepting DDL Commands
Understanding how to trap DDL statements is easier said than done. We know that all statements must be parsed before they’re dispatched to whatever part of the engine that will execute them. That means we need to add some kind of function to the list of hooks Postgres calls during that process.
It turns out that one of the categories the Postgres dispatcher uses is reserved for utility commands. If we check out the Postgres source, there’s even a test module which specifically leverages this functionality: test_oat_hooks. If we examine test_oat_hooks.c a bit, we’ll find this code in REGRESS_utility_command:
if (REGRESS_deny_utility_commands && !superuser_arg(GetUserId()))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("permission denied: %s", action)));
This code essentially checks a custom GUC to decide whether or not to block statements at all, and will only block commands for non-superusers. If both of these criteria match, it throws an error, and that’s really the extent of the function. This example demonstrates how to indiscriminately reject all utility commands, but it’s the first step to isolating DDL events.
How do we isolate DDL specifically? If we look for the string “DDL” in the Postgres source code, src/backend/tcop/utility.c stands out dramatically. The GetCommandLogLevel
function categorizes all command tags into various categories for the log_statement GUC. The REGRESS_utility_command
signature includes a PlannedStmt
parameter which includes a utilityStmt
field. If we pass that into the GetCommandLogLevel
function, we only want statements identified as LOGSTMT_DDL
.
If we take that into consideration, blocking DDL looks something like this:
Node *parsetree = pstmt->utilityStmt;
if (deny_ddl && GetCommandLogLevel(parsetree) == LOGSTMT_DDL)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("DDL is not allowed on this system."),
errhint("Set the noddl.enable GUC to false to continue.")));
The particular hook we want to set is named ProcessUtility_hook
. Assuming our callback function is named noddl_ProcessUtility
, we just need to add this to the end of our PGinit
function:
ProcessUtility_hook = noddl_ProcessUtility;
If we stopped here, our hook function would be fully operational and our work would be done. Unfortunately it’s not quite that simple.
Being a Good Neighbor
Unlike some extensible software, Postgres does not provide utility functions to register hooks in some kind of dedicated callback stack. Instead, it defines several global variables which extensions may choose to override, one of which we set in the previous section. However, these global variables may be manipulated by any extension loaded into the process environment, which could represent a user session, background worker, or some other kind of mixed context.
What if shared_preload_libraries
is set like this:
shared_preload_libraries = "pg_stat_statements, noddl"
Now what happens if the noddl extension sets the ProcessUtility_hook
hook? In this case, it would essentially deactivate the pg_stat_statements extension! What we really need to do is preserve any previously set hook and propagate the call in a kind of simulated stack. Every well-behaved extension does this, so in theory every extension’s hooks are eventually honored. That means we need another global variable:
static ProcessUtility_hook_type next_ProcessUtility_hook = NULL;
Then we need to preserve any previously defined ProcessUtility hook at the end of PGinit
before overriding it:
if (ProcessUtility_hook)
next_ProcessUtility_hook = ProcessUtility_hook;
ProcessUtility_hook = noddl_ProcessUtility;
And finally, the full body of our own ProcessUtility function hook needs to physically invoke either the default Postgres ProcessUtility function, or a previously defined hook:
static void
noddl_ProcessUtility(
PlannedStmt *pstmt,
const char *queryString,
bool readOnlyTree,
ProcessUtilityContext context,
ParamListInfo params,
QueryEnvironment *queryEnv,
DestReceiver *dest,
QueryCompletion *qc)
{
Node *parsetree = pstmt->utilityStmt;
// Reject DDL if enabled
if (deny_ddl && GetCommandLogLevel(parsetree) == LOGSTMT_DDL)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
errmsg("DDL is not allowed on this system."),
errhint("Disable the noddl.enable GUC to continue."))
);
// Call downstream hooks
if (next_ProcessUtility_hook)
next_ProcessUtility_hook(
pstmt, queryString, readOnlyTree, context, params,
queryEnv, dest, qc
);
else
standard_ProcessUtility(
pstmt, queryString, readOnlyTree, context, params,
queryEnv, dest, qc
);
} // noddl_ProcessUtility
In an ideal world, Postgres would handle hook stacks directly, if only to prevent one misbehaving extension from ruining it for everybody. It’s a bit ironic that our extension has less of its own code than calls to maintain an unbroken hook chain.
Some Assembly Required
Believe it or not, that is the entire extension. Who knew writing a C extension for Postgres would be so simple? Since we already have a makefile, the final steps are to make, install, and activate the extension. Let’s start with building it:
make clean
make
sudo make install
That will deposit the extension in the appropriate library directory, and copy the control file to the directory Postgres uses to track available extensions. Next we activate the extension by adding it to shared_preload_libraries:
shared_preload_libraries = "noddl"
And finally, restart Postgres. Debian-based systems would use a command like this:
sudo systemctl restart postgresql@17-main
Red Hat family systems would use a command like this:
sudo systemctl restart postgresql-17
Or you can do it the old-fashioned way:
pg_ctl -D /path/to/pgdata restart -m fast
Once Postgres restarts, the noddl extension is available and active. But remember, our GUC is disabled by default, so we need to enable it. Connect to Postgres as a superuser and issue these commands:
ALTER SYSTEM SET noddl.enable = true;
SELECT pg_reload_conf();
Then attempt to execute literally any DDL statement:
postgres=# CREATE TEMP TABLE foo(id INT);
ERROR: DDL is not allowed on this system.
HINT: Disable the noddl.enable GUC to continue.
How rude! Fortunately we used PGC_SUSET
for this GUC, meaning superusers can override it at the session level:
postgres=# SET noddl.enable = false;
SET
postgres=# CREATE TEMP TABLE foo(id INT);
CREATE TABLE
Unlike an event trigger, it’s not possible to circumvent this extension by disabling the event_triggers GUC. If we remove the noddl.enable
GUC, the only way to enable DDL again is to unload the extension itself. There are rare environments that require structurally static deployments, and this would be one way to enforce that restriction.
Conclusion
Postgres extensions can leverage nearly any function, struct, macro, callback hook, and several global session variables and specialized APIs found in the Postgres source. It’s an incredible amount of flexibility and power, and using it requires a certain amount of restraint and responsible governance. There’s also a certain gleeful madness when you realize just how deep the Postgres extension rabbit-hole actually is. Oh, the things we could do!
The noddl extension demonstrated here is comparatively trivial, almost pointless, and barely scratches the surface of Postgres extensibility. There’s a reason over 1000 extensions exist for Postgres, and the list grows longer every single day. Short of collaborating with the core team to patch the Postgres source itself, it’s the easiest way to add functionality to Postgres.
We at pgEdge encourage you to join us in the Postgres extension revolution, where the sky and your creativity are the only limits. What will you add? We’re excited to find out!