GRANT SELECT to all tables in postgresql
Solution 1:
I thought it might be helpful to mention that, as of 9.0, postgres does have the syntax to grant privileges on all tables (as well as other objects) in a schema:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user;
Here's the link.
Solution 2:
My (non-one-liner) solution:
#!/bin/bash
for table in `echo "SELECT schemaname || '.' || relname FROM pg_stat_user_tables;" | psql -A -t my_database_name`;
do
echo "GRANT SELECT ON TABLE $table to my_new_user;"
echo "GRANT SELECT ON TABLE $table to my_new_user;" | psql my_database_name
done
Run from the privileged user, it worked like a charm.
Solution 3:
This can be done with a two-step process.
Run this query:
select 'grant all on '||schemaname||'.'||tablename||' to $foo;' from pg_tables where schemaname in ('$bar', '$baz') order by schemaname, tablename;
Replacements:
$foo
= username you want to grant permissions for
$bar
,$baz
= schemas you want to grant permissions in (can be just "public")That's going to give you a list of queries that will generate the required permissions. Copy the output, paste it into another query, and execute.
Solution 4:
I ended up doing this, and it worked:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO PUBLIC;
Solution 5:
This is what I used:
psql dbname -tc "select 'grant select on '||relname||' to readonly;' from pg_stat_user_tables" | psql dbname
I feel it's more natural to do formatting and where-clauses in sql..