Setting up pg_partman

[rdilipk]

I am having some trouble understanding this section of the HOW-TO documentation. (feel free to direct me to any other place where this question has been answered. The closest I came across was this one.)

Superuser is still required to install pg_partman. It is recommended that a dedicated role is created for running pg_partman functions and to be the owner of all partition sets that pg_partman maintains.

As I understand, CREATE EXTENSION of pg_partman will necessarily require superuser privileges. Correct? At that point, I am unable execute any of the GRANTs specified in the top-level README. For e.g. trying to run:

GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman;

as a non-privileged user fails with permisson denied for custom_time_partitions, given that postgres user owns that table.

Am I at all going about doing this the right way?

[keithf4]

You're not able to grant privileges to objects unless you already have the existing privileges to allow you to apply such a grant. Otherwise anyone could just arbitrarily grant themselves access to objects they want to see and kind of defeats the purpose of a privilege system.

So, in order to grant privileges to the pg_partman objects, you're still going to have to do that as either a superuser or at least someone that has the ability to give such grants.

Hopefully I'm understanding your question and issue here. Otherwise, please feel free to followup.

[rdilipk]

Hi Keith -- following up on this one after a while. A quick recap: I am (still) trying to grant these privileges that you recommend in your documentation:

GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman;  -- PG11+ only

In your reply above you mention that the SQL above has to executed either as a superuser (no bueno in my case) or at least someone that has the ability to give such grants. It's the latter I am interested in. What kind of privileges must an user/role hold in order to grant these /without/ being a superuser?