public
Last active

Postgresql - Changing ownership on all tables

  • Download Gist
change_db_owner.sh
Shell
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

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!

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.