Re: Per-Database Roles

On May23, 2012, at 00:21 , Josh Berkus wrote:
>> If you could help me work out the semantics and the high-level issues,
>> I'd love to spend time on this for 9.3...
>
> Syntax seems simple: CREATE LOCAL ROLE ...
>
> For that matter, let's keep other things simple:
>
> 1. local roles can inherit only from other local roles
> 2. global roles can inherit only from other global roles
> 3. only a global role can be a database owner

+1 to (2) and (3).

If we do (1), we need to allow granting privileges on global objects
(think tablespaces) to local roles. Otherwise, how would you make a
tablespace usable to a certain local role? That, however, seems
difficult, because we wouldn't want local role OIDs to appear in
pg_tablespace's spacl column.

If we, OTOH, allowed local roles to inherit from global roles, we
could still forbit such GRANTs. One would then simply have to create a
global proxy role which has the necessary privileges, and which local roles
would inherit from instead of being granted the privileges directly.

Allowing (1) doesn't seem that hard, because it just requires us to
be able to store dependencies of local roles on global roles, which
pg_shdepend already (almost, we'd probably need to add deptype
SHARED_DEPENDENCY_MEMBER) support.

best regards,
Florian Pflug