Postgresql: what does GRANT ALL PRIVILEGES ON DATABASE do?

Solution 1:

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.

Solution 2:

It is possible to set up multiple logins to act as the database owner:

• Create a "nologin" role to act as the owner: create role dbowner nologin
• Change the owner of your database to this: alter database mydb owner dbowner
• Grant all your logins to this new role: grant dbowner to user1, user2

Now, if either user1 or user2 login, they have all permissions on "mydb" without any further grants required.

However, I would consider this solution carefully. It is tempting to have your web application use one of these logins to avoid the pain of creating additional grants whenever the schema is updated, but you're removing a very useful form of protection this way. Use the above solution if you really do want multiple "admins", but stick with the "grant all privileges on all tables in schema ..." pattern above for the login for your "normal use" application.