Postgresql - Changing ownership on all tables

change_db_owner.sh

#!/bin/bash

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

This script sets ownership for all tables, sequences, views and enum types 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
   --dry-run    Prints info about the target server without making changes
EOF
}

dry_run() {
    echo "Dry run mode enabled"
    psql -c "\conninfo" ${DB_NAME}
    echo "The following Tables, Sequences, and Views would be transferred to ${NEW_OWNER}:"

    for tbl in "TABLES" "--------" `psql -qAt -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tableowner != '${NEW_OWNER}';" ${DB_NAME}` \
            "SEQUENCES" "--------" `psql -qAt -c "SELECT sequencename AS sequence_name FROM pg_sequences WHERE schemaname = 'public' and sequenceowner != '${NEW_OWNER}';" ${DB_NAME}` \
            "VIEWS" "--------" `psql -qAt -c "SELECT viewname AS view_name FROM pg_views WHERE schemaname = 'public' and viewowner != '${NEW_OWNER}';" ${DB_NAME}` \
            "ENUM TYPES" "--------" `psql -qAt -c "SELECT t.typname AS type_name FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace WHERE t.typtype = 'e' AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND pg_get_userbyid(t.typowner) != '${NEW_OWNER}';" ${DB_NAME}` ;
    do
        echo "$tbl"
    done

    exit 0
}

DB_NAME=""
NEW_OWNER=""
DRY_RUN=false

# Handle short options manually using a while loop
while [[ $# -gt 0 ]]; do
    case "$1" in
        -h)
            usage
            exit 0
            ;;
        -d)
            DB_NAME="$2"
            shift 2
            ;;
        -o)
            NEW_OWNER="$2"
            shift 2
            ;;
        --dry-run)
            DRY_RUN=true
            shift
            ;;
        *)
            echo "Invalid option: $1"
            usage
            exit 1
            ;;
    esac
done

# Check for required parameters
if [[ -z $DB_NAME ]] || [[ -z $NEW_OWNER ]]; then
    usage
    exit 1
fi

# Dry run mode logic
if [ "$DRY_RUN" = true ]; then
    dry_run
fi

# Actual execution logic
for tbl in `psql -qAt -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tableowner != '${NEW_OWNER}';" ${DB_NAME}` \
           `psql -qAt -c "SELECT sequencename AS sequence_name FROM pg_sequences WHERE schemaname = 'public' and sequenceowner != '${NEW_OWNER}';" ${DB_NAME}` \
           `psql -qAt -c "SELECT viewname AS view_name FROM pg_views WHERE schemaname = 'public' and viewowner != '${NEW_OWNER}';" ${DB_NAME}` ;
do
    psql -c "ALTER TABLE \"$tbl\" OWNER TO ${NEW_OWNER}" ${DB_NAME}
done

for enum_typ in `psql -qAt -c "SELECT t.typname AS type_name FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace WHERE t.typtype = 'e' AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND pg_get_userbyid(t.typowner) != '${NEW_OWNER}';" ${DB_NAME}` ;
do
    psql -c "ALTER TYPE \"$enum_typ\" OWNER TO ${NEW_OWNER}" ${DB_NAME}
done