Re: [GENERAL] Surprising syntax error

The statement:

revoke all on view internal.all_objects from public;

yields a syntax error. The docs show that the word "view" is not
acceptable in this statement which is fine but the surprising thing is
that:

 revoke all on table internal.all_objects from public;

works fine even though all_objects is a view and not a table.

Now that I know about it, this doesn't bother me but it was a surprise
and I wonder whether the the parser/planner/whatever should be a bit
smarter about allowing the word table to apply to non-tables, and
whether the word view ought to be allowed.

__
Marc

Marc Munro wrote:
-- Start of PGP signed section.
> The statement:
>
> revoke all on view internal.all_objects from public;
>
> yields a syntax error. The docs show that the word "view" is not
> acceptable in this statement which is fine but the surprising thing is
> that:
>
> ? revoke all on table internal.all_objects from public;
>
> works fine even though all_objects is a view and not a table.
>
> Now that I know about it, this doesn't bother me but it was a surprise
> and I wonder whether the the parser/planner/whatever should be a bit
> smarter about allowing the word table to apply to non-tables, and
> whether the word view ought to be allowed.

Yes, I can confirm this behavior on CVS HEAD, and it is odd:

test=> CREATE SCHEMA internal;
CREATE SCHEMA
test=> CREATE VIEW internal.all_objects AS SELECT 1;
CREATE VIEW
test=> REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
ERROR: syntax ERROR AT OR near "internal"
LINE 1: REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
^
test=> REVOKE ALL ON TABLE internal.all_objects FROM PUBLIC;
REVOKE

Is there a downside to adding "VIEW" in parser/gram.y:privilege_target?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Added to TODO:

Allow GRANT/REVOKE on views to use the VIEW keyword rather
than just TABLE

http://archives.postgresql.org/pgsql-hackers/2008-06/msg01133.php

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Marc Munro wrote:
> -- Start of PGP signed section.
> > The statement:
> >
> > revoke all on view internal.all_objects from public;
> >
> > yields a syntax error. The docs show that the word "view" is not
> > acceptable in this statement which is fine but the surprising thing is
> > that:
> >
> > ? revoke all on table internal.all_objects from public;
> >
> > works fine even though all_objects is a view and not a table.
> >
> > Now that I know about it, this doesn't bother me but it was a surprise
> > and I wonder whether the the parser/planner/whatever should be a bit
> > smarter about allowing the word table to apply to non-tables, and
> > whether the word view ought to be allowed.
>
> Yes, I can confirm this behavior on CVS HEAD, and it is odd:
>
> test=> CREATE SCHEMA internal;
> CREATE SCHEMA
> test=> CREATE VIEW internal.all_objects AS SELECT 1;
> CREATE VIEW
> test=> REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
> ERROR: syntax ERROR AT OR near "internal"
> LINE 1: REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
> ^
> test=> REVOKE ALL ON TABLE internal.all_objects FROM PUBLIC;
> REVOKE
>
> Is there a downside to adding "VIEW" in parser/gram.y:privilege_target?
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Are we still tracking beginner TODOs separately? I'm thinking this
falls into that category...

Barring objection, I'll mark it as easy.

On Aug 21, 2008, at 5:38 PM, Bruce Momjian wrote:

>
> Added to TODO:
>
> Allow GRANT/REVOKE on views to use the VIEW keyword rather
> than just TABLE
>
> http://archives.postgresql.org/pgsql-hackers/2008-06/msg01133.php
>
>
> ----------------------------------------------------------------------
> -----
>
> Bruce Momjian wrote:
>> Marc Munro wrote:
>> -- Start of PGP signed section.
>>> The statement:
>>>
>>> revoke all on view internal.all_objects from public;
>>>
>>> yields a syntax error. The docs show that the word "view" is not
>>> acceptable in this statement which is fine but the surprising
>>> thing is
>>> that:
>>>
>>> ? revoke all on table internal.all_objects from public;
>>>
>>> works fine even though all_objects is a view and not a table.
>>>
>>> Now that I know about it, this doesn't bother me but it was a
>>> surprise
>>> and I wonder whether the the parser/planner/whatever should be a bit
>>> smarter about allowing the word table to apply to non-tables, and
>>> whether the word view ought to be allowed.
>>
>> Yes, I can confirm this behavior on CVS HEAD, and it is odd:
>>
>> test=> CREATE SCHEMA internal;
>> CREATE SCHEMA
>> test=> CREATE VIEW internal.all_objects AS SELECT 1;
>> CREATE VIEW
>> test=> REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
>> ERROR: syntax ERROR AT OR near "internal"
>> LINE 1: REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
>> ^
>> test=> REVOKE ALL ON TABLE internal.all_objects FROM PUBLIC;
>> REVOKE
>>
>> Is there a downside to adding "VIEW" in parser/
>> gram.y:privilege_target?
>>
>> --
>> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>> EnterpriseDB http://enterprisedb.com
>>
>> + If your life is a hard drive, Christ can be your backup. +
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! wrote:
> Are we still tracking beginner TODOs separately? I'm thinking this
> falls into that category...
>
> Barring objection, I'll mark it as easy.

I already had; markers just don't cust/paste easily like they used to.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

depends if you think hacking the bison grammar is a beginner task.

cheers

andrew

Decibel! wrote:
> Are we still tracking beginner TODOs separately? I'm thinking this
> falls into that category...
>
> Barring objection, I'll mark it as easy.
>
> On Aug 21, 2008, at 5:38 PM, Bruce Momjian wrote:
>
>>
>> Added to TODO:
>>
>> Allow GRANT/REVOKE on views to use the VIEW keyword rather
>> than just TABLE
>>
>> http://archives.postgresql.org/pgsql-hackers/2008-06/msg01133.php
>>
>>
>> ---------------------------------------------------------------------------
>>
>>
>> Bruce Momjian wrote:
>>> Marc Munro wrote:
>>> -- Start of PGP signed section.
>>>> The statement:
>>>>
>>>> revoke all on view internal.all_objects from public;
>>>>
>>>> yields a syntax error. The docs show that the word "view" is not
>>>> acceptable in this statement which is fine but the surprising thing is
>>>> that:
>>>>
>>>> ? revoke all on table internal.all_objects from public;
>>>>
>>>> works fine even though all_objects is a view and not a table.
>>>>
>>>> Now that I know about it, this doesn't bother me but it was a surprise
>>>> and I wonder whether the the parser/planner/whatever should be a bit
>>>> smarter about allowing the word table to apply to non-tables, and
>>>> whether the word view ought to be allowed.
>>>
>>> Yes, I can confirm this behavior on CVS HEAD, and it is odd:
>>>
>>> test=> CREATE SCHEMA internal;
>>> CREATE SCHEMA
>>> test=> CREATE VIEW internal.all_objects AS SELECT 1;
>>> CREATE VIEW
>>> test=> REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
>>> ERROR: syntax ERROR AT OR near "internal"
>>> LINE 1: REVOKE ALL ON VIEW internal.all_objects FROM PUBLIC;
>>> ^
>>> test=> REVOKE ALL ON TABLE internal.all_objects FROM PUBLIC;
>>> REVOKE
>>>
>>> Is there a downside to adding "VIEW" in parser/gram.y:privilege_target?
>>>
>>> -- Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>>> EnterpriseDB http://enterprisedb.com
>>>
>>> + If your life is a hard drive, Christ can be your backup. +
>>>
>>> --Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>> -- Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>> EnterpriseDB http://enterprisedb.com
>>
>> + If your life is a hard drive, Christ can be your backup. +
>>
>> --Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
> --Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>

On Fri, 2008-08-22 at 12:42 -0400, Andrew Dunstan wrote:
> depends if you think hacking the bison grammar is a beginner task.

It may be anything from beginners task to quite complex . Some things
are just copy&paste.

-------------
Hannu

On Tuesday 01 July 2008 01:39:13 Bruce Momjian wrote:
> Is there a downside to adding "VIEW" in parser/gram.y:privilege_target?

The SQL standard doesn't specify it. And there is no need for it.

Peter Eisentraut wrote:
> On Tuesday 01 July 2008 01:39:13 Bruce Momjian wrote:
> > Is there a downside to adding "VIEW" in parser/gram.y:privilege_target?
>
> The SQL standard doesn't specify it. And there is no need for it.

While we don't _need_ it, it would make our system more consistent; we
have made similar changes for views in other areas.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Peter Eisentraut wrote:
>> On Tuesday 01 July 2008 01:39:13 Bruce Momjian wrote:
>>> Is there a downside to adding "VIEW" in parser/gram.y:privilege_target?
>>
>> The SQL standard doesn't specify it. And there is no need for it.

> While we don't _need_ it, it would make our system more consistent; we
> have made similar changes for views in other areas.

I'm not sure it'd make the system more consistent. Because the SQL
standard says you use GRANT ON TABLE for a view. we'd have to always
ensure that we accepted that; whereas in at least some other places
we are trying to be picky about TABLE/VIEW/SEQUENCE actually matching
the object type.

Given the spec precedent, I'm inclined to leave it alone. It's not like
there aren't plenty of other SQL quirks that surprise novices.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Peter Eisentraut wrote:
> >> On Tuesday 01 July 2008 01:39:13 Bruce Momjian wrote:
> >>> Is there a downside to adding "VIEW" in parser/gram.y:privilege_target?
> >>
> >> The SQL standard doesn't specify it. And there is no need for it.
>
> > While we don't _need_ it, it would make our system more consistent; we
> > have made similar changes for views in other areas.
>
> I'm not sure it'd make the system more consistent. Because the SQL
> standard says you use GRANT ON TABLE for a view. we'd have to always
> ensure that we accepted that; whereas in at least some other places
> we are trying to be picky about TABLE/VIEW/SEQUENCE actually matching
> the object type.
>
> Given the spec precedent, I'm inclined to leave it alone. It's not like
> there aren't plenty of other SQL quirks that surprise novices.

OK, removed from TODO.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

>> While we don't _need_ it, it would make our system more consistent; we
>> have made similar changes for views in other areas.
>
> I'm not sure it'd make the system more consistent. Because the SQL
> standard says you use GRANT ON TABLE for a view. we'd have to always
> ensure that we accepted that; whereas in at least some other places
> we are trying to be picky about TABLE/VIEW/SEQUENCE actually matching
> the object type.
>
> Given the spec precedent, I'm inclined to leave it alone. It's not like
> there aren't plenty of other SQL quirks that surprise novices.

I fail to understand why it's advantageous to artificially create
surprising behavior. There are cases where PostgreSQL now accepts
either ALTER VIEW or ALTER TABLE where it previously accepted only the
latter, so the situation is hardly without precedent. I find it
exceedingly unlikely that anyone is relying on GRANT ON VIEW to NOT
work.

...Robert

Robert Haas wrote:
> >> While we don't _need_ it, it would make our system more consistent; we
> >> have made similar changes for views in other areas.
> >
> > I'm not sure it'd make the system more consistent. Because the SQL
> > standard says you use GRANT ON TABLE for a view. we'd have to always
> > ensure that we accepted that; whereas in at least some other places
> > we are trying to be picky about TABLE/VIEW/SEQUENCE actually matching
> > the object type.
> >
> > Given the spec precedent, I'm inclined to leave it alone. It's not like
> > there aren't plenty of other SQL quirks that surprise novices.
>
> I fail to understand why it's advantageous to artificially create
> surprising behavior. There are cases where PostgreSQL now accepts
> either ALTER VIEW or ALTER TABLE where it previously accepted only the
> latter, so the situation is hardly without precedent. I find it
> exceedingly unlikely that anyone is relying on GRANT ON VIEW to NOT
> work.

Yes, I assumed we were following the recent work on ALTER TABLE/VIEW
with GRANT/REVOKE. Peter, Tom, how is GRANT/REVOKE different?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Yes, I assumed we were following the recent work on ALTER TABLE/VIEW
> with GRANT/REVOKE. Peter, Tom, how is GRANT/REVOKE different?

GRANT/REVOKE behavior is specified by the standard, whereas the stuff
we allow under ALTER VIEW is all an extension to the standard --- not
merely syntax-wise, but functionality.

A concrete reason not to do it is that if someone writes GRANT ON VIEW,
their code won't port to other DBs that are following the spec, and
it'll be only because we allowed non-spec syntactic sugar, not because
they're using functionality not covered by the spec.

We routinely complain about mysql inventing nonstandard ways to express
things that have perfectly good spec-compliant equivalents. How would
this be different?

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > Yes, I assumed we were following the recent work on ALTER TABLE/VIEW
> > with GRANT/REVOKE. Peter, Tom, how is GRANT/REVOKE different?
>
> GRANT/REVOKE behavior is specified by the standard, whereas the stuff
> we allow under ALTER VIEW is all an extension to the standard --- not
> merely syntax-wise, but functionality.
>
> A concrete reason not to do it is that if someone writes GRANT ON VIEW,
> their code won't port to other DBs that are following the spec, and
> it'll be only because we allowed non-spec syntactic sugar, not because
> they're using functionality not covered by the spec.
>
> We routinely complain about mysql inventing nonstandard ways to express
> things that have perfectly good spec-compliant equivalents. How would
> this be different?

OK, so this is a standards issue, then, OK, makes sense.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +