commit 86bcac9fa93079fe8b264a28530712bdb09d685f
parent c1b2f723f023a2daa5e086eed176ac19483ad3a7
Author: Hubert depesz Lubaczewski <depesz@depesz.com>
Date: Mon, 7 Aug 2017 16:38:11 +0200
Add "what current user is" assert functions
In process also add proper README
Diffstat:
4 files changed, 155 insertions(+), 18 deletions(-)
diff --git a/CHANGES b/CHANGES
@@ -1,2 +1,6 @@
+2017-08-07 :
+- Added functions to assert that user (loading patch) is either superuser, or
+ is not superuser, or is one of listed users.
+
2010-08-22 :
- Initial release to github.com : http://github.com/depesz/Versioning
diff --git a/README b/README
@@ -1,18 +0,0 @@
-Versioning
-
-This project strives to provide simple way to manage changes to
-database.
-
-Instead of making changes on development server, then finding
-differences between production and development, deciding which ones
-should be installed on production, and finding a way to install them -
-you start with writing diffs themselves!
-
-More detailed description is (so far) in here:
-http://www.depesz.com/index.php/2010/08/22/versioning/
-
-To install versioning simply run install.versioning.sql in your database
-(all of them: production, stage, test, devel, ...).
-
-If you'd like to suggest new functionality or ask anything - please use
-contact form on http://www.depesz.com/
diff --git a/README.md b/README.md
@@ -0,0 +1,114 @@
+# NAME
+
+**Versioning** - simplistic take on tracking and applying changes to databases.
+
+# DESCRIPTION
+
+This project strives to provide simple way to manage changes to
+database.
+
+Instead of making changes on development server, then finding
+differences between production and development, deciding which ones
+should be installed on production, and finding a way to install them -
+you start with writing diffs themselves!
+
+# INSTALLATION
+
+To install versioning simply run install.versioning.sql in your database
+(all of them: production, stage, test, devel, ...).
+
+# USAGE
+
+In your files with patches to database, put whole logic in single
+transaction, and use \_v.\* functions - usually \_v.register_patch() at
+least to make sure everything is OK.
+
+For example. Let's assume you have patch files:
+
+## 000-base.sql:
+
+```
+create table users (id serial primary key, username text);
+```
+
+## 001-users.sql:
+
+```
+insert into users (username) values ('depesz');
+```
+
+To change it to use versioning you would change the files, to this
+state:
+
+## 000-base.sql:
+
+```
+BEGIN;
+select _v.register_patch('000-base', NULL, NULL);
+create table users (id serial primary key, username text);
+COMMIT;
+```
+
+## 001-users.sql:
+
+```
+BEGIN;
+select _v.register_patch('001-users', ARRAY['000-base'], NULL);
+insert into users (username) values ('depesz');
+COMMIT;
+```
+
+This will make sure that patch 001-users can only be applied after
+000-base.
+
+# AVAILABLE FUNCTIONS
+
+## \_v.register_patch( TEXT )
+
+Registers named patch, or dies if it is already registered.
+
+Returns integer which is id of patch in \_v.patches table - only if it
+succeeded.
+
+## \_v.register_patch( TEXT, TEXT[] )
+
+Same as \_v.register_patch( TEXT ), but checks is all given patches (given as
+array in second argument) are already registered.
+
+## \_v.register_patch( TEXT, TEXT[], TEXT[] )
+
+Same as \_v.register_patch( TEXT, TEXT[] ), but also checks if there are no conflicts with preexisting patches.
+
+Third argument is array of names of patches that conflict with current one. So
+if any of them is installed - register_patch will error out.
+
+## \_v.unregister_patch( TEXT )
+
+Removes information about given patch from the versioning data.
+
+It doesn't remove objects that were created by this patch - just removes
+metainformation.
+
+## \_v.assert_user_is_superuser()
+
+Make sure that current patch is being loaded by superuser.
+
+If it's not - it will raise exception, and break transaction.
+
+## \_v.assert_user_is_not_superuser()
+
+Make sure that current patch is not being loaded by superuser.
+
+If it is - it will raise exception, and break transaction.
+
+## \_v.assert_user_is_one_of(TEXT, TEXT, ... )
+
+Make sure that current patch is being loaded by one of listed users.
+
+If ```current_user``` is not listed as one of arguments - function will raise
+exception and break the transaction.
+
+# SUPPORT
+
+If you'd like to suggest new functionality or ask anything - please use
+contact information from https://depesz.com/
diff --git a/install.versioning.sql b/install.versioning.sql
@@ -94,4 +94,41 @@ END;
$$ language plpgsql;
COMMENT ON FUNCTION _v.unregister_patch( TEXT ) IS 'Function to unregister patches in database. Dies if the patch is not registered, or if unregistering it would break dependencies.';
+CREATE OR REPLACE FUNCTION _v.assert_user_is_superuser() RETURNS TEXT as $$
+DECLARE
+ v_super bool;
+BEGIN
+ SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user;
+ IF v_super THEN
+ RETURN 'assert_user_is_superuser: OK';
+ END IF;
+ RAISE EXCEPTION 'Current user is not superuser - cannot continue.';
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.assert_user_is_superuser() IS 'Function that can be used to make sure that patch is being applied using superuser account.';
+
+CREATE OR REPLACE FUNCTION _v.assert_user_is_not_superuser() RETURNS TEXT as $$
+DECLARE
+ v_super bool;
+BEGIN
+ SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user;
+ IF v_super THEN
+ RAISE EXCEPTION 'Current user is superuser - cannot continue.';
+ END IF;
+ RETURN 'assert_user_is_not_superuser: OK';
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.assert_user_is_not_superuser() IS 'Function that can be used to make sure that patch is being applied using normal (not superuser) account.';
+
+CREATE OR REPLACE FUNCTION _v.assert_user_is_one_of(VARIADIC p_acceptable_users TEXT[] ) RETURNS TEXT as $$
+DECLARE
+BEGIN
+ IF current_user = any( p_acceptable_users ) THEN
+ RETURN 'assert_user_is_one_of: OK';
+ END IF;
+ RAISE EXCEPTION 'User is not one of: % - cannot continue.', p_acceptable_users;
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.assert_user_is_one_of(TEXT[]) IS 'Function that can be used to make sure that patch is being applied by one of defined users.';
+
COMMIT;
