Recently at Postgres Conference 2026 in San Jose, I presented a talk called Let's Build a Postgres Extension! Since that entire presentation was primarily focused on writing a C extension while exploring the Postgres source code, I only mentioned pure SQL extensions as an aside. But what's more likely in the Postgres community in general: C devs, or people who know SQL?

It turns out that you can do a lot with functions, triggers, views, tables, and various other Postgres-native capabilities. The extension system doesn't care whether the contents are compiled C or plain SQL. It just wants a control file, a SQL script, and an optional Makefile to help with installation.

So let's build a relatively trivial extension article entirely in SQL.

What Do We Want?

First things first: we need a plan. What should this extension actually do? I wrote about blocking DDL a while back with a C extension, so why not revisit that example with SQL?

This being pure SQL, there are other handy elements we can add with very little effort, so how about:

  • A setting to enable or disable the extension.

  • A setting to allow or block superusers from executing DDL.

  • A role that allows members to bypass the DDL restriction.

  • A function to add users to the bypass role.

  • A function to remove users from the bypass role.

  • A view to see which users are in the bypass role.

  • An event trigger to actually block DDL attempts.

Rather than a simple event trigger to prevent DDL execution, we are building a kind of DDL execution management suite. That should hopefully demonstrate just how capable a purely SQL implementation can be.

Three Files and a Dream

Every Postgres extension, regardless of complexity, boils down to the same basic structure:

  • A control file to describe the extension.

  • A SQL script to create the tables, views, functions, etc.

  • An optional Makefile to copy the SQL script and control file to the right place. Unlike a C project, there's no build step for a SQL-only extension because there's nothing to compile.

Here's what our project directory looks like:

block_ddl/

├── block_ddl--1.0.sql

├── block_ddl.control

└── Makefile

Let's start with the control file. It tells Postgres the extension's name, version, description, and the settings of a handful of behavioral flags. Ours looks like this:

 # block_ddl extension

comment = 'DDL blocking for Postgres'

default_version = '1.0'

superuser = true

relocatable = false

The comment is what shows up in \dx and the pg_extension catalog view. The default_version tells Postgres which SQL script to load when someone runs CREATE EXTENSION block_ddl without specifying a version. The superuser = true flag means only superusers can install or update this extension. That's the default, but it's better to be explicit.

The relocatable = false flag deserves a quick explanation. A relocatable extension can be moved between schemas after installation with ALTER EXTENSION ... SET SCHEMA. Ours can't because the SQL script references a specific schema internally using the @extschema@ substitution token. It's still possible (and recommended) to define the schema during installation, but not afterwards.

Next comes the Makefile. For a C extension, the Makefile orchestrates compilation and linking. For a SQL-only extension, it just copies the control and SQL file to the library folder where Postgres keeps extensions. Here’s the whole thing:

EXTENSION = block_ddl

DATA = block_ddl--1.0.sql

 

PG_CONFIG = pg_config

PGXS := $(shell $(PG_CONFIG) --pgxs)

include $(PGXS)

There's normally also a MODULES line to specify C source files for compilation. Without it, make install simply copies the control file and SQL script to the correct directories. The PGXS build infrastructure handles the rest.

With the boilerplate out of the way, it's time to have some fun.

A Bit of Bookkeeping

Before we really get going, the extension needs to live in a schema. Some of the objects in that schema need to be publicly accessible. So the first thing in our file needs to look like this:

GRANT USAGE ON SCHEMA @extschema@ TO PUBLIC;

Usage just means the schema objects are visible. Users won't be able to create objects or even select from tables unless granted specifically.

After that, we need to account for the configuration settings. You may think the first choice for this is to use session variables, but that's a subtle trap. The problem here is that SQL-only extensions don't have access to the finer points of system variables, such as limiting them to superusers, system start, service reload, etc. That means there's nothing preventing users from overriding them with a simple SET statement.

The next option is a configuration table. The extension documentation says we can register these such that dumping and restoring a database retains values, and it's trivial to control table updates. So let's start our extension with this:

CREATE TABLE @[email protected]_config (
  name     TEXT PRIMARY KEY,
  setting  TEXT NOT NULL
);

INSERT INTO @[email protected]_config 
VALUES ('enabled', 'off'), ('allow_super', 'on');

SELECT pg_catalog.pg_extension_config_dump('@[email protected]_config', '');

GRANT SELECT ON @[email protected]_config TO PUBLIC;

CREATE OR REPLACE FUNCTION @[email protected]_config(p_name TEXT, p_setting TEXT)
RETURNS BOOLEAN AS
$$
BEGIN
  IF p_name IN ('enabled', 'allow_super') THEN
    UPDATE @[email protected]_config
       SET setting = (CASE WHEN p_setting = 'on' THEN 'on' ELSE 'off' END)
     WHERE name = p_name;
  END IF;

  RETURN true;
END;
$$ LANGUAGE plpgsql;

REVOKE EXECUTE ON FUNCTION @[email protected]_config(TEXT, TEXT) FROM PUBLIC;

Now only superusers can configure the extension! Regular users still need to be able to read the configuration table because the event trigger runs as that user. In any case, we now have a convenient configuration interface.

On a Role

The next step is to allow certain users to bypass the DDL restriction. The easiest way to do this is to create a role that a superuser can grant to these allowed users. We can also take care of our helpful grant/revoke functions here:

CREATE ROLE block_ddl_allowed_user;

CREATE OR REPLACE FUNCTION @[email protected]_ddl_bypass_user(p_user TEXT)
RETURNS BOOLEAN AS
$$
BEGIN
  EXECUTE format('GRANT block_ddl_allowed_user TO %I', p_user);
  RETURN true;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION @[email protected]_ddl_bypass_user(p_user TEXT)
RETURNS BOOLEAN AS
$$
BEGIN
  EXECUTE format('REVOKE block_ddl_allowed_user FROM %I', p_user);
  RETURN true;
END;
$$ LANGUAGE plpgsql;

REVOKE EXECUTE ON FUNCTION @[email protected]_ddl_bypass_user(TEXT) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION @[email protected]_ddl_bypass_user(TEXT) FROM PUBLIC;

The reason for the long block_ddl_allowed_user role name which incorporates the extension name is to prevent name collisions. This role likely isn't already in use, and its purpose is obvious. The functions mean admins don't need to remember the role name itself, but they're not required either.

The final thing to add is the view which lists bypass users:

 CREATE VIEW @[email protected]_ddl_bypass_users AS
SELECT u.rolname AS user_name
  FROM pg_authid x
  JOIN pg_auth_members m on (m.roleid=x.oid)
  JOIN pg_authid u on (m.member=u.oid)
 WHERE x.rolname = 'block_ddl_allowed_user';

GRANT SELECT ON @[email protected]_ddl_bypass_users TO PUBLIC;

Is that a query you knew off the top of your head? Probably not. Now the extension takes care of it so you don't have to.

You Shall Not Pass

The core of our extension is a DDL blocker: an event trigger that fires on ddl_command_start and raises an exception unless the session user is a superuser. The C version of this blocking routine was quite a bit more complex than what we're building here.

Here's our DDL blocking function:

CREATE OR REPLACE FUNCTION @[email protected]_block_ddl()
RETURNS event_trigger AS
$$
DECLARE
  enabled TEXT;
  allow_super TEXT;
BEGIN
  -- Get our current configuration settings
  SELECT setting INTO enabled
    FROM @[email protected]_config WHERE name = 'enabled';
  SELECT setting INTO allow_super
    FROM @[email protected]_config WHERE name = 'allow_super';

  -- Only block if:
  -- 1. The extension is enabled
  IF enabled != 'on' THEN
    RETURN;
  -- 2. Superusers are allowed and the user is a superuser
  ELSIF allow_super = 'on' AND
        (SELECT rolsuper FROM pg_catalog.pg_roles
          WHERE rolname = CURRENT_USER) THEN
    RETURN;
  -- 3. The user is a member of block_ddl_allowed_user
  ELSIF EXISTS (SELECT * FROM @[email protected]_ddl_bypass_users
          WHERE user_name = CURRENT_USER) THEN
    RETURN;
  END IF;

  RAISE EXCEPTION 'DDL command "%" denied by block_ddl', tg_tag
      USING HINT = 'Connect as a superuser, '
                   'or a user with block_ddl_allowed_user access';

END;
$$ LANGUAGE plpgsql;

The RETURNS event_trigger declaration makes this function eligible for use with CREATE EVENT TRIGGER. It's a special return type that signals to Postgres how to invoke the function.

The superuser check queries the pg_catalog.pg_roles for the current_user. This allows superusers to masquerade as other users for testing purposes, and potentially to prevent accidental DDL executions, provided they SET ROLE some_other_user first. The final check is against the v_ddl_bypass_users view we created. It may be tempting to use the pg_has_role information function for this, but that shows effective privileges, not actual membership. Superusers have all privileges, so would automatically pass this check if we didn't explicitly validate against role membership.

With the function in place, creating the event trigger is a one-liner to call the function:

CREATE EVENT TRIGGER block_ddl ON ddl_command_start
  EXECUTE FUNCTION @[email protected]_block_ddl(); 

The ddl_command_start event fires before any DDL command executes. If our function raises an exception at this point, the command never runs. Easy peasy.

What qualifies as "DDL" in the eyes of Postgres? Quite a lot, actually. The ddl_command_start event fires for CREATE, ALTER, DROP, GRANT, REVOKE, COMMENT, REINDEX, REFRESH MATERIALIZED VIEW, SECURITY LABEL, and SELECT INTO. It does not fire for commands targeting databases, roles, tablespaces, or ironically, event triggers themselves.

We could also filter to specific command tags using a WHEN clause:

CREATE EVENT TRIGGER block_ddl ON ddl_command_start
  WHEN TAG IN ('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE')
  EXECUTE FUNCTION @[email protected]_block_ddl();

But where's the fun in that?

Kicking the Tires

Time to see if this thing actually works. First, install the extension files:

$ cd block_ddl
$ sudo make install

That copies block_ddl.control and block_ddl--1.0.sql to the extension directory. Now connect to a database and create the extension:

CREATE SCHEMA block_ddl;
CREATE EXTENSION block_ddl WITH SCHEMA block_ddl;

\dx block_ddl

                          List of installed extensions
   Name    | Version | Default version |  Schema   |        Description        
-----------+---------+-----------------+-----------+---------------------------
 block_ddl | 1.0     | 1.0             | block_ddl | DDL blocking for Postgres

The extension is installed. Let's verify the event trigger is in place:

SELECT evtname, evtevent, evtenabled
  FROM pg_event_trigger
 WHERE evtname = 'block_ddl';

  evtname  |      evtevent       | evtenabled
-----------+---------------------+------------
 block_ddl | ddl_command_start   | O

The O in evtenabled means "origin," which is the default enabled state (fires in all contexts except replication). It's go time!

Testing the Blocker

Blocking is off by default. Let's confirm by creating a throwaway table:

CREATE TABLE scratch (id int);
-- CREATE TABLE

DROP TABLE scratch;
-- DROP TABLE

No complaints. Now let's enable the blocker:

 SELECT block_ddl.alter_config('enabled', 'on');

Then another test:

 CREATE TABLE scratch (id int);
-- CREATE TABLE

Still works. Superusers get a free pass by default. Let's fix that loophole:

SELECT block_ddl.alter_config('allow_super', 'off');
CREATE TABLE scratch (id int);

ERROR:  DDL command "CREATE TABLE" denied by block_ddl
HINT:  Connect as a superuser, or a user with block_ddl_allowed_user access
CONTEXT:  PL/pgSQL function block_ddl.fn_block_ddl() line 27 at RAISE

Now DDL commands are now stopped cold. This should work for any potential DDL:

CREATE INDEX ON scratch (id);
ERROR:  DDL command "CREATE INDEX" denied by block_ddl

ALTER TABLE scratch ADD COLUMN name text;
ERROR:  DDL command "ALTER TABLE" denied by block_ddl 

Does our bypass system work?

SELECT block_ddl.add_ddl_bypass_user('postgres');
CREATE TABLE scratch (id int);
-- CREATE TABLE

The explicit bypass now allows DDL. What about regular users? Let's create one and test again:

CREATE USER app_user;
SET ROLE app_user;

CREATE TABLE nope (id int);
ERROR:  DDL command "CREATE TABLE" denied by block_ddl

Works exactly as advertised!

The Fine Print

SQL-only extensions are powerful, but they're not a complete replacement for C. A few tradeoffs are worth understanding before you commit to one approach or the other.

The GUC security gap. In the C version of this extension, the GUC is registered with PGC_SUSET context, meaning only superusers can change it. In our SQL-only version, block_ddl.enabled would be a custom parameter that any session can modify. We had to devise a somewhat convoluted workaround for this by using a configuration table. This wouldn't be necessary if there was some kind of SQL interface to true variable registration for extensions.

Event trigger blind spots. Some DDL commands don't fire event triggers at all. Operations on databases, roles, tablespaces, and event triggers themselves are exempt. Operations such as CREATE DATABASE or ALTER ROLE are entirely exempt. That's where Postgres's built-in privilege system (or pg_hba.conf restrictions) should do the heavy lifting. Once again, a C extension has access to capabilities our SQL version can only dream about.

No background workers or hooks. C extensions can register background workers, intercept query planning, hook into the executor, and modify server behavior at a fundamental level. SQL-only extensions operate entirely within the SQL layer. If your use case involves any of those deeper capabilities, C is the only option.

For everything else? Functions, triggers, event triggers, views, types, domains, operators, aggregates, tables, and more can all live inside a SQL-only extension. That covers a remarkable amount of ground.

Wrapping Up

The Postgres extension system is often perceived as something that requires C expertise, a compiler toolchain, and a deep understanding of the server internals. That’s only really the case if you need the deep internals. If you've ever written a collection of utility functions and wished you could install them with a single command, you're already thinking in extensions. The packaging is the point.

Our block_ddl extension demonstrates custom configuration tables, roles, functions, views, and event triggers. All of these are standard SQL primitives that any Postgres user already knows. The only new pieces are a minimal control file and Makefile. That's a few lines of overhead to gain clean installation and removal, version management, and dependency tracking.

If you've got a handful of functions, views, or triggers that you deploy to every database in your environment, consider taking an afternoon to wrap them in an extension. Your future self, and anyone else who inherits those databases, may thank you for it.