Skip to content

Instantly share code, notes, and snippets.

@kehh

kehh/change_db_owner.sh

Forked from sharoonthomas/change_db_owner.sh
Created October 19, 2012 01:42
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kehh/3915802 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kehh/3915802 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. kehh revised this gist Oct 19, 2012. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions change_db_owner.sh
    100644 → 100755
    View file
    Original file line number Diff line number Diff line change
    @@ -21,7 +21,7 @@ DB_NAME=
    NEW_OWNER=
    PG_USER=

    while getopts "hd:o:U:" OPTION
    while getopts "h:d:o:U:" OPTION
    do
    case $OPTION in
    h)
    @@ -52,4 +52,4 @@ for tbl in `psql -U${PG_USER} -qAt -c "select tablename from pg_tables where sch
    `psql -U${PG_USER} -qAt -c "select table_name from information_schema.views where table_schema = 'public';" ${DB_NAME}` ;
    do
    psql -U${PG_USER} -c "alter table \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
    done
    done
  2. kehh revised this gist Oct 19, 2012. 1 changed file with 13 additions and 7 deletions.
    20 changes: 13 additions & 7 deletions change_db_owner.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -12,14 +12,16 @@ Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto
    OPTIONS:
    -h Show this message
    -d Database name
    -U Postgres user to run this script as
    -o Owner
    EOF
    }

    DB_NAME=
    NEW_OWNER=
    PG_USER=

    while getopts "hd:o:" OPTION
    while getopts "hd:o:U:" OPTION
    do
    case $OPTION in
    h)
    @@ -31,19 +33,23 @@ do
    ;;
    o)
    NEW_OWNER=$OPTARG
    ;;
    ;;
    U)
    PG_USER=$OPTARG
    ;;
    esac
    done

    echo "User $PG_USER"
    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}` ;
    for tbl in `psql -U${PG_USER} -qAt -c "select tablename from pg_tables where schemaname = 'public';" ${DB_NAME}` \
    `psql -U${PG_USER} -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" ${DB_NAME}` \
    `psql -U${PG_USER} -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
    psql -U${PG_USER} -c "alter table \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
    done
  3. Sharoon Thomas revised this gist Jun 11, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion change_db_owner.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -45,5 +45,5 @@ for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'pu
    `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} ;
    psql -c "alter table \"$tbl\" owner to ${NEW_OWNER}" ${DB_NAME} ;
    done
  4. gingerlime created this gist Apr 24, 2012.
    49 changes: 49 additions & 0 deletions change_db_owner.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,49 @@
    #!/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
    -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