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;

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.

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.

Solution 4

I ended up doing this, and it worked:

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

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

View more solutions

Comments

[Magnus Hagander]

If you use pg_stat_user_tables instead of all_tables, you don't need your grep... Also, pass -A -t to psql to get rid of formatted output.

[Adam Matan]

I'll upgrade soon, so this is really good news. Thanks!

[Linger]

In English please.

[kristianp]

Does this affect all databases on the server that use the public schema?

[Basil Bourque]

Note that as of Postgres 9.0, this answer's approach is doing it the hard way. In 9.x, we now have the "ON ALL" seen in this other answer.

[GuiSim]

If I create a new table, will this user have access to the newly created table?

[SkyRaT]

@GuiSim No, You have to set the default privileges on a schema, where ytou create the table: postgresql.org/docs/current/static/…

[SkyRaT]

@kristianp No, every database in the PG cluster has its own public schema. It affect all tables (functions) in the schema public for current DB you are connected to.

[Faiz]

Note: this doesn't work on Redshift yet

[szeitlin]

I'm trying this, but I'm getting "WARNING: no privileges were granted for xxx" for every table. This is not a particularly helpful message as it tells me nothing about what the problem is.

[TimH]

@szeitlin Here are two links that should help you figure that out (found by Googling "postgresql WARNING: no privileges were granted for "): - postgresql.org/message-id/24612.1210687964%40sss.pgh.pa.us - stackoverflow.com/questions/25691037/… Hope that helps.

[szeitlin]

I ended up doing this (see below): serverfault.com/a/919358/282107

[deFreitas]

@TimH you also have to grant schema access

[anneb]

this one doesn't work where table or schema names contain uppercase letters. Adding a modified version below

[arhak]

very good when tables are created afterwards, and read access is meant to be granted by default