Inconsistent syntax in GRANT

--- Josh Berkus <josh@agliodbs.com> escreveu:

However,
GRANT SELECT ON TABLE table1 TO someuser;
... works, while ....
GRANT SELECT ON SEQUENCE table1_id_seq TO someuser;
... raises an error.

This is inconsistent. Do people agree with me that the parser
should
accept "SEQUENCE" there, since the optional object name works for all

other objects? Is there some technical reason this is difficult to
do?

It should but it's not implemented yet. There is no difficulty in doing
it. But I want to propose the following idea: if some object depends on
another object and its type is 'DEPENDENCY_INTERNAL' we could
grant/revoke privileges automagically to it. Or maybe create another
type of dependency to do so.
Comments?

Euler Taveira de Oliveira
euler[at]yahoo_com_br

_______________________________________________________
Yahoo! doce lar. Fa�a do Yahoo! sua homepage.
http://br.yahoo.com/homepageset.html

Euler,

It should but it's not implemented yet. There is no difficulty in doing
it. But I want to propose the following idea: if some object depends on
another object and its type is 'DEPENDENCY_INTERNAL' we could
grant/revoke privileges automagically to it. Or maybe create another
type of dependency to do so.
Comments?

I think this would be difficult to work out. Personally, the only
clear-cut case I can think of is SERIAL columns; other dependancies would
require a lot of conditional logic.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

Euler,

It should but it's not implemented yet. There is no difficulty in doing
it. But I want to propose the following idea: if some object depends on
another object and its type is 'DEPENDENCY_INTERNAL' we could
grant/revoke privileges automagically to it. Or maybe create another
type of dependency to do so.
Comments?

I think this would be difficult to work out. Personally, the only
clear-cut case I can think of is SERIAL columns; other dependancies would
require a lot of conditional logic.

Addded to TODO:

* Allow SERIAL sequences to inherit permissions from the base table?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Josh Berkus wrote:

Folks,

Just got tripped up by this:

GRANT SELECT ON table1 TO someuser;
GRANT SELECT ON table1_id_seq TO someuser;
.... both work

However,
GRANT SELECT ON TABLE table1 TO someuser;
... works, while ....
GRANT SELECT ON SEQUENCE table1_id_seq TO someuser;
... raises an error.

This is inconsistent. Do people agree with me that the parser should
accept "SEQUENCE" there, since the optional object name works for all
other objects? Is there some technical reason this is difficult to do?

The following patch allows VIEW and SEQUENCE for GRANT. I didn't add
checks for relkind, figuring it wasn't worth it, right?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The following patch allows VIEW and SEQUENCE for GRANT. I didn't add
checks for relkind, figuring it wasn't worth it, right?

The permissions for a sequence aren't the same as they are for a table.
We've sort of ignored the point to date, but if we're going to add
special syntax for granting on a sequence, I don't think we should
continue to ignore it.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The following patch allows VIEW and SEQUENCE for GRANT. I didn't add
checks for relkind, figuring it wasn't worth it, right?

The permissions for a sequence aren't the same as they are for a table.
We've sort of ignored the point to date, but if we're going to add
special syntax for granting on a sequence, I don't think we should
continue to ignore it.

Uh, how are they different? You mean just UPDATE and none of the
others do anything?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce, Tom,

The permissions for a sequence aren't the same as they are for a
table. We've sort of ignored the point to date, but if we're going to
add special syntax for granting on a sequence, I don't think we should
continue to ignore it.

Uh, how are they different? You mean just UPDATE and none of the
others do anything?

Yes, it would be nice to have real permissions for sequences, specifically
USE (which allows nextval() and currval()) and UPDATE (which would allow
setval() ). However, I don't know that the added functionality would
justify breaking backwards-compatibility.

Oh, and Bruce, I can't imagine needing specific relkind so I think that
part's fine.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian wrote:

The following patch allows VIEW and SEQUENCE for GRANT. I didn't add
checks for relkind, figuring it wasn't worth it, right?

I think checking the relkind is pretty reasonable, and should require
only a few lines of code -- why not do it?

-Neil

Josh Berkus <josh@agliodbs.com> writes:

Uh, how are they different? You mean just UPDATE and none of the
others do anything?

Yes, it would be nice to have real permissions for sequences, specifically
USE (which allows nextval() and currval()) and UPDATE (which would allow
setval() ). However, I don't know that the added functionality would
justify breaking backwards-compatibility.

We could maintain backwards compatibility by continuing to accept the
old equivalences when you say GRANT ON TABLE. But when you say GRANT ON
SEQUENCE, I think it should use sequence-specific privilege keywords,
and not allow the privileges that don't mean anything for sequences,
like DELETE.

I'm not sure offhand what keywords we'd want to use, but now is the time
to look at it, *before* it becomes set in stone that GRANT ON SEQUENCE
is just another spelling of GRANT ON TABLE.

(The subtext of this is that I don't have a lot of use for allowing
variant syntaxes that don't actually do anything different ...)

regards, tom lane

On Thu, Jan 05, 2006 at 11:44:24 -0800,
Josh Berkus <josh@agliodbs.com> wrote:

Bruce, Tom,

The permissions for a sequence aren't the same as they are for a
table. We've sort of ignored the point to date, but if we're going to
add special syntax for granting on a sequence, I don't think we should
continue to ignore it.

Uh, how are they different? You mean just UPDATE and none of the
others do anything?

Yes, it would be nice to have real permissions for sequences, specifically
USE (which allows nextval() and currval()) and UPDATE (which would allow
setval() ). However, I don't know that the added functionality would
justify breaking backwards-compatibility.

It might be nice to split nextval and currval access as well. nextval access
corresponds to INSERT and currval access to SELECT.

Bruno Wolff III wrote:

On Thu, Jan 05, 2006 at 11:44:24 -0800,
Josh Berkus <josh@agliodbs.com> wrote:

Bruce, Tom,

The permissions for a sequence aren't the same as they are for a
table. We've sort of ignored the point to date, but if we're going to
add special syntax for granting on a sequence, I don't think we should
continue to ignore it.

Uh, how are they different? You mean just UPDATE and none of the
others do anything?

Yes, it would be nice to have real permissions for sequences, specifically
USE (which allows nextval() and currval()) and UPDATE (which would allow
setval() ). However, I don't know that the added functionality would
justify breaking backwards-compatibility.

It might be nice to split nextval and currval access as well. nextval access
corresponds to INSERT and currval access to SELECT.

Uh, that is already in the code. nextval()/setval() is UPDATE, and
currval() is SELECT.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On 1/6/06, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Bruno Wolff III wrote:

It might be nice to split nextval and currval access as well. nextval access
corresponds to INSERT and currval access to SELECT.

Uh, that is already in the code. nextval()/setval() is UPDATE, and
currval() is SELECT.

This seems weird. Shouldn't nextval/currval go together and setval
separately?

Considering there's no currval() without nextval(), what point
is disallowing currval() when user is able to call nextval()?

I rather want to allow nextval/currval and disable setval as it
allows regular user to DoS the database.

--
marko

[removing Tom from CC as he bounces gmail]

Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

Uh, how are they different? You mean just UPDATE and none of the
others do anything?

Yes, it would be nice to have real permissions for sequences, specifically
USE (which allows nextval() and currval()) and UPDATE (which would allow
setval() ). However, I don't know that the added functionality would
justify breaking backwards-compatibility.

We could maintain backwards compatibility by continuing to accept the
old equivalences when you say GRANT ON TABLE. But when you say GRANT ON
SEQUENCE, I think it should use sequence-specific privilege keywords,
and not allow the privileges that don't mean anything for sequences,
like DELETE.

OK.

I'm not sure offhand what keywords we'd want to use, but now is the time
to look at it, *before* it becomes set in stone that GRANT ON SEQUENCE
is just another spelling of GRANT ON TABLE.

Sequences do not support INSERT, UPDATE, or DELETE, but we overload
UPDATE to control nextval()/setval(), so I just allowed SELECT and
UPDATE. I am not sure it makes any sense to allow rules, references,
and triggers on sequences. However, using ALL or TABLE keywords you can
define those permissions to a sequence.

(The subtext of this is that I don't have a lot of use for allowing
variant syntaxes that don't actually do anything different ...)

FYI, SQL03 defines GRANT SEQUENCE.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

FYI, SQL03 defines GRANT SEQUENCE.

Oh. Well, then that gives us precedent to go by. What do they specify
as the privileges for sequences?

regards, tom lane

Marko Kreen wrote:

On 1/6/06, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Bruno Wolff III wrote:

It might be nice to split nextval and currval access as well. nextval access
corresponds to INSERT and currval access to SELECT.

Uh, that is already in the code. nextval()/setval() is UPDATE, and
currval() is SELECT.

This seems weird. Shouldn't nextval/currval go together and setval
separately?

Uh, logically, yes, but practially currval just reads/SELECTs, while
nextval modifies/UPDATEs.

Considering there's no currval() without nextval(), what point
is disallowing currval() when user is able to call nextval()?

Not sure. I think SET SESSION AUTHORIZATION would make it possible.

I rather want to allow nextval/currval and disable setval as it
allows regular user to DoS the database.

Oh, interesting. We could easily have INSERT control that if we wanted,
but I think you have to make a clear use case to override the risk of
breaking applications.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

FYI, SQL03 defines GRANT SEQUENCE.

Oh. Well, then that gives us precedent to go by. What do they specify
as the privileges for sequences?

They don't seem to specify which actions go with which objects in the
GRANT statement, nor do they specify what permissions should control the
nextval-style statements. Seems like something they should have
specified.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On 1/6/06, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Marko Kreen wrote:

On 1/6/06, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Bruno Wolff III wrote:

It might be nice to split nextval and currval access as well. nextval access
corresponds to INSERT and currval access to SELECT.

Uh, that is already in the code. nextval()/setval() is UPDATE, and
currval() is SELECT.

This seems weird. Shouldn't nextval/currval go together and setval
separately?

Uh, logically, yes, but practially currval just reads/SELECTs, while
nextval modifies/UPDATEs.

Yeah, thats the mechanics behind it, but the currval() only
works if the user was already able to call nextval(), so I see
no point in separating them.

In other words: there is nothing to do with only access to currval(),
and with access to nextval() but not to currval() user loses only
in convinience.

Considering there's no currval() without nextval(), what point
is disallowing currval() when user is able to call nextval()?

Not sure. I think SET SESSION AUTHORIZATION would make it possible.

/me confused, looks at docs...

Huh? I really hope you are mistaken. This would mean the sequence
state for currval() is kept per-user not per-backend. This would
make impossible to make several connections as same user. Is Postgres
really that broken?

I rather want to allow nextval/currval and disable setval as it
allows regular user to DoS the database.

Oh, interesting. We could easily have INSERT control that if we wanted,
but I think you have to make a clear use case to override the risk of
breaking applications.

I'd turn it around: is there any use-case for setval() for regular user?
IMHO it's a admin-level operation, dangerous, and not needed for regular
work.

--
marko

Marko Kreen <markokr@gmail.com> writes:

On 1/6/06, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Uh, logically, yes, but practially currval just reads/SELECTs, while
nextval modifies/UPDATEs.

Yeah, thats the mechanics behind it, but the currval() only
works if the user was already able to call nextval(), so I see
no point in separating them.

You are completely wrong on this, because not all the code in a session
necessarily executes at the same privilege level. For instance, the
nextval() might be executed inside a SECURITY DEFINER function. It
might be reasonable to give code outside that function the right to see
what had been assigned (by executing currval()) without also saying that
it could do further nextvals().

I do agree that it would be a good idea to support a privilege
distinction between nextval() and setval().

Oh, interesting. We could easily have INSERT control that if we wanted,
but I think you have to make a clear use case to override the risk of
breaking applications.

There is no backwards-compatibility risk, because we'd still have the
old GRANT ON TABLE syntax grant both underlying rights. You'd have to
use the new syntax to get to a state where you had nextval but not
setval privilege or vice versa.

regards, tom lane

On Fri, Jan 06, 2006 at 19:11:27 +0200,
Marko Kreen <markokr@gmail.com> wrote:

On 1/6/06, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Considering there's no currval() without nextval(), what point
is disallowing currval() when user is able to call nextval()?

I rather want to allow nextval/currval and disable setval as it
allows regular user to DoS the database.

What I was thinking with this, is that you might allow someone the ability
to insert records into a table which would make use of nextval, but not
allow them to run nextval directly. But after inserting a record allow them
to use currval to see what value was assigned.
People could still mess with things by doing INSERTs and aborting the
transaction, so this may not be the best example for why you would want this.