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.

I'm trying to grant all privileges on all tables of a given database to a new postgres user (not the owner). It seems that GRANT ALL PRIVILEGES ON DATABASE my_db TO new_user; does not do that. After running said command successfully (as the postgres user), I get the following as new_user:

$ psql -d my_db
my_db => SELECT * FROM a_table_in_my_db;
ERROR:  permission denied for relation a_table_in_my_db

Two questions:

1) What does the command above do, then, if not granting all permissions on all tables on my_db?

2) What's the proper way to grant all permissions on all tables to a user? (including on all tables created in the future)

share|improve this question

1 Answer 1

up vote 27 down vote accepted

The answers to your questions come from the online PostgreSQL 8.4 docs.

  1. GRANT ALL PRIVILEGES ON DATABASE grants the CREATE, CONNECT, and TEMPORARY privileges on a database to a role (users are properly referred to as roles). None of those privileges actually permits a role to read data from a table; SELECT privilege on the table is required for that.

  2. I'm not sure there is a "proper" way to grant all privileges on all tables to a role. The best way to ensure a given role has all privileges on a table is to ensure that the role owns the table. By default, every newly created object is owned by the role that created it, so if you want a role to have all privileges on a table, use that role to create it.

    PostgreSQL 9.0 introduces the following syntax that is almost what you want:

    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

    The rub is that if you create tables in schemas outside the default "public" schema, this GRANT won't apply to them. If you do use non-public schemas, you'll have to GRANT the privileges to those schemas separately.

share|improve this answer
1  
can a database have multiple owners? if so, how to add another owner? –  rz. Nov 4 '10 at 6:31
    
no I don't think a database can have more than one owner, you could give them all the writes of an owner though –  giant_squid Nov 4 '10 at 7:09
4  
don't forget you have to make the same on sequences: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user; or you can't insert any records. –  user179328 Jul 6 '13 at 2:10

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.