PostgreSQL grants on all tables
Posted on September 8, 2010, under GNU/Linux.
As much as I love PostgreSQL, its system of managing user privileges is a pain in the ass. PostgreSQL has no equivalent to MySQL’s method of granting a privilege on all of the tables in a database, including tables that you create in the future.
In MySQL, it’s as easy as:
GRANT SELECT ON db_name.* TO user@hostname;
The best that I’ve been able to achieve with PostgreSQL is this abomination:
$ cat generate_grants.txt
SELECT 'grant select on '||schemaname||'.'||tablename||' to user_name;'
FROM pg_tables
WHERE schemaname IN ('public')
ORDER BY schemaname, tablename;
$
$ cat generate_grants.txt | psql -U nickh -W -d database \|
grep '^ grant' >grants.txt
$
$ head -2 grants.txt
grant select on public.table1 to user_name;
grant select on public.table2 to user_name;
$
$ cat grants.txt | psql -U nickh -W -d database
*shudder*
Thanks to Ben Williams’ blog post for the initial query to generate the GRANT statements.
