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)


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:


    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.

| 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 – hellomynameisjoel Nov 4 '10 at 7:09
  • 18
    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
  • Even if it doesn't work try this := GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user; – Kaustubh Feb 26 '15 at 6:16

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.

| improve this answer | |
  • 2
    Chris Cogdon, just a small fix: alter database mydb owner to dbowner – ademchenko Jan 14 '19 at 23:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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