Postgres limitation in user management

["Kar, Swapnil (TR Technology)" <Swapnil.Kar@xxxxxxxxxxxxxxxxxx>]

Hello Team,

 

I am facing a limitation with Postgres user management and require your assistance or input around it. Let me brief you the scenario here –

 

We have 2 sets of database user groups –

 

1. App – who owns the application schemas (and tables)
2. Support – who provides db support

 

We want Support users to have no SELECT or DML privilege but only ALTER TABLE to perform any troubleshooting in the database.

 

In Postgres, to have alter system privilege one should be the owner of the schema/table but App users are not keen to make them temporarily as owner of the schema during the investigation time. Because they loose the ownership and can’t perform ALTER table commands.

 

Now another option 2 is to – grant app_user to support_user;

This way ownership is not transferred but support is able to perform select and DML.

 

Option 3 is to grant rds_superuser privilege to support and in this case they will become more powerful superuser in the DB. This is also not a solution for our requirement.

 

Do you think there is a way to deal with this situation ?

 

Any help and guidance here is highly appreciated.

 

Regards,

Swapnil

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html