current_setting and set_config in PostgreSQL, measured

A custom setting — app.tenant, app.user_id, myapp.anything — is how most applications tell PostgreSQL who is asking: the application calls set_config, and a row-level security policy, a trigger or a default reads it back with current_setting. The documentation gives each function one table row. What they do with a setting that PostgreSQL has never heard of takes more than a row to describe.

We ran every form we could think of on PostgreSQL 17.11 and wrote down what came back. A setting that was never set reads as NULL, and once anything has named it in a SET, a RESET or a set_config — even one that was rolled back — it reads as an empty string from then on: two different “not set” states, of which one makes a cast fail. A transaction-local value outside a transaction lasts until the implicit transaction ends, which is usually one statement. A default on the role makes every login as that role start as that tenant. And any role with a connection may set any custom setting that no extension has claimed; the privilege that exists for parameters does not stop that.

What does current_setting return when the setting is not set?

It depends on whether the session has ever touched the name. Before that, current_setting('app.tenant') fails with unrecognized configuration parameter "app.tenant", and current_setting('app.tenant', true) returns NULL. After the name has been set once and reset — or set for one transaction that has ended — both forms return an empty string, without an error. A bare RESET of a name that was never set, a SET LOCAL outside a transaction and a SET in a transaction that rolled back did the same. The documentation describes only the first state: “If there is no such setting, current_setting throws an error unless missing_ok is supplied and is true (in which case NULL is returned).”

For a row-level security policy that is the difference between “no rows” and “every query on the table fails”. We put the same policy on two tables, once as tenant_id = current_setting('app.tenant', true)::int and once with nullif(…, '') around the setting:

A connection pool hands out sessions in that last state as a matter of course: DISCARD ALL leaves the empty string behind.

Why does a policy fail with invalid input syntax for type uuid: ""?

Because the setting it casts is an empty string, not NULL. current_setting('app.tenant', true)::uuid gave ERROR: invalid input syntax for type uuid: "" in a session where the setting had been set and reset, exactly as the int cast did; in a session that had never named the setting the same expression is NULL and the policy returns no rows. So the error appears on connections that have served a request before, and not on a fresh one. nullif(current_setting('app.tenant', true), '')::uuid returns NULL in both.

The same empty string comes from set_config('app.tenant', NULL, false): a setting cannot be null, the call returned an empty string and left one behind. Setting a name that does not exist yet TO DEFAULT created it as an empty string as well.

How long does a value last?

Three of these rows are worth a second look.

Outside a transaction, transaction-local usually means one statement. select set_config('app.tenant', '1', true) returned 1, and the next statement read ''. Within that one statement it worked — the same SELECT that set it counted tenant 1’s 20 invoices in a subquery — which is why a one-line test of it passes. Strictly it lasts until the implicit transaction ends: two statements sent as one string, psql -c "select set_config(…, true); select count(*) from invoices", shared it, and the second counted 20. PERFORM set_config(…, true) in a PL/pgSQL function is no different: the value outlives the function and lasts until the surrounding transaction ends, and when the function is called with no transaction open, that is the end of the statement that called it.

A session-level value comes back. With SET app.tenant = '2' on the session, a transaction that did SET LOCAL app.tenant = '1' read 1 inside and 2 again after its commit. Transaction-local values do not clean up what was there before them; they cover it. A connection that someone once gave a session-level tenant keeps it under every transaction that follows, until something resets the session: DISCARD ALL did, a rollback of a later transaction did not.

A function can carry its own value. A function created with SET app.tenant = '2' counted tenant 2’s 10 invoices while the caller’s session was set to tenant 1, and the caller read 1 again afterwards. A caller that had never set the name read '' afterwards, not NULL: calling such a function is one more way a session moves from the first “not set” state to the second. It is a clean way to give one function a fixed context, and it needs a privilege; see “Who may set a custom setting?” below.

Can a custom setting have a default?

Yes, on the database or on the role, and it changes what “not set” means.

alter database app_db set app.tenant = '';

After this, a new session started in the second state straight away: current_setting('app.tenant') returned '' without an error and was not NULL, a RESET returned to '', and so did the end of a transaction-local value. For sessions opened in that database after the change, app.tenant has one “not set” state instead of two. Connections that were already open kept the NULL state until they were closed, and the default covers that one name in that one database: the same role in another database, and another name such as app.user_id, still read NULL. The policy with the direct cast now failed on every new connection instead of only on used ones, which is the better failure: it shows up in the first test. The ALTER DATABASE itself needed a superuser or the privilege from the next section; the database’s owner got permission denied to set parameter "app.tenant".

A default with a value in it is a different thing. With alter role … set app.tenant = '2', a new session of that role counted tenant 2’s 10 invoices without setting anything, and the setting read 2 again after a RESET and after a transaction that had been tenant 1. DISCARD ALL returned to 2 as well, so a pooler’s reset does not remove it. Every path that forgets to set the tenant is now tenant 2. We would only ever put the empty string there.

There is a third place for a default: the connection’s startup options. With PGOPTIONS="-c app.tenant=1" a session counted tenant 1’s 20 invoices without running a SET, a RESET returned to 1, and it took precedence over the role’s default.

The setting does not appear in pg_settings in either case: a query for name = 'app.tenant' returned no rows while the value was set, with and without a default, and SHOW ALL did not list it either; SHOW app.tenant does return it. The defaults themselves are in pg_db_role_setting.

Who may set a custom setting?

Anyone who can run SQL on the connection. A role with nothing but SELECT on the table ran SET app.tenant = '1' and counted tenant 1’s 20 invoices. There is a privilege for parameters, GRANT SET ON PARAMETER; that role did not have it, and the SET went through regardless. So did a value passed in the connection’s startup options.

What the privilege did govern is making a value stick. Without it, an ordinary role got permission denied to set parameter "app.tenant" for ALTER ROLE … SET app.tenant = '1' on itself and for CREATE FUNCTION … SET app.tenant = '2'; the same role could ALTER ROLE … SET work_mem on itself without it. ALTER FUNCTION … SET gave the same error. After GRANT SET ON PARAMETER app.tenant they went through, and a function that a superuser had created with the clause ran for a role without the grant.

So a custom setting is not a credential. It tells the policy what the application says, and whoever can send a statement can say something else. That is the same conclusion as in a role per tenant vs a session variable, where we measured what a database role per tenant does and does not add.

What else did we run into?

• The name needs a dot. set_config('tenant', '1', false) and SET tenant = '1' both failed with unrecognized configuration parameter "tenant". The documentation says PostgreSQL “will accept a setting for any two-part parameter name”.
• The prefix can belong to someone. In a session that had not run PL/pgSQL yet, SET plpgsql.nonexistent = '1' was accepted; the first PL/pgSQL block then gave WARNING: invalid configuration parameter name "plpgsql.nonexistent", removing it, with the detail "plpgsql" is now a reserved prefix, and the value was gone. Once PL/pgSQL was loaded, the same SET was an error. Pick a prefix that is not the name of an extension you load.
• Names are case-insensitive. After SET App.Tenant = '2', both current_setting('app.tenant') and current_setting('APP.TENANT') returned 2.
• The value is always text. pg_typeof(current_setting('app.tenant')) is text; the cast is yours, and so is handling the empty string.
• SET takes no parameter. Sent with a bound parameter, SET app.tenant = $1 failed with syntax error at or near "$1"; set_config('app.tenant', $1, false) with the same parameter ran. Code that sends bound parameters needs the function form.
• Parallel workers see it. With a parallel plan forced, the policy’s current_setting under a Gather node still counted tenant 1’s 20 invoices, for a session-level value and for a transaction-local one.

What should a policy look like?

create policy tenant on invoices
  using (tenant_id = nullif(current_setting('app.tenant', true), '')::int);

true for the session that never set it, nullif for the session that set it once and no longer has a value. Both “not set” states then give no rows, and neither gives an error. Any other value that is not an integer is an error, not “no rows”: 'abc' and a single space both failed with invalid input syntax for type integer. And the cast is lenient: ' 1 ' counted tenant 1’s invoices and '0x2' tenant 2’s. Set the value with set_config('app.tenant', $1, true) as the first statement of a transaction, and pass an empty string when there is no tenant rather than skipping the call, so that a session-level value left on the connection is covered. It is covered for that transaction only; a query on the same connection outside a transaction read the leftover value again. What goes wrong around that — a pooler, a framework’s connection handling, views, writes — is indexed in PostgreSQL row-level security for SaaS: what we measured.

What we did not measure

Settings defined by an extension, which have a type and a real default. ALTER SYSTEM and postgresql.conf as a place for the default. Whether a managed service lets you run ALTER DATABASE … SET or GRANT SET ON PARAMETER for a custom name. Versions before 17. And the cost of current_setting in a policy, which is in row-level security performance in PostgreSQL, measured.

Sources: the PostgreSQL 17 documentation on configuration settings functions and customized options, read on 12 October 2026. All results are our own, on PostgreSQL 17.11 in a throwaway container, on 12 October 2026.