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