Skip to content

Instantly share code, notes, and snippets.

@mhglover
Last active December 16, 2015 14:59
Show Gist options
  • Save mhglover/5452615 to your computer and use it in GitHub Desktop.
Save mhglover/5452615 to your computer and use it in GitHub Desktop.
bash script for adding permissions on every table in every database on postgresql
#!/bin/bash
hostname="my.db.server.hostname.com"
user="myusername"
#get a list of all the databases
databases=`psql -wl -h $hostname | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | grep -v ":" | awk {'print $1'}`
for database in $databases; do
#get a list of all the tables
tables=$(psql -h $hostname $database -A -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';")
for table in $tables ; do
echo "Granting select to $user on $table"
psql -h $hostname $database -c "GRANT SELECT ON $table to $user;"
done
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment