Postgresql doesn’t make it very easy to grant permissions on all tables and sequences to a particular user, so here’s a little script I’ve hacked up to help.
#!/bin/bash
DBNAME=$1
SCHEMA=$2
USER=$3
for table in `echo "SELECT relname FROM pg_stat_all_tables WHERE schemaname='$SCHEMA';" | psql $DBNAME | grep -v "pg_" | grep "^ "`;
do
echo "GRANT SELECT,UPDATE,DELETE,INSERT ON TABLE $SCHEMA.$table to $USER;"
done
for grant in `echo "select n.nspname||'.'||c.relname from pg_class c, pg_namespace n where n.oid = c.relnamespace and c.relkind in ('S') and n.nspname in ('$SCHEMA');" | psql $DBNAME | grep "^ $SCHEMA"`;
do
echo "GRANT ALL ON $grant TO $USER";
done
Run, e.g. grant.sh – if you don’t use schemas then it will be ‘public’.
how is this script working can you please explain its working
Thank you! Script working on my servers.