local/postgresql-database-create

   1 #!/bin/sh -eux
   2 db="$1"
   3 owner="${2:-$db}"
   4 sudo -u postgres psql template1 -a -f - <<-EOF
   5         \set ON_ERROR_STOP on
   6         DO LANGUAGE plpgsql \$\$
   7         BEGIN
   8                 IF NOT EXISTS (
   9                  SELECT *
  10                  FROM pg_catalog.pg_user
  11                  WHERE usename = '$owner'
  12                  LIMIT 1
  13                 ) THEN
  14                         CREATE ROLE $owner
  15                          LOGIN
  16                          NOCREATEDB
  17                          NOCREATEROLE
  18                          NOINHERIT
  19                          NOSUPERUSER;
  20                 END IF;
  21         END;
  22         \$\$;
  23         EOF
  24 case $(sudo -u postgres psql template1 -t -c \
  25         "SELECT datname FROM pg_catalog.pg_database WHERE datname = '$db' LIMIT 1") in
  26  (" $db") true;;
  27  (*)
  28         sudo -u postgres psql template1 -a -f - <<-EOF
  29                 \set ON_ERROR_STOP on
  30                 CREATE DATABASE $db WITH OWNER=$owner;
  31                 EOF
  32         ;;
  33  esac
  34 sudo -u postgres psql template1 -a -f - <<-EOF
  35         \set ON_ERROR_STOP on
  36         REVOKE ALL ON DATABASE $db FROM public;
  37         EOF
  38 sudo -u postgres psql "$db" -a -f - <<-EOF
  39         \set ON_ERROR_STOP on
  40         GRANT ALL ON SCHEMA public TO $owner WITH GRANT OPTION;
  41         EOF