-- Complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use "CREATE EXTENSION pg_safer_settings" to load this file. \quit -------------------------------------------------------------------------------------------------------------- comment on extension pg_safer_settings is $markdown$ # The `pg_safer_settings` PostgreSQL extension `pg_safer_settings` provides a handful of functions and mechanisms to make dealing with settings in Postgres a bit … safer. ## Rationalization and usage patterns Out of the box, PostgreSQL offers [a mechanism](#rehashing-how-settings-work-in-postgresql) for custom settings, but with a couple of caveats: 1. Every `ROLE` can read (`SHOW`) most settings. 2. Every `ROLE` can override (`SET`) most settings for the current session or transaction. 3. There is no type checking for settings; they are text values; you may not discover that they are faulty until you read them. Indeed, it is not possible to define a custom setting with restricted access. ### Forcing settings for databases or roles Let's first look at limitation ② that any `ROLE` can override a `current_setting()`, even though an administrator may wish to force a database-wide setting value or force a specific value for a specific role. dba.stackexchange.com is filled with questions from users trying to do just that. They try something like the following: ```sql ALTER DATABASE mydb SET app.settings.bla = 'blegh'; ALTER ROLE myrole IN DATABASE mydb SET app.settings.bla TO DEFAULT; ``` \[See the [`ALTER ROLE`](https://www.postgresql.org/docs/current/sql-alterrole.html) and [`ALTER DATABASE`](https://www.postgresql.org/docs/current/sql-alterdatabase.html) documentation for details and possibilities of the syntax.] The problem is that setting the configuration values in that way only changes the _defaults_. These defaults can be changed by the user (in this case `myrole`): ```sql -- To change for the duration of the session: SET app.settings.bla = 'blegherrerbypass'; -- or: SELECT set_config('app.settings.bla', 'blegherrerbypass', false); -- To change for the duration of the transaction: SET LOCAL app.settings.bla = 'blegherrerbypass'; -- or: SELECT set_config('app.settings.bla', 'blegherrerbypass', true); ``` The workaround is to _ignore_ such setting overrides that are local to transactions or sessions. To that end, `pg_safer_settings` provides the `pg_db_setting()` function, which reads the setting value directly from Postgres its `pg_db_role_settings` catalog, thereby bypassing clever hacking attempts. `pg_db_setting()` does not resolve caveat ① or ③—the fact that settings are world-readable and plain text, respectively. ### Type-safe, read-restricted settings To maintain settings that are type-safe and can be read/write-restricted _per_ setting, `pg_safer_settings` offers the ability to create and maintain your own configuration tables. Please note that these are _not_ your average settings table that tend to come with all kinds of SQL-ignorant frameworks. The configuration tables made by `pg_safer_settings` are singletons, and stores their settings in columns, _not_ rows. You as the DB designer add columns, and the triggers on the table maintain an `IMMUTABLE` function for you with the current column value (except if you want the value to be secret). See the [`pg_safer_settings_table`](#table-pg_safer_settings_table) documentation for details. ## Rehashing how settings work in PostgreSQL | Command | Function | | -------- | ------------------------------------ | | `SET` | `set_config(text, text, bool)` | | `SHOW` | `current_setting(text, text, bool)` | ## The origins of `pg_safer_settings` `pg_safer_settings` was spun off from the PostgreSQL backend of FlashMQ.com—the [scalable MQTT hosting service](https://www.flashmq.com/) that supports millions of concurrent MQTT connections. Its release as a separate extension was part of a succesfull effort to modularize the FlashMQ.com PostgreSQL schemas and, in so doing: - reduce and formalize the interdepencies between parts of the system; - let the public gaze improve the discipline around testing, documentation and other types of polish; and - share the love back to the open source / free software community. ## Authors and contributors * [Rowan](https://www.bigsmoke.us/) originated this extension in 2022 while developing the PostgreSQL backend for the [FlashMQ SaaS MQTT cloud broker](https://www.flashmq.com/). Rowan does not like to see himself as a tech person or a tech writer, but, much to his chagrin, [he _is_](https://blog.bigsmoke.us/category/technology). Some of his chagrin about his disdain for the IT industry he poured into a book: [_Why Programming Still Sucks_](https://www.whyprogrammingstillsucks.com/). Much more than a “tech bro”, he identifies as a garden gnome, fairy and ork rolled into one, and his passion is really to [regreen and reenchant his environment](https://sapienshabitat.com/). One of his proudest achievements is to be the third generation ecological gardener to grow the wild garden around his beautiful [family holiday home in the forest of Norg, Drenthe, the Netherlands](https://www.schuilplaats-norg.nl/) (available for rent!). $markdown$; -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_readme() returns text volatile set search_path from current set pg_readme.include_view_definitions to 'true' set pg_readme.include_routine_definitions_like to '{test__%}' language plpgsql as $plpgsql$ declare _readme text; begin create extension if not exists pg_readme with cascade; _readme := pg_extension_readme('pg_safer_settings'::name); raise transaction_rollback; -- to `DROP EXTENSION` if we happened to `CREATE EXTENSION` for just this. exception when transaction_rollback then return _readme; end; $plpgsql$; comment on function pg_safer_settings_readme() is $md$This function utilizes the `pg_readme` extension to generate a thorough README for this extension, based on the `pg_catalog` and the `COMMENT` objects found therein. $md$; -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_version() returns text stable leakproof parallel safe return ( select pg_extension.extversion from pg_catalog.pg_extension where pg_extension.extname = 'pg_safer_settings' ); comment on function pg_safer_settings_version() is $md$Returns the currently (being) installed version of the `pg_safer_settings` extension. $md$; -------------------------------------------------------------------------------------------------------------- -- Bump entry version. create function pg_safer_settings_meta_pgxn() returns jsonb stable language sql return jsonb_build_object( 'name' ,'pg_safer_settings' ,'abstract' ,'A handful of functions and mechanisms to make dealing with settings in Postgres a bit … safer.' ,'description' ,'The pg_safer_settings extension bundles a handful of functions and mechanisms to make dealing with' ' settings in Postgres a bit … safer.' ,'version' ,pg_safer_settings_version() ,'maintainer' ,array[ 'Rowan Rodrik van der Molen ' ] ,'license' ,'postgresql' ,'prereqs' ,'{ "runtime": { "requires": { "pg_utility_trigger_functions": 0 } }, "test": { "requires": { "pgtap": 0 } }, "develop": { "recommends": { "pg_readme": 0 } } }'::jsonb ,'provides' ,('{ "pg_safer_settings": { "file": "pg_safer_settings--0.8.9.sql", "version": "' || pg_safer_settings_version() || '", "docfile": "README.md" } }')::jsonb ,'resources' ,'{ "homepage": "https://blog.bigsmoke.us/tag/pg_safer_settings", "bugtracker": { "web": "https://github.com/bigsmoke/pg_safer_settings/issues" }, "repository": { "url": "https://github.com/bigsmoke/pg_safer_settings.git", "web": "https://github.com/bigsmoke/pg_safer_settings", "type": "git" } }'::jsonb ,'meta-spec' ,'{ "version": "1.0.0", "url": "https://pgxn.org/spec/" }'::jsonb ,'generated_by' ,'`select pg_safer_settings_meta_pgxn()`' ,'tags' ,array[ 'configuration', 'function', 'functions', 'plpgsql', 'settings', 'trigger' ] ); comment on function pg_safer_settings_meta_pgxn() is $md$Returns the JSON meta data that has to go into the `META.json` file needed for PGXN—PostgreSQL Extension Network—packages. The `Makefile` includes a recipe to allow the developer to: `make META.json` to refresh the meta file with the function's current output, including the `default_version`. And indeed, `pg_safer_settings` can be found on PGXN: https://pgxn.org/dist/pg_safer_settings/ $md$; -------------------------------------------------------------------------------------------------------------- create function pg_db_setting(pg_setting_name$ text, pg_role$ regrole = 0) returns text stable -- security definer return ( select regexp_replace(expanded_settings.raw_setting, E'^[^=]+=', '') from pg_catalog.pg_db_role_setting inner join pg_catalog.pg_database on pg_database.oid = pg_db_role_setting.setdatabase cross join lateral unnest(pg_db_role_setting.setconfig) as expanded_settings(raw_setting) where pg_database.datname = current_database() and pg_db_role_setting.setrole = coalesce( pg_role$, 0 -- 0 means “not role-specific” ) and expanded_settings.raw_setting like pg_setting_name$ || '=%' limit 1 ); comment on function pg_db_setting(text, regrole) is $md$`pg_db_setting()` allows you to look up a setting value as `SET` for a `DATABASE` or `ROLE`, ignoring the local (transaction or session) value for that setting. Example: ```sql CREATE DATABASE mydb; CONNECT TO mydb CREATE ROLE myrole; ALTER DATABASE mydb SET app.settings.bla = 1::text; ALTER ROLE myrole IN DATABASE mydb SET app.settings.bla = 2::text; SET ROLE myrole; SET app.settings.bla TO 3::text; SELECT current_setting('app.settings.bla', true); -- '3' SELECT pg_db_role_setting('app.settings.bla'); -- '1' SELECT pg_db_role_setting('app.settings.bla', current_user); -- '2' ``` $md$; -------------------------------------------------------------------------------------------------------------- create procedure test__pg_db_setting() set search_path from current set plpgsql.check_asserts to true set pg_readme.include_this_routine_definition to true language plpgsql as $$ begin execute 'ALTER DATABASE ' || current_database() || ' SET pg_safer_settings.test_pg_db_setting = ''foo'''; assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo'; set pg_safer_settings.settings.test_pg_db_setting = 'bar'; assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo'; assert pg_db_setting('pg_safer_settings.unknown_setting') is null; create role __test_role; execute 'ALTER ROLE __test_role IN DATABASE ' || current_database() || ' SET pg_safer_settings.test_pg_db_setting = ''foobar'''; assert pg_db_setting('pg_safer_settings.test_pg_db_setting', '__test_role') = 'foobar'; assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo'; raise transaction_rollback; exception when transaction_rollback then end; $$; comment on procedure test__pg_db_setting() is $md$This routine tests the `pg_db_setting()` function. The routine name is compliant with the `pg_tst` extension. An intentional choice has been made to not _depend_ on the `pg_tst` extension its test runner or developer-friendly assertions to keep the number of inter-extension dependencies to a minimum. $md$; -------------------------------------------------------------------------------------------------------------- create table pg_safer_settings_table ( table_regclass regclass primary key ,table_schema name not null default current_schema ,table_name name not null default 'cfg' ,unique (table_schema, table_name) ,setting_getter_prefix name not null default 'current_' ,secret_setting_prefix name not null default 'secret_' ,pg_safer_settings_version text not null default pg_safer_settings_version() ,owning_extension_name name ,owning_extension_version text ,check ((owning_extension_name is null) = (owning_extension_version is null)) ); comment on table pg_safer_settings_table is $md$Insert a row in `pg_safer_settings_table` to have its triggers automatically create _your_ configuration table, plus the requisite triggers that create and replace the `current_()` functions as needed. `pg_safer_settings_table` has default for all its columns. In the simplest form, you can do a default-only insert: ```sql CREATE SCHEMA ext; CREATE SCHEMA myschema; SET search_path TO myschema, ext; CREATE EXTENSION pg_safer_settings WITH SCHEMA ext; INSERT INTO ext.pg_safer_settings_table DEFAULT VALUES RETURNING *; ``` $md$; comment on column pg_safer_settings_table.setting_getter_prefix is $md$The automatically created/replaced setting getter functions will be named by prepending `setting_getter_prefix` to the column name for that setting. The default value (`'current_'`) of the `setting_getter_prefix` follows the naming of Postgres its own `current_setting()` function name. $md$; comment on column pg_safer_settings_table.secret_setting_prefix is $md$When a setting's column name starts with the `secret_setting_prefix`, its automatically generated getter function will be a `STABLE` function that, when called, looks up the column value in the table rather than the default `IMMUTABLE` function (with the configuration value cached in the `RETURN` clause) that would otherwise have been created. The reason for this is that the schema for functions can be retrieved by everyone, and thus any role would be able to read the secret value even if that role has not been granted `SELECT` privileges on the column (nor `EXECUTE` access to the `IMMUTABLE` function). $md$; comment on column pg_safer_settings_table.owning_extension_name is $md$The name of the extension that registered a specific settings table. Make sure that this column contains the name of your extension if your extension inserts a `pg_safer_settings_table` through its set up scripts. $md$; comment on column pg_safer_settings_table.owning_extension_version is $md$The version of the extension that registered a specific settings table. This version is set automatically by the `pg_safer_settings_table__register()` trigger function. $md$; select pg_catalog.pg_extension_config_dump( 'pg_safer_settings_table' ,'WHERE owning_extension_name IS NULL' ); -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_table__register() returns trigger set search_path from current language plpgsql as $$ declare _current_table_schema name; _current_table_name name; _col_name name; _copying bool; begin assert tg_when in ('BEFORE', 'AFTER'); assert tg_level = 'ROW'; assert tg_op in ('INSERT', 'UPDATE', 'DELETE'); assert tg_table_schema = current_schema; assert tg_table_name = 'pg_safer_settings_table'; assert tg_nargs = 0; -- When we are inside a `COPY` command, it is likely that we're restoring from a `pg_dump`. -- Otherwise, why would you want to bulk insert into such a small table? _copying := tg_op = 'INSERT' and exists ( select from pg_stat_progress_copy where relid = tg_relid and command = 'COPY FROM' and type = 'PIPE' ); if tg_op = 'INSERT' and tg_when = 'BEFORE' and _copying then -- Make sure we don't keep using the pre-`pg_dump` OID. NEW.table_regclass := (NEW.table_schema || '.' || NEW.table_name)::regclass; elsif tg_op = 'INSERT' and tg_when = 'BEFORE' and not _copying then if NEW.table_regclass is not null or exists( select from pg_catalog.pg_class where pg_class.relnamespace = NEW.table_schema::regnamespace::oid and pg_class.relname = NEW.table_name ) then raise exception using message = format( 'The configuration table `%I.%I` already exist.' ,NEW.table_schema, NEW.table_name ) ,detail = format( 'But I (`%I` on `%I.%I`) was not triggered by a `COPY` command.' ,tg_name, tg_table_schema, tg_table_name ) ,hint = 'Make sure to never use the `pg_dump --insert` flag.' ,schema = tg_table_schema ,table = tg_table_name; end if; execute 'CREATE TABLE ' || quote_ident(NEW.table_schema) || '.' || quote_ident(NEW.table_name) || '(' || 'is_singleton BOOLEAN NOT NULL DEFAULT TRUE UNIQUE' || ' CONSTRAINT check_true CHECK (is_singleton = TRUE)' || ',inserted_at TIMESTAMPTZ NOT NULL DEFAULT now()' || ',updated_at TIMESTAMPTZ NOT NULL DEFAULT now()' || ')'; NEW.table_regclass := (NEW.table_schema || '.' || NEW.table_name)::regclass; execute 'COMMENT' || ' ON TABLE ' || NEW.table_regclass::text || ' IS ''Add your own (typed and constrained!) columns to this table as needed.'''; execute 'CREATE TRIGGER update_on_copy' || ' BEFORE INSERT ON ' || NEW.table_regclass::text || ' FOR EACH ROW' || ' EXECUTE FUNCTION pg_safer_settings_table__update_on_copy()'; execute 'CREATE TRIGGER create_or_replace_getters' || ' AFTER INSERT OR UPDATE ON ' || NEW.table_regclass::text || ' FOR EACH ROW' || ' EXECUTE FUNCTION pg_safer_settings_table__create_or_replace_getters()'; execute 'CREATE TRIGGER no_delete' || ' BEFORE DELETE ON ' || NEW.table_regclass::text || ' FOR EACH STATEMENT' || ' EXECUTE FUNCTION no_delete()'; execute 'COMMENT ON TRIGGER no_delete ON ' || NEW.table_regclass::text || ' IS $markdown$The `no_delete()` trigger function comes from the very unpretentious [`pg_utility_trigger_functions`](https://github.com/bigsmoke/pg_utility_trigger_functions) extension. $markdown$'; if NEW.owning_extension_name is not null then NEW.owning_extension_version := coalesce( NEW.owning_extension_version ,( select extversion from pg_catalog.pg_extension where extname = NEW.owning_extension_name ) ); -- Allow the new settings table to be backed up by `pg_dump`: perform pg_catalog.pg_extension_config_dump(NEW.table_regclass, ''); end if; elsif tg_op = 'INSERT' and tg_when = 'AFTER' and not _copying then execute 'INSERT INTO ' || NEW.table_regclass::text || ' VALUES (DEFAULT, DEFAULT, DEFAULT)'; elsif tg_op = 'UPDATE' and tg_when = 'BEFORE' then NEW.updated_at := now(); select pg_class.relnamespace::name ,pg_class.relname from pg_catalog.pg_class where pg_class.oid = NEW.table_regclass into _current_table_schema ,_current_table_name ; if _current_table_schema != NEW.table_schema then raise notice 'Table has been moved from the % schema to %; updating record in % te reflect this.', NEW.table_schema, _current_table_schema, tg_table_name; NEW.table_schema := _current_table_schema; end if; if _current_table_name != NEW.table_name then raise notice 'Table has been renamed from % to %; updating record in % to reflect this.', NEW.table_name, _current_table_name, tg_table_name; NEW.table_name := _current_table_name; end if; if NEW.table_name != OLD.table_name then execute 'ALTER TABLE ' || NEW.table_regclass::text || ' RENAME TO ' || NEW.table_name; end if; if NEW.table_schema != OLD.table_schema then execute 'ALTER TABLE ' || NEW.table_regclass::text || ' SET SCHEMA ' || NEW.table_schema; end if; if NEW.setting_getter_prefix != OLD.setting_getter_prefix then raise exception 'Changing `setting_getter_prefix` not supported (yet).'; end if; if NEW.secret_setting_prefix != OLD.secret_setting_prefix then raise exception 'Changing `secret_setting_prefix` not supported (yet).'; end if; elsif tg_op = 'DELETE' and tg_when = 'AFTER' then for _col_name in select column_name from pg_safer_settings_table_columns(OLD.table_schema, OLD.table_name) loop execute 'DROP FUNCTION ' || quote_ident(OLD.table_schema) || '.' || quote_ident(OLD.setting_getter_prefix || _col_name) || '()'; end loop; execute 'DROP TABLE ' || OLD.table_regclass::name; end if; if tg_op in ('INSERT', 'UPDATE') then return NEW; end if; return OLD; end; $$; comment on function pg_safer_settings_table__register() is $md$This trigger function creates and maintains the safer settings tables that are registered with it. $md$; create trigger before_trigger before insert or update or delete on pg_safer_settings_table for each row execute function pg_safer_settings_table__register(); create trigger after_trigger after insert or update or delete on pg_safer_settings_table for each row execute function pg_safer_settings_table__register(); -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_table_columns(table_schema$ name, table_name$ name) returns setof information_schema.columns stable returns null on null input leakproof parallel safe set search_path from current set pg_readme.include_this_routine_definition to true language sql begin atomic select columns.* from information_schema.columns where columns.table_schema = table_schema$ and columns.table_name = table_name$ and not columns.column_name = any (array['is_singleton', 'inserted_at', 'updated_at']); end; -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_table__create_or_replace_getters() returns trigger set search_path from current language plpgsql as $$ declare _func_name name; _func_signature text; _func_already_existed bool; _func_owning_extension name; _col_name name; _col_type text; _col_no int; _val_old text; _val_new text; _pg_safer_settings_table pg_safer_settings_table; _col_privilege information_schema.column_privileges; _extension_context_detection_object name; _extension_context name; begin assert tg_when = 'AFTER'; assert tg_level = 'ROW'; assert tg_op in ('INSERT', 'UPDATE'); assert tg_relid in (select table_regclass from pg_safer_settings_table); -- The extension context may be: -- a) outside of a `CREATE EXTENSION` / `ALTER EXTENSION` context (`_extension_context IS NULL`); -- b) inside the `CREATE EXTENSION` / `ALTER EXTENSION` context of the extension owning the config -- table to which this trigger is attached; or -- c) inside the `CREATE EXTENSION` / `ALTER EXTENSION` context of extension that changes settings in -- another extension's configuration table. _extension_context_detection_object := format( 'extension_context_detector_%s' ,floor(pg_catalog.random() * 1000) ); execute format('CREATE TEMPORARY TABLE %I (col int) ON COMMIT DROP', _extension_context_detection_object); select pg_extension.extname into _extension_context from pg_catalog.pg_depend inner join pg_catalog.pg_extension on pg_extension.oid = pg_depend.refobjid where pg_depend.classid = 'pg_catalog.pg_class'::regclass and pg_depend.objid = _extension_context_detection_object::regclass and pg_depend.refclassid = 'pg_catalog.pg_extension'::regclass ; execute format('DROP TABLE %I', _extension_context_detection_object); select * into _pg_safer_settings_table from pg_safer_settings_table where pg_safer_settings_table.table_regclass = tg_relid ; for _col_name ,_col_type ,_col_no in select columns.column_name ,coalesce( quote_ident(columns.domain_schema) || '.' || quote_ident(columns.domain_name), quote_ident(columns.udt_schema) || '.' || quote_ident(columns.udt_name) ) ,columns.ordinal_position from pg_safer_settings_table_columns(tg_table_schema, tg_table_name) as columns loop _func_name := _pg_safer_settings_table.setting_getter_prefix || _col_name; _func_signature := quote_ident(tg_table_schema) || '.' || quote_ident(_func_name) || '()'; _func_already_existed := to_regprocedure(_func_signature) is not null; execute format('SELECT %s.%I::TEXT', '$1', _col_name) using NEW into _val_new; if tg_op = 'UPDATE' then execute format('SELECT %s.%I::TEXT', '$1', _col_name) using OLD into _val_old; else _val_old := null; end if; if _val_old is distinct from _val_new or to_regproc(quote_ident(tg_table_schema) || '.' || quote_ident(_func_name) || '()') is null then if (_pg_safer_settings_table.owning_extension_name is not null) and _extension_context is not null and _func_already_existed then -- Temporarily let the extension that changed the setting steal the wrapper function, -- because only the owner of a function can use `CREATE OR REPLACE FUNCTION` on it. -- (And we don't want to `DROP FUNCTION`, because the function may have dependents.) execute 'ALTER EXTENSION ' || quote_ident(_pg_safer_settings_table.owning_extension_name) || ' DROP FUNCTION ' || _func_signature; execute 'ALTER EXTENSION ' || quote_ident(_extension_context) || ' ADD FUNCTION ' || _func_signature; end if; if _col_name like _pg_safer_settings_table.secret_setting_prefix || '%' then execute 'CREATE OR REPLACE FUNCTION ' || _func_signature || ' RETURNS ' || _col_type || ' LANGUAGE SQL' || ' STABLE LEAKPROOF PARALLEL SAFE' || ' RETURN (SELECT ' || quote_ident(_col_name) || ' FROM ' || quote_ident(tg_table_schema) || '.' || quote_ident(tg_table_name) || ')'; else execute 'CREATE OR REPLACE FUNCTION ' || _func_signature || ' RETURNS ' || _col_type || ' LANGUAGE SQL' || ' IMMUTABLE LEAKPROOF PARALLEL SAFE' || ' RETURN ' || quote_nullable(_val_new) || '::' || _col_type; end if; if _extension_context is not null and _extension_context is distinct from _pg_safer_settings_table.owning_extension_name then -- The changing of this setting (and the (re)creating of the wrapper function) was triggered -- by an extension set up script, but _not_ the extension that owns the configuration table. -- The `CREATE OR REPLACE FUNCTION` will have made the (re)created function belong to the -- triggering extension. Let it relinguish the function. execute 'ALTER EXTENSION ' || quote_ident(_extension_context) || ' DROP FUNCTION ' || _func_signature; if _pg_safer_settings_table.owning_extension_name is not null then -- The configuration table does belong to an extension. Better give ownership of the -- wrapper function back to that same extension. execute 'ALTER EXTENSION ' || quote_ident(_pg_safer_settings_table.owning_extension_name) || ' ADD FUNCTION ' || _func_signature; end if; end if; execute 'COMMENT ON FUNCTION ' || _func_signature || $sqlstr$ IS $md$This function was generated by the `$sqlstr$ || tg_name || $sqlstr$` trigger on the `$sqlstr$ || tg_table_schema || $sqlstr$.$sqlstr$ || tg_table_name || $sqlstr$` table. This function wraps around the value of the `$sqlstr$ || tg_table_name || $sqlstr$.$sqlstr$ || _col_name || $sqlstr$` column and is automatically recreated every time that the value of that column changes. $sqlstr$ || case when col_description(tg_relid, _col_no) is not null then $sqlstr$ See [`$sqlstr$ || tg_table_name || $sqlstr$`](#table-$sqlstr$ || tg_table_name || $sqlstr$) for documentation on the underlying column. $sqlstr$ else '' end || $sqlstr$ $md$ $sqlstr$; end if; for _col_privilege in select column_privileges.* from information_schema.column_privileges where column_privileges.table_schema = tg_table_schema and column_privileges.table_name = tg_table_name and column_privileges.column_name = _col_name and column_privileges.privilege_type = 'SELECT' loop execute 'GRANT EXECUTE ON FUNCTION ' || quote_ident(tg_table_schema) || '.' || quote_ident(_func_name) || '()' || ' TO ' || case when _col_privilege.grantee = 'PUBLIC' then 'PUBLIC' else quote_ident(_col_privilege.grantee) end || case when _col_privilege.is_grantable = 'YES' then ' WITH GRANT OPTION' else '' end; end loop; end loop; return NEW; end; $$; comment on function pg_safer_settings_table__create_or_replace_getters() is $md$This trigger function automatically `CREATE OR REPLACE`s, for each configuration column in the table that it is attached to: a function that returns the current value for that column. The created function will have the same name as the column it wraps, prefixed with the `setting_getter_prefix` from the [`pg_safer_settings_table`](#table-pg_safer_settings_table). Normally, the created function will be `IMMUTABLE` and return a hard-coded copy of the lastest configuration value, except if the column name it reflects starts with the `secret_setting_prefix` from the [`pg_safer_settings_table`](#table-pg_safer_settings_table)—then the function will be a `STABLE` function that, upon invocation, retrieves the value from the configuration table. `SELECT` privileges on the setting columns are translated into `EXECUTE` permissions on the wrapper functions. $md$; -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_table__update_on_copy() returns trigger set search_path from current language plpgsql as $$ declare _copying bool; begin assert tg_when = 'BEFORE'; assert tg_level = 'ROW'; assert tg_op = 'INSERT'; assert tg_relid in (select table_regclass from pg_safer_settings_table); -- When we are inside a `COPY` command, it is likely that we're restoring from a `pg_dump`. -- Otherwise, why would you want to bulk insert into a singleton table (with max. 1 row)? _copying := tg_op = 'INSERT' and exists ( select from pg_stat_progress_copy where relid = tg_relid and command = 'COPY FROM' and type = 'PIPE' ); if _copying then execute 'UPDATE ' || tg_relid::regclass::text || ' SET ' || ( select string_agg( quote_ident(pg_attribute.attname) || ' = $1.' || quote_ident(pg_attribute.attname) ,', ' ) from pg_catalog.pg_attribute where pg_attribute.attrelid = tg_relid and pg_attribute.attnum > 0 ) using NEW; return null; -- Cancel INSERT. end if; return NEW; end; $$; comment on function pg_safer_settings_table__update_on_copy() is $md$`UPDATE` instead of `INSERT` when triggered from a `COPY FROM STDIN` statement. Without this trigger, when another extension sets up a `pg_safer_settings_table` from one of its setup scripts, `pg_restore` will crash, because it would try to `INSERT` _twice_: 1. as a result of the `INSERT AFTER` trigger on `pg_safer_settings_table`, _and_ 2. as a result of the contents of the created settings table always being included in the `pg_dump`. Because you want to remember your settings, right? $md$; -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_table__col_must_mirror_current_setting() returns trigger set search_path from current language plpgsql as $$ declare _cfg_column name; _pg_setting_name text; _db_setting_value text; _new_value_text text; begin assert tg_when = 'BEFORE'; assert tg_level = 'ROW'; assert tg_op in ('INSERT', 'UPDATE'); assert tg_table_schema = current_schema; assert tg_relid in (select table_regclass from pg_safer_settings_table); assert tg_nargs = 2; _cfg_column := tg_argv[0]; _pg_setting_name := tg_argv[1]; execute format('SELECT %s.%I::TEXT', '$1', _cfg_column) using NEW into _new_value_text; if current_setting(_pg_setting_name, true) is null then raise exception 'current_setting(''%'', true) IS NULL;' ' therefore the `%.%.%` column has to be `NULL` as well.', _pg_setting_name, tg_table_schema, tg_table_name, _cfg_column; end if; if current_setting(_pg_setting_name, true) != _new_value_text then raise exception 'current_setting(''%'', true) ≠ ''%'' = new `%.%.%` value.', _pg_setting_name, _new_value_text, tg_table_schema, tg_table_name, _cfg_column; end if; return true; end; $$; comment on function pg_safer_settings_table__col_must_mirror_current_setting() is $md$If you want to forbid changing a configuration table column value to something that is not in sync with the current value of the given setting, use this trigger function. Use it as a constraint trigger: ```sql create constraint trigger must_mirror_db_role_setting__max_plumbus_count after insert or update on your.cfg for each row execute function safer_settings_table__col_must_mirror_db_role_setting( 'max_plumbus_count', 'app.settings.max_plumbus_count' ); ``` $md$; -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_table__col_must_mirror_db_role_setting() returns trigger set search_path from current language plpgsql as $$ declare _cfg_column name; _pg_setting_name text; _regrole regrole; _db_setting_value text; _new_value_text text; begin assert tg_when = 'AFTER'; assert tg_level = 'ROW'; assert tg_op in ('INSERT', 'UPDATE'); assert tg_relid in (select table_regclass from pg_safer_settings_table); assert tg_nargs between 2 and 3; _cfg_column := tg_argv[0]; _pg_setting_name := tg_argv[1]; _regrole := null; if tg_nargs > 2 then _regrole := tg_argv[2]; end if; execute format('SELECT %s.%I::TEXT', '$1', _cfg_column) using NEW into _new_value_text; _db_setting_value := app.pg_db_setting(_pg_setting_name, _regrole); if _db_setting_value is distinct from _new_value_text then raise exception 'New `%.%.%` value % is not in sync with DB(-role)-level setting ''%''.', tg_table_schema, tg_table_name, _cfg_column, _new_value_text, _pg_setting_name; end if; return NEW; end; $$; comment on function pg_safer_settings_table__col_must_mirror_db_role_setting() is $md$If you want to forbid changing a configuration table column value to something that is not in sync with the given setting (for the optionally given `ROLE`) `SET` on the `DATABASE` level, this trigger function is your friend. Use it as a constraint trigger: ```sql create constraint trigger must_mirror_db_role_setting__deployment_tier after insert or update on your.cfg for each row execute function safer_settings_table__col_must_mirror_db_role_setting( 'deployment_tier', 'app.settings.deployment_tier' ); ``` Alternatively, you may wish to `SET` the PostgreSQL setting automatically whenever the column is `UPDATE`d. In that case, use the `pg_safer_settings_table__mirror_col_to_db_role_setting()` trigger function instead. Note that there is _no way_—not even using event triggers—to automatically catch configuration changes as the `ALTER DATABASE` level as they happen. Triggers using this function will only catch incompatibilities when the trigger is … triggered. $md$; -------------------------------------------------------------------------------------------------------------- create function pg_safer_settings_table__mirror_col_to_db_role_setting() returns trigger set search_path from current language plpgsql as $$ declare _cfg_column name; _pg_setting_name text; _regrole regrole; _db_setting_value text; _new_value_text text; begin assert tg_when = 'AFTER'; assert tg_level = 'ROW'; assert tg_op in ('INSERT', 'UPDATE'); assert tg_relid in (select table_regclass from pg_safer_settings_table); assert tg_nargs between 2 and 3; _cfg_column := tg_argv[0]; _pg_setting_name := tg_argv[1]; _regrole := null; if tg_nargs > 2 then _regrole := tg_argv[2]; end if; execute format('SELECT %s.%I::TEXT', '$1', _cfg_column) using NEW into _new_value_text; _db_setting_value := app.pg_db_setting(_pg_setting_name, _regrole); if _db_setting_value is distinct from _new_value_text then if _regrole is null then execute 'ALTER DATABASE ' || current_database() || ' SET ' || _pg_setting_name || ' TO ' || quote_literal(_new_value_text); else execute 'ALTER ROLE ' || _regrole::text || ' IN DATABASE ' || current_database() || ' SET ' || _pg_setting_name || ' TO ' || quote_literal(_new_value_text); end if; end if; return NEW; end; $$; comment on function pg_safer_settings_table__mirror_col_to_db_role_setting() is $md$If, for some reason, you find it useful to keep a configuration column value synced to a database/role-level setting, this trigger function has your back. For the opposite requirement—to enforce equality of a configuration column value to a database (role) setting—, see the `pg_safer_settings_table__col_must_mirror_db_role_setting()` trigger function. $md$; -------------------------------------------------------------------------------------------------------------- create procedure test__pg_safer_settings_table() set search_path from current set pg_readme.include_this_routine_definition to true language plpgsql as $$ declare _pg_safer_settings_table pg_safer_settings_table; _cfg_record record; begin insert into pg_safer_settings_table (table_name) values ('test__cfg') returning * into _pg_safer_settings_table ; assert _pg_safer_settings_table.setting_getter_prefix = 'current_'; select * into _cfg_record from test__cfg; assert _cfg_record.is_singleton; alter table test__cfg add boolean_test_setting bool not null default false; update test__cfg set boolean_test_setting = default; select * into _cfg_record from test__cfg; assert _cfg_record.boolean_test_setting = false; assert current_boolean_test_setting() = false; assert ( select provolatile = 'i' from pg_proc where pronamespace = current_schema::regnamespace and proname = 'current_boolean_test_setting' ); alter table test__cfg add secret_test_setting text; update test__cfg set secret_test_setting = 'Th1s1ss3cr3t'; assert current_secret_test_setting() = 'Th1s1ss3cr3t'; assert ( select provolatile = 's' from pg_proc where pronamespace = current_schema::regnamespace and proname = 'current_secret_test_setting' ); delete from pg_safer_settings_table where table_name = 'test__cfg'; raise transaction_rollback; exception when transaction_rollback then end; $$; -------------------------------------------------------------------------------------------------------------- create procedure test_dump_restore__pg_safer_settings_table(test_stage$ text) set search_path from current set plpgsql.check_asserts to true set pg_readme.include_this_routine_definition to true language plpgsql as $$ declare _cfg_record record; begin assert test_stage$ in ('pre-dump', 'post-restore'); if test_stage$ = 'pre-dump' then insert into pg_safer_settings_table (table_name) values ('test__cfg'); alter table test__cfg add boolean_test_setting bool not null default false ,add secret_test_setting text ; update test__cfg set secret_test_setting = 'Th1s1ss3cr3t' ; create extension pg_safer_settings_table_dependent_extension; assert current_subext_number_setting() = 4; update subextension_cfg set subext_number_setting = 5; assert current_subext_number_setting() = 5; create extension pg_safer_settings_table_dependent_subextension; assert current_subext_text_setting() = 'Set by subsubextension'; elsif test_stage$ = 'post-restore' then select * into strict _cfg_record from test__cfg; assert _cfg_record.boolean_test_setting = false; assert current_boolean_test_setting() = false; assert _cfg_record.secret_test_setting = 'Th1s1ss3cr3t'; assert current_secret_test_setting() = 'Th1s1ss3cr3t'; assert to_regprocedure('current_boolean_test_setting()') is not null; assert to_regprocedure('current_secret_test_setting()') is not null; delete from pg_safer_settings_table where table_name = 'test__cfg'; assert to_regprocedure('current_boolean_test_setting()') is null; assert to_regprocedure('current_secret_test_setting()') is null; assert current_subext_number_setting() = 5, 'The configaration value set _after_ `CREATE EXTENSION` should have been preserved.'; assert current_subext_bool_setting() = true; assert current_subext_text_setting() = 'Set by subsubextension', 'The configuration value set by the subsubextension should have been preserver.'; end if; end; $$; --------------------------------------------------------------------------------------------------------------