Last active
January 11, 2024 20:26
-
-
Save arssher/69ea1ad34feaa8e24ab52bc8eb93a2dd to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| apt-get update | |
| apt-get install bc | |
| psql -qtA -U cloud_admin postgres -c "select datname from pg_database where datname "'!'"= 'postgres' and datname "'!'"~ 'template.*';" > dbs |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| start_time="$(date -u +%s.%N)" | |
| cat dbs | while read db | |
| do | |
| alter_sql=$(cat <<-'END' | |
| DO $$ | |
| DECLARE | |
| schema_owner TEXT; | |
| BEGIN | |
| IF EXISTS( | |
| SELECT nspname | |
| FROM pg_catalog.pg_namespace | |
| WHERE nspname = 'public' | |
| ) | |
| THEN | |
| SELECT nspowner::regrole::text | |
| FROM pg_catalog.pg_namespace | |
| WHERE nspname = 'public' | |
| INTO schema_owner; | |
| IF schema_owner = 'cloud_admin' OR schema_owner = 'zenith_admin' | |
| THEN | |
| ALTER SCHEMA public OWNER TO nutorbitx; | |
| END IF; | |
| END IF; | |
| END | |
| $$; | |
| END | |
| ) | |
| echo $(date +"%T.%N") alter $db | |
| psql -U cloud_admin ${db} -c "${alter_sql}" | |
| grant_sql=$(cat <<-'END' | |
| DO $$ | |
| BEGIN | |
| IF EXISTS( | |
| SELECT nspname | |
| FROM pg_catalog.pg_namespace | |
| WHERE nspname = 'public' | |
| ) AND | |
| current_setting('server_version_num')::int/10000 >= 15 | |
| THEN | |
| IF EXISTS( | |
| SELECT rolname | |
| FROM pg_catalog.pg_roles | |
| WHERE rolname = 'web_access' | |
| ) | |
| THEN | |
| GRANT CREATE ON SCHEMA public TO web_access; | |
| END IF; | |
| END IF; | |
| END | |
| $$; | |
| END | |
| ) | |
| echo $(date +"%T.%N") granting $db | |
| psql -U cloud_admin ${db} -c "${grant_sql}" | |
| done | |
| end_time="$(date -u +%s.%N)" | |
| elapsed="$(bc <<<"$end_time-$start_time")" | |
| echo "$elapsed seconds elapsed" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment