pg_extension_name: pg_safer_settings pg_extension_version: 0.8.12 pg_readme_generated_at: 2024-03-08 16:47:30.417977+00 pg_readme_version: 0.6.6

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 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:

ALTER DATABASE mydb
    SET app.settings.bla = 'blegh';
ALTER ROLE myrole
    IN DATABASE mydb
    SET app.settings.bla TO DEFAULT;

[See the ALTER ROLE and ALTER DATABASE 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):

-- 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 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 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

Object reference

Tables

There are 1 tables that directly belong to the pg_safer_settings extension.

Table: pg_safer_settings_table

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_<cfg_column>() functions as needed.

pg_safer_settings_table has default for all its columns. In the simplest form, you can do a default-only insert:

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 *;

The above will cause a table to be created, with the following characteristics:

  • The new configuration table will be called cfg, because 'cfg' is the default value for the pg_safer_settings_table.table_name column.
  • The new table will be singleton table, in that it can have only one row, which is enforced by the constraints on the new table’s is_singleton column.

Storing each setting as a separate column instead of as rows has a number of advantages:

  1. A column can have any type. (Alternatively, we could add a type hint for the text values that would typically be stored in a row-based schema and then use that type hint for the return value of the getter functions.)
  2. Columns can have column constraints, or be part of a multi-column constraint.
  3. It’s easier to write triggers if you don’t have to worry which row contains the particular setting that the trigger function is involved with. When storing settings as rows, it becomes even more cumbersome if you need to deal with inter-setting trigger magic.
  4. When settings are stored as columns, it’s easy to add generated/computed columns, which provide an alternative view of a setting or combine multiple settings.
  5. It’s easy to provide defaults for settings, because these are simply the columns’ defaults.
  6. NOT NULL constraints, like all constraints, become easier.

The disadvantage of dividing settings over columns rather than rows is:

  1. If you need/want to do concurrent updates, you may run into lock contention.

The pg_safer_settings_table table has 8 attributes:

  1. pg_safer_settings_table.table_regclass regclass

    • NOT NULL
    • PRIMARY KEY (table_regclass)

  2. pg_safer_settings_table.table_schema name

    • NOT NULL
    • DEFAULT CURRENT_SCHEMA

  3. pg_safer_settings_table.table_name name

    • NOT NULL
    • DEFAULT 'cfg'::name

  4. pg_safer_settings_table.setting_getter_prefix name

    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.

    • NOT NULL
    • DEFAULT 'current_'::name

  5. pg_safer_settings_table.secret_setting_prefix name

    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).

    • NOT NULL
    • DEFAULT 'secret_'::name

  6. pg_safer_settings_table.pg_safer_settings_version text

    • NOT NULL
    • DEFAULT pg_safer_settings_version()

  7. pg_safer_settings_table.owning_extension_name name

    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.

  8. pg_safer_settings_table.owning_extension_version text

    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.

Routines

Function: pg_db_setting (text, regrole)

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:

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'

Function arguments:

| Arg. # | Arg. mode | Argument name | Argument type | Default expression | | —— | ––––– | —————————————————————– | –––––––––––––––––––––––––––––––––– | —————–– | | $1 | IN | pg_setting_name$ | text | | | $2 | IN | pg_role$ | regrole | 0 |

Function return type: text

Function attributes: STABLE

Function: pg_safer_settings_meta_pgxn()

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/

Function return type: jsonb

Function attributes: STABLE

Function: pg_safer_settings_readme()

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.

Function return type: text

Function-local settings:

  • SET search_path TO ext, ext, pg_temp
  • SET pg_readme.include_view_definitions TO true
  • SET pg_readme.include_routine_definitions_like TO {test__%}

Function: pg_safer_settings_table__col_must_mirror_current_setting()

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:

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'
    );

Function return type: trigger

Function-local settings:

  • SET search_path TO ext, ext, pg_temp

Function: pg_safer_settings_table__col_must_mirror_db_role_setting()

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:

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 UPDATEd. 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.

Function return type: trigger

Function-local settings:

  • SET search_path TO ext, ext, pg_temp

Function: pg_safer_settings_table_columns (name, name)

Function arguments:

| Arg. # | Arg. mode | Argument name | Argument type | Default expression | | —— | ––––– | —————————————————————– | –––––––––––––––––––––––––––––––––– | —————–– | | $1 | IN | table_schema$ | name | | | $2 | IN | table_name$ | name | |

Function return type: SETOF information_schema.columns

Function attributes: STABLE, LEAKPROOF, RETURNS NULL ON NULL INPUT, PARALLEL SAFE, ROWS 1000

Function-local settings:

  • SET search_path TO ext, ext, pg_temp
  • SET pg_readme.include_this_routine_definition TO true
CREATE OR REPLACE FUNCTION ext.pg_safer_settings_table_columns("table_schema$" name, "table_name$" name)
 RETURNS SETOF information_schema.columns
 LANGUAGE sql
 STABLE PARALLEL SAFE STRICT LEAKPROOF
 SET search_path TO 'ext', 'ext', 'pg_temp'
 SET "pg_readme.include_this_routine_definition" TO 'true'
BEGIN ATOMIC
 SELECT columns.table_catalog,
     columns.table_schema,
     columns.table_name,
     columns.column_name,
     columns.ordinal_position,
     columns.column_default,
     columns.is_nullable,
     columns.data_type,
     columns.character_maximum_length,
     columns.character_octet_length,
     columns.numeric_precision,
     columns.numeric_precision_radix,
     columns.numeric_scale,
     columns.datetime_precision,
     columns.interval_type,
     columns.interval_precision,
     columns.character_set_catalog,
     columns.character_set_schema,
     columns.character_set_name,
     columns.collation_catalog,
     columns.collation_schema,
     columns.collation_name,
     columns.domain_catalog,
     columns.domain_schema,
     columns.domain_name,
     columns.udt_catalog,
     columns.udt_schema,
     columns.udt_name,
     columns.scope_catalog,
     columns.scope_schema,
     columns.scope_name,
     columns.maximum_cardinality,
     columns.dtd_identifier,
     columns.is_self_referencing,
     columns.is_identity,
     columns.identity_generation,
     columns.identity_start,
     columns.identity_increment,
     columns.identity_maximum,
     columns.identity_minimum,
     columns.identity_cycle,
     columns.is_generated,
     columns.generation_expression,
     columns.is_updatable
    FROM information_schema.columns
   WHERE (((columns.table_schema)::name = pg_safer_settings_table_columns."table_schema$") AND ((columns.table_name)::name = pg_safer_settings_table_columns."table_name$") AND (NOT ((columns.column_name)::name = ANY (ARRAY['is_singleton'::text, 'inserted_at'::text, 'updated_at'::text]))));
END

Function: pg_safer_settings_table__create_or_replace_getters()

This trigger function automatically CREATE OR REPLACEs, 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.

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—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.

Function return type: trigger

Function-local settings:

  • SET search_path TO ext, ext, pg_temp

Function: pg_safer_settings_table__mirror_col_to_db_role_setting()

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.

Function return type: trigger

Function-local settings:

  • SET search_path TO ext, ext, pg_temp

Function: pg_safer_settings_table__register()

This trigger function creates and maintains the safer settings tables that are registered with it.

Function return type: trigger

Function-local settings:

  • SET search_path TO ext, ext, pg_temp

Function: pg_safer_settings_table__update_on_copy()

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?

Function return type: trigger

Function-local settings:

  • SET search_path TO ext, ext, pg_temp

Function: pg_safer_settings_version()

Returns the currently (being) installed version of the pg_safer_settings extension.

Function return type: text

Function attributes: STABLE, LEAKPROOF, PARALLEL SAFE

Procedure: test_dump_restore__pg_safer_settings_table (text)

Procedure arguments:

| Arg. # | Arg. mode | Argument name | Argument type | Default expression | | —— | ––––– | —————————————————————– | –––––––––––––––––––––––––––––––––– | —————–– | | $1 | IN | test_stage$ | text | |

Procedure-local settings:

  • SET search_path TO ext, ext, pg_temp
  • SET plpgsql.check_asserts TO true
  • SET pg_readme.include_this_routine_definition TO true
CREATE OR REPLACE PROCEDURE ext.test_dump_restore__pg_safer_settings_table(IN "test_stage$" text)
 LANGUAGE plpgsql
 SET search_path TO 'ext', 'ext', 'pg_temp'
 SET "plpgsql.check_asserts" TO 'true'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
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 generated_setting text
                not null
                generated always as (case when boolean_test_setting then 'yes' else 'no' end) stored
            ,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;
        assert current_subext_bool_setting();
        assert current_subext_generated_setting() = 'yes';
        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.generated_setting = 'no';
        assert current_generated_setting() = 'no';

        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_generated_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_generated_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_generated_setting() = 'yes';

        assert current_subext_text_setting() = 'Set by subsubextension',
            'The configuration value set by the subsubextension should have been preserver.';
    end if;
end;
$procedure$

Procedure: test__pg_db_setting()

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.

Procedure-local settings:

  • SET search_path TO ext, ext, pg_temp
  • SET plpgsql.check_asserts TO true
  • SET pg_readme.include_this_routine_definition TO true
CREATE OR REPLACE PROCEDURE ext.test__pg_db_setting()
 LANGUAGE plpgsql
 SET search_path TO 'ext', 'ext', 'pg_temp'
 SET "plpgsql.check_asserts" TO 'true'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
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;
$procedure$

Procedure: test__pg_safer_settings_table()

Procedure-local settings:

  • SET search_path TO ext, ext, pg_temp
  • SET pg_readme.include_this_routine_definition TO true
CREATE OR REPLACE PROCEDURE ext.test__pg_safer_settings_table()
 LANGUAGE plpgsql
 SET search_path TO 'ext', 'ext', 'pg_temp'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
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;
$procedure$

Colophon

This README.md for the pg_safer_settings extension was automatically generated using the pg_readme PostgreSQL extension.