create partition on behalf of table owner

Question

I am looking for the way of creation new partitions in PostgreSQL DB from the user who doesn’t own the parent table. However, I am constantly facing “ERROR: must be owner of table” I tried such approach:

1. Direct creation of partition from current user to user who owns the table – mentioned ERROR ☹
2. Create stored procedure on table owner schema – grant execution on procedure to another user (from which I want to create a partition) – attempt to call the procedure (from user who should create the partition) and same ERROR ☹

So what is the correct approach to solve my challenge.

PS. I am from Oracle world and just started discovery of PostgreSQL Permission model. So may be I am missing smth in privileges.

Answer 1

Create stored procedure on table owner schema

This approach should work.

grant execution on procedure to another user

That's not enough. Just doing something via a function call doesn't affect the permission checks, they're still applied to the current role. What you need for a procedure to affect permission is set the SECURITY DEFINER option. (Check the tips for doing it properly).