-
-
Save bspkrs/b997ed7f1eb1268f3403 to your computer and use it in GitHub Desktop.
| #!/bin/bash | |
| usage() | |
| { | |
| cat << EOF | |
| usage: $0 options | |
| This script sets ownership for all tables, sequences, views, and functions for a given schema. | |
| Run this script as your postgres OS user. | |
| Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto | |
| Also merged changes from @sharoonthomas | |
| bspkrs: Added function code based on http://dba.stackexchange.com/a/9710/31043 | |
| and changed messy object quoting to use quote_ident(). | |
| OPTIONS: | |
| -h Show this message | |
| -d Database name | |
| -o New Owner | |
| -s Schema (defaults to public) | |
| EOF | |
| } | |
| DB_NAME=""; | |
| NEW_OWNER=""; | |
| SCHEMA="public"; | |
| while getopts "hd:o:s:" OPTION; do | |
| case $OPTION in | |
| h) | |
| usage; | |
| exit 1; | |
| ;; | |
| d) | |
| DB_NAME=$OPTARG; | |
| ;; | |
| o) | |
| NEW_OWNER=$OPTARG; | |
| ;; | |
| s) | |
| SCHEMA=$OPTARG; | |
| ;; | |
| esac | |
| done | |
| if [[ -z $DB_NAME ]] || [[ -z $NEW_OWNER ]]; then | |
| usage; | |
| exit 1; | |
| fi | |
| # Using the NULL byte as the separator as its the only character disallowed from PG table names | |
| IFS=\0; | |
| for tbl in `psql -qAt -R\0 -c "SELECT quote_ident(schemaname) || '.' || quote_ident(tablename) FROM pg_catalog.pg_tables WHERE schemaname = '${SCHEMA}';" ${DB_NAME}` \ | |
| `psql -qAt -R\0 -c "SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) FROM information_schema.sequences WHERE sequence_schema = '${SCHEMA}';" ${DB_NAME}` \ | |
| `psql -qAt -R\0 -c "SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) FROM information_schema.views WHERE table_schema = '${SCHEMA}';" ${DB_NAME}` ; | |
| do | |
| psql -c "ALTER TABLE $tbl OWNER TO ${NEW_OWNER}" ${DB_NAME}; | |
| done | |
| for func in `psql -qAt -R\0 -c "SELECT quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = '${SCHEMA}';" ${DB_NAME}` ; | |
| do | |
| psql -c "ALTER FUNCTION $func OWNER TO ${NEW_OWNER}" ${DB_NAME}; | |
| done | |
| unset IFS; |
Doesn't seem to love 0 in the schema name:
sudo -u postgres ~/bin/pg_change_schema_owner.sh -d prd -o prd -s survey360
ERROR: relation "survey36" does not exist
ERROR: syntax error at or near "."
LINE 1: ALTER TABLE .flyway_survey_history_table OWNER TO prd
I fixed my issues by using IFS=| and removing the -R\0 . I don't think it works with the separator null string.
same problem with the \0 - bash doesn't like it. The script is also missing the change for the materialized view.
for mview in $(psql -qAt -R\0 -c "select quote_ident(schemaname) || '.' || quote_ident(matviewname) from pg_catalog.pg_matviews where schemaname = '${SCHEMA}';" ${DB_NAME}) ;
do
psql -c "ALTER MATERIALIZED VIEW
done
I had to change owner for all schemas and tables in a database so I removed the SCHEMA option, and I filtered with WHERE schemaname != 'pg_catalog'. I have added "ALTER SCHEMA" for all schemas found. Furthermore I have removed the -R\0 and, since function parameters have white spaces I have processed them with a while cycle rather than a for one.
I have not materialized views.
And.... it works! Thanks for this useful script
#!/bin/bash
usage()
{
cat << EOF
usage: $0 options
This script sets ownership for all tables, sequences, views, and functions for a given schema.
Run this script as your postgres OS user.
Credit: Based on http://stackoverflow.com/a/2686185/305019 by Alex Soto
Also merged changes from @sharoonthomas
bspkrs: Added function code based on http://dba.stackexchange.com/a/9710/31043
and changed messy object quoting to use quote_ident().
OPTIONS:
-h Show this message
-d Database name
-o New Owner
-s Schema (defaults to public)
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 quote_ident(schemaname) || '.' || quote_ident(tablename) FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog';" ${DB_NAME}` \
`psql -qAt -c "SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) FROM information_schema.sequences WHERE sequence_schema != 'pg_catalog';" ${DB_NAME}` \
`psql -qAt -c "SELECT quote_ident(table_schema) || '.' || quote_ident(table_name) FROM information_schema.views WHERE table_schema != 'pg_catalog';" ${DB_NAME}` ;
do
psql -c "ALTER TABLE $tbl OWNER TO ${NEW_OWNER}" ${DB_NAME};
done
psql -qAt -c "SELECT DISTINCT quote_ident(table_schema) FROM information_schema.tables WHERE table_schema != 'pg_catalog';" ${DB_NAME} | while read schema ;
do
psql -c "ALTER SCHEMA $schema OWNER TO ${NEW_OWNER}" ${DB_NAME};
done
psql -qAt -c "SELECT quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname != 'pg_catalog';" ${DB_NAME} | while read func ;
do
psql -c "ALTER FUNCTION $func OWNER TO ${NEW_OWNER}" ${DB_NAME};
done
Love the script! One comment on an improvement... In our case the db owner had a "." in it (control panel uses domain name as username). I was able to modify the script to escape the "." but did notice it missed databases with an "_" (underscore) in their name. Anyway thought I'd mention it as a possible improvement - and thanks for the script - worked great for me!