0

Is there a way to have a PostgreSQL user that can drop all but specified tables?

We need the user to be able to drop some tables but not all. It would be preferred to be able to use something like revoke where we could specify which tables the user should not be able to drop.

3

Since you're running 9.3, you could potentially use an event trigger to catch the tag DROP TABLE and prevent that for certain users.

Denish Patel has a example in his blog post here Preventing Human Errors in Postgres. Search for the string "Function to prevent single table drop". That should give you enough of a start to modify for your specific needs.

2

DROP TABLE isn't a GRANTable right, it's held only by the table owner and the superuser.

Kassandry has already noted that event triggers are one option.

Another is to create a wrapper function, like my_drop_table(thetable regclass), and mark it SECURITY DEFINER as the owner of all the tables (or superuser, but I really don't recommend that). The function would check if the table is one of the protected ones and, if not, issue the DROP TABLE command via EXECUTE format('DROP TABLE %I;', thetable).

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.