Skip to content

Instantly share code, notes, and snippets.

@jahrmando
Forked from gingerlime/change_db_owner.sh
Last active June 19, 2020 23:17
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jahrmando/fb14c2c48a7dc9fcfe5721fb04d11dd2 to your computer and use it in GitHub Desktop.
Save jahrmando/fb14c2c48a7dc9fcfe5721fb04d11dd2 to your computer and use it in GitHub Desktop.
Postgresql - Changing ownership on all tables, funtions and views
#!/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
Also merged changes from @sharoonthomas
OPTIONS:
-h Show this message
-d Database name
-o Owner
-r Hostname Database (default: localhost)
-u User Database (default: postgres)
EOF
}
DB_NAME=
NEW_OWNER=
HOST_DB=
USER_DB=
while getopts "hd:o:r:u:" OPTION
do
case $OPTION in
h)
usage
exit 1
;;
d)
DB_NAME=$OPTARG
;;
o)
NEW_OWNER=$OPTARG
;;
r)
HOST_DB=$OPTARG
;;
u)
USER_DB=$OPTARG
;;
esac
done
if [[ -z $DB_NAME ]] || [[ -z $NEW_OWNER ]]
then
usage
exit 1
fi
HOST_DB=${HOST_DB:-localhost}
USER_DB=${USER_DB:-postgres}
for tbl in `psql -h ${HOST_DB} -U ${USER_DB} -qAt -c "select tablename from pg_tables where schemaname = 'public';" ${DB_NAME}`
do
psql -h ${HOST_DB} -U ${USER_DB} -c "alter TABLE \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
echo "ON: ${tbl}" ;
done
for tbl in `psql -h ${HOST_DB} -U ${USER_DB} -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" ${DB_NAME}`
do
psql -h ${HOST_DB} -U ${USER_DB} -c "alter SEQUENCE \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
echo "ON: ${tbl}" ;
done
for tbl in `psql -h ${HOST_DB} -U ${USER_DB} -qAt -c "select table_name from information_schema.views where table_schema = 'public';" ${DB_NAME}` ;
do
psql -h ${HOST_DB} -U ${USER_DB} -c "alter VIEW \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
echo "ON: ${tbl}" ;
done
for tbl in `psql -h ${HOST_DB} -U ${USER_DB} -qAt -c "select matviewname as view_name from pg_matviews where schemaname='public';" ${DB_NAME}` ;
do
psql -h ${HOST_DB} -U ${USER_DB} -c "alter MATERIALIZED VIEW \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
echo "ON: ${tbl}" ;
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment