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”:
Note: there may be a quicker/easier way to do this, but this works for me.