PostgreSQL 11 - Can I turn a schema into the wild west?

Question

I have been given a task to set up a development database where all users able to access a given project schema (each project would have its own schema) would be able to do anything to any table, including to tables they do not own. The reason I have been asked to make the schemas a free-for-all is my supervisor does not want any time spent administering the database beyond me adding/deleting schemas. I am trying to set this up with a PostgreSQL 11 Amazon RDS.

For the test schema, in an attempt to simplify the SQL, I have created a group role and added the users working on the project (each has inherit permissions enabled) to the group. I am then granting permissions to the group at the database and schema level. My hope was that everyone in the group would be able to do anything the group can do, and do it to table(s) created by any other user in the group.

My initial attempt had limited success. I can add a table and the other users can view the table, append data to the table, edit individual cells, and delete rows. However, they are unable to delete the table I created. In addition, the users can only do that to tables I create. If another user creates a table, no one is able to even view the table, even though I set default privileges on the schema before anyone created any tables. I came across the reason for this while trying to make it work and am hoping there is a way around it.

To open things up as much as I can, I took the following actions:

• Gave the group role full permission to the database with GRANT ALL ON DATABASE dbname TO grpname;

• After I created the schema I ran: GRANT ALL ON SCHEMA testschema TO grpname;

  ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON TABLES TO grpname;

  ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON SEQUENCES TO grpname;

  ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT EXECUTE ON FUNCTIONS TO grpname;

To confirm the reason I found regarding why ALTER DEFAULT PRIVILEGES only gave those privileges to tables I created, I created a new schema with the group role as the owner and added tables as several users, including myself. Since the group role did not create the tables, its members did, no one could view any table other than their own, let alone modify another user's table.

A final wrinkle involves where this development database is located. It is being added to a server group that includes our main production database so I cannot make all users a superuser. Not sure that would fix things, but thought I would mention it.

Have I missed/overlooked something that would allow a schema free-for-all?

Answer 1

I have been given a task to set up a development database where all users able to access a given project schema (each project would have its own schema) would be able to do anything to any table, including to tables they do not own.

That cannot work, as the documentation states:

The right to modify or destroy an object is inherent in being the object's owner, and cannot be granted or revoked in itself. (However, like all privileges, that right can be inherited by members of the owning role; see Section 21.3.)

The only easy ways out are to have everybody use the same database user or to use superusers, both of which are obviously not great ideas. But then the requirement sounds like nobody cares about the data and their integrity anyway.

Answer 2

It depends how much discipline the users have.

You could have a schema_owner group that all project users would be members of. As long as users are disciplined about changing the ownership of things they create to that group role.... this could work. A further caveat that I have found it useful to use ownership to determine who created what in order to understand why something was created. That's a little bit harder here.