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?

1 Answer
1

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:

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:

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.
– AustinNov 30 '12 at 11:19