Changes the owner on all tables, sequences, views, and functions in a PostgreSQL database with support for identifiers with whitespace and non-public schemas.

pg_change_schema_owner.sh

#!/bin/bash

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

This script sets ownership for all tables, sequences, views, and functions for a given schema.
Run this script as your postgres OS user.

Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto
        Also merged changes from @sharoonthomas

bspkrs: Added function code based on http://dba.stackexchange.com/a/9710/31043
        and changed messy object quoting to use quote_ident().

OPTIONS:
   -h      Show this message
   -d      Database name
   -o      New Owner
   -s      Schema (defaults to public)
EOF
}

DB_NAME="";
NEW_OWNER="";
SCHEMA="public";
while getopts "hd:o:s:" OPTION; do
    case $OPTION in
        h)
            usage;
            exit 1;
            ;;
        d)
            DB_NAME=$OPTARG;
            ;;
        o)
            NEW_OWNER=$OPTARG;
            ;;
        s)
            SCHEMA=$OPTARG;
            ;;
    esac
done

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

# Using the NULL byte as the separator as its the only character disallowed from PG table names
IFS=\0;
for tbl in `psql -qAt -R\0 -c "SELECT quote_ident(schemaname) || '.' || quote_ident(tablename) FROM pg_catalog.pg_tables WHERE schemaname = '${SCHEMA}';" ${DB_NAME}` \
           `psql -qAt -R\0 -c "SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) FROM information_schema.sequences WHERE sequence_schema = '${SCHEMA}';" ${DB_NAME}` \
           `psql -qAt -R\0 -c "SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) FROM information_schema.views WHERE table_schema = '${SCHEMA}';" ${DB_NAME}` ;
do
    psql -c "ALTER TABLE $tbl OWNER TO ${NEW_OWNER}" ${DB_NAME};
done

for func in `psql -qAt -R\0 -c "SELECT quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = '${SCHEMA}';" ${DB_NAME}` ;
do
    psql -c "ALTER FUNCTION $func OWNER TO ${NEW_OWNER}" ${DB_NAME};
done
unset IFS;

Love the script! One comment on an improvement... In our case the db owner had a "." in it (control panel uses domain name as username). I was able to modify the script to escape the "." but did notice it missed databases with an "_" (underscore) in their name. Anyway thought I'd mention it as a possible improvement - and thanks for the script - worked great for me!

Doesn't seem to love 0 in the schema name:

sudo -u postgres ~/bin/pg_change_schema_owner.sh -d prd -o prd -s survey360
ERROR:  relation "survey36" does not exist
ERROR:  syntax error at or near "."
LINE 1: ALTER TABLE .flyway_survey_history_table OWNER TO prd

I fixed my issues by using IFS=| and removing the -R\0 . I don't think it works with the separator null string.

same problem with the \0 - bash doesn't like it. The script is also missing the change for the materialized view.

for mview in $(psql -qAt -R\0 -c "select quote_ident(schemaname) || '.' || quote_ident(matviewname) from pg_catalog.pg_matviews where schemaname = '${SCHEMA}';" ${DB_NAME}) ;
do
psql -c "ALTER MATERIALIZED VIEW $mview OWNER TO ${NEW_OWNER}" ${DB_NAME};
done

I had to change owner for all schemas and tables in a database so I removed the SCHEMA option, and I filtered with WHERE schemaname != 'pg_catalog'. I have added "ALTER SCHEMA" for all schemas found. Furthermore I have removed the -R\0 and, since function parameters have white spaces I have processed them with a while cycle rather than a for one.
I have not materialized views.

And.... it works! Thanks for this useful script

#!/bin/bash

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

This script sets ownership for all tables, sequences, views, and functions for a given schema.
Run this script as your postgres OS user.

Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto
        Also merged changes from @sharoonthomas

bspkrs: Added function code based on http://dba.stackexchange.com/a/9710/31043
        and changed messy object quoting to use quote_ident().

OPTIONS:
   -h      Show this message
   -d      Database name
   -o      New Owner
   -s      Schema (defaults to public)
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 quote_ident(schemaname) || '.' || quote_ident(tablename) FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog';" ${DB_NAME}` \
           `psql -qAt -c "SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) FROM information_schema.sequences WHERE sequence_schema != 'pg_catalog';" ${DB_NAME}` \
           `psql -qAt -c "SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) FROM information_schema.views WHERE table_schema != 'pg_catalog';" ${DB_NAME}` ;
do
    psql -c "ALTER TABLE $tbl OWNER TO ${NEW_OWNER}" ${DB_NAME};
done

psql -qAt -c "SELECT DISTINCT quote_ident(table_schema) FROM information_schema.tables WHERE table_schema != 'pg_catalog';" ${DB_NAME} | while read schema ;
do
    psql -c "ALTER SCHEMA $schema OWNER TO ${NEW_OWNER}" ${DB_NAME};
done

psql -qAt -c "SELECT quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname != 'pg_catalog';" ${DB_NAME} | while read func ;
do
    psql -c "ALTER FUNCTION $func OWNER TO ${NEW_OWNER}" ${DB_NAME};
done