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

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

From

hubert depesz lubaczewski

Date:

09 February 2021, 17:40:59

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?

depesz

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

From

Edward Macnaghten

Date:

09 February 2021, 17:49:19

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

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

From

hubert depesz lubaczewski

Date:

09 February 2021, 18:10:24

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

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

From

Adrian Klaver

Date:

09 February 2021, 18:41:02

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

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

From

hubert depesz lubaczewski

Date:

09 February 2021, 18:45:03

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

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

From

Adrian Klaver

Date:

09 February 2021, 18:50:23

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

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

From

Tom Lane

Date:

09 February 2021, 20:00:42

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

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

From

hubert depesz lubaczewski

Date:

09 February 2021, 20:24:53

> 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

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

From

Adrian Klaver

Date:

09 February 2021, 20:58:15

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