Allow db extensions to be configured declaratively

[redbaron]

Extensions are to be enabled per database and application normally cannot do it as it doesn't use superuser. What do you think about changing pg.spec.databases to following (can be done in a backward compatible way were old shape of this key is transformed into new one with empty extensions key):

databases:
  dbname:
     owner: "user"
     extensions: ["pgcrypto"]

then operator checks for enabled extension in a reconciliation loop and enables missing ones.

With this change it would be fairly straightforward to start using new extension without manual intervention.

Alternative approach is to have a dedicated migration step in CI/CD pipeline which uses superuser to make all necessary adjustments, but this might be a bigger change for some setups (like ours) where application is responsible for it's own migrations at start time.

[henricook]

This would be super useful

[Jan-M]

We use a whitelist extension to allow non super users to install extensions. This works well for us. Have a look in spilo. But more ideas of what makes sense are welcome and this is not necessarily an objection to the idea.

…

On Fri 15. Feb 2019 at 16:21, Henri Cook ***@***.***> wrote: This would be super useful — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#490 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABYGQ0oRLnLYv60iMpJU21bJ_nY0T5Uqks5vNtBrgaJpZM4a92T0> .

[redbaron]

Hm, is something missing?

dbname=> select current_user, rolsuper from pg_roles where rolname = current_user;
 current_user | rolsuper 
--------------+----------
 app          | f

dbname=> \l dbname
                             List of databases
  Name  | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
--------+-------+----------+-------------+-------------+-------------------
 dbname | app   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 


dbname=> show extwlist.extensions;
                                               extwlist.extensions                                               
-----------------------------------------------------------------------------------------------------------------
 btree_gin,btree_gist,citext,hstore,intarray,ltree,pgcrypto,pgq,pg_trgm,postgres_fdw,uuid-ossp,hypopg,pg_partman
(1 row)

dbname=> create extension pgcrypto;
ERROR:  permission denied to create extension "pgcrypto"
HINT:  Must be superuser to create this extension.

[erthalion]

@redbaron Can you check that you have pgextwlist in your shared_preload_libraries? There is no magic here, so probably pgextlist just misconfigured.

[FxKu]

With #843 merged this is now possible. See this section for more detail.