Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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;
share|improve this question
add comment

6 Answers

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.

share|improve this answer
    
I'll upgrade soon, so this is really good news. Thanks! –  Adam Matan Jun 26 '11 at 15:31
add comment
up vote 6 down vote accepted

My (non-one-liner) solution:

#!/bin/bash

for table in `echo "SELECT schemaname || '.' || 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.

share|improve this answer
2  
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
add comment

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.

share|improve this answer
add comment

Check this blog post.

share|improve this answer
add comment

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
share|improve this answer
    
In English please. –  Linger Nov 30 '12 at 14:14
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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