Our database (Greenplum 6, Postgres 9.x) is set up in a way where certain people can only access certain data. To manage this when a "project" is started, a new schema for this project is created. Along with that, two new roles are created project_ro and project_fa for read-only and full-access respectively. (These roles have the corresponding create and usage grants). People are then added to a project by granting them one of those roles. The problem with this approach seems to be that, now, because a user role is part of the "project" role, they can create tables in their own name. Meaning their user role owns the table, view, etc. This makes it so that other users cannot read these tables and the admins cannot interact with these objects, because they are owned by the user, not by the "project" role (project-fa).
As a preface, because of our architecture there are no super users within our department.
I cannot figure out a way to prevent a user role from creating a table when they are part of a role that has the correct privileges. So what I want is that when a user role attempts to create it does not work. Forcing them to act from another role (set role project_fa). Simply telling them they need to change their role before creating an object or altering its owner afterwards is not an option, as it's error prone and not a robust solution.
Is this possible with the way roles are set up now or do we need to rethink our access control?
By default access will not be granted to project_ro and project_fa roles on newly created objects within the schema. Users whoever creates objects should grant privileges to respective _ro and _fa roles. but most of the users do not grant the privileges after creating objects.
To fix this issue, Please request your DBA to schedule a script which can regularily grant the correct privileges on all non-view objects in the schema to schema related _ro and _fa roles. project_fa role should be the owner for all non-view objects.
Views can internally access data from tables in multiple schema's. If the view owner don't have access on underlying tables, all users including gpadmin will get permission denied error if they run select on that view. so whoever have access on underlying objects should be the owner for the views. select access on the views can be granted to _ro and _fa roles.