pg_extension_name | pg_extension_version | pg_readme_generated_at | pg_readme_version |
---|---|---|---|
pg_safer_settings |
0.8.12 |
2024-03-08 16:47:30 +0000 |
0.6.6 |
pg_safer_settings
provides a handful of functions and mechanisms to make
dealing with settings in Postgres a bit … safer.
Out of the box, PostgreSQL offers a mechanism for custom settings, but with a couple of caveats:
- Every
ROLE
can read (SHOW
) most settings. - Every
ROLE
can override (SET
) most settings for the current session or transaction. - 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.
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.
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.
Command | Function |
---|---|
SET |
set_config(text, text, bool) |
SHOW |
current_setting(text, text, bool) |
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.
- Rowan originated this extension in 2022 while developing the PostgreSQL backend for the FlashMQ SaaS MQTT cloud broker. Rowan does not like to see himself as a tech person or a tech writer, but, much to his chagrin, he is. Some of his chagrin about his disdain for the IT industry he poured into a book: Why Programming Still Sucks. 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. 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 (available for rent!).
There are 1 tables that directly belong to the pg_safer_settings
extension.
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 thepg_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:
- 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.) - Columns can have column constraints, or be part of a multi-column constraint.
- 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.
- 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.
- It's easy to provide defaults for settings, because these are simply the columns' defaults.
NOT NULL
constraints, like all constraints, become easier.
The disadvantage of dividing settings over columns rather than rows is:
- If you need/want to do concurrent updates, you may run into lock contention.
The pg_safer_settings_table
table has 8 attributes:
-
pg_safer_settings_table.table_regclass
regclass
NOT NULL
PRIMARY KEY (table_regclass)
-
pg_safer_settings_table.table_schema
name
NOT NULL
DEFAULT CURRENT_SCHEMA
-
pg_safer_settings_table.table_name
name
NOT NULL
DEFAULT 'cfg'::name
-
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 thesetting_getter_prefix
follows the naming of Postgres its owncurrent_setting()
function name.NOT NULL
DEFAULT 'current_'::name
-
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 aSTABLE
function that, when called, looks up the column value in the table rather than the defaultIMMUTABLE
function (with the configuration value cached in theRETURN
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 (norEXECUTE
access to theIMMUTABLE
function).NOT NULL
DEFAULT 'secret_'::name
-
pg_safer_settings_table.pg_safer_settings_version
text
NOT NULL
DEFAULT pg_safer_settings_version()
-
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. -
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.
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
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
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__%}
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
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 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.
Function return type: trigger
Function-local settings:
SET search_path TO ext, ext, pg_temp
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
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
.
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
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
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
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:
- as a result of the
INSERT AFTER
trigger onpg_safer_settings_table
, and - 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
Returns the currently (being) installed version of the pg_safer_settings
extension.
Function return type: text
Function attributes: STABLE
, LEAKPROOF
, PARALLEL SAFE
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$
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-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$
This README.md
for the pg_safer_settings
extension was automatically generated using the pg_readme
PostgreSQL extension.