public
Last active — forked from gingerlime/change_db_owner.sh

Postgresql - Changing ownership on all tables

  • Download Gist
change_db_owner.sh
Shell
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
#!/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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.