82

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

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.

  • 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 7:20
  • 5
    If I create a new table, will this user have access to the newly created table? – GuiSim Sep 5 '14 at 19:13
  • 8
    @GuiSim No, You have to set the default privileges on a schema, where ytou create the table: postgresql.org/docs/current/static/… – SkyRaT Jan 24 '16 at 9: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 9:36
11

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.

  • 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. – Basil Bourque Jul 6 '14 at 5:48
9

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.

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

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
  • 1
    In English please. – Linger Nov 30 '12 at 14:14
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.

0

I ended up doing this, and it worked:

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.