Reply to comment

Have you ever tried to grant all privileges on a database in Postgre, only to find that "GRANT ALL PRIVILEGES ON DATABASE foo TO bar" doesn't actually do that?

PgSQL makes it quite hard to grant all privileges on a database to a user, because what they don't tell you up front is that in order to get this done as you'd expect, you have to grant the privileges per table to the user.

I have written a script called pg-help that does all the nasty dirty work for you! You can use it to grant all privileges on a database to a user frighteningly easy. Feel free to comment if you found this script useful. Thanks!

First, I'll show usage:

pg-help grant all mydb myuser

Yes, it's that simple! To specify more granular privileges (and in this case, say don't grant privileges to sequences) try this:

pg-help grant --no-sequence "SELECT, UPDATE" mydb myuser

Piece of cake!

So, here's the script:

#!/bin/bash

## Thanks to the user in #postgresql, RhodiumToad :)

function usage {
	echo "Postgres database-wide privilege operations by cha0s"
	echo "Usage: pg-help [OPTION] {grant|revoke} PRIVILEGES DATABASE USERNAME"
	
	echo "Options:"
	echo "  --help                              Print this usage and exit."  
	echo "  -v, --verbose                       Print the executed SQL."  
	echo "  -nt, --no-table                     Don't affect tables."  
	echo "  -ns, --no-sequence                  Don't affect sequences."  
	echo "  -nv, --no-view                      Don't affect views."  
	echo
	echo "PRIVILEGES may either be 'all' (which is translated to 'all privileges'), or a"
	echo "QUOTED comma-delimited list of privileges to affect. Privileges are:"
	echo "SELECT INSERT UPDATE DELETE RULE REFERENCES TRIGGER"
	echo
	echo "DATABASE is your Postgres database name."
	echo
	echo "USERNAME is your Postgres user name."
	echo
	echo "It is assumed that this script will be run as a user with enough privileges to"
	echo "effect the changes."
	echo
	echo "Report bugs, feature requests, love letters to cha...@therealcha0s.net "
	
	exit
}

## Parse args
until test -z "$1"; do
	case "$1" in
		--help)
			usage
		;;
		--verbose | -v)
			verbose=1
		;;
		--no-table | -nt)
			nt=1
		;;
		--no-sequence | -ns)
			ns=1
		;;
		--no-view | -nv)
			nv=1
		;;
		*)
			break;
		;;
	esac
	shift
done

case "$1" in
	grant | g)
		OP="grant"
		OP_PREP="to"
	;;
	revoke | r)
		OP="revoke"
		OP_PREP="from"
	;;
	*)
		echo "Invalid operation specified." >&2
		usage
	;;
esac

if test -z "$2"; then
	echo "No privileges specified." >&2
	usage
fi

if test "$2" = "all"; then
	OP_PRIV="all privileges"
else
	OP_PRIV=$2
fi

if test -z "$3"; then
	echo "No database specified." >&2
	usage
fi

DB_NAME=$3

if test -z "$4"; then
	echo "No user specified." >&2
	usage
fi

DB_USER=$4

if test -z "$nt"; then
	REL_TABLE="r"
fi
if test -z "$ns"; then
	REL_SEQ="S"
fi
if test -z "$nv"; then
	REL_VIEW="v"
fi

TABLE_SQL="SELECT array_to_string(
	ARRAY(
		SELECT quote_ident(c.relname) 
		FROM pg_catalog.pg_class c 
		LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
		WHERE c.relkind IN ('${REL_TABLE}', '${REL_SEQ}', '${REL_VIEW}')
		AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
		AND pg_catalog.pg_table_is_visible(c.oid)
	), 
	', '
);"

TABLE_LIST=`psql -t -c "${TABLE_SQL}" ${DB_NAME}`
if test -z "$TABLE_LIST" || test "$TABLE_LIST" = " "; then
	echo "No tables were selected based on the parameters specified." >&2
	echo "Invoke with the --help parameter to view usage details." >&2
	exit
fi

OP_SQL="${OP} ${OP_PRIV} on ${TABLE_LIST} ${OP_PREP} ${DB_USER}" 

if test -n "$verbose"; then
	echo $OP_SQL
fi
psql -c "${OP_SQL}" ${DB_NAME}

Enjoy! ^_^