0 like 0 dislike
12 views

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (13.3k points)  

Bash script for changing all object’s (TABLES / SEQUENCES / VIEWS / FUNCTIONS / AGGREGATES / TYPES)ownership in a particular schema in one go. No special code included in a script, I basically picked the technique suggested and simplified the implementation method via script. Actually, REASSIGN OWNED BYcommand does most of the work smoothly, however, it changes database-wide objects ownership regardless of any schema. Two eventualities, where you may not use REASSIGN OWNED BY:

1. If the user by mistake creates all his objects with super-user(postgres), and later intend to change to other user, then REASSIGN OWNED BY will not work and it merely error out as:
postgres=# reassign owned by postgres to user1; ERROR: cannot reassign ownership of objects owned by role postgres because they are required by the database system

2. If user wish to change just only one schema objects ownership.
Either cases of changing objects, from “postgres” user to other user or just changing only one schema objects, we need to loop through each object by collecting object details from pg_catalog’s & information_schema and callingALTER TABLE / FUNCTION / AGGREGATE / TYPE etc.
I liked the technique of tweaking pg_dump output using OS commands(sed/egrep), because it known that by nature the pg_dump writes ALTER .. OWNER TO of every object (TABLES / SEQUENCES / VIEWS / FUNCTIONS / AGGREGATES / TYPES) in its output. Grep’ing those statements from pg_dump stdout by replacing new USER/SCHEMANAME with sed and then passing back those statements to psql client will fix the things even if the object owned by Postgres user. I used same approach in script and allowed user to pass NEW USER NAME and SCHEMA NAME, so to replace it in ALTER…OWNER TO.. statement.
Script usage and output:

sh change_owner.sh  -n new_rolename -S schema_name
-bash-4.1$ sh change_owner.sh -n user1 -S public
 Summary:
        Tables/Sequences/Views : 16
        Functions              : 43
        Aggregates             : 1
       Type                   : 2
Script
======
#!/bin/bash
# Script changes all the object ownership from x to y
# Provided following environment variable are
# mandatory PGUSER/PGDATABASE/PGPORT/PGHOST beforehand
#------------------------------------------------------
flag=0
usage()
{
 echo "Usage:"
 echo " sh change_owner.sh -n new_role -S schema_name_of_objects"
 echo ""
 echo " -n Give the new role name for the objects to be owned."
 echo " -S Schema in which objects has to be changed."
 echo " "
 echo " Note: Set PGUSER - PGDATABASE - PGPORT - PGHOST - PG binaries in PATH "
 echo -e " ---- before running the script.\n"
 exit 1;
}
if env |grep -q ^PGPORT= && env | grep -q ^PGUSER= && env | grep -q ^PGDATABASE= && env | grep -q ^PGHOST=
then
 PGDATABASE=`env | grep -i pgdatabase | cut -d "=" -f2`
else
 echo -e "\nScript need PGHOST/PGUSER/PGDATABASE/PGPORT environment variables set... \n"
 usage
fi
if ! command -v psql >/dev/null 2>&1; then
 echo -e "\nPostgreSQL psql client not set in Path..."
 usage
fi
PGSQLBIN=`command -v psql`
DUMPBIN=`command -v pg_dump`
while getopts ":n:S:" options; do
 case $options in
 n)
 flag=$((flag+1))
 NEWROLE=${OPTARG}
 ;;
 S)
 flag=$((flag+1))
 SCHEMAS=${OPTARG}
 ;;
 \?) echo "Unknown option: -$OPTARG" >&2; usage ;exit 1;;
 esac
done
if [ $flag -ne 2 ]; then
 echo -e "\n\t All options are Mandatory...!!!"
 usage
 exit 1
fi
UCHECK=$($PGSQLBIN -t -c "select 1 from pg_authid where rolname = '$NEWROLE';")
SCHECK=$($PGSQLBIN -t -c "select 1 from pg_namespace where nspname = '$SCHEMAS';")
UUCHECK=${UCHECK:-0}
SSCHECK=${SCHECK:-0}
if [ $UUCHECK -eq 1 ] && [ $SSCHECK -eq 1 ];
then
 $DUMPBIN -s -c -U $PGUSER ${PGDATABASE} | egrep "${SCHEMAS}\..*OWNER TO"| sed -e "s/OWNER TO.*;$/OWNER TO ${NEWROLE};/" >/tmp/oc.sql
 $DUMPBIN -s -c -U $PGUSER ${PGDATABASE} | egrep "${SCHEMAS}\..*OWNER TO"| sed -e "s/OWNER TO.*;$/OWNER TO ${NEWROLE};/" \
 | $PGSQLBIN -U postgres -d ${PGDATABASE} >>/dev/null
 echo -e "\n Summary: "
 echo " Tables/Sequences/Views : `grep -i "alter table" /tmp/oc.sql | wc -l`"
 echo " Functions : `grep -i "alter function" /tmp/oc.sql | wc -l`"
 echo " Aggregates : `grep -i "alter aggregate" /tmp/oc.sql | wc -l`"
 echo " Type : `grep -i "alter type" /tmp/oc.sql | wc -l`"
 echo ""
else
 echo " User ($NEWROLE) or Schema ($SCHEMAS) doesnt exist, pass the valid one..... "
fi

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.
...