Re: Restricting the CREATEROLE privilege

On Thu, Feb 25, 2010 at 01:26, Wappler, Robert <rwappler@ophardt.com> wrote:
> Good Morning,
> is there a way to limit the CREATEROLE privilege to a specific database?
> I currently set up an automated integration test environment. This includes a
> database owned by a specific user which should have all degrees of freedom
> for installing whatever database schemas are in the current revision as well as
> creating roles used by the test cases to access the database.

You could create a base role that does not have connect privileges on
the other databases.  Then just inherit from that role.  Something
like:
CREATE ROLE base_user;
REVOKE CONNECT ON database from base_user;
...

CREATE ROLE my_user inherit base_user;

You could also go the other route and default deny connect databases
and explicitly allow connect.