I'm trying to set up a system with a PostgreSQL database per user, with a PHP-FPM resource pool for an associated account. I need to grant all privileges on the database to the other user, but it seems that it's only possible to do this for tables.

I've tried

grant all privileges on database username to username_shadow

but this gives only limited privileges.

I've upgraded to PGSQL 9.2, which has the ability to grant privileges on schema, but I can't get a useful result. How do I simply make another user have all the privileges of the first on the same database?


Don't try to duplicate sets of rights like this, it'll become a management nightmare. Use roles and inheritance.

Instead, create a ROLE (group) and make both users members of the role. Grant the role the required rights and ownership of any required objects, and the users will automatically inherit those access rights.

To make the transition, either:

  • Turn the existing user into the shared role by renaming it and dropping its LOGIN right, turning it from a login role (user) into a non-login role (group), then make a new user with the original name; or

  • Manually GRANT the new role all the required rights, using GRANT ... ON DATABASE, GRANT ... ON SCHEMA, GRANT ... ON ALL TABLES IN SCHEMA, etc.

Here's a demo of the 1st approach. Say we have an original user named test with ownership of a table and some other grants:

regress=# CREATE USER test WITH PASSWORD 'original user pw';
regress=# CREATE TABLE testtab(x integer);
regress=# ALTER TABLE testtab OWNER TO test;

We can convert it to a shared role and make a new user with the same name:

regress=# ALTER ROLE test RENAME TO test_group;
NOTICE:  MD5 password cleared because of role rename
regress=# ALTER ROLE test_group NOLOGIN;
regress=# CREATE USER test IN GROUP test_group PASSWORD 'original user pw';

So long as you set the same password, the user won't notice the difference.

You can now create a new user and add it to the same role, giving it the same access that the original test user had before you turned it into the role test_group. In this case I'm using the separate steps of creating a user then granting them role membership; the effect is the same as the above, I'm just showing you two different ways to do it:

regress=# CREATE USER newuser PASSWORD 'fred';
regress=# GRANT test_group TO newuser;

Now newuser can SELECT * FROM testtab even though testtab was owned by the user test and had no GRANTs to allow other users to access it.

| improve this answer | |
  • This sounds like something I should have known earlier - thank you! I'll actually go with the manual process, since I only have pilot accounts so far and I need to semi-automate the process of creating new accounts. – Austin Nov 30 '12 at 11:19

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.