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

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;

To grant permissions also for tables created in the future use:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;

Here's the link.

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

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

I ended up doing this, and it worked:

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO PUBLIC;

Answer 5

I ended up here because my DB user saw only a few tables and not the newer ones. If this is your case, this has helped me.

1. Grant privileges to all existing tables:

   GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
   

2. Grant privileges to all new tables to be created in future (via default privileges):

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;
   

3. You can also double-check that all tables are granted correctly.

   • Count all existing tables:

     SELECT COUNT(*)
     FROM pg_catalog.pg_tables
     WHERE schemaname != 'pg_catalog' AND
           schemaname != 'information_schema';
     

   • Count all tables the user has access to:

     SELECT COUNT(*)
     FROM information_schema.role_table_grants
     WHERE grantee = 'user';
     

   The count of last two queries must be the same.

Answer 6

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

Answer 7

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

Answer 8

DO $$
DECLARE
    schemaname text;
BEGIN
    FOR schemaname IN (SELECT nspname FROM pg_namespace) LOOP
        EXECUTE 'GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA ' || schemaname || ' TO user';
    END LOOP;
END $$;

Answer 9

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.

Answer 10

The (one-liner solution) script by Adam Matan is great when there are many schema's, but it doesn't work where schema names or table names contain uppercase letters or special characters.

Modified version:

#!/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