Re: sequence privileges in information schema

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> We currently don't represent the sequence privileges in the information
> schema. We could perhaps do a subset of them.

> In the SQL standard, there is only the USAGE privilege, and its only
> purpose (AFAICT) is to allow the NEXT VALUE FOR expression.
> PostgreSQL's nextval(), by contrast, requires both USAGE and UPDATE
> privileges.

> So with regard to the information schema, we could

> a) show USAGE privileges as is (and perhaps SELECT and UPDATE as is, as
> well)

> b) show USAGE privileges only if UPDATE is also granted

> c) show nothing, avoiding the whole issue.

> Any ideas?

A1 (show only USAGE) doesn't seem particularly helpful, as it provides
information that's entirely inadequate, and furthermore an app might
reasonably expect that seeing USAGE there means it's got the ability to
do nextval(). On the other hand, nextval() isn't exactly NEXT VALUE
FOR, so it's not like anyone is going to drop exactly-SQL-spec code in
here and have it work without changes.

On balance I think I'd vote for A2, that is show all privileges as-is.
Option B could be argued to be a shade more standards compliant, but
I can't help thinking that showing something other than the real
underlying privileges would come back to bite us in the long run.

If we want to be more nearly standards compliant here, I think the
answer is to adjust the privilege check, not do some weird
information-losing mapping in the view. (Note: I'm not proposing such
an adjustment now. If we get around to doing exactly-compliant NEXT
VALUE FOR, that'd be the time to think about it, I think.)

regards, tom lane