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. 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?

2 Answers 2

3

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.

3
  • Thank you Laurenz. That is what I suspected. I felt I had to check because my boss was insistent that what he wanted was possible. I will let him know it is, but not where he wanted it. He will need to spin up a new Amazon RDS for the development database so having everyone as a superuser does not endanger my production database.
    – Mike Smith
    Jul 16, 2021 at 15:09
  • 1
    You won't get a superuser on a hosted database... Jul 16, 2021 at 16:46
  • Good to know. I do not have administrative permissions to our Amazon account so I was unaware of that restriction. I will modify my suggestion to limit that suggestion to a server on our local network where we have complete control of the database.
    – Mike Smith
    Jul 16, 2021 at 17:35
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.

1
  • 1
    Raphael, thank you for the suggestion! It makes complete sense and have added this to my working notes (and upvoted it) in case another request like this comes in. That said, the project that was the source of this question finished awhile ago and the database has been deleted.
    – Mike Smith
    Jun 14 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.