On Wed, May 29, 2013, at 10:08 AM, Stephen Frost wrote: > This capability might well come with a real way to have per-database > roles in general, which has been asked for quite often as well. You > would then be able to have an 'auditor' role in each database and have > them actually be different roles- would that match your needs..?
Yes, if we had per-database roles, it would work. However, I don't think it's necessary. We've already got role permissions specific to a database; so we're most of the way there. The main piece missing is a way for me to assign a role to a user, but only for a specific database. Let me rephrase this, using a different syntax... CAST <user> AS <role> ON <database> This statement permits the <user> to execute "SET ROLE <role>" when they are attached to <database>. The <user> doesn't inherit from the role, it's only a permission that enables them to SET ROLE and only when attached to the permitted database. I think this would solve my problem. Suppose again I've got a database cluster with a "sales" and an "hr" database. In this database cluster, I've got an "auditor" role which can read the audit_table in the respective database. Now, suppose I wish for Tom to be an auditor for Sales, and not for HR. I'd issue "CAST tom AS auditor ON sales". When Tom normally joins the database, he wouldn't see auditor tables (since this CAST isn't really role inheritance). But, if he wanted to see them, and he were in the Sales database, he'd issue: "SET ROLE auditor". Then he could query audit_table. On other hand, just because Tom joined the HR database to enter his timeslips, he'd not have access to audit_table; and if he did a "SET ROLE auditor" it'd fail to escalate his permission. I hope this makes sense and that it might be general enough. Best, Clark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
