Asked By: Anonymous
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:
- Direct creation of partition from current user to user who owns the table â€“ mentioned ERROR â˜¹
- 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.
Answered By: Anonymous
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).