blob: 9ca7481eea2ea083b41090804c1f1a3a4fd67a77 (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
#!/usr/bin/env bash
set -euo pipefail
PSQL=$1
DB=$2
DB_USER=$3
echo 'creating role, database, assigning owner...'
"$PSQL" <<EOF
DO \$\$
BEGIN
CREATE ROLE $DB_USER WITH LOGIN;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
\$$;
SELECT 'CREATE DATABASE $DB'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DB')\gexec
ALTER DATABASE $DB OWNER TO $DB_USER;
EOF
echo 'echo creating pgcrypto extension...'
"$PSQL" -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;" "$DB"
readarray -t TABLES < <("$PSQL" -qAt -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public';" "$DB")
readarray -t SEQUENCES < <("$PSQL" -qAt -c "SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public';" "$DB")
readarray -t VIEWS < <("$PSQL" -qAt -c "SELECT table_name FROM information_schema.views WHERE table_schema = 'public';" "$DB")
cat <<EOF >&2
patching owner:
tables: ${TABLES[*]}
sequences: ${SEQUENCES[*]}
views: ${VIEWS[*]}
EOF
STMT=""
for tbl in "${TABLES[@]}" "${SEQUENCES[@]}" "${VIEWS[@]}"; do
STMT+="ALTER TABLE \"$tbl\" OWNER TO $DB_USER;"
STMT+=$'\n'
done
"$PSQL" -c "$STMT" "$DB"
echo "success"
|
