Skip to content

@gingerlime /change_db_owner.sh
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Postgresql - Changing ownership on all tables
#!/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
@spficklin

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

@gingerlime
Owner

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...

@BBoyan

great script, thnx a lot ;)

@mintsoft

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!

@thm1118

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

@jahrmando

Great script, Thank you!

@bspkrs

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

@RockyMM

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.