Ever tried the subject task with PostgreSQL? It's a bit difficult. You need something along the lines of this script, which generates the required GRANT statements:

#!/bin/sh

DB="$1"
USER="$2"
PSQL="psql -q -n -A -t"
SCHEMES="'public'"

if [ -z "$1" ]; then
        echo No name given
        exit
fi

if [ -z "$2" ]; then
        USER="$1"
fi
echo "-- Granting rights on $DB to $USER ($SCHEMES)"
# tables
Q="select 'grant all on '||schemaname||'.'||tablename||' to \"$USER\";' from pg_tables where schemaname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# views
Q="select 'grant all on '||schemaname||'.'||viewname||' to \"$USER\";' from pg_views where schemaname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# sequences
Q="select 'grant all on function '||n.nspname||'.'||p.proname||'('||oidvectortypes(p.proargtypes)||') to \"$USER\";' from pg_proc p, pg_namespace n where n.oid = p.pronamespace and n.nspname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

# functions
Q="select 'grant all on '||n.nspname||'.'||c.relname||' to \"$USER\";' from pg_class c, pg_namespace n where n.oid = c.relnamespace and c.relkind IN ('S') and n.nspname in ($SCHEMES);"
$PSQL -c "$Q" "$DB";

This will generate a script which in turn will grant the rights. A typical call (as the postgres user):

./grant mydatabase myuser | psql mydatabase

posted at 23:27 | path: /unix | permanent link to this entry