Обсуждение: Restricting the CREATEROLE privilege

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.

I do not want that user to create roles, which are then available in every
database.

--

    Robert...



 

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.

On 2010-02-25, Alex Hunsaker wrote:

> 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;
>

Unfortunately, base_user inherits the connect privileges from role
PUBLIC, regardless, whether it was created with NOINHERIT.

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

That other way round seems to be the only solution for now, first revoke
the CONNECT privileges from PUBLIC and then grant them to individual
roles.

How about changing the CREATEROLE privilege to be associated with a
specific database instead of affecting all databases?

--
Robert...

On Thu, Feb 25, 2010 at 08:22, Wappler, Robert <rwappler@ophardt.com> wrote:
> Unfortunately, base_user inherits the connect privileges from role
> PUBLIC, regardless, whether it was created with NOINHERIT.

Yeah, IMO the documentation does not really spell out that limitation.

> How about changing the CREATEROLE privilege to be associated with a
> specific database instead of affecting all databases?

Well just on the grounds that it would break every current user of
CREATE ROLE... that's probably not going to happen.  I could imagine
there could be some syntax sugar for this.  But I don't think it would
be any nicer as you would probably need to REVOKE PUBLIC and inherit
anyway.  Not to mention I'm not sure what the semantics would be or
where it gets its 'default' permissions.  A ruff idea would be for
each database (except the connected one) REVOKE ALL on database.  Of
course feel free to flesh it out and submit a patch :).  In any event
its certainly too late for 9.0 and would not be back patched anyway...