pg_safer_settings 0.6.1

This Release
pg_safer_settings 0.6.1
Date
Status
Stable
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.
Released By
bigsmoke
License
GPL 3
Resources
Special Files
Tags

Extensions

pg_safer_settings 0.6.1

Documentation

LICENSE
LICENSE

README


pg_extension_name: pg_safer_settings pg_extension_version: 0.6.1 pg_readme_generated_at: 2023-01-07 18:00:18.493472+00

pg_readme_version: 0.3.8

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:

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

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

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:

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

The pg_safer_settings_table table has 6 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()

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:

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'

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:

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

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:

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 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 ((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: an IMMUTABLE function that returns the most up-to-date value for that column.

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__mirror_col_to_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. To get this trigger

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__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 pgsafer_settings_table pg_safer_settings_table; cfgrecord record; begin insert into pg_safer_settings_table (table_name) values ('test__cfg') returning * into pgsafer_settings_table ; assert pgsafer_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.