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

Alvar Freude <alvar(at)a-blast(dot)org> writes:
> I have a question about best practices writing PostgreSQL extensions. Is it OK to write extensions which create users and grant/revoke rights on the created functions to this users?

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.

> Is it possible to add options to CREATE EXTENSION by the extension itself e.g. to make user names configurable?

No.

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.

regards, tom lane