Something that would implement the following pseudo-code:
GRANT SELECT ON TABLE * TO my_new_user;
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.
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.
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;
$foo = username you want to grant permissions for
$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.
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..
I'm working with postgres 8.4 and to give all privileges to a user do the following:
#!/bin/bash for table in `echo "SELECT schemaname||'.'||relname FROM pg_stat_all_tables WHERE schemaname NOT IN('pg_catalog','pg_toast','information_schema')" | psql -t db `; do echo "grant select on table $table to my_new_user;" echo "grant select on table $table to my_new_user;" | psql db done
one way to fix this is to write a stored procedure. unfortunately there is no "grant everything to all tables" command or so. you really need a procedure or some external shell script maybe to make this work.
I ended up doing this, and it worked:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;