Thread: permissions / ACLs made easier?
This idea is meant as an alternative to MySQL-style "GRANT ... *" or other similar permissions schemes. I posted a similar message on -hackers here: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01393.php I'm posting on -general now to get some feedback from potential users to see if it actually solves problems for a significant group of people. The use case is an application with several roles like: * admin user - owns all the objects related to that application * normal user - INSERT/UPDATE/DELETE plus sequence usage * read-only user - for reporting The feature that I'm suggesting is a "GRANT mask": [ not real syntax, just for illustration ] 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. 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 * you can create new objects without needing to issue appropriate GRANT statements for each user * you can easily see the permissions/ACLs you have set up without inspecting each object 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. 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? 2. If you are using multiple roles currently, would this feature simplify the management of those roles and their privileges? 3. If you are using an ORM, would this feature help you separate privileges better (include the name of the ORM)? 4. If you use "GRANT ... *" in MySQL, would this be an adequate substitute when using PostgreSQL? Regards, Jeff Davis
On Mon, Jun 29, 2009 at 1:01 PM, Jeff Davis <pgsql@j-davis.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