Created

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

Postgresql - Changing ownership on all tables

View change_db_owner.sh
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
#!/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

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

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!

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.