Henrietta Dombrovskaya: Can we make permissions management more user-friendly?

Thank you to everybody who contributed to the discussion about grants and permissions in PostgreSQL. I understand that my post from two weeks ago was more emotional than factual. Today I will present specific cases of how the flexibility of setting permissions in PostgreSQL might cause problems in production settings. Let me make it clear: permission inheritance’s flexibility is a treasure. I won’t be able to do my job if PostgreSQL won’t offer that option. Or probably I would, but it would take significantly more effort both to set up and maintain. I would never want to go to the pre-8 situation when roles could not be granted to other roles.  However, same as we have a system of checks and balances in our political system, same as we need laws to prevent democracy from becoming anarchy, I believe some additional rules are needed to be added to limit the unlimited flexibility for roles and permissions. I come from a very practical need to support a massive production environment with hundreds of PostgreSQL instances which are touched by hundreds of users who continuously deploy new features, add partitions, drop and create tables and schemas; the users who want to be completely isolated from other users without a necessity to span a new host for each new application. In short, I need an environment in which access control can be automated, no user can break existing permissions conventions, and no user has more privileges than are necessary to complete the task.  Here are some problems I face on the path to accomplishing this task Viewing all access privileges for a specific user To manage access, the first thing you need is to be able to see what privileges any given user has, but that task is not easy. There is no single command which would provide this information. For a given role, you can see the list of roles this role is granted, but you need to write a recursive query to go through the whole chain of roles. After that, you need to collect information about all individual grants[...]