Can we remove support for standard_conforming_strings = off yet?

Hi

út 30. 12. 2025 v 6:29 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

standard_conforming_strings has defaulted to ON since 2010 (see
0839f312e in the 9.1 release). I propose that it's finally time to
force it on and get rid of code that supports the "off" setting.

Aside from pure code removal, there are nontrivial benefits that will
accrue:

* The setting has always been a bit of a security hazard, since
client code that escapes strings assuming one setting is at big
risk of SQL injection if the server is actually using the other.
We've disclaimed such issues as not-our-problem, but that doesn't
make them not a hazard.

* As discussed in the NOTES at the head of src/backend/parser/gram.y,
it's not okay for raw parsing to depend on changeable GUCs. Sure,
gram.y doesn't use that setting; but scan.l does, so it's broken and
dangerous. (See also the comment in scan.l above the declaration of
standard_conforming_strings.)

The code-removal aspect shouldn't be minimized either. There's
a nontrivial portion of scan.l that isn't reachable unless
standard_conforming_strings is off, and reducing the size of the
lexer probably translates directly to performance benefits.

I would envision this working similarly to what we previously did with
default_with_oids: the GUC still exists so that applications can query
it, and we even allow you to explicitly set it to 'on'; only setting
it to 'off' will fail.

I haven't looked into subsidiary questions such as whether we can
similarly lobotomize escape_string_warning, or when it'd be okay to
remove the client-side support for non-standard-conforming strings
in the psql and ecpg lexers. I'll push forward on those questions
if there's consensus that doing something about this is acceptable
in principle.

+1

Regards

Pavel

I wrote:

standard_conforming_strings has defaulted to ON since 2010 (see
0839f312e in the 9.1 release). I propose that it's finally time to
force it on and get rid of code that supports the "off" setting.

Here's a draft patch series for that.

As I was working through it, I realized that there's one
potentially-nasty point that might cause upgrading problems.
To wit, pg_dump and pg_dumpall have historically replicated the
source server's standard_conforming_strings setting into their
output: they emit a SET command for that, and any string literals
appearing in views or the like will be escaped accordingly.
So if your old installation had standard_conforming_strings = off,
and all you have from it is existing pg_dump output (either text
or archive format), you are in a sticky situation because that
dump will not restore cleanly. This isn't impossible to get
out of, but you'd probably have to stand up a pre-v19 server,
restore the dump into that, and take a fresh dump made with
standard_conforming_strings = on. The alternative would be
manual correction of literals in the dump script, which seems
far too error-prone to be recommendable.

This isn't a problem if you can make the dump with v19 pg_dump
(and therefore it's not an issue for pg_upgrade cases). The attached
patch series tweaks pg_dump to force standard_conforming_strings = on
while dumping, so that it will produce a usable dump even if the
source server had the wrong default.

Nonetheless, if I thought there were more than epsilon existing
installations still running standard_conforming_strings = off
as a global setting, I'd worry that we still can't get away with
making this change. But if I believed that, I wouldn't be
proposing it. This observation does raise the stakes a bit though.

Another comment worth making is that I oversold the code-savings
benefit:

The code-removal aspect shouldn't be minimized either. There's
a nontrivial portion of scan.l that isn't reachable unless
standard_conforming_strings is off, and reducing the size of the
lexer probably translates directly to performance benefits.

When I wrote that I was thinking we could get rid of the flex rules
for the <xe> exclusive state, but of course we cannot: those rules
are shared with the escape-string syntax E'...'. In the attached,
the rules flex sees don't change at all. We can get rid of the
code supporting escape_string_warning, because those warnings are
unreachable unless standard_conforming_strings = off. But that's
no large amount of code.

I still think this is worth doing on the grounds of closing
edge-case security holes. But perhaps it's not quite as
attractive as I thought yesterday. A compromise position
could be to apply only 0003 attached to change pg_dump[all]'s
behavior, then wait a few more years so that the dump/reload
hazard is minimized before we reconsider the main patch.

Anyway, patches attached.

regards, tom lane

As I was working through it, I realized that there's one
potentially-nasty point that might cause upgrading problems.
To wit, pg_dump and pg_dumpall have historically replicated the
source server's standard_conforming_strings setting into their
output: they emit a SET command for that, and any string literals
appearing in views or the like will be escaped accordingly.
So if your old installation had standard_conforming_strings = off,
and all you have from it is existing pg_dump output (either text
or archive format), you are in a sticky situation because that
dump will not restore cleanly.

I could see allowing the SET command for a few more revisions, but not
allowing it to be the global setting. This would give pg_restore a window
to catch that potentially noisy set of forlorn dump outputs. That's if
we're being really cautious.

This isn't impossible to get
out of, but you'd probably have to stand up a pre-v19 server,
restore the dump into that, and take a fresh dump made with
standard_conforming_strings = on.

This gets me thinking that we might make use of a script that does a
bridging pg_restore (taking the intermediate version's bindir as a
parameter) followed by a pg_upgrade to the current version. If we had that,
we could save ourselves a lot of future hand wringing about similar
breaking changes.

The alternative would be
manual correction of literals in the dump script, which seems
far too error-prone to be recommendable.

Ick.

Nonetheless, if I thought there were more than epsilon existing
installations still running standard_conforming_strings = off
as a global setting, I'd worry that we still can't get away with
making this change. But if I believed that, I wouldn't be
proposing it. This observation does raise the stakes a bit though.

To be fair, our epsilon is not the people who are currently running such an
installation, but people who ONCE RAN such an installation, shut it down,
and now want it back. Such people are already experiencing "downtime", so a
multi-step restoration process isn't unreasonable. Furthermore, I would
suspect that the vast majority of such restoration needs are forensic in
nature, so restoring to the same version as the dumped version is vastly
preferable if not legally required.

Another comment worth making is that I oversold the code-savings
benefit:

The code-removal aspect shouldn't be minimized either. There's
a nontrivial portion of scan.l that isn't reachable unless
standard_conforming_strings is off, and reducing the size of the
lexer probably translates directly to performance benefits.

I will admit that this potential code savings was what got me interested in
the thread, but not every prospector finds gold.

Anyway, patches attached.

The patches look right at a glance, but I haven't been able to thoroughly
review them yet.

On 2025-12-30 Tu 5:50 PM, Tom Lane wrote:

I wrote:

standard_conforming_strings has defaulted to ON since 2010 (see
0839f312e in the 9.1 release). I propose that it's finally time to
force it on and get rid of code that supports the "off" setting.

Here's a draft patch series for that.

As I was working through it, I realized that there's one
potentially-nasty point that might cause upgrading problems.
To wit, pg_dump and pg_dumpall have historically replicated the
source server's standard_conforming_strings setting into their
output: they emit a SET command for that, and any string literals
appearing in views or the like will be escaped accordingly.
So if your old installation had standard_conforming_strings = off,
and all you have from it is existing pg_dump output (either text
or archive format), you are in a sticky situation because that
dump will not restore cleanly. This isn't impossible to get
out of, but you'd probably have to stand up a pre-v19 server,
restore the dump into that, and take a fresh dump made with
standard_conforming_strings = on. The alternative would be
manual correction of literals in the dump script, which seems
far too error-prone to be recommendable.

Have we ever promised that dumps made using pg_dump/pg_dumpall from
other than the target version work?

I don't see this as a big issue, unless I'm misunderstanding.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

Andrew Dunstan <andrew@dunslane.net> writes:

On 2025-12-30 Tu 5:50 PM, Tom Lane wrote:

As I was working through it, I realized that there's one
potentially-nasty point that might cause upgrading problems.
To wit, pg_dump and pg_dumpall have historically replicated the
source server's standard_conforming_strings setting into their
output: they emit a SET command for that, and any string literals
appearing in views or the like will be escaped accordingly.
So if your old installation had standard_conforming_strings = off,
and all you have from it is existing pg_dump output (either text
or archive format), you are in a sticky situation because that
dump will not restore cleanly.

Have we ever promised that dumps made using pg_dump/pg_dumpall from
other than the target version work?

We may not promise it, but I think we've always tried very hard to
not break old pg_dump files, precisely because they might be the only
available backup.

I don't see this as a big issue, unless I'm misunderstanding.

Personally I would call it a deal-breaker if I thought it'd affect
more than a very very tiny number of people. But the entire premise
of this patch is that nobody is using standard_conforming_strings =
off in production anymore. If that isn't true it's probably a
mistake to go forward anyway.

regards, tom lane

On 2025-12-31 We 12:04 PM, Tom Lane wrote:

Andrew Dunstan<andrew@dunslane.net> writes:

On 2025-12-30 Tu 5:50 PM, Tom Lane wrote:

As I was working through it, I realized that there's one
potentially-nasty point that might cause upgrading problems.
To wit, pg_dump and pg_dumpall have historically replicated the
source server's standard_conforming_strings setting into their
output: they emit a SET command for that, and any string literals
appearing in views or the like will be escaped accordingly.
So if your old installation had standard_conforming_strings = off,
and all you have from it is existing pg_dump output (either text
or archive format), you are in a sticky situation because that
dump will not restore cleanly.

Have we ever promised that dumps made using pg_dump/pg_dumpall from
other than the target version work?

We may not promise it, but I think we've always tried very hard to
not break old pg_dump files, precisely because they might be the only
available backup.

I don't see this as a big issue, unless I'm misunderstanding.

Personally I would call it a deal-breaker if I thought it'd affect
more than a very very tiny number of people. But the entire premise
of this patch is that nobody is using standard_conforming_strings =
off in production anymore. If that isn't true it's probably a
mistake to go forward anyway.

I think you're probably right that very few people are using it. Not
sure how we'd find out if they are, though.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

I don't see this as a big issue, unless I'm misunderstanding.

Personally I would call it a deal-breaker if I thought it'd affect
more than a very very tiny number of people. But the entire premise
of this patch is that nobody is using standard_conforming_strings =
off in production anymore. If that isn't true it's probably a
mistake to go forward anyway.

I think you're probably right that very few people are using it. Not sure
how we'd find out if they are, though.

I've poked around for a while now, and I can't find anything that should
have been removed that wasn't already removed.

I'm a teensy bit uneasy that we're outright removing escape_string_warning,
perhaps allowing it to be set but not actually setting the GUC might allow
some ancient script to avoid an error, but that unease isn't enough to stop
anything.

I do think we should make an effort in the release notes to say that
db-dumps with standard_conforming_strings = off must first be restored to a
pre-19 instance before upgrading, but that's about it.

On Thu, Jan 1, 2026 at 12:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Personally I would call it a deal-breaker if I thought it'd affect
more than a very very tiny number of people. But the entire premise
of this patch is that nobody is using standard_conforming_strings =
off in production anymore. If that isn't true it's probably a
mistake to go forward anyway.

FWIW, I spent a few minutes looking, and I only found issues from ~5
years ago about software not working with the setting off. I didn't
see any details on what they were running in the application stack
that required it:

https://github.com/PostgREST/postgrest/issues/1992

https://github.com/npgsql/npgsql/issues/3333

--
John Naylor
Amazon Web Services

On Mon, Jan 05, 2026 at 05:03:48PM +0700, John Naylor wrote:

On Thu, Jan 1, 2026 at 12:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Personally I would call it a deal-breaker if I thought it'd affect
more than a very very tiny number of people. But the entire premise
of this patch is that nobody is using standard_conforming_strings =
off in production anymore. If that isn't true it's probably a
mistake to go forward anyway.

FWIW, I spent a few minutes looking, and I only found issues from ~5
years ago about software not working with the setting off. I didn't
see any details on what they were running in the application stack
that required it:

https://github.com/PostgREST/postgrest/issues/1992

https://github.com/npgsql/npgsql/issues/3333

I had another one ~4 years ago:

https://github.com/powa-team/powa-archivist/issues/51

Since I fixed powa-archivist at that time, I don't know if anyone else would
have faced the same problem, although the OP is likely still using the same
setting.

Julien Rouhaud <rjuju123@gmail.com> writes:

On Mon, Jan 05, 2026 at 05:03:48PM +0700, John Naylor wrote:

On Thu, Jan 1, 2026 at 12:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Personally I would call it a deal-breaker if I thought it'd affect
more than a very very tiny number of people. But the entire premise
of this patch is that nobody is using standard_conforming_strings =
off in production anymore. If that isn't true it's probably a
mistake to go forward anyway.

FWIW, I spent a few minutes looking, and I only found issues from ~5
years ago about software not working with the setting off. I didn't
see any details on what they were running in the application stack
that required it:
https://github.com/PostgREST/postgrest/issues/1992
https://github.com/npgsql/npgsql/issues/3333

I had another one ~4 years ago:
https://github.com/powa-team/powa-archivist/issues/51
Since I fixed powa-archivist at that time, I don't know if anyone else would
have faced the same problem, although the OP is likely still using the same
setting.

So, nobody's actually spoken against this change. I think we should
go forward with it, for the reasons I gave at the top of the thread
and because "if not now, when?". I have little doubt that if there
is anyone still running with standard_conforming_strings = off, they
aren't going to change until forced to. So unless we want to live
with the potential security hazard forever, we're going to have to
make a breaking change sometime.

regards, tom lane

On Tuesday, January 20, 2026, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think we should go forward with it,

Seems entirely reasonable for us to do this.

+1

David J.

Hi,

On Tue, Jan 20, 2026 at 01:31:53PM -0500, Tom Lane wrote:

So, nobody's actually spoken against this change. I think we should
go forward with it, for the reasons I gave at the top of the thread
and because "if not now, when?". I have little doubt that if there
is anyone still running with standard_conforming_strings = off, they
aren't going to change until forced to. So unless we want to live
with the potential security hazard forever, we're going to have to
make a breaking change sometime.

+1 from me. All of that is sensible, plus from an extension point of view it's
easy to write code that is broken with standard_conforming_strings = off so
it's one less thing to worry about.

Julien Rouhaud <rjuju123@gmail.com> writes:

On Tue, Jan 20, 2026 at 01:31:53PM -0500, Tom Lane wrote:

So, nobody's actually spoken against this change. I think we should
go forward with it, for the reasons I gave at the top of the thread
and because "if not now, when?". I have little doubt that if there
is anyone still running with standard_conforming_strings = off, they
aren't going to change until forced to. So unless we want to live
with the potential security hazard forever, we're going to have to
make a breaking change sometime.

+1 from me. All of that is sensible, plus from an extension point of view it's
easy to write code that is broken with standard_conforming_strings = off so
it's one less thing to worry about.

Pushed.

regards, tom lane

On Wed, Jan 21, 2026 at 03:09:30PM -0500, Tom Lane wrote:

Julien Rouhaud <rjuju123@gmail.com> writes:

On Tue, Jan 20, 2026 at 01:31:53PM -0500, Tom Lane wrote:

So, nobody's actually spoken against this change. I think we should
go forward with it, for the reasons I gave at the top of the thread
and because "if not now, when?". I have little doubt that if there
is anyone still running with standard_conforming_strings = off, they
aren't going to change until forced to. So unless we want to live
with the potential security hazard forever, we're going to have to
make a breaking change sometime.

+1 from me. All of that is sensible, plus from an extension point of view it's
easy to write code that is broken with standard_conforming_strings = off so
it's one less thing to worry about.

Pushed.

Great, thanks.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.

hi.

_allocAH is called by OpenArchive and CreateArchive. in _allocAH, we can set

AH->public.std_strings = true;

that would be more future-proof, otherwise after OpenArchive,
executing the query ``SET standard_conforming_strings = off;``
would result in an error.

--
jian
https://www.enterprisedb.com/