-
-
Save victor-abz/0152a7311ca3b4c6de9bfa89afd19ecf to your computer and use it in GitHub Desktop.
Changes the owner on all tables, sequences, views, and functions in a PostgreSQL database with support for identifiers with whitespace and non-public schemas.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
usage() | |
{ | |
cat << EOF | |
usage: $0 options | |
This script sets ownership for all tables, sequences, views, and functions for a given schema. | |
Run this script as your postgres OS user. | |
sh pg_change_schema_owner.sh -d db_name -o owner_name | |
Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto | |
Also merged changes from @sharoonthomas | |
bspkrs: Added function code based on http://dba.stackexchange.com/a/9710/31043 | |
and changed messy object quoting to use quote_ident(). | |
OPTIONS: | |
-h Show this message | |
-d Database name | |
-o New Owner | |
-s Schema (defaults to public) | |
EOF | |
} | |
DB_NAME=""; | |
NEW_OWNER=""; | |
while getopts "hd:o:" OPTION; do | |
case $OPTION in | |
h) | |
usage; | |
exit 1; | |
;; | |
d) | |
DB_NAME=$OPTARG; | |
;; | |
o) | |
NEW_OWNER=$OPTARG; | |
;; | |
esac | |
done | |
if [[ -z $DB_NAME ]] || [[ -z $NEW_OWNER ]]; then | |
usage; | |
exit 1; | |
fi | |
for tbl in `psql -qAt -c "SELECT quote_ident(schemaname) || '.' || quote_ident(tablename) FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog';" ${DB_NAME}` \ | |
`psql -qAt -c "SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) FROM information_schema.sequences WHERE sequence_schema != 'pg_catalog';" ${DB_NAME}` \ | |
`psql -qAt -c "SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) FROM information_schema.views WHERE table_schema != 'pg_catalog';" ${DB_NAME}` ; | |
do | |
psql -c "ALTER TABLE $tbl OWNER TO ${NEW_OWNER}" ${DB_NAME}; | |
done | |
psql -qAt -c "SELECT DISTINCT quote_ident(table_schema) FROM information_schema.tables WHERE table_schema != 'pg_catalog';" ${DB_NAME} | while read schema ; | |
do | |
psql -c "ALTER SCHEMA $schema OWNER TO ${NEW_OWNER}" ${DB_NAME}; | |
done | |
psql -qAt -c "SELECT quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname != 'pg_catalog';" ${DB_NAME} | while read func ; | |
do | |
psql -c "ALTER FUNCTION $func OWNER TO ${NEW_OWNER}" ${DB_NAME}; | |
done |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment