How to I select value of GUC that has - in its name?

On 09/02/2021 14:40, hubert depesz lubaczewski wrote:

Hi,
question from IRC, but I couldn't find an answer.

I can set custom guc with - in name, but I can't figure out how to
select it.

Without minus, it works great:

=$ psql -X -c 'show custom.guc'
ERROR: unrecognized configuration parameter "custom.guc"

=$ psql -X -c "alter user depesz set custom.guc = '123'"
ALTER ROLE

=$ psql -X -c 'show custom.guc'
custom.guc
------------
123
(1 row)

If I'd try to set variable with - in name:

$ alter user depesz set custom.bad-guc = '1a';
ERROR: syntax error at or near "-"
LINE 1: alter user depesz set custom.bad-guc = '1a';

Have you tried enclosing "bad-guc" in double quotes?

Eddy

On Tue, Feb 09, 2021 at 02:49:19PM +0000, Edward Macnaghten wrote:

$ alter user depesz set custom.bad-guc = '1a';
ERROR: syntax error at or near "-"
LINE 1: alter user depesz set custom.bad-guc = '1a';

Have you tried enclosing "bad-guc" in double quotes?

I'm sorry, but have you read my mail?

I did. On set and on reading.

Best regards,

depesz

On 2/9/21 6:40 AM, hubert depesz lubaczewski wrote:

Hi,
question from IRC, but I couldn't find an answer.

I can set custom guc with - in name, but I can't figure out how to
select it.

Without minus, it works great:

=$ psql -X -c 'show custom.guc'
ERROR: unrecognized configuration parameter "custom.guc"

=$ psql -X -c "alter user depesz set custom.guc = '123'"
ALTER ROLE

=$ psql -X -c 'show custom.guc'
custom.guc
------------
123
(1 row)

If I'd try to set variable with - in name:

$ alter user depesz set custom.bad-guc = '1a';
ERROR: syntax error at or near "-"
LINE 1: alter user depesz set custom.bad-guc = '1a';
^
$ alter user depesz set custom."bad-guc" = '1a';
ALTER ROLE

$ select * from pg_db_role_setting where setrole = 'depesz'::regrole;
setdatabase │ setrole │ setconfig
─────────────┼─────────┼─────────────────────────────────────────────────────────
0 │ 16384 │ {application_name=xxx,custom.guc=123,custom.bad-guc=1a}
(1 row)

OK. Looks like it's set. But I can't show it (this is after reconnect):

$ show custom."bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"

$ show "custom"."bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"

$ show "custom.bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"

I know I can simply not use dashes in names, but if I can *set* it, how
can I get the value back?

The only way I found so far is:

select setconfig[array_position(setconfig, 'custom.bad-guc=1a')] from
pg_db_role_setting where setrole = 'aklaver'::regrole;

setconfig
-------------------
custom.bad-guc=1a

depesz

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Feb 09, 2021 at 07:41:02AM -0800, Adrian Klaver wrote:

The only way I found so far is:
select setconfig[array_position(setconfig, 'custom.bad-guc=1a')] from
pg_db_role_setting where setrole = 'aklaver'::regrole;

Yeah, but this will require some parsing if I don't know what the value
is.
Of course, it's not impossible, I can:
with x as (
select unnest(setconfig) as u
from pg_db_role_setting
where setrole = 'depesz'::regrole
)
select regexp_replace(u, '^[^=]*=', '') from x where u like 'custom.bad-guc=%';

but I find it curious that I can set the guc using normal-ish SET, but
can't get it using SHOW or even select current_setting()

depesz

On 2/9/21 7:45 AM, hubert depesz lubaczewski wrote:

On Tue, Feb 09, 2021 at 07:41:02AM -0800, Adrian Klaver wrote:

The only way I found so far is:
select setconfig[array_position(setconfig, 'custom.bad-guc=1a')] from
pg_db_role_setting where setrole = 'aklaver'::regrole;

Yeah, but this will require some parsing if I don't know what the value
is.
Of course, it's not impossible, I can:
with x as (
select unnest(setconfig) as u
from pg_db_role_setting
where setrole = 'depesz'::regrole
)
select regexp_replace(u, '^[^=]*=', '') from x where u like 'custom.bad-guc=%';

but I find it curious that I can set the guc using normal-ish SET, but
can't get it using SHOW or even select current_setting()

Yeah, I think that part is a bug report.

depesz

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 2/9/21 7:45 AM, hubert depesz lubaczewski wrote:

but I find it curious that I can set the guc using normal-ish SET, but
can't get it using SHOW or even select current_setting()

Yeah, I think that part is a bug report.

After digging around in the code, I find the reason is that the entries in
pg_db_role_setting.setconfig are parsed with ParseLongOption, which quoth:

/*
* A little "long argument" simulation, although not quite GNU
* compliant. Takes a string of the form "some-option=some value" and
* returns name = "some_option" and value = "some value" in malloc'ed
* storage. Note that '-' is converted to '_' in the option name. If
* there is no '=' in the input string then value will be NULL.
*/

Sure enough,

regression=> show custom."bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"
regression=> show custom."bad_guc";
custom.bad_guc
----------------
1a
(1 row)

So that's where the setting went.

There's a second problem here with arbitrary GUC names, which is that
a name containing '=' isn't exactly gonna do what you want either.

There are probably other places that are not terribly careful about
funny characters in GUC names. In a quick test, I see that pg_dumpall
seems to dump the ALTER USER SET safely, but I wouldn't want to bet
that everything else copes.

I think we should probably sanitize custom GUC names at least to the
extent of forbidding '=' and '-'. Maybe we should go further and
insist they look like regular identifiers.

(Fortunately, ALTER USER SET with a custom GUC is superuser-only,
so there's no need to worry about security issues here. But we
should eliminate surprises.)

regards, tom lane

Sure enough,
regression=> show custom."bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"
regression=> show custom."bad_guc";
custom.bad_guc
----------------
1a
(1 row)
So that's where the setting went.

Oh, that's interesting. Unfortuantley it can also lead to problems:
alter user depesz set custom.bad_guc='2b';
$ select * from pg_db_role_setting where setrole = 'depesz'::regrole;
setdatabase │ setrole │ setconfig
─────────────┼─────────┼───────────────────────────────────────────────────────────────────────────
0 │ 16384 │ {application_name=xxx,custom.guc=123,custom.bad-guc=1a,custom.bad_guc=2b}
(1 row)

And now I can get:
$ show custom."bad_guc";
custom.bad_guc
────────────────
2b
(1 row)

But the bad-guc is no longer available.

(Fortunately, ALTER USER SET with a custom GUC is superuser-only,
so there's no need to worry about security issues here. But we
should eliminate surprises.)

Yeah. Realistically I wouldn't use variable names with - in them, but some people clearly are trying.

Thanks, and best regards,

depesz

On 2/9/21 9:00 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 2/9/21 7:45 AM, hubert depesz lubaczewski wrote:

but I find it curious that I can set the guc using normal-ish SET, but
can't get it using SHOW or even select current_setting()

Yeah, I think that part is a bug report.

After digging around in the code, I find the reason is that the entries in
pg_db_role_setting.setconfig are parsed with ParseLongOption, which quoth:

/*
* A little "long argument" simulation, although not quite GNU
* compliant. Takes a string of the form "some-option=some value" and
* returns name = "some_option" and value = "some value" in malloc'ed
* storage. Note that '-' is converted to '_' in the option name. If
* there is no '=' in the input string then value will be NULL.
*/

Sure enough,

regression=> show custom."bad-guc";
ERROR: unrecognized configuration parameter "custom.bad-guc"
regression=> show custom."bad_guc";
custom.bad_guc
----------------
1a
(1 row)

So that's where the setting went.

There's a second problem here with arbitrary GUC names, which is that
a name containing '=' isn't exactly gonna do what you want either.

There are probably other places that are not terribly careful about
funny characters in GUC names. In a quick test, I see that pg_dumpall
seems to dump the ALTER USER SET safely, but I wouldn't want to bet
that everything else copes.

I think we should probably sanitize custom GUC names at least to the
extent of forbidding '=' and '-'. Maybe we should go further and
insist they look like regular identifiers.

(Fortunately, ALTER USER SET with a custom GUC is superuser-only,
so there's no need to worry about security issues here. But we
should eliminate surprises.)

Hmm, further food for thought:

test(5432)=# alter user aklaver reset custom."bad_guc" ;
ALTER ROLE

test(5432)=# select setconfig from pg_db_role_setting where setrole =
'aklaver'::regrole;
setconfig
---------------------
{custom.bad-guc=1a}
(1 row)

test(5432)=# alter user aklaver reset custom."bad-guc" ;
ALTER ROLE

test(5432)=# select setconfig from pg_db_role_setting where setrole =
'aklaver'::regrole;
setconfig
-----------
(0 rows)

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com