Modification : vm_{host,hosted,remote} -> {host,local,remote}/ .

[lhc/ateliers.git] / local / postgresql-database-create

diff --git a/local/postgresql-database-create b/local/postgresql-database-create
new file mode 100755 (executable)
index 0000000..d26b80d
--- /dev/null
+++ b/local/postgresql-database-create
@@ -0,0 +1,41 @@
+#!/bin/sh -eux
+db="$1"
+owner="${2:-$db}"
+sudo -u postgres psql template1 -a -f - <<-EOF
+       \set ON_ERROR_STOP on
+       DO LANGUAGE plpgsql \$\$
+       BEGIN
+               IF NOT EXISTS (
+                SELECT *
+                FROM pg_catalog.pg_user
+                WHERE usename = '$owner'
+                LIMIT 1
+               ) THEN
+                       CREATE ROLE $owner
+                        LOGIN
+                        NOCREATEDB
+                        NOCREATEROLE
+                        NOINHERIT
+                        NOSUPERUSER;
+               END IF;
+       END;
+       \$\$;
+       EOF
+case $(sudo -u postgres psql template1 -t -c \
+       "SELECT datname FROM pg_catalog.pg_database WHERE datname = '$db' LIMIT 1") in
+ (" $db") true;;
+ (*)
+       sudo -u postgres psql template1 -a -f - <<-EOF
+               \set ON_ERROR_STOP on
+               CREATE DATABASE $db WITH OWNER=$owner;
+               EOF
+       ;;
+ esac
+sudo -u postgres psql template1 -a -f - <<-EOF
+       \set ON_ERROR_STOP on
+       REVOKE ALL ON DATABASE $db FROM public;
+       EOF
+sudo -u postgres psql "$db" -a -f - <<-EOF
+       \set ON_ERROR_STOP on
+       GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
+       EOF