postgresql hooks for fun and profit

HOOKSfor Fun and Profit

[email protected] 15xMarch 2, 2017

http://github.com/davidfetterTuesday, March 7, 17

mailto:[email protected]

mailto:[email protected]

http://github.com/davidfetter

http://github.com/davidfetter

$ whoami

Tuesday, March 7, 17

David Fetter


David Fetter

Husband and father of four


David Fetter


Long-time PostgreSQL contributor


David Fetter



Expander of SQL


David Fetter



Expander of SQL

Happy contractor for OpenSCG at GE Digital


Rate me!


Have you ever?


Have you ever?

Updated or deleted every row


Have you ever?

Updated or deleted every row

by accident?


Have you ever?

Wanted to slow people down when they auth too fast


Have you ever?


Have you ever?

Made a cool constraint


Have you ever?


that required SERIALIZABLE


Have you ever?

Wanted to replace our TSP-based planner with one based on simulated annealing


You could:

Updated or deleted from a whole table without meaning to?

Lock out everyone who could make a mistake...


You could


You could


Hope the network layer handles it


You could



and isn’t Mallory


You could


You could



Add a check for that


You could



Add a check for that

in. every. single. trigger.


You could


Um....er....


Hooks to the Rescue!


Hooks?


Snag


Bag


Act


Back


You need to write C


Preamble#include "postgres.h"

/* Headers you need start here: */

/* Headers you need end here: */

PG_MODULE_MAGIC;

/* Hooking: All hooks start with this. */void _PG_init(void);

/* Unhooking: All hooks end with this. */void _PG_fini(void);


Snag/Bag/* * There is a type for each hook. * This is not one of them. * You will need it for hooking and unhooking. */

static System_hook_type prev_system_hook = NULL;

void _PG_init(void){ prev_system_hook = System_hook; System_hook = blackhole_hook;}


Act

staticvoid blackhole_hook{ /* Your hook code goes here */}


Back

void _PG_fini(void){ System_hook = prev_system_hook;}


82 hooks


You could:

Updated or deleted from a whole table without meaning to?

Lock out everyone who could make a mistake...

Make a simple hook for simple errors


https://github.com/davidfetter/postgresql_projects/tree/require_where

https://github.com/davidfetter/postgresql_projects/tree/require_where

Boring C Code:


Live Demo!


Have you ever?


Have you ever?



Make a simple hook!


https://github.com/davidfetter/postgresql_projects/tree/require_serializable

https://github.com/davidfetter/postgresql_projects/tree/require_serializable

You could


You could



and isn’t Mallory


You could



and isn’t Mallory

auth_delay (contrib)


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/auth_delay/auth_delay.c

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/auth_delay/auth_delay.c

You could


Um....er....

2313 LoC later: Wow!


https://github.com/wulczer/saio

https://github.com/wulczer/saio

Questions?


tl;dr


tl;dr

• Lots has already been done


tl;dr


• Your imagination is the main limit


tl;dr


• Your imagination is the main limit

• Have fun!


Thank you!


postgresql hooks for fun and profit

Software