Best Practices for Extensions, limitations and recommended use for monitoring

Hi all,

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? Is it possible to add options to CREATE EXTENSION by the extension itself e.g. to make user names configurable?

I’m the author of Posemo (PostgreSQL Secure monitoring), a new PostgreSQL monitoring framework for monitoring everything in PostgreSQL in a secure way with an unprivileged user and a simple way to add new checks. (In development and available here under PostgreSQL license: https://github.com/alvar-freude/Posemo)

The Posemo framework creates for each check a PostgreSQL function with the SECURITY DEFINER option, which can (only) be called by an unprivileged user, who only can call the functions and don’t need access to any data, system table etc.

A simple alive check looks like this (the author of the check has only to write some meta data and the SQL, “SELECT true” in this example here):

CREATE FUNCTION posemo.alive() RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path TO posemo, pg_temp
AS $$
SELECT true
$$;

ALTER FUNCTION posemo.alive() OWNER TO posemo_admin;
REVOKE ALL ON FUNCTION posemo.alive() FROM PUBLIC;
GRANT ALL ON FUNCTION posemo.alive() TO posemo;

For more complex checks I create Types, some checks can write something into tables.

CREATE TYPE posemo.transactions_type AS (
database character varying(64),
xact_commit bigint,
xact_rollback bigint
);
ALTER TYPE posemo.transactions_type OWNER TO posemo_admin;

At the moment I have an installation program, which creates everything in a database/schema, which can be chosen by the admin. Of course, it’s also possible to load a dump. Checks may have some tables to store some data a (e.g. the writeable check inserts a row and deletes old rows).

The Posemo application calls the functions (depending a local config file) and builds the result.

If it’s easier to use an extension or install Posemo via installation script depends on the environment.

At the moment I think, that it would be a good idea to have (at least) the option to use Posemo as Extension, but the need for two users is a possible drawback: can and should the extension create the necessary users and can a extension change the owner of it’s own objects?
Or is this a bad idea because it breaks everything?

Changing grants later should be not a good idea, because this is not part of a dump.

What about local checks of the user? It may use the “requires” parameter and should work without any problems.

Thank you for all comments! :-)

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/