Postgresql - Changing ownership on all tables
#!/bin/bash | |
usage() | |
{ | |
cat << EOF | |
usage: $0 options | |
This script set ownership for all table, sequence and views for a given database | |
Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto | |
OPTIONS: | |
-h Show this message | |
-d Database name | |
-o Owner | |
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 tablename from pg_tables where schemaname = 'public';" ${DB_NAME}` \ | |
`psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" ${DB_NAME}` \ | |
`psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" ${DB_NAME}` ; | |
do | |
psql -c "alter table \"$tbl\" 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