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;

score 237 · Accepted Answer · answered Jun 26 '11 at 14:00

237

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.

answered Jun 26 '11 at 14:00

TimH

2,486
1
14
3

I'll upgrade soon, so this is really good news. Thanks! – Adam Matan Jun 26 '11 at 15:31
Does this affect all databases on the server that use the public schema? – kristianp May 28 '14 at 07:20
12

If I create a new table, will this user have access to the newly created table? – GuiSim Sep 05 '14 at 19:13
14

@GuiSim No, You have to set the `default privileges` on a schema, where ytou create the table: http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html – SkyRaT Jan 24 '16 at 09:33
@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. – SkyRaT Jan 24 '16 at 09:36
Note: this doesn't work on Redshift yet – Faiz Oct 06 '16 at 06:56
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. – szeitlin Jul 03 '18 at 18:13
@szeitlin Here are two links that should help you figure that out (found by Googling "postgresql WARNING: no privileges were granted for "): - https://www.postgresql.org/message-id/24612.1210687964%40sss.pgh.pa.us - https://stackoverflow.com/questions/25691037/postgresql-permissions-explained Hope that helps. – TimH Jul 05 '18 at 11:05
I ended up doing this (see below): https://serverfault.com/a/919358/282107 – szeitlin Jul 05 '18 at 18:05
@TimH you also have to [grant schema access](https://dba.stackexchange.com/a/91974/92162) – deFreitas Aug 11 '18 at 16:16

score 14 · Answer 2 · edited May 03 '22 at 03:53

14

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.

edited May 03 '22 at 03:53

leeand00

4,807
13
64
106

answered Aug 30 '09 at 14:36

Adam Matan

12,504
19
54
73

3

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. – Magnus Hagander Aug 30 '09 at 18:33
1

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](http://serverfault.com/a/284278/142214). – Basil Bourque Jul 06 '14 at 05:48
this one doesn't work where table or schema names contain uppercase letters. Adding a modified version below – anneb Mar 26 '19 at 19:11

score 13 · Answer 3 · edited Oct 30 '14 at 05:46

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.

score 10 · Answer 4 · answered Jul 03 '18 at 18:20

10

I ended up doing this, and it worked:

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

answered Jul 03 '18 at 18:20

szeitlin

201
2
3

very good when tables are created afterwards, and read access is meant to be granted by default – arhak May 16 '20 at 13:10

score 4 · Answer 5 · answered Nov 23 '21 at 16:37

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.

Grant privileges to all existing tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;

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;
```

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.

score 2 · Answer 6 · edited Aug 05 '14 at 13:32

2

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

edited Aug 05 '14 at 13:32

HBruijn

72,524
21
127
192

answered Aug 05 '14 at 13:25

stox

41
4

score 1 · Answer 7 · edited Jun 14 '13 at 12:01

1

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

edited Jun 14 '13 at 12:01

Rup

255
5
15

answered Nov 30 '12 at 00:38

wilson

11
1

1

In English please. – Linger Nov 30 '12 at 14:14

score 0 · Answer 8 · answered Apr 05 '10 at 18:04

0

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.

answered Apr 05 '10 at 18:04

postgresql007

11
2

score 0 · Answer 9 · answered Mar 26 '19 at 19:17

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

GRANT SELECT to all tables in postgresql

9 Answers9

Linked