“Grant all” in Postgres

Warning - Old Content

This post is quite old, and it might not apply anymore, or maybe there's a better way to do the same thing nowadays. Take with a big grain of salt.

The security model in postgres is very granular, and is locked down by default. this is great for production, but is kind of a pain as a developer working locally. Coming from MySQL, it’s natural to try “GRANT ALL ON * TO some_user;” for development. Unfortunately, that just doesn’t work. From a fair bit of googling, the general advice is “just write a script to do that”, which is very unhelpful (You’re trying to write a DB app, not shell scripts).

To save others the trouble of writing their own script, here’s a quick script to do it. Just save it, make it executable, then run it as your admin user.

#!/bin/bash

# location of psql executable
PSQL="/usr/bin/psql"

DB_NAME=$1
GRANT_USERNAME=$2

if [ "$GRANT_USERNAME" = "" ]; then
echo "usage: $0 dbname grant_username"
exit 1
fi

# grant perms on the db as a whole
COMMAND="GRANT ALL ON DATABASE $DB_NAME TO $GRANT_USERNAME;"
echo "$COMMAND"
$PSQL -q --command="$COMMAND"

# this SQL will list all tables/relations.  
# it's based on the internal \dt command
LIST_SQL="SELECT c.relname as \"Name\"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;"

# iterate over the relations and grant perms on each
for table in `$PSQL $DB_NAME --command="$LIST_SQL" -P tuples_only`; do
COMMAND="GRANT ALL ON $table TO $GRANT_USERNAME;"
echo $COMMAND
$PSQL $DB_NAME -q --command="$COMMAND"
done

exit 0

You can use the \z command to confirm the permissions, somewhat like mysql’s “show grants”:

\z relation_name  

Note: there may be a quicker/easier way to do this, but this works for me.