Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Force standard_conforming_strings to always be ON.

  2. Doc: remove obsolete, confused <note> about rowtype I/O syntax.

  1. Can we remove support for standard_conforming_strings = off yet?

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-30T05:28:58Z

    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.

			regards, tom lane

  2. Re: Can we remove support for standard_conforming_strings = off yet?

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-12-30T05:32:13Z

    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


>
>                         regards, tom lane
>
>
>

  3. Re: Can we remove support for standard_conforming_strings = off yet?

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-30T22:50:10Z

    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

  4. Re: Can we remove support for standard_conforming_strings = off yet?

    Corey Huinker <corey.huinker@gmail.com> — 2025-12-31T01:05:19Z

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

  5. Re: Can we remove support for standard_conforming_strings = off yet?

    Andrew Dunstan <andrew@dunslane.net> — 2025-12-31T16:02:36Z

    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

  6. Re: Can we remove support for standard_conforming_strings = off yet?

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-12-31T17:04:41Z

    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

  7. Re: Can we remove support for standard_conforming_strings = off yet?

    Andrew Dunstan <andrew@dunslane.net> — 2025-12-31T19:01:12Z

    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