Thread: [BUGS] BUG #14662: 'has_table_privilege()' function fails with error,"invalid name syntax" when using Japanese symbols

The following bug has been logged on the website:

Bug reference:      14662
Logged by:          Justin Muise
Email address:      jmuise@esri.com
PostgreSQL version: 9.5.3
Operating system:   Windows 8.1
Description:

SYNOPSIS:
The 'has_table_privilege()' function will return the error, "invalid name
syntax", when the table used in the function contains certain Japanese
characters. 

POSTGRESQL SPECIFIC INFORMATION:
Version:
"PostgreSQL 9.5.3, compiled by Visual C++ build 1800, 64-bit"

Special Installation requirements:
N/A

Special startup parameters:
N/A

OPERATING SYSTEM INFORMATION:
OS: Windows 8.1 Enterprise
Processor: Intel® Xeon® CPU E5-1620 v3 @ 3.50GHz
System type: 64-bit Operating System, x64-based processor

STEPS TO REPRODUCE:
1) Running the following SQL as the postgres user will cause the reported
error:
=====
create user viewer2;

create table land_場地
(id    integer,
title    varchar(40));

grant SELECT on table land_場地 to viewer2;

SELECT has_table_privilege('viewer2', 'land_場地', 'select');
=====

2) The full error reported is as follows:
=====
ERROR:  invalid name syntax

********** Error **********

ERROR: invalid name syntax
SQL state: 42602
=====

3) I would expect the above to work similar to the example below, which
simply contains different Japanese characters in the table name. Running the
following SQL will complete successfully:
====
create user viewer1;

create table lodging_宿泊
(id    integer,
title    varchar(40));

grant SELECT on table lodging_宿泊 to viewer1;

SELECT has_table_privilege('viewer1', 'lodging_宿泊', 'select');
====

4) The result is as follow:
====
t
====



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

jmuise@esri.com writes:
> The 'has_table_privilege()' function will return the error, "invalid name
> syntax", when the table used in the function contains certain Japanese
> characters. 

What character encoding are you using?  I'm suspicious that the underlying
problem is use of a non-ASCII-safe encoding (eg SJIS) on the client side,
combined with improper encoding conversion due to inaccurate
client_encoding and/or server_encoding settings.

FWIW, I can't reproduce any problem with your script here; but the
script arrived in what looks to be UTF8 encoding, and I suspect that
must not be what you were using when you hit the problem.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Tom,

Thank you for your quick response! Please see the attached image.

Regards,

Justin

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 19, 2017 10:03 AM
To: Justin Muise <JMuise@esri.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14662: 'has_table_privilege()' function fails with error, "invalid name syntax" when using
Japanesesymbols 

jmuise@esri.com writes:
> The 'has_table_privilege()' function will return the error, "invalid
> name syntax", when the table used in the function contains certain
> Japanese characters.

What character encoding are you using?  I'm suspicious that the underlying problem is use of a non-ASCII-safe encoding
(egSJIS) on the client side, combined with improper encoding conversion due to inaccurate client_encoding and/or
server_encodingsettings. 

FWIW, I can't reproduce any problem with your script here; but the script arrived in what looks to be UTF8 encoding,
andI suspect that must not be what you were using when you hit the problem. 

            regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Justin Muise <JMuise@esri.com> writes:
> Thank you for your quick response! Please see the attached image.

Hm.  If I'm reading that correctly, you've got encoding set to UTF8
but the OS is going to think the encoding is WIN1252, which will likely
affect the behavior of isspace(), which'd successfully break this
function.  I'm not sure how the system allowed you to do that.

I wonder though whether it wouldn't be better for SplitIdentifierString
to use scanner_isspace() rather than isspace().  That would cause it
to not treat things like   as whitespace, but maybe that's OK.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Tom,

Just wanted to see if here was any additional information/testing you required from me, and if you are still reviewing
thisissue. 

Thanks,

Justin

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 19, 2017 10:44 AM
To: Justin Muise <JMuise@esri.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14662: 'has_table_privilege()' function fails with error, "invalid name syntax" when using
Japanesesymbols 

Justin Muise <JMuise@esri.com> writes:
> Thank you for your quick response! Please see the attached image.

Hm.  If I'm reading that correctly, you've got encoding set to UTF8 but the OS is going to think the encoding is
WIN1252,which will likely affect the behavior of isspace(), which'd successfully break this function.  I'm not sure how
thesystem allowed you to do that. 

I wonder though whether it wouldn't be better for SplitIdentifierString to use scanner_isspace() rather than isspace().
That would cause it to not treat things like   as whitespace, but maybe that's OK. 
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Justin Muise <JMuise@esri.com> writes:
> Just wanted to see if here was any additional information/testing you required from me, and if you are still
reviewingthis issue.
 

I was just waiting for comments on the proposed patch.

https://www.postgresql.org/message-id/flat/10129.1495302480%40sss.pgh.pa.us
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thanks Tom! I really appreciate the update and the effort on this issue.

Regards,

Justin

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, May 23, 2017 8:21 AM
To: Justin Muise <JMuise@esri.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14662: 'has_table_privilege()' function fails with error, "invalid name syntax" when using
Japanesesymbols 

Justin Muise <JMuise@esri.com> writes:
> Just wanted to see if here was any additional information/testing you required from me, and if you are still
reviewingthis issue. 

I was just waiting for comments on the proposed patch.


https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_message-2Did_flat_10129.1495302480-2540sss.pgh.pa.us&d=DwIBAg&c=n6-cguzQvX_tUIrZOS_4Og&r=DSmvHwg5rR1RV-2-kBtDyA&m=woa_dhFZd0A_phCQUdw0OQj02qstiO0DJhR0tjgyQqQ&s=UlzkV2VpEn2V4_adMOi5yEUmPBE7q9uCfDm4sGuCEaE&e=

        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Tom,

Just looking for an update on this issue. Am I correct in understanding that this issue will be resolved in a future
patch/release?


https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_message-2Did_flat_10129.1495302480-2540sss.pgh.pa.us-2310129.1495302480-40sss.pgh.pa.us&d=DwIFAg&c=n6-cguzQvX_tUIrZOS_4Og&r=_FEeQAnA9FRXs10hnoeUZCSje-rIbSXKER9SaYW7nt8&m=Z7UwyKizlmjsShmG_K31KN_p7CLdalvAO6dXQbI9zRs&s=FYHImdhzg71hcEuj7mpG5w_xvDqtznO5dFoDfczRGUU&e=


Thanks again for all your support with this!

Regards,
Justin

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, May 23, 2017 8:21 AM
To: Justin Muise <JMuise@esri.com>
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #14662: 'has_table_privilege()' function fails with error, "invalid name syntax" when using
Japanesesymbols 

Justin Muise <JMuise@esri.com> writes:
> Just wanted to see if here was any additional information/testing you required from me, and if you are still
reviewingthis issue. 

I was just waiting for comments on the proposed patch.


https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_message-2Did_flat_10129.1495302480-2540sss.pgh.pa.us&d=DwIBAg&c=n6-cguzQvX_tUIrZOS_4Og&r=DSmvHwg5rR1RV-2-kBtDyA&m=woa_dhFZd0A_phCQUdw0OQj02qstiO0DJhR0tjgyQqQ&s=UlzkV2VpEn2V4_adMOi5yEUmPBE7q9uCfDm4sGuCEaE&e=

        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs