Modifying pg_dump To Exclude Event Triggers

2024-06-30

At $WORK, we have a case where we have implemented an event trigger to prevent customers from dropping an extension. As this extension is part of contrib and normally installed by users, we can’t prevent them from dropping it normally. However, event triggers can only be created by superusers, so a pg_dump of the database creates a dump that can’t be restored by a non-superuser. To solve this, lets implement a custom pg_dump that optionally excludes event triggers.

You can see the following implementation in this diff on my fork of the Postgres repository.

Adding A New Option
Filtering Event Triggers
Filtering Event Trigger Returning Functions
Testing

Adding A New Option

To begin with, we need to add a new switch to pg_dump that will allow us to exclude event triggers on command. The switches generally come in two varieties - short and long. As pg_dump already uses most of the alphabet in the short options (abBcCdeEfFhjnNOpRsStTUvwWxZ), we’ll use a long option: --no-event-triggers. This aligns with similar options in pg_dump like --no-comments and --no-subscriptions.

This is fortunately straightforward to implement - in pg_backup.h we add a new boolean variable to the _dumpOptions struct:

typedef struct _dumpOptions
{
    ConnParams cparams;
    /* ... */

    /* flags for various command-line long options */
	int			disable_dollar_quoting;
	int			column_inserts;
	int			if_exists;
    /* ... */
    int			outputNoEventTriggers;
    /* ... */
} DumpOptions;

In pg_dump.c, we add the option to the long_options array:

static struct option long_options[] = {
    /* ... */
    {"no-table-access-method", no_argument, &dopt.outputNoTableAm, 1},
    {"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1},
    {"no-event-triggers", no_argument, &dopt.outputNoEventTriggers, 1},
    /* ... */
};

And finally, we add the option to the help output:

static void
help(const char *progname)
{
    printf(_("%s dumps a database as a text file or to other formats.\n\n"), progname);
    /* ... */
    printf(_("  --no-event-triggers          do not dump event triggers\n"));
}

With all that done, we now have a dump option we can use to filter out event triggers.

Filtering Event Triggers

The next step is to actually filter out the event triggers from the dump if the option is set. The entrypoint for this is the getEventTriggers function in pg_dump.c:

/*
 * getEventTriggers
 *	  get information about event triggers
 */
EventTriggerInfo *
getEventTriggers(Archive *fout, int *numEventTriggers)
{
    /* ... */
    return evtinfo;
}

As this function takes an Archive pointer as an argument, we can access the dump options from it. We can then check if the outputNoEventTriggers option is set and skip the event triggers if it is:

EventTriggerInfo *
getEventTriggers(Archive *fout, int *numEventTriggers)
{
    /* ... */
    if (fout->dopt->outputNoEventTriggers)
    {
        pg_log_info("excluded event triggers");
        *numEventTriggers = 0;
        return NULL;
    }

    pg_log_info("reading event triggers");
    /* ... */
    return evtinfo;
}

With this change, we can now exclude event triggers from the dump if the --no-event-triggers option is set. However, this results in a somewhat incomplete dump - we have some functions references by these triggers left hanging, so we’ll also need to exclude them.

Filtering Event Trigger Returning Functions

There are a few ways we can identify the functions that are used to return event triggers. The most straightforward is to look for functions that have the EVENT TRIGGER type:

SELECT
    p.oid,
    p.proname
FROM
    pg_proc p
WHERE
    p.prorettype = 'pg_catalog.event_trigger'::regtype;

Another option would be to use pg_depend to find functions that are depended on by event triggers:

SELECT
	pg_proc.oid AS function_oid,
	pg_proc.proname AS function_name,
	pg_proc.pronamespace::regnamespace AS function_schema,
	pg_proc.proowner::regrole AS function_owner,
	pg_event_trigger.oid AS trigger_oid,
	pg_event_trigger.evtname AS trigger_name
FROM
	pg_depend
	INNER JOIN pg_event_trigger ON
			pg_event_trigger.oid = pg_depend.objid
			AND pg_depend.classid = 'pg_event_trigger'::regclass
	INNER JOIN pg_proc ON pg_depend.refobjid = pg_proc.oid
		AND pg_depend.refclassid = 'pg_proc'::regclass
WHERE
	pg_depend.deptype = 'n';

In the interests of simplicity, we’ll use the first method. There is a reasonably complex function, getFuncs, that is used to get all functions in the database, that aren’t aggregates, internal constructor funcs, and a couple of other special cases (e.g. binary-upgrade mode includes extension-managed functions). We can add a check for the pg_catalog.event_trigger return type to this query:

FuncInfo *
getFuncs(Archive *fout, int *numFuncs)
{
    /* ... */
    const char *not_event_trigger_check;

    not_event_trigger_check = (fout->dopt->outputNoEventTriggers ? "\nAND p.prorettype <> 'pg_catalog.event_trigger'::regtype\n" : "\n");

    /* ... */

		appendPQExpBuffer(query,
						  "SELECT p.tableoid, p.oid, p.proname, p.prolang, "
						  "p.pronargs, p.proargtypes, p.prorettype, "
						  "p.proacl, "
						  "acldefault('f', p.proowner) AS acldefault, "
						  "p.pronamespace, "
						  "p.proowner "
						  "FROM pg_proc p "
						  "LEFT JOIN pg_init_privs pip ON "
						  "(p.oid = pip.objoid "
						  "AND pip.classoid = 'pg_proc'::regclass "
						  "AND pip.objsubid = 0) "
						  "WHERE %s"
						  "%s"
						  "\n  AND NOT EXISTS (SELECT 1 FROM pg_depend "
						  "WHERE classid = 'pg_proc'::regclass AND "
						  "objid = p.oid AND deptype = 'i')"
						  "\n  AND ("
						  "\n  pronamespace != "
						  "(SELECT oid FROM pg_namespace "
						  "WHERE nspname = 'pg_catalog')"
						  "\n  OR EXISTS (SELECT 1 FROM pg_cast"
						  "\n  WHERE pg_cast.oid > %u "
						  "\n  AND p.oid = pg_cast.castfunc)"
						  "\n  OR EXISTS (SELECT 1 FROM pg_transform"
						  "\n  WHERE pg_transform.oid > %u AND "
						  "\n  (p.oid = pg_transform.trffromsql"
						  "\n  OR p.oid = pg_transform.trftosql))",
						  not_agg_check,
						  not_event_trigger_check,
						  g_last_builtin_oid,
						  g_last_builtin_oid);

    /* ... */

    return finfo;
}

This is a little ugly, but gets the job done without having to add a lot of additional complexity. This doesn’t correctly handle all cases - much older versions of Postgres won’t do the filtering correctly, but at $WORK we don’t have anything older than 12, so this is sufficient for our needs. Should I want this to be upstreamed, I’ll need to do some more robust testing on older versions.

Testing

With all this done, we can now test our changes. We can build pg_dump as normal, and then run it with the --no-event-triggers option:

$ ~/dev/mble/pgsql/bin/pg_dump --quote-all-identifiers -O -s -v 2>&1 | rg '(CREATE EVENT TRIGGER|(FUNCTION.* RETURNS "event_trigger"))' || echo "No event triggers found"
CREATE FUNCTION "public"."event_trigger_function_name"() RETURNS "event_trigger"
CREATE EVENT TRIGGER "trigger_name" ON "ddl_command_start"

$ ~/dev/mble/pgsql/bin/pg_dump --no-event-triggers --quote-all-identifiers -O -s -v 2>&1 | rg '(CREATE EVENT TRIGGER|(FUNCTION.* RETURNS "event_trigger"))' || echo "No event triggers found"
No event triggers found

Postgres has a fairly robust TAP testing suite for pg_dump and other binary utilities, where they are invoked by Perl scripts using IPC::Run. We can run these tests by running make check in the src/bin/pg_dump directory:

$ cd src/bin/pg_dump/
$ make check
# snip
# +++ tap check in src/bin/pg_dump +++
t/001_basic.pl ................ ok
t/002_pg_dump.pl .............. ok
t/003_pg_dump_with_server.pl .. ok
t/004_pg_dump_parallel.pl ..... ok
t/010_dump_connstr.pl ......... ok
All tests successful.
Files=5, Tests=10018, 19 wallclock secs ( 0.23 usr  0.03 sys +  3.31 cusr  2.78 csys =  6.35 CPU)
Result: PASS

To assert that our new option is working correctly, we can add a new test to the t/002_pg_dump.pl script:

my %pgdump_runs = (
    # snip
	no_event_triggers => {
		dump_cmd => [
			'pg_dump', '--no-sync',
			"--file=$tempdir/no_event_triggers.sql",
			'--no-event-triggers', 'postgres',
		]
	},
    # snip
);

my %full_runs = (
    # snip
    no_event_triggers => 1,
);

We can now run the tests and observe they fail, after adding no_event_triggers to the full_runs hash:

$ make check
# snip
# +++ tap check in src/bin/pg_dump +++
t/001_basic.pl ................ ok
t/002_pg_dump.pl .............. 4387/?
#   Failed test 'no_event_triggers: should dump CREATE EVENT TRIGGER test_event_trigger'
#   at t/002_pg_dump.pl line 4960.
# Review no_event_triggers results in /Users/mblewitt/dev/mble/postgres/src/bin/pg_dump/tmp_check/tmp_test_XEVK

#   Failed test 'no_event_triggers: should dump CREATE FUNCTION dump_test.event_trigger_func'
#   at t/002_pg_dump.pl line 4960.
# Review no_event_triggers results in /Users/mblewitt/dev/mble/postgres/src/bin/pg_dump/tmp_check/tmp_test_XEVK
t/002_pg_dump.pl .............. 9428/? # Looks like you failed 2 tests of 9907.
t/002_pg_dump.pl .............. Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/9907 subtests
t/003_pg_dump_with_server.pl .. ok
t/004_pg_dump_parallel.pl ..... ok
t/010_dump_connstr.pl ......... ok

Test Summary Report
-------------------
t/002_pg_dump.pl            (Wstat: 512 (exited 2) Tests: 9907 Failed: 2)
  Failed tests:  4964, 4969
  Non-zero exit status: 2
Files=5, Tests=10018, 16 wallclock secs ( 0.19 usr  0.03 sys +  3.03 cusr  2.38 csys =  5.63 CPU)
Result: FAIL

We can update our expectations in the test script by adding the expected output to the %tests hash:

my %tests = (
    # snip
	'CREATE FUNCTION dump_test.event_trigger_func' => {
		create_order => 32,
		create_sql => 'CREATE FUNCTION dump_test.event_trigger_func()
					   RETURNS event_trigger LANGUAGE plpgsql
					   AS $$ BEGIN RETURN; END;$$;',
		regexp => qr/^
			\QCREATE FUNCTION dump_test.event_trigger_func() RETURNS event_trigger\E
			\n\s+\QLANGUAGE plpgsql\E
			\n\s+AS\ \$\$
			\Q BEGIN RETURN; END;\E
			\$\$;/xm,
		like =>
		  { %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
		unlike => {
			exclude_dump_test_schema => 1,
			only_dump_measurement => 1,
			no_event_triggers => 1
		},
	},
    # snip
	'CREATE EVENT TRIGGER test_event_trigger' => {
		create_order => 33,
		create_sql => 'CREATE EVENT TRIGGER test_event_trigger
					   ON ddl_command_start
					   EXECUTE FUNCTION dump_test.event_trigger_func();',
		regexp => qr/^
			\QCREATE EVENT TRIGGER test_event_trigger \E
			\QON ddl_command_start\E
			\n\s+\QEXECUTE FUNCTION dump_test.event_trigger_func();\E
			/xm,
		like => { %full_runs, section_post_data => 1, },
		unlike => {
			no_event_triggers => 1,
		},
	},
)

The crucial part of this is the no_event_triggers key in the unlike hash. As we don’t expect the create_sql to be present in the dump, we can assert that it isn’t present by adding this key.

With this change, we can now run the tests again and see that they pass:

$ cd src/bin/pg_dump/
$ make check
# snip
# +++ tap check in src/bin/pg_dump +++
t/001_basic.pl ................ ok
t/002_pg_dump.pl .............. ok
t/003_pg_dump_with_server.pl .. ok
t/004_pg_dump_parallel.pl ..... ok
t/010_dump_connstr.pl ......... ok
All tests successful.
Files=5, Tests=10018, 19 wallclock secs ( 0.23 usr  0.03 sys +  3.31 cusr  2.78 csys =  6.35 CPU)
Result: PASS

With this, we have successfully implemented a new option in pg_dump to exclude event triggers from the dump. This will allow us to create dumps that can be restored by non-superusers, even if event triggers are present in the database.

Matt Blewitt

Modifying pg_dump To Exclude Event Triggers

Contents

Adding A New Option

Filtering Event Triggers

Filtering Event Trigger Returning Functions

Testing