Re: Best Practices for Extensions, limitations and recommended use for monitoring

Hi Tom,

> Am 14.08.2018 um 17:07 schrieb Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> I'd say that's generally deprecated. Per the documentation, you can write
> CREATE USER commands in an extension script if you like, but the roles
> won't be considered to "belong" to the extension, and won't be dropped
> when it is. This is mainly because roles are cluster-wide but extensions
> are only local to one database. Consider for example what will happen
> when somebody tries to load your extension into more than one database in
> the same cluster.

Yes, that’s one of the problems ;-)

There may be two solutions:
a) check, if the roles exist and skip CREATE ROLE if they already exist, else create them.
b) check, if the roles exist and throw an exception if not.

For b), the administrator should create the roles and has to give them the correct rights (e.g. posemo_admin has NOLOGIN, …). With a), the extension can set the rights, but it may be strange, that it creates users.

I tend to use variant a) (create the roles), because this needs lower administrative effort.

And there is variant c): don’t build an extension … – but as more I think about it, it looks more reasonable to use the extension mechanism.

> For the particular use-case you're describing here, maybe it'd make
> sense to grant privileges to the predefined role pg_monitor, which
> exists in v10 and up. v11 has some additional predefined roles that
> perhaps would fit in, too.

The posemo_admin user (owner of all functions etc) is member of pg_monitor (in v10+; and superuser below v10). But only the posemo user (unprivileged user, has no other rights then EXECUTE on the functions) should have executable rights.

Ciao
Alvar

--
Alvar C.H. Freude | https://alvar.a-blast.org | af(at)alvar-freude(dot)de
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/