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