Postgres Pro
Downloads
Home   >   mailing lists

Thread: Does has_table_privilege() have a case bug

Does has_table_privilege() have a case bug

From
johnf
Date:
I create a table named "Account_Text_Table" the owner is 'johnf'.

select has_table_privilege('johnf', 'public.Account_Text_Table', 'SELECT')
I get the following error:

ERROR:  relation "public.account_text_table" does not exist

SUSE 10.3 Postgres 8.1.9
--
John Fabiani

Re: Does has_table_privilege() have a case bug

From
Jeff Davis
Date:
On Tue, 2008-02-05 at 16:10 -0800, johnf wrote:
> I create a table named "Account_Text_Table" the owner is 'johnf'.
>
> select has_table_privilege('johnf', 'public.Account_Text_Table', 'SELECT')
> I get the following error:
>
> ERROR:  relation "public.account_text_table" does not exist

PostgreSQL folds to lower case unless you put the name in double-quotes.
Try putting the table name in double-quotes.

E.g.: select has_table_privilege('johnf', 'public."Account_Text_Table"',
'SELECT')

Regards,
    Jeff Davis

Re: Does has_table_privilege() have a case bug

From
brian
Date:
Jeff Davis wrote:
> On Tue, 2008-02-05 at 16:10 -0800, johnf wrote:
>> I create a table named "Account_Text_Table" the owner is 'johnf'.
>>
>> select has_table_privilege('johnf', 'public.Account_Text_Table', 'SELECT')
>> I get the following error:
>>
>> ERROR:  relation "public.account_text_table" does not exist
>
> PostgreSQL folds to lower case unless you put the name in double-quotes.
> Try putting the table name in double-quotes.
>
> E.g.: select has_table_privilege('johnf', 'public."Account_Text_Table"',
> 'SELECT')
>

That won't help if the table was not created with a quoted name.

test=# CREATE TABLE Account_Text_Table (foo CHAR(1));
CREATE TABLE

test=# SELECT * FROM Account_Text_Table;
  foo
-----
(0 rows)

test=# SELECT * FROM "Account_Text_Table";
ERROR:  relation "Account_Text_Table" does not exist

test=# DROP TABLE account_text_table;
DROP TABLE

test=# CREATE TABLE "Account_Text_Table" (foo CHAR(1));
CREATE TABLE

test=# SELECT * FROM Account_Text_Table;
ERROR:  relation "account_text_table" does not exist

test=# SELECT * FROM "Account_Text_Table";
  foo
-----
(0 rows)

So, you'll need to quote the table name when you create it. But this
means that you'll *always* have to quote references to it.

Easiest to stick with lowercase, IMO.

b

Re: Does has_table_privilege() have a case bug

From
johnf
Date:
On Tuesday 05 February 2008 04:40:18 pm brian wrote:
> Jeff Davis wrote:
> > On Tue, 2008-02-05 at 16:10 -0800, johnf wrote:
> >> I create a table named "Account_Text_Table" the owner is 'johnf'.
> >>
> >> select has_table_privilege('johnf', 'public.Account_Text_Table',
> >> 'SELECT') I get the following error:
> >>
> >> ERROR:  relation "public.account_text_table" does not exist
> >
> > PostgreSQL folds to lower case unless you put the name in double-quotes.
> > Try putting the table name in double-quotes.
> >
> > E.g.: select has_table_privilege('johnf', 'public."Account_Text_Table"',
> > 'SELECT')
>
> That won't help if the table was not created with a quoted name.
>
> test=# CREATE TABLE Account_Text_Table (foo CHAR(1));
> CREATE TABLE
>
> test=# SELECT * FROM Account_Text_Table;
>   foo
> -----
> (0 rows)
>
> test=# SELECT * FROM "Account_Text_Table";
> ERROR:  relation "Account_Text_Table" does not exist
>
> test=# DROP TABLE account_text_table;
> DROP TABLE
>
> test=# CREATE TABLE "Account_Text_Table" (foo CHAR(1));
> CREATE TABLE
>
> test=# SELECT * FROM Account_Text_Table;
> ERROR:  relation "account_text_table" does not exist
>
> test=# SELECT * FROM "Account_Text_Table";
>   foo
> -----
> (0 rows)
>
> So, you'll need to quote the table name when you create it. But this
> means that you'll *always* have to quote references to it.
>
> Easiest to stick with lowercase, IMO.
>
> b
>
Thanks guys!


--
John Fabiani