From 8dcaa5a94ba2f13f1a513c87f3edb6d463fe9126 Mon Sep 17 00:00:00 2001 From: glyn Date: Fri, 3 Jan 2014 10:53:39 +0000 Subject: [PATCH] Initial check in of functions. --- README.md | 45 +++++++ hkey/Makefile | 3 + hkey/hkey.c | 49 +++++++ hkey/hkey.sql | 7 + user_replication.sql | 305 +++++++++++++++++++++++++++++++++++++++++++ 5 files changed, 409 insertions(+) create mode 100644 README.md create mode 100644 hkey/Makefile create mode 100644 hkey/hkey.c create mode 100644 hkey/hkey.sql create mode 100644 user_replication.sql diff --git a/README.md b/README.md new file mode 100644 index 0000000..db135c5 --- /dev/null +++ b/README.md @@ -0,0 +1,45 @@ +user_replication +================ + +A basic table and trigger based user replication example for use +with PostgreSQL and Slony-I. + +About +----- + +This is something I created back in 2008 to easily keep users in sync +across a slony cluster. + +The supplied functions have various limitations, one of which is the "options" +field only being useful for managing role membership, anything more complicated +will most likely fail without further work. + +It has the option to store the standard postgresql md5 encrypted passwords, or +or allow unencrypted password retrieval via a PGP encryption using a symmetric-key +obfuscated during construction with a (rather horrible) c function named "hkey". + +If you want to use the hkey obfuscation functionality see *Using the hkey obfuscation* +below. + +To use execute the sql script on each node: + + # psql -d user_replication.sql + +Now put the 'replicated_users' table into replication using slony and users +can be managed by the create_replicated_user etc functions. + +SELECT create_replicated_user('superted', 'test', 'IN GROUP users'); +SELECT detail_replicated_user('superted'); +SELECT alter_replicated_user('superted', 'test', 'IN GROUP admins'); +SELECT alter_replicated_user('superted', 'test', 'IN GROUP admins'); + +Using the hkey obfuscation +-------------------------- + + # cd hkey + # make + # make install + # /usr/local/pgsql/bin/psql -d -U < hkey.sql + +Then alter "v_use_hkey := false" to "v_use_hkey := true" in both the +decrypt_replicated_users and encrypt_replicated_users functions. \ No newline at end of file diff --git a/hkey/Makefile b/hkey/Makefile new file mode 100644 index 0000000..f6e281b --- /dev/null +++ b/hkey/Makefile @@ -0,0 +1,3 @@ +MODULES = hkey +PGXS := $(shell pg_config --pgxs) +include $(PGXS) diff --git a/hkey/hkey.c b/hkey/hkey.c new file mode 100644 index 0000000..d0bfedc --- /dev/null +++ b/hkey/hkey.c @@ -0,0 +1,49 @@ +//Glyn 08/05/2008 -- Function to obfuscate enctryption key generation based on username + +#include "postgres.h" +#include "fmgr.h" +#include + +#ifdef PG_MODULE_MAGIC +PG_MODULE_MAGIC; +#endif + +Datum hkey( PG_FUNCTION_ARGS ); + +PG_FUNCTION_INFO_V1( hkey ); +Datum +hkey( PG_FUNCTION_ARGS ) +{ + // variable declarations + char key[] = "91836zi8euwq45270"; + text *uname; + int keylen; + int unamelen; + text *keying; + + // Get arguments. If we declare our function as STRICT, then this check is superfluous. + if( PG_ARGISNULL(0) ) { + PG_RETURN_NULL(); + } + uname = PG_GETARG_TEXT_P(0); + + // Calculate string sizes. + keylen = strlen(key); + unamelen = VARSIZE(uname) - VARHDRSZ; + + // Allocate memory and set data structure size. + // Don't forget to add the type overhead (size of the length of the word at the start of the value) of int4 / VARHDRSZ + keying = (text *)palloc( keylen + unamelen + VARHDRSZ); + + // VARATT_SIZEP depreciated as of 8.3 + //VARATT_SIZEP( keying ) = keylen + unamelen + VARHDRSZ; + SET_VARSIZE(keying, keylen + unamelen + VARHDRSZ); + + // Construct keying string. + strncpy( VARDATA(keying), key, keylen ); + strncpy( VARDATA(keying) + keylen, + VARDATA(uname), + unamelen ); + + PG_RETURN_TEXT_P( keying ); +} diff --git a/hkey/hkey.sql b/hkey/hkey.sql new file mode 100644 index 0000000..75648c1 --- /dev/null +++ b/hkey/hkey.sql @@ -0,0 +1,7 @@ +CREATE OR REPLACE FUNCTION hkey( text ) RETURNS text +AS + 'hkey.so', 'hkey' +LANGUAGE C STRICT IMMUTABLE; + +REVOKE ALL ON FUNCTION hkey( TEXT ) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION hkey( TEXT ) TO admins; \ No newline at end of file diff --git a/user_replication.sql b/user_replication.sql new file mode 100644 index 0000000..22d0cea --- /dev/null +++ b/user_replication.sql @@ -0,0 +1,305 @@ +-- *************************************************************************************************************** +-- User management routines - Glyn Astill 21/03/2008 +-- +-- Creates table and triggers to create, alter and drop users based on inserts into the table +-- Creates a set of functions to manage users via rows in the table +-- Once the table is put into (slony) replication then users sould get replicated on all nodes. +-- *************************************************************************************************************** + +SET search_path TO public; + +DROP TABLE IF EXISTS public.replicated_users; +CREATE TABLE public.replicated_users +( + username text PRIMARY KEY, + password BYTEA NOT NULL, + options text +); + +-- + +DROP FUNCTION IF EXISTS public.decrypt_replicated_users(bytea, text); +CREATE OR REPLACE FUNCTION public.decrypt_replicated_users(in_pass bytea, in_username text) +RETURNS text AS +$BODY$ +DECLARE + v_use_hkey boolean; +BEGIN + v_use_hkey := false; + IF (v_use_hkey) THEN + RETURN pgp_sym_decrypt(in_pass, hkey(in_username)); + ELSE + RETURN convert_from(in_pass, 'latin1'); + END IF; +END; +$BODY$ +LANGUAGE plpgsql IMMUTABLE; +REVOKE ALL ON FUNCTION public.decrypt_replicated_users(bytea, text) FROM PUBLIC; + +-- + +DROP FUNCTION IF EXISTS public.encrypt_replicated_users(text, text); +CREATE OR REPLACE FUNCTION public.encrypt_replicated_users(in_pass text, in_username text) +RETURNS bytea AS +$BODY$ +DECLARE + v_use_hkey boolean; +BEGIN + v_use_hkey := false; + IF (v_use_hkey) THEN + RETURN pgp_sym_encrypt(in_pass, hkey(in_username)); + ELSE + RETURN convert_to('md5' || md5(in_pass || in_username), 'latin1'); + END IF; +END; +$BODY$ +LANGUAGE plpgsql IMMUTABLE; +REVOKE ALL ON FUNCTION public.encrypt_replicated_users(text, text) FROM PUBLIC; + +-- + +DROP FUNCTION IF EXISTS public.replicate_users(); +CREATE OR REPLACE FUNCTION public.replicate_users() +RETURNS TRIGGER AS +$BODY$ +DECLARE + v_query text; + v_query2 text; + v_query3 text; + v_notice text; +BEGIN + + v_query := ''; + v_query2 := ''; + v_query3 := ''; + v_notice := ''; + + IF (TG_OP <> 'DELETE') THEN + IF ((upper(NEW.options) ~ 'SUPERUSER') OR (upper(NEW.options) ~ 'CREATEDB') OR (upper(NEW.options) ~ 'CREATEROLE')) THEN + RAISE NOTICE 'USER REPLICATION SYSTEM: Sorry, restricted to creating users without SUPERUSER or CREATE options.'; + RETURN NULL; + END IF; + END IF; + + IF (TG_OP = 'INSERT') THEN + IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN + IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN + v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username)) || ' '; + ELSE + v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' '; + END IF; + + IF ((NEW.options IS NOT NULL) AND (NEW.options <> '') and (upper(NEW.options) ~ 'IN GROUP')) THEN + v_query := v_query || NEW.options; + END IF; + + v_notice := 'Create user: ' || NEW.username; + ELSE + v_notice := 'Create user failed: no username supplied'; + END IF; + + RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice; + + IF (v_query <> '') THEN + EXECUTE v_query; + RETURN NEW; + END IF; + ELSEIF (TG_OP = 'UPDATE') THEN + IF (NEW.username = OLD.username) THEN + IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN + IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN + v_query := 'ALTER USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username)); + v_notice := 'Alter user: change password for ' || NEW.username; + END IF; + IF ((NEW.options IS NOT NULL) AND (NEW.options <> '')) THEN + v_query2 := 'GRANT ' || replace(replace(NEW.options, 'IN GROUP ', ''),'in group ','') || ' TO ' || quote_ident(NEW.username); + + IF (v_notice = '') THEN + v_notice := 'Alter user: ' || NEW.username || ' ' || NEW.options; + ELSE + v_notice := v_notice || ' ' || NEW.options; + END IF; + + IF ((OLD.options IS NOT NULL) AND (OLD.options <> '')) THEN + v_query3 := 'REVOKE ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ' FROM ' || quote_ident(NEW.username); + v_notice := v_notice || ' (revoked ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ')'; + END IF; + END IF; + + IF (((NEW.options IS NULL) OR (NEW.options = '')) AND ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NULL) OR (public.decrypt_replicated_users(NEW.password, NEW.username) = ''))) THEN + v_notice := 'Alter user failed: no actions supplied'; + END IF; + ELSE + v_notice := 'Alter user failed: no username supplied'; + END IF; + ELSE + v_notice := 'Alter user failed: cannot change username'; + END IF; + + RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice; + + IF ((v_query <> '') or (v_query2 <> '')) THEN + IF (v_query <> '') THEN + EXECUTE v_query; + END IF; + + IF (v_query2 <> '') THEN + EXECUTE v_query3; + END IF; + + IF (v_query3 <> '') THEN + EXECUTE v_query2; + END IF; + + IF ((NEW.options IS NULL) OR (NEW.options = '')) THEN + NEW.options := OLD.options; + END IF; + + IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NULL) OR (public.decrypt_replicated_users(NEW.password, NEW.username) = '')) THEN + NEW.password := OLD.password; + END IF; + + RETURN NEW; + END IF; + ELSEIF (TG_OP = 'DELETE') THEN + IF ((OLD.username IS NOT NULL) AND (OLD.username <> '')) THEN + v_query := 'DROP USER ' || quote_ident(OLD.username); + v_notice := 'Drop user: ' || OLD.username; + ELSE + v_notice := 'Drop user failed: no username supplied'; + END IF; + + RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice; + + IF (v_query <> '') THEN + EXECUTE v_query; + RETURN OLD; + END IF; + END IF; + + RETURN NULL; + +END; +$BODY$ +LANGUAGE plpgsql VOLATILE SECURITY DEFINER; +REVOKE ALL ON FUNCTION public.replicate_users() FROM PUBLIC; + +-- + +CREATE TRIGGER replicate_users_trigger +BEFORE INSERT OR UPDATE OR DELETE ON public.replicated_users +FOR EACH ROW EXECUTE PROCEDURE public.replicate_users() ; +ALTER TABLE public.replicated_users ENABLE ALWAYS TRIGGER replicate_users_trigger; + +-- + +DROP FUNCTION IF EXISTS public.create_replicated_user(text, text, text); +CREATE OR REPLACE FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) +RETURNS boolean AS $BODY$ +BEGIN + INSERT into public.replicated_users (username, password, options) VALUES (cusername,public.encrypt_replicated_users(cpassword, cusername),coptions); + IF FOUND THEN + RETURN true; + ELSE + RETURN false; + END IF; +END; +$BODY$ +LANGUAGE plpgsql VOLATILE SECURITY DEFINER; +REVOKE ALL ON FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC; + +-- + +DROP FUNCTION IF EXISTS public.drop_replicated_user(text); +CREATE OR REPLACE FUNCTION public.drop_replicated_user(cusername text) +RETURNS boolean AS +$BODY$ +BEGIN + DELETE FROM public.replicated_users WHERE username=cusername; + IF FOUND THEN + RETURN true; + ELSE + RETURN false; + END IF; +END; +$BODY$ +LANGUAGE plpgsql VOLATILE SECURITY DEFINER; +REVOKE ALL ON FUNCTION public.drop_replicated_user(cusername text) FROM PUBLIC; + +-- + +DROP FUNCTION IF EXISTS public.alter_replicated_user(text, text, text); +CREATE OR REPLACE FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) RETURNS boolean AS $BODY$ +BEGIN + UPDATE public.replicated_users SET password = public.encrypt_replicated_users(cpassword, cusername), options = coptions WHERE username = cusername; + IF FOUND THEN + RETURN true; + ELSE + RETURN false; + END IF; +END; +$BODY$ +LANGUAGE plpgsql VOLATILE SECURITY DEFINER; +REVOKE ALL ON FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC; + +-- + +DROP FUNCTION IF EXISTS public.check_replicated_user(text); +CREATE OR REPLACE FUNCTION public.check_replicated_user(usr text) +RETURNS integer AS +$BODY$ +DECLARE + v_num integer; +BEGIN + SELECT INTO v_num count(*) FROM public.replicated_users WHERE username = usr; + RETURN v_num; +END; +$BODY$ +LANGUAGE plpgsql STABLE SECURITY DEFINER; +REVOKE ALL ON FUNCTION public.check_replicated_user(usr text) FROM PUBLIC; + +-- + +DROP FUNCTION IF EXISTS public.detail_replicated_user(text); +CREATE OR REPLACE FUNCTION public.detail_replicated_user(cusername text) +RETURNS text AS +$BODY$ +DECLARE + v_user_detail_rec record; + v_strresult text; +BEGIN + v_strresult := ''; + SELECT INTO v_user_detail_rec username, public.decrypt_replicated_users(password, username) AS "password", options FROM public.replicated_users WHERE username=cusername; + IF FOUND THEN + v_strresult := 'Username : ' || v_user_detail_rec.username || E'\nPassword : ' || v_user_detail_rec.password || E'\nOptions : ' || v_user_detail_rec.options || E'\n'; + RETURN v_strresult; + ELSE + v_strresult := 'Replicated user not found : ' || cusername || E'\n'; + RETURN v_strresult; + END IF; +END; +$BODY$ +LANGUAGE plpgsql STABLE SECURITY DEFINER; +REVOKE ALL ON FUNCTION public.detail_replicated_user(cusername text) FROM PUBLIC; + +-- *************************************************************************************************************** +-- Other unrelated useful functions +-- *************************************************************************************************************** + +-- Check for logged in user sessions > 1. Note that pooled sessions persist for connection_life_time or equivalent after logout. +-- DROP FUNCTION IF EXISTS public.check_user_session(text, text); +-- CREATE OR REPLACE FUNCTION public.check_user_session(uame text, dname text) +-- RETURNS boolean AS +-- $BODY$ +-- SELECT CASE WHEN count(*) > 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2; +-- $BODY$ +-- LANGUAGE sql STABLE; + +-- Check if a user is logged in +-- DROP FUNCTION IF EXISTS public.check_user_logged(text, text); +-- CREATE OR REPLACE FUNCTION public.check_user_logged(uame text, dname text) +-- RETURNS boolean AS +-- $BODY$ +-- SELECT CASE WHEN count(*) >= 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2; +-- $BODY$ +-- LANGUAGE sql STABLE; \ No newline at end of file -- 2.39.5