Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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

This comment has been minimized.

Show comment Hide comment
@spficklin

spficklin Jun 19, 2012

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⭕️" 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

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⭕️" 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

This comment has been minimized.

Show comment Hide comment
@gingerlime

gingerlime Jun 19, 2012

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

Owner

gingerlime commented Jun 19, 2012

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

This comment has been minimized.

Show comment Hide comment
@BBoyan

BBoyan Mar 1, 2013

great script, thnx a lot ;)

BBoyan commented Mar 1, 2013

great script, thnx a lot ;)

@mintsoft

This comment has been minimized.

Show comment Hide comment
@mintsoft

mintsoft May 4, 2013

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!

mintsoft commented May 4, 2013

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

This comment has been minimized.

Show comment Hide comment
@thm1118

thm1118 Mar 3, 2014

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

thm1118 commented Mar 3, 2014

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

@jahrmando

This comment has been minimized.

Show comment Hide comment
@jahrmando

jahrmando Jun 18, 2014

Great script, Thank you!

Great script, Thank you!

@bspkrs

This comment has been minimized.

Show comment Hide comment
@bspkrs

bspkrs Oct 7, 2014

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

bspkrs commented Oct 7, 2014

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

@RockyMM

This comment has been minimized.

Show comment Hide comment
@RockyMM

RockyMM Mar 16, 2015

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.

RockyMM commented Mar 16, 2015

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