Best Practices for Extensions, limitations and recommended use formonitoring

Hi all,

I have a question about best practices writing PostgreSQL extensions. Is it OK to write extensions which create users
andgrant/revoke rights on the created functions to this users? Is it possible to add options to CREATE EXTENSION by the
extensionitself e.g. to make user names configurable? 

I’m the author of Posemo (PostgreSQL Secure monitoring), a new PostgreSQL monitoring framework for monitoring
everythingin PostgreSQL in a secure way with an unprivileged user and a simple way to add new checks. (In development
andavailable 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
calledby 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
writeablecheck 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
needfor two users is a possible drawback: can and should the extension create the necessary users and can a extension
changethe 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@alvar-freude.de
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/