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)
Best Answer
The answers to your questions come from the online PostgreSQL 8.4 docs.
GRANT ALL PRIVILEGES ON DATABASE
grants theCREATE
,CONNECT
, andTEMPORARY
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.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 toGRANT
the privileges to those schemas separately.