Transaction local custom settings set to '' rather than removed entirely after transaction ends

From: Bradley Ayers <bradley(dot)ayers(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Transaction local custom settings set to '' rather than removed entirely after transaction ends
Date: 2018-02-17 22:54:19
Message-ID: CA+Q86ij0KDCB0G45G509-8q0DNR611gcKG-sSM83GA1EBL7boA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Before creating a custom local setting, current_setting(string) will raise
an error:

select current_setting('foo.bar');
ERROR: unrecognized configuration parameter "foo.bar"

After assigning a local value during a transaction and finishing the
transaction, the setting becomes recognised, has an empty string value:

begin;

set local "foo.bar" to 'baz';

rollback;

select current_setting('foo.bar');

current_setting

select pg_typeof(current_setting('foo.bar'));

pg_typeof

text

This behaviour lasts for the duration of the connection, and returns to the
initial state after reconnecting.

This caused me problems, as I was using the new 'missing_ok' parameter for
current_setting (added in PostgreSQL 9.6) to return null, e.g.

select current_setting('foo.bar.baz', true);
NULL

I am using local settings to store an (e.g. application user UUID), which I
then use in RLS policies and also refer to in a DEFAULT clause for a table
column, e.g.

create table doc (
id uuid primary key,
title text,
author uuid default current_setting('app.user.id', true)::uuid
);

However app.user.id is only set if a user is performing a query, it's also
possible for a app service role to connect to the database and insert rows
too. For this to work I rely on current_setting returning null (rather than
an empty string).

I would like the presence of a local setting to not leak out of a
transaction.

--
Cheers,
Brad

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Eisentraut 2018-02-18 03:43:38 Re: BUG #15044: materialized views incompatibility with logical replication in postgres 10
Previous Message Francisco Olarte 2018-02-16 15:31:44 Re: BUG #15071: Error in PostgreSQL-specific :: type cast