Postgresql – Granting rights on postgresql database to another user

permissionspostgresql

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?

Best Answer

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';
CREATE ROLE
regress=# CREATE TABLE testtab(x integer);
CREATE TABLE
regress=# ALTER TABLE testtab OWNER TO test;
ALTER TABLE

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
ALTER ROLE
regress=# ALTER ROLE test_group NOLOGIN;
ALTER ROLE
regress=# CREATE USER test IN GROUP test_group PASSWORD 'original user pw';
CREATE ROLE

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';
CREATE ROLE
regress=# GRANT test_group TO newuser;
GRANT ROLE

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.

Related Topic