GRANT SELECT to all tables in postgresql

Question

Is there a one-liner that grants the SELECT permissions to a new user postgresql?

Something that would implement the following pseudo-code:

GRANT SELECT ON TABLE * TO my_new_user;

Answer 1

My (non-one-liner) solution:

#!/bin/bash

for table in `echo "SELECT relname FROM pg_stat_all_tables;" | psql database | grep -v "pg_" | grep "^ "`;
do
    echo "GRANT SELECT ON TABLE $table to my_new_user;"
    echo "GRANT SELECT ON TABLE $table to my_new_user;" | psql database
done

Run from the privileged user, it worked like a charm.

Answer 2

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.

Answer 3

This can be done with a two-step process.

1. 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")

2. 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.

Answer 4

Check this blog post.

Answer 5

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.