#!/bin/bash
#
# Reassign a database owner and all its objects.
#
# But dece, there is REASSIGN OWNED!! Yes and it does not work if the owner is
# `postgres` because it reassign objects across all databases, wrecking havok on
# your cluster.
#
# This script manually alters the database owner, then for all public
# (non-system) schemas it reassigns the owner of:
# - the schema itself
# - tables
# - sequences
# - views
# - foreign tables
#
# Finally it also reassigns the owner of foreign servers. If you don't want them
# to be reassigned, comment the block at the end of the script.
#
# If I'm missing something please let me know. This script is a base work to
# modify to your needs.

usage() {
    echo "Usage: $0 <database> <new_owner>"
}

[ $# -ne 2 ] && usage && exit  # assumes usage is a defined command.

info() {
    printf '\e[33m%s\e[0m\n' "> $1"
}

database="$1"
new_owner="$2"
echo "🤖 <(Reassigning all objects of database \"$database\" to \"$new_owner\".)"

# psql command, here we run it as the Unix user postgres, adapt as needed.
# For simplicity, always add the database parameter.
psql="sudo -u postgres psql -d $database"

# Change global database owner.
$psql -c "ALTER DATABASE \"$database\" OWNER TO $new_owner"

# Loop over all schemas. Most databases will only use 'public'.
schemas="$($psql -qAt -c "
    SELECT nspname
    FROM pg_catalog.pg_namespace
    WHERE nspname != 'information_schema' AND nspname NOT LIKE 'pg\\_%';
")"
for schema in $schemas; do
    info "Schema $schema"

    $psql -c "ALTER SCHEMA $schema OWNER TO $new_owner"

    echo "Reassigning tables…"
    tables="$($psql -qAt -c "
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = '$schema'
    ")"
    for table in $tables ; do
        echo -n "    $table "
        $psql -c "ALTER TABLE \"$schema\".\"$table\" OWNER TO $new_owner"
    done

    echo "Reassigning sequences…"
    sequences="$($psql -qAt -c "
        SELECT sequence_name
        FROM information_schema.sequences
        WHERE sequence_schema = '$schema'
    ")"
    for sequence in $sequences ; do
        echo -n "    $sequence "
        $psql -c "ALTER SEQUENCE \"$schema\".\"$sequence\" OWNER TO $new_owner"
    done

    echo "Reassigning views…"
    views="$($psql -qAt -c "
        SELECT table_name
        FROM information_schema.views
        WHERE table_schema = '$schema'
    ")"
    for view in $views ; do
        echo -n "    $view "
        $psql -c "ALTER VIEW \"$schema\".\"$view\" OWNER TO $new_owner"
    done

    echo "Reassigning materialized views…"
    mviews="$($psql -qAt -c "
        SELECT matviewname
        FROM pg_matviews
        WHERE schemaname = '$schema'
    ")"
    for mview in $mviews ; do
        echo -n "    $mview "
        $psql -c "ALTER MATERIALIZED VIEW \"$schema\".\"$mview\" OWNER TO $new_owner"
    done

    echo "Reassigning foreign tables…"
    ftables="$($psql -qAt -c "
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = '$schema' AND table_type = 'FOREIGN'
    ")"
    for ftable in $ftables ; do
        echo -n "    $ftable "
        $psql -c "ALTER FOREIGN TABLE \"$schema\".\"$ftable\" OWNER TO $new_owner"
    done

done # end of schema loop

# Foreign servers.
# Comment/uncomment the paragraph below if you want to reassign foreign servers
# as well. Foreign data wrappers owners are not reassigned because PostgreSQL
# requires a superuser to own them.

echo "Reassigning foreign servers…"
fservers="$($psql -qAt -c "
    SELECT foreign_server_name
    FROM information_schema.foreign_servers
")"
for fserver in $fservers ; do
    echo -n "    $fserver "
    $psql -c "ALTER SERVER $fserver OWNER TO $new_owner"
done