Postgresql - Changing ownership on all tables, funtions and views

change_db_owner.sh

#!/bin/bash

usage()
{
cat << EOF
usage: $0 options

This script set ownership for all table, sequence and views for a given database and schema.

NOTES:
- Please make sure you have the necessary permissions to change the ownership of the tables.
- PGPASS file should be set up to avoid password prompt.

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
   -s      Schema name (default: public)
   -o      Owner
   -r      Hostname Database (default: localhost)
   -P      Port Database (default: 5432) 
   -u      User Database (default: postgres)
EOF
}

DB_NAME=
DB_SCHEMA=
NEW_OWNER=
HOST_DB=
HOST_PORT=
USER_DB=

while getopts "h:d:o:r:u:s:P:" OPTION
do
    case $OPTION in
        h)
            usage
            exit 1
            ;;
        d)
            DB_NAME=$OPTARG
            ;;
        s)
            DB_SCHEMA=$OPTARG
            ;;
        o)
            NEW_OWNER=$OPTARG
            ;;
        r)
            HOST_DB=$OPTARG
            ;;
        P)
            HOST_PORT=$OPTARG
            ;;
        u)
            USER_DB=$OPTARG
            ;;
    esac
done

if [[ -z $DB_NAME ]] || [[ -z $NEW_OWNER ]]
then
     usage
     exit 1
fi

HOST_DB=${HOST_DB:-localhost}
HOST_PORT=${HOST_PORT:-5432}
USER_DB=${USER_DB:-postgres}
DB_SCHEMA=${DB_SCHEMA:-public}

# Change owner of all tables, sequences and views
for ITEM in `psql -h ${HOST_DB} -p ${HOST_PORT} -U ${USER_DB} -qAt -c "SELECT TABLENAME FROM PG_TABLES WHERE SCHEMANAME = '${DB_SCHEMA}';" ${DB_NAME}`
do
    psql -h ${HOST_DB} -p ${HOST_PORT} -d ${DB_NAME} -U ${USER_DB} -c "ALTER TABLE ${DB_SCHEMA}.${ITEM} OWNER TO ${NEW_OWNER}" ;
    echo "ON: ${ITEM}" ;
done

for ITEM in `psql -h ${HOST_DB} -p ${HOST_PORT} -U ${USER_DB} -qAt -c "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA = '$DB_SCHEMA';" ${DB_NAME}`
do
    psql -h ${HOST_DB} -p ${HOST_PORT} -d ${DB_NAME} -U ${USER_DB} -c "ALTER SEQUENCE ${DB_SCHEMA}.${ITEM} OWNER TO ${NEW_OWNER}" ;
    echo "ON: ${ITEM}" ;
done

for ITEM in `psql -h ${HOST_DB} -p ${HOST_PORT} -U ${USER_DB} -qAt -c "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = '${DB_SCHEMA}';" ${DB_NAME}` ;
do
    psql -h ${HOST_DB} -p ${HOST_PORT} -d ${DB_NAME} -U ${USER_DB} -c "ALTER VIEW ${DB_SCHEMA}.${ITEM} OWNER TO ${NEW_OWNER}" ${DB_NAME} ;
    echo "ON: ${ITEM}" ;
done

for ITEM in `psql -h ${HOST_DB} -p ${HOST_PORT} -U ${USER_DB} -qAt -c "SELECT MATVIEWNAME AS VIEW_NAME FROM PG_MATVIEWS WHERE SCHEMANAME = '${DB_SCHEMA}';" ${DB_NAME}` ;
do
    psql -h ${HOST_DB} -p ${HOST_PORT} -d ${DB_NAME} -U ${USER_DB} -c "ALTER MATERIALIZED VIEW ${DB_SCHEMA}.${ITEM} OWNER TO ${NEW_OWNER}" ;
    echo "ON: ${ITEM}" ;
done