PostgreSQL Auditing Schema

structure.sql

--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: audit; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA audit;

--
-- Name: meta; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA meta;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = meta, pg_catalog;
--
-- Name: create_audit_table(text, text, text); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION create_audit_table("table" text, schema text DEFAULT 'audit'::text, from_schema text DEFAULT 'public'::text) RETURNS boolean
	LANGUAGE plpgsql STRICT
	AS $_$
BEGIN
	
	PERFORM * FROM information_schema.tables WHERE table_schema = $2 AND table_name = $1;
	IF FOUND THEN
		RAISE EXCEPTION 'Audit table "%.%" already exists.', $2, $1;
	END IF;
	
	--If we're still here, create the audit table
	EXECUTE 'CREATE TABLE '||$2||'.'||$1||' AS 
		SELECT 1::integer as tuple_id,
		NOW()::timestamptz as tuple_start,
		NOW()::timestamptz as tuple_stop, 
		''''::text as tuple_start_action,
		''''::text as tuple_stop_action,
		1::integer as tuple_revision,
		NULL::timestamptz as deleted_timestamp,
		NULL::integer as deleted_by,
		* FROM '||$1||' WHERE FALSE';
	EXECUTE 'CREATE SEQUENCE '||$2||'.'||$1||'_tuple_id_seq';
	EXECUTE 'ALTER TABLE '||$2||'.'||$1||' ALTER COLUMN tuple_id SET DEFAULT nextval('''||$2||'.'||$1||'_tuple_id_seq'')';
	EXECUTE 'UPDATE '||$2||'.'||$1||' SET tuple_id = nextval('''||$2||'.'||$1||'_tuple_id_seq'')';
	EXECUTE 'ALTER SEQUENCE '||$2||'.'||$1||'_tuple_id_seq OWNED BY '||$2||'.'||$1||'.tuple_id';
	EXECUTE 'ALTER TABLE '||$2||'.'||$1||' ADD PRIMARY KEY (tuple_id)';
	
	--Create the auditing trigger
	EXECUTE 'DROP TRIGGER IF EXISTS "'||$1||'_tr_audit" ON '||from_schema||'.'||$1;
	EXECUTE 'CREATE TRIGGER "'||$1||'_tr_audit" AFTER INSERT OR UPDATE OR DELETE ON '||from_schema||'.'||$1||' FOR EACH ROW EXECUTE PROCEDURE "meta"."trigger_audit"();';
	
	--Create the truncation trigger
	EXECUTE 'DROP TRIGGER IF EXISTS "'||$1||'_tr_audit_truncate" ON '||from_schema||'.'||$1;
	EXECUTE 'CREATE TRIGGER "'||$1||'_tr_audit_truncate" AFTER TRUNCATE ON '||from_schema||'.'||$1||' FOR EACH STATEMENT EXECUTE PROCEDURE "meta"."trigger_audit_truncate"();';
	
	RETURN TRUE;
	
END;
$_$;

--
-- Name: FUNCTION create_audit_table("table" text, schema text, from_schema text); Type: COMMENT; Schema: meta; Owner: -
--
COMMENT ON FUNCTION create_audit_table("table" text, schema text, from_schema text) IS 'Creates an auditing table in the specified schema. Takes 3 arguments:
1. The name of the table to be audited (required)
2. The name of the schema to create the audit table in (default ''audit'')
3. The name of the schema the audited table resides in (default ''public'')';

--
-- Name: ensure_auditing_fields(); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION ensure_auditing_fields() RETURNS boolean
	LANGUAGE plpgsql
	AS $$
DECLARE
	r RECORD;
BEGIN

	FOR r IN 
		SELECT
			t.table_schema,
			t.table_name,
			count(CASE WHEN c.column_name = 'created_timestamp' THEN 1 ELSE NULL END) as has_created_timestamp,
			count(CASE WHEN c.column_name = 'modified_timestamp' THEN 1 ELSE NULL END) as has_modified_timestamp,
			count(CASE WHEN c.column_name = 'modified_by' THEN 1 ELSE NULL END) as has_modified_by,
			count(CASE WHEN c.column_name = 'created_by' THEN 1 ELSE NULL END) as has_created_by
		FROM 
			information_schema.tables t
			JOIN information_schema.columns c USING (table_schema, table_name)
		WHERE
			t.table_schema IN ('public')
			AND t.table_type = 'BASE TABLE'
		GROUP BY
			1, 2
	LOOP
		IF r.has_created_timestamp = 0 THEN
			RAISE NOTICE '%.% - Adding created_timestamp', r.table_schema, r.table_name;
			EXECUTE 'ALTER TABLE '||r.table_schema||'.'||r.table_name||' ADD COLUMN created_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP';
		END IF;
		IF r.has_modified_timestamp = 0 THEN
			RAISE NOTICE '%.% - Adding modified_timestamp', r.table_schema, r.table_name;
			EXECUTE 'ALTER TABLE '||r.table_schema||'.'||r.table_name||' ADD COLUMN modified_timestamp TIMESTAMPTZ';
			EXECUTE 'CREATE TRIGGER '||r.table_name||'_tr_modified_timestamp BEFORE UPDATE 
ON '||r.table_schema||'.'||r.table_name||' FOR EACH ROW 
EXECUTE PROCEDURE meta.trigger_modified_timestamp()';
		END IF;
		IF r.has_created_by = 0 THEN
			RAISE NOTICE '%.% - Adding created_by', r.table_schema, r.table_name;
			EXECUTE 'ALTER TABLE '||r.table_schema||'.'||r.table_name||' ADD COLUMN created_by INTEGER DEFAULT meta.session_get(''user_id'')::integer';
			EXECUTE 'ALTER TABLE '||r.table_schema||'.'||r.table_name||' ADD CONSTRAINT
				'||r.table_name||'_fk_created_by FOREIGN KEY (created_by)
				REFERENCES public.users(id)
				ON DELETE NO ACTION
				ON UPDATE NO ACTION
				DEFERRABLE INITIALLY IMMEDIATE';
		END IF;
		IF r.has_modified_by = 0 THEN
			RAISE NOTICE '%.% - Adding modified_by', r.table_schema, r.table_name;
			EXECUTE 'ALTER TABLE '||r.table_schema||'.'||r.table_name||' ADD COLUMN modified_by INTEGER';
			EXECUTE 'ALTER TABLE '||r.table_schema||'.'||r.table_name||' ADD CONSTRAINT
				'||r.table_name||'_fk_modified_by FOREIGN KEY (modified_by)
				REFERENCES public.users(id)
				ON DELETE NO ACTION
				ON UPDATE NO ACTION
				DEFERRABLE INITIALLY IMMEDIATE';
		END IF;
				
	END LOOP;

	RETURN TRUE;

END;
$$;

--
-- Name: FUNCTION ensure_auditing_fields(); Type: COMMENT; Schema: meta; Owner: -
--
COMMENT ON FUNCTION ensure_auditing_fields() IS 'Ensures that created_timestamp, modified_timestamp, and modified_by exist on
every table.';

--
-- Name: ensure_deferrable_fks(); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION ensure_deferrable_fks() RETURNS SETOF text
	LANGUAGE plpgsql
	AS $$
DECLARE
	r meta.constraints_view%ROWTYPE;
BEGIN
FOR r IN 
	SELECT 
		*
	FROM 
		meta.constraints_view 
	WHERE 
		constraint_type = 'FOREIGN KEY'
		AND is_deferrable = 'NO'
LOOP

	RETURN NEXT 
		'ALTER TABLE ' || r.table_schema || '.' || r.table_name || 
		' DROP CONSTRAINT ' || r.constraint_name || ' RESTRICT;';
	  
	RETURN NEXT 
		'ALTER TABLE ' || r.table_schema || '.' || r.table_name || 
		' ADD CONSTRAINT ' || r.constraint_name || 
		' FOREIGN KEY (' || r.column_name || ')' ||
		' REFERENCES ' || r.references_schema || '.' || r.references_table ||  
		'(' || r.references_field || ')' ||
		' ON DELETE ' || r.on_delete || 
		' ON UPDATE ' || r.on_update || 
		' DEFERRABLE INITIALLY IMMEDIATE;';
	
	

END LOOP;

END;
$$;

--
-- Name: ensure_session_table(); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION ensure_session_table() RETURNS boolean
	LANGUAGE plpgsql
	AS $$
BEGIN

	PERFORM *
	FROM pg_catalog.pg_class
	WHERE relname = '_session_variables' AND relnamespace = pg_my_temp_schema();
	
	IF NOT FOUND THEN
		CREATE TEMPORARY TABLE _session_variables (
			"key" TEXT PRIMARY KEY,
			"value" TEXT
		);
	END IF;
	
	RETURN TRUE;

END;
$$;

--
-- Name: FUNCTION ensure_session_table(); Type: COMMENT; Schema: meta; Owner: -
--
COMMENT ON FUNCTION ensure_session_table() IS 'Creates a temporary session table if one doesn''t exist already';

--
-- Name: object_description(text, text); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION object_description("table" text, schema text DEFAULT 'public'::text) RETURNS text
	LANGUAGE sql STABLE STRICT
	AS $_$
SELECT 
  pg_catalog.obj_description(c.oid, 'pg_class')
FROM pg_catalog.pg_class c
	 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = $1
  AND n.nspname = $2
$_$;

--
-- Name: session_get(text); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION session_get(text) RETURNS text
	LANGUAGE plpgsql STRICT
	AS $_$
DECLARE
	xValue TEXT;
BEGIN
  
  PERFORM meta.ensure_session_table();
	
  SELECT "value" INTO xValue FROM _session_variables WHERE "key" = $1;
	
  RETURN xValue;
	
END;
$_$;

--
-- Name: session_set(text, text); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION session_set(text, INOUT text) RETURNS text
	LANGUAGE plpgsql
	AS $_$
BEGIN

	PERFORM meta.ensure_session_table();
	
	UPDATE _session_variables
	SET "value" = $2 
	WHERE "key" = $1;
	
	IF NOT FOUND THEN
		INSERT INTO _session_variables VALUES ($1, $2);
	END IF;
	
	RETURN;
	
END;
$_$;

--
-- Name: trigger_audit(); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION trigger_audit() RETURNS trigger
	LANGUAGE plpgsql
	AS $_$
DECLARE
	rev INTEGER;
BEGIN

	IF TG_OP = 'DELETE' THEN
		EXECUTE '
			UPDATE audit.' || TG_TABLE_NAME || ' 
			SET 
				deleted_timestamp = NOW(), 
				deleted_by = meta.session_get(''user_id'')::integer,
				tuple_stop = NOW(),
				tuple_stop_action = ''D''
			WHERE 
				id = $1 
				AND tuple_stop_action IS NULL
		' USING OLD.id;
	END IF;
	
	IF TG_OP = 'UPDATE' THEN
	
		EXECUTE '
			UPDATE audit.' || TG_TABLE_NAME || ' 
			SET 
				tuple_stop = NOW(),
				tuple_stop_action = ''U''
			WHERE 
				id = $1 
				AND tuple_stop_action IS NULL
			RETURNING
				tuple_revision
		' INTO rev USING OLD.id;
		
		EXECUTE '
			INSERT INTO audit.' || TG_TABLE_NAME || '
			SELECT 
				nextval(''audit.' || TG_TABLE_NAME || '_tuple_id_seq''), 
				NOW(), 
				NULL, 
				''U'', 
				NULL, 
				$1, 
				NULL, 
				NULL, 
				($2).*
		' USING rev + 1, NEW;
		
	END IF;
	
	IF TG_OP = 'INSERT' THEN
		EXECUTE '
			INSERT INTO audit.' || TG_TABLE_NAME || '
			SELECT 
				nextval(''audit.' || TG_TABLE_NAME || '_tuple_id_seq''), 
				NOW(), 
				NULL, 
				''I'', 
				NULL, 
				1, 
				NULL, 
				NULL, 
				($1).*
		' USING NEW;
	END IF;
	
	RETURN NULL;

END;
$_$;

--
-- Name: trigger_audit_truncate(); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION trigger_audit_truncate() RETURNS trigger
	LANGUAGE plpgsql
	AS $$
BEGIN
	EXECUTE 'TRUNCATE TABLE audit.' || TG_TABLE_NAME || ' RESTART IDENTITY';
	RETURN NULL;
END;
$$;

--
-- Name: trigger_modified_timestamp(); Type: FUNCTION; Schema: meta; Owner: -
--
CREATE FUNCTION trigger_modified_timestamp() RETURNS trigger
	LANGUAGE plpgsql
	AS $$
BEGIN
	NEW.modified_timestamp := CURRENT_TIMESTAMP;
	NEW.modified_by = meta.session_get('user_id')::integer;
	RETURN NEW;
END;
$$;

SET search_path = audit, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: users; Type: TABLE; Schema: audit; Owner: -; Tablespace: 
--
CREATE TABLE users (
	tuple_id integer NOT NULL,
	tuple_start timestamp with time zone,
	tuple_stop timestamp with time zone,
	tuple_start_action text,
	tuple_stop_action text,
	tuple_revision integer,
	deleted_timestamp timestamp with time zone,
	deleted_by integer,
	id integer,
	name text,
	created_timestamp timestamp with time zone,
	modified_timestamp timestamp with time zone,
	created_by integer,
	modified_by integer
);

--
-- Name: users_tuple_id_seq; Type: SEQUENCE; Schema: audit; Owner: -
--
CREATE SEQUENCE users_tuple_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

--
-- Name: users_tuple_id_seq; Type: SEQUENCE OWNED BY; Schema: audit; Owner: -
--
ALTER SEQUENCE users_tuple_id_seq OWNED BY users.tuple_id;

SET search_path = meta, pg_catalog;
--
-- Name: constraints_view; Type: VIEW; Schema: meta; Owner: -
--
CREATE VIEW constraints_view AS
 SELECT tc.constraint_name,
	tc.constraint_type,
	tc.table_schema,
	tc.table_name,
	kcu.column_name,
	tc.is_deferrable,
	tc.initially_deferred,
	rc.match_option AS match_type,
	rc.update_rule AS on_update,
	rc.delete_rule AS on_delete,
	ccu.table_schema AS references_schema,
	ccu.table_name AS references_table,
	ccu.column_name AS references_field
   FROM (((information_schema.table_constraints tc
	 LEFT JOIN information_schema.key_column_usage kcu ON (((((tc.constraint_catalog)::text = (kcu.constraint_catalog)::text) AND ((tc.constraint_schema)::text = (kcu.constraint_schema)::text)) AND ((tc.constraint_name)::text = (kcu.constraint_name)::text))))
	 LEFT JOIN information_schema.referential_constraints rc ON (((((tc.constraint_catalog)::text = (rc.constraint_catalog)::text) AND ((tc.constraint_schema)::text = (rc.constraint_schema)::text)) AND ((tc.constraint_name)::text = (rc.constraint_name)::text))))
	 LEFT JOIN information_schema.constraint_column_usage ccu ON (((((rc.unique_constraint_catalog)::text = (ccu.constraint_catalog)::text) AND ((rc.unique_constraint_schema)::text = (ccu.constraint_schema)::text)) AND ((rc.unique_constraint_name)::text = (ccu.constraint_name)::text))));

SET search_path = public, pg_catalog;
--
-- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--
CREATE TABLE users (
	id integer NOT NULL,
	name text NOT NULL,
	created_timestamp timestamp with time zone DEFAULT now() NOT NULL,
	modified_timestamp timestamp with time zone,
	created_by integer DEFAULT (meta.session_get('user_id'::text))::integer,
	modified_by integer
);

--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--
CREATE SEQUENCE users_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1;

--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--
ALTER SEQUENCE users_id_seq OWNED BY users.id;

SET search_path = audit, pg_catalog;
--
-- Name: tuple_id; Type: DEFAULT; Schema: audit; Owner: -
--
ALTER TABLE ONLY users ALTER COLUMN tuple_id SET DEFAULT nextval('users_tuple_id_seq'::regclass);

SET search_path = public, pg_catalog;
--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--
ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);

SET search_path = audit, pg_catalog;
--
-- Data for Name: users; Type: TABLE DATA; Schema: audit; Owner: -
--
COPY users (tuple_id, tuple_start, tuple_stop, tuple_start_action, tuple_stop_action, tuple_revision, deleted_timestamp, deleted_by, id, name, created_timestamp, modified_timestamp, created_by, modified_by) FROM stdin;
\.

--
-- Name: users_tuple_id_seq; Type: SEQUENCE SET; Schema: audit; Owner: -
--
SELECT pg_catalog.setval('users_tuple_id_seq', 1, false);

SET search_path = public, pg_catalog;
--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: -
--
COPY users (id, name, created_timestamp, modified_timestamp, created_by, modified_by) FROM stdin;
\.

--
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--
SELECT pg_catalog.setval('users_id_seq', 1, false);

SET search_path = audit, pg_catalog;
--
-- Name: users_pkey; Type: CONSTRAINT; Schema: audit; Owner: -; Tablespace: 
--
ALTER TABLE ONLY users
	ADD CONSTRAINT users_pkey PRIMARY KEY (tuple_id);

SET search_path = public, pg_catalog;
--
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--
ALTER TABLE ONLY users
	ADD CONSTRAINT users_pkey PRIMARY KEY (id);

--
-- Name: users_tr_audit; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER users_tr_audit AFTER INSERT OR DELETE OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE meta.trigger_audit();

--
-- Name: users_tr_audit_truncate; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER users_tr_audit_truncate AFTER TRUNCATE ON users FOR EACH STATEMENT EXECUTE PROCEDURE meta.trigger_audit_truncate();

--
-- Name: users_tr_modified_timestamp; Type: TRIGGER; Schema: public; Owner: -
--
CREATE TRIGGER users_tr_modified_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE meta.trigger_modified_timestamp();

--
-- Name: users_fk_created_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY users
	ADD CONSTRAINT users_fk_created_by FOREIGN KEY (created_by) REFERENCES users(id) DEFERRABLE;

--
-- Name: users_fk_modified_by; Type: FK CONSTRAINT; Schema: public; Owner: -
--
ALTER TABLE ONLY users
	ADD CONSTRAINT users_fk_modified_by FOREIGN KEY (modified_by) REFERENCES users(id) DEFERRABLE;

--
-- PostgreSQL database dump complete
--