0

I have a role engineer1 | No inheritance, Cannot login | member of {priv_app}. The priv_app user ownes the tables in the schema. Users are members of engineer1 by default which only grants them select access. However, there are reasons the user may need to alter tables so they need to elevate their privileges to the priv_app role using set role priv_app. However this gives them the rights of the table's owner. Which is acceptable except that a user can then grant another user DML privileges on any tables.

Is there a away to prevent the table owner from granting privileges? If not what would be the correct way to architect security groups so that users can alter tables but not grant privileges.

  • A table owner can do with the tables as they please: drop, grant, revoke, etc. What you could do for altering tables is doing so via executing any required ALTER TABLE command from within a function with SECURITY DEFINER. – Ezequiel Tolnay Jul 29 '16 at 5:03
0

Answered by @Ziggy Crueltyfree Zeitgeister 's comment.

A table owner can do with the tables as they please: drop, grant, revoke, etc. What you could do for altering tables is doing so via executing any required ALTER TABLE command from within a function with SECURITY DEFINER.

| improve this answer | |

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.