CODEWITHSUNDEEP
postgresql
Haris
Haris

Reputation: 55

How can I create a role with privileges to drop tables in PostgreSQL?

I need to create separate role which will have the privilege to drop a table. The problem is that the database contains more than 1000 tables. Is there any way to change the privileges(add the new role) for all the tables at once without doing it for each one of them manually?

Upvotes: 1

Views: 4175

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

There is no way you can do that easily in PostgreSQL. The only role (=user) that can drop an object is

  1. the owner of the object (the role that created it)

  2. a direct or indirect member of that role

  3. a user with CREATE privilege on the schema that contains the table

  4. a superuser

So the only way to have a user that can drop any table is to create a superuser or give the user CREATE on all schemas. A superuser can do everything else too, and you cannot restrict that. A user with CREATE on a schema can create arbitrary objects in it.

I think the requirement is strange, but what you could do is the following: create a SECURITY DEFINER function that belongs to a superuser and can drop any table, revoke the EXECUTE privilege from PUBLIC and grant it only to the desired role.

An alternative might be to use schema permissions and write an event trigger that prevents the user from doing anything but dropping objects.

Upvotes: 3

Related Questions