Re: permissions / ACLs made easier?

On Mon, Jun 29, 2009 at 1:01 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

>
>
> CREATE USER read_only_user
> GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM admin_user;
>
> "read_only_user" would automatically have SELECT privileges on any table
> that admin_user has SELECT privileges on, and automatically have USAGE
> privileges on any schema that admin_user has privileges on.

So, you're proposing the ability to inherit privileges from another user /
role? That could be useful, but typically, when I have lots of roles
hanging around, their privileges are mutually exclusive to the point where
this won't help.

>
> The benefits are:
> * you can create a new role after the fact, and you don't have to
> issue GRANT statements for every object in the database

Interesting for sure, but now in your example, I have to write a
separate grant for the maybe 3 or 4 tables that shouldn't be read by
read_only_user in the schema (i.e. ss #'s or other top-secret stuff
that read_only_user
shouldn't
see). And if I'm a novice, I could easily get confused and give the
world the ability to see what they really should not be seeing, just
b/c I took the short route. Personally, I'd prefer being forced to
write individual grants just
to be sure I know what has privileges on what.

> * you can create new objects without needing to issue appropriate
> GRANT statements for each user

One of the things I've always appreciated about pg is that you have to
be explicit about your permissions. However, making things slightly easier
isn't necessarily a bad thing.

> * you can easily see the permissions/ACLs you have set up without
> inspecting each object

Maybe I'm missing this part of what you're proposing. Honestly, losing
object level security is more a concern for me than being forced to write a
pile of scripts. Maybe having a tool (like pgAdmin or pg_dump, something
like pg_dump --privs_by_role <rolename> ) generate a sql script for the
grants that a role has would be more appropriate than a core change.

>
>
> This scheme only helps when you have broad roles, like the
> admin/normal/read-only I listed above, and you don't complicate things
> with lots of exceptions. It's flexible enough that you can use it in
> interesting ways with groups and individual GRANT statements, but by
> that time the simplicity of this feature is most likely lost.

Agreed.

>
>
> With that in mind, who out there would really use this feature?
>
> 1. If you aren't using separate roles now, would you be more likely to
> do so with a feature like this?

Not likely, the people I've worked with in the past are in the routine as
role / non-role shops based on dev practices, dba experience, etc... I
think this would just be another feature that would get a 'huh, neat' type
of response.

>
>
> 2. If you are using multiple roles currently, would this feature
> simplify the management of those roles and their privileges?

Not really, as above, I think that most [well-designed] RBAC solutions
have enough mutual exclusivity where permissions inheritance at the time of
user creation may complicate issues.

--Scott