Postgresql - Changing ownership on all tables

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

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
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 tablename from pg_tables where schemaname = 'public';" ${DB_NAME}` \
           `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" ${DB_NAME}` \
           `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" ${DB_NAME}` ;
do  
    psql -c "alter table \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
done

Here's an update that allows you to specify the schema as well:

!/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

OPTIONS:
-h Show this message
-d Database name
-s Schema
-o Owner
EOF
}

DB_NAME=
SCHEMA=
NEW_OWNER=

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

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

echo "Database: ${DB_NAME}"
echo "Schema: ${SCHEMA}"
echo "User: ${NEW_OWNER}"

for tbl in psql -qAt -c "select tablename from pg_tables where schemaname = '${SCHEMA}';" ${DB_NAME}
psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = '${SCHEMA}';" ${DB_NAME}
psql -qAt -c "select table_name from information_schema.views where table_schema = '${SCHEMA}';" ${DB_NAME} ;
do
psql -c "alter table ${SCHEMA}.${tbl} owner to ${NEW_OWNER}" ${DB_NAME} ;
done

Perhaps it's best if you fork my gist and then update it rather than posting it as comment? It would look nicer and would be easier for others to use or fork...

great script, thnx a lot ;)

Hiya,

I've forked this and added support for a schema other than public and quoted_identifier table names containing whitespace, not the prettiest implementation but it works!

more simple ,one line code: https://gist.github.com/thm1118/9320610

Great script, Thank you!

Here's my fork of this that adds in functions as well: https://gist.github.com/bspkrs/b997ed7f1eb1268f3403
Thanks for the original!

Here's my fork that updates tables not in 'pg_catalog' schema https://gist.github.com/RockyMM/dd89aed75f3de9a2cd76 This is since the tables in my database are prevalently not in "public" schema.

Thanks a lot, great script!!

This uses ALTER TABLE on sequences and views... it should probably use ALTER SEQUENCE and ALTER VIEW, respectively.

Great script. Thank you.

Here is my updated version that includes a --dry-run mode, which will list what objects will be transferred (good for checking before and after). Also, I've added support for custom enum types:
https://gist.github.com/ureyes84/ee4dc2604dfd940bc8c24fae1bf87035