Initial check in of functions.

diff --git a/README.md b/README.md
new file mode 100644 (file)
index 0000000..db135c5
--- /dev/null
+++ b/README.md
@@ -0,0 +1,45 @@
+user_replication\r
+================\r
+\r
+A basic table and trigger based user replication example for use\r
+with PostgreSQL and Slony-I.\r
+\r
+About\r
+-----\r
+\r
+This is something I created back in 2008 to easily keep users in sync \r
+across a slony cluster.\r
+\r
+The supplied functions have various limitations, one of which is the "options" \r
+field only being useful for managing role membership, anything more complicated \r
+will most likely fail without further work.\r
+\r
+It has the option to store the standard postgresql md5 encrypted passwords, or \r
+or allow unencrypted password retrieval via a PGP encryption using a symmetric-key\r
+obfuscated during construction with a (rather horrible) c function named "hkey".\r
+\r
+If you want to use the hkey obfuscation functionality see *Using the hkey obfuscation*\r
+below.\r
+\r
+To use execute the sql script on each node:\r
+\r
+    # psql -d <db> user_replication.sql\r
+\r
+Now put the 'replicated_users' table into replication using slony and users\r
+can be managed by the create_replicated_user etc functions.\r
+\r
+SELECT create_replicated_user('superted', 'test', 'IN GROUP users');\r
+SELECT detail_replicated_user('superted');\r
+SELECT alter_replicated_user('superted', 'test', 'IN GROUP admins');\r
+SELECT alter_replicated_user('superted', 'test', 'IN GROUP admins');\r
+\r
+Using the hkey obfuscation\r
+--------------------------\r
+\r
+    # cd hkey\r
+    # make\r
+    # make install\r
+    # /usr/local/pgsql/bin/psql -d <db> -U <user> < hkey.sql\r
+\r
+Then alter "v_use_hkey := false" to "v_use_hkey := true" in both the\r
+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 (file)
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 (file)
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 <string.h>
+
+#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 (file)
index 0000000..75648c1
--- /dev/null
+++ b/hkey/hkey.sql
@@ -0,0 +1,7 @@
+CREATE OR REPLACE FUNCTION hkey( text ) RETURNS text\r
+AS\r
+  'hkey.so', 'hkey'\r
+LANGUAGE C STRICT IMMUTABLE;\r
+\r
+REVOKE ALL ON FUNCTION hkey( TEXT ) FROM PUBLIC;\r
+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 (file)
index 0000000..22d0cea
--- /dev/null
+++ b/user_replication.sql
@@ -0,0 +1,305 @@
+-- ***************************************************************************************************************\r
+-- User management routines - Glyn Astill 21/03/2008\r
+--\r
+-- Creates table and triggers to create, alter and drop users based on inserts into the table\r
+-- Creates a set of functions to manage users via rows in the table\r
+-- Once the table is put into (slony) replication then users sould get replicated on all nodes.\r
+-- ***************************************************************************************************************\r
+\r
+SET search_path TO public;\r
+\r
+DROP TABLE IF EXISTS public.replicated_users;\r
+CREATE TABLE public.replicated_users\r
+(\r
+  username text PRIMARY KEY,\r
+  password BYTEA NOT NULL,\r
+  options text\r
+);  \r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.decrypt_replicated_users(bytea, text);\r
+CREATE OR REPLACE FUNCTION public.decrypt_replicated_users(in_pass bytea, in_username text) \r
+RETURNS text AS \r
+$BODY$\r
+DECLARE\r
+       v_use_hkey boolean;\r
+BEGIN\r
+       v_use_hkey := false;\r
+       IF (v_use_hkey) THEN\r
+               RETURN pgp_sym_decrypt(in_pass, hkey(in_username));\r
+       ELSE\r
+               RETURN convert_from(in_pass, 'latin1');\r
+       END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql IMMUTABLE;\r
+REVOKE ALL ON FUNCTION public.decrypt_replicated_users(bytea, text) FROM PUBLIC;\r
+  \r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.encrypt_replicated_users(text, text);\r
+CREATE OR REPLACE FUNCTION public.encrypt_replicated_users(in_pass text, in_username text) \r
+RETURNS bytea AS \r
+$BODY$\r
+DECLARE\r
+       v_use_hkey boolean;\r
+BEGIN\r
+       v_use_hkey := false;\r
+       IF (v_use_hkey) THEN\r
+               RETURN pgp_sym_encrypt(in_pass, hkey(in_username));\r
+       ELSE\r
+               RETURN convert_to('md5' || md5(in_pass  || in_username), 'latin1');\r
+       END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql IMMUTABLE;\r
+REVOKE ALL ON FUNCTION public.encrypt_replicated_users(text, text) FROM PUBLIC;\r
+  \r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.replicate_users();\r
+CREATE OR REPLACE FUNCTION public.replicate_users() \r
+RETURNS TRIGGER AS \r
+$BODY$\r
+DECLARE\r
+       v_query text;\r
+       v_query2 text;\r
+       v_query3 text;\r
+       v_notice text;\r
+BEGIN\r
+\r
+       v_query := '';\r
+       v_query2 := '';\r
+       v_query3 := '';\r
+       v_notice := '';\r
+\r
+       IF (TG_OP <> 'DELETE') THEN\r
+               IF ((upper(NEW.options) ~ 'SUPERUSER') OR (upper(NEW.options) ~ 'CREATEDB') OR (upper(NEW.options) ~ 'CREATEROLE')) THEN\r
+                       RAISE NOTICE 'USER REPLICATION SYSTEM: Sorry, restricted to creating users without SUPERUSER or CREATE options.';\r
+                       RETURN NULL;\r
+               END IF;\r
+       END IF;\r
+\r
+       IF (TG_OP = 'INSERT') THEN\r
+               IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN   \r
+                       IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN\r
+                               v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD  ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username)) || ' ';\r
+                       ELSE  \r
+                               v_query := 'CREATE USER ' || quote_ident(NEW.username) || ' ';\r
+                       END IF;\r
+\r
+                       IF ((NEW.options IS NOT NULL) AND (NEW.options <> '') and (upper(NEW.options) ~ 'IN GROUP')) THEN\r
+                               v_query := v_query || NEW.options;\r
+                       END IF;\r
+\r
+                       v_notice := 'Create user: ' || NEW.username;\r
+               ELSE\r
+                       v_notice := 'Create user failed: no username supplied';\r
+               END IF;\r
+\r
+               RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
+\r
+               IF (v_query <> '') THEN\r
+                       EXECUTE v_query;\r
+                       RETURN NEW;\r
+               END IF;\r
+       ELSEIF (TG_OP = 'UPDATE') THEN\r
+               IF (NEW.username = OLD.username) THEN\r
+                       IF ((NEW.username IS NOT NULL) AND (NEW.username <> '')) THEN   \r
+                               IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NOT NULL) AND (public.decrypt_replicated_users(NEW.password, NEW.username) <> '')) THEN\r
+                                       v_query := 'ALTER USER ' || quote_ident(NEW.username) || ' WITH ENCRYPTED PASSWORD  ' || quote_literal(public.decrypt_replicated_users(NEW.password, NEW.username));\r
+                                       v_notice := 'Alter user: change password for ' || NEW.username;\r
+                               END IF;\r
+                               IF ((NEW.options IS NOT NULL) AND (NEW.options <> '')) THEN\r
+                                       v_query2 := 'GRANT ' || replace(replace(NEW.options, 'IN GROUP ', ''),'in group ','') || ' TO ' || quote_ident(NEW.username);\r
+\r
+                                       IF (v_notice = '') THEN\r
+                                               v_notice := 'Alter user: ' || NEW.username || ' ' || NEW.options;\r
+                                       ELSE \r
+                                               v_notice := v_notice || ' ' || NEW.options;\r
+                                       END IF;\r
+\r
+                                       IF ((OLD.options IS NOT NULL) AND (OLD.options <> '')) THEN\r
+                                               v_query3 := 'REVOKE ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ' FROM ' || quote_ident(NEW.username);\r
+                                               v_notice := v_notice || ' (revoked ' || replace(replace(OLD.options, 'IN GROUP ', ''),'in group ','') || ')';\r
+                                       END IF;\r
+                               END IF;\r
+\r
+                               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 \r
+                                       v_notice := 'Alter user failed: no actions supplied';\r
+                               END IF;\r
+                       ELSE\r
+                               v_notice := 'Alter user failed: no username supplied';\r
+                       END IF;\r
+               ELSE \r
+                       v_notice := 'Alter user failed: cannot change username';\r
+               END IF;\r
+\r
+               RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
+\r
+               IF ((v_query <> '') or (v_query2 <> '')) THEN\r
+                       IF (v_query <> '') THEN\r
+                               EXECUTE v_query;\r
+                       END IF;\r
+       \r
+                       IF (v_query2 <> '') THEN\r
+                               EXECUTE v_query3;\r
+                       END IF;\r
+\r
+                       IF (v_query3 <> '') THEN\r
+                               EXECUTE v_query2;\r
+                       END IF;\r
+       \r
+                       IF ((NEW.options IS NULL) OR (NEW.options = '')) THEN\r
+                               NEW.options := OLD.options;\r
+                       END IF;\r
+       \r
+                       IF ((public.decrypt_replicated_users(NEW.password, NEW.username) IS NULL) OR (public.decrypt_replicated_users(NEW.password, NEW.username) = '')) THEN\r
+                               NEW.password := OLD.password;\r
+                       END IF;\r
+       \r
+                       RETURN NEW;\r
+               END IF;\r
+       ELSEIF (TG_OP = 'DELETE') THEN\r
+               IF ((OLD.username IS NOT NULL) AND (OLD.username <> '')) THEN\r
+                       v_query := 'DROP USER ' || quote_ident(OLD.username);\r
+                       v_notice := 'Drop user: ' || OLD.username;\r
+               ELSE\r
+                       v_notice := 'Drop user failed: no username supplied';\r
+               END IF;\r
+\r
+               RAISE NOTICE 'USER REPLICATION SYSTEM: %', v_notice;\r
+\r
+               IF (v_query <> '') THEN\r
+                       EXECUTE v_query;\r
+                       RETURN OLD;\r
+               END IF;\r
+       END IF;\r
+\r
+       RETURN NULL;\r
+  \r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.replicate_users() FROM PUBLIC;\r
+\r
+--\r
+\r
+CREATE TRIGGER replicate_users_trigger \r
+BEFORE INSERT OR UPDATE OR DELETE ON public.replicated_users\r
+FOR EACH ROW EXECUTE PROCEDURE public.replicate_users() ;\r
+ALTER TABLE public.replicated_users ENABLE ALWAYS TRIGGER replicate_users_trigger;\r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.create_replicated_user(text, text, text);\r
+CREATE OR REPLACE FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) \r
+RETURNS boolean AS $BODY$    \r
+BEGIN  \r
+       INSERT into public.replicated_users (username, password, options) VALUES (cusername,public.encrypt_replicated_users(cpassword, cusername),coptions);\r
+       IF FOUND THEN\r
+               RETURN true;\r
+       ELSE\r
+               RETURN false;\r
+       END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.create_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;\r
+   \r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.drop_replicated_user(text);\r
+CREATE OR REPLACE FUNCTION public.drop_replicated_user(cusername text) \r
+RETURNS boolean AS \r
+$BODY$\r
+BEGIN\r
+       DELETE FROM public.replicated_users WHERE username=cusername; \r
+       IF FOUND THEN\r
+               RETURN true;\r
+       ELSE\r
+               RETURN false;\r
+       END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.drop_replicated_user(cusername text) FROM PUBLIC;\r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.alter_replicated_user(text, text, text);\r
+CREATE OR REPLACE FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) RETURNS boolean AS $BODY$\r
+BEGIN  \r
+       UPDATE public.replicated_users SET password = public.encrypt_replicated_users(cpassword, cusername), options =  coptions WHERE username = cusername;\r
+       IF FOUND THEN\r
+               RETURN true;\r
+       ELSE\r
+               RETURN false;\r
+       END IF;\r
+END;\r
+$BODY$\r
+LANGUAGE plpgsql VOLATILE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.alter_replicated_user(cusername text, cpassword text, coptions text) FROM PUBLIC;\r
+\r
+--\r
+   \r
+DROP FUNCTION IF EXISTS public.check_replicated_user(text);\r
+CREATE OR REPLACE FUNCTION public.check_replicated_user(usr text) \r
+RETURNS integer AS\r
+$BODY$\r
+DECLARE \r
+       v_num integer;\r
+BEGIN\r
+       SELECT INTO v_num count(*) FROM public.replicated_users WHERE username = usr;\r
+       RETURN v_num;\r
+END;\r
+$BODY$\r
+LANGUAGE plpgsql STABLE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.check_replicated_user(usr text) FROM PUBLIC;\r
+\r
+--\r
+\r
+DROP FUNCTION IF EXISTS public.detail_replicated_user(text);\r
+CREATE OR REPLACE FUNCTION public.detail_replicated_user(cusername text) \r
+RETURNS text AS \r
+$BODY$\r
+DECLARE\r
+       v_user_detail_rec record;\r
+       v_strresult text;\r
+BEGIN\r
+       v_strresult := '';\r
+       SELECT INTO v_user_detail_rec username, public.decrypt_replicated_users(password, username) AS "password", options FROM public.replicated_users WHERE username=cusername;\r
+       IF FOUND THEN\r
+               v_strresult :=  'Username : ' || v_user_detail_rec.username || E'\nPassword : ' || v_user_detail_rec.password || E'\nOptions  : ' || v_user_detail_rec.options || E'\n';\r
+               RETURN v_strresult;\r
+       ELSE\r
+               v_strresult := 'Replicated user not found : ' || cusername || E'\n';\r
+               RETURN v_strresult;\r
+       END IF;\r
+END;\r
+$BODY$ \r
+LANGUAGE plpgsql STABLE SECURITY DEFINER;\r
+REVOKE ALL ON FUNCTION public.detail_replicated_user(cusername text) FROM PUBLIC;\r
+\r
+-- ***************************************************************************************************************\r
+-- Other unrelated useful functions\r
+-- ***************************************************************************************************************\r
+\r
+-- Check for logged in user sessions > 1. Note that pooled sessions persist for connection_life_time or equivalent after logout.   \r
+-- DROP FUNCTION IF EXISTS public.check_user_session(text, text);\r
+-- CREATE OR REPLACE FUNCTION public.check_user_session(uame text, dname text) \r
+-- RETURNS boolean AS\r
+-- $BODY$\r
+--     SELECT CASE WHEN count(*) > 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;\r
+-- $BODY$ \r
+-- LANGUAGE sql STABLE;\r
+\r
+-- Check if a user is logged in\r
+-- DROP FUNCTION IF EXISTS public.check_user_logged(text, text);\r
+-- CREATE OR REPLACE FUNCTION public.check_user_logged(uame text, dname text)\r
+-- RETURNS boolean AS\r
+-- $BODY$\r
+--     SELECT CASE WHEN count(*) >= 1 THEN true ELSE false END FROM pg_stat_activity WHERE "usename" = $1 AND "datname" = $2;\r
+-- $BODY$\r
+-- LANGUAGE sql STABLE;
\ No newline at end of file