Обсуждение: 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/

Alvar Freude <alvar@a-blast.org> writes:
> 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? 

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

Hi Tom,

> Am 14.08.2018 um 17:07 schrieb Tom Lane <tgl@sss.pgh.pa.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
extensionmechanism. 


> 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
theposemo 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@alvar-freude.de
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/