Skip to content

Instantly share code, notes, and snippets.

@dmutende
Last active July 8, 2020 08:33
Show Gist options
  • Save dmutende/4c5da8d26e57f0900e923ff3baea2c3c to your computer and use it in GitHub Desktop.
Save dmutende/4c5da8d26e57f0900e923ff3baea2c3c to your computer and use it in GitHub Desktop.
ALTER owner to tables, sequences and views on PostgreSQL databases using bash
#!/bin/bash
DB=[YOUR_DB_NAME]
OWNER=[NEW_OWNER]
# alter tables owner
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" $DB` ; do psql -c "alter table \"$tbl\" owner to $OWNER" $DB ; done
# alter sequences owner
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" $DB` ; do psql -c "alter table \"$tbl\" owner to $OWNER" $DB ; done
# alter views owner
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" $DB` ; do psql -c "alter table \"$tbl\" owner to $OWNER" $DB ; done
#set timezone
psql -c "ALTER DATABASE\"$DB\" SET timezone TO 'Africa/Nairobi';" $DB
@muneneevans
Copy link

Does this grant privileges on all table to the user

@dmutende
Copy link
Author

Yeah. The first one for tables, then for sequences then for views. Respectively. It'll loop through the various database objects

@dmutende
Copy link
Author

I've corrected a minor error on # alter tables owner, It was missing the backticks (`)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment