Re: has_table_privilege for a table in unprivileged schema causes an error

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I'm not sure that it's a good idea to change this behavior.

> In the case of an unqualified name, the permissions on the schemas in
> the search path can affect which table is chosen in the first place.
> ... So I think this only matters for qualified names.

Yeah, that agrees with my expectations.

> Also, the current system generally tries not to reveal any information
> about the contents of schemas for which you have no permissions.

I don't think that argument holds up, at least not if this is implemented
the way I'd expect. It would change the results for a schema on which
you lack usage permission from "permission denied for schema a" to
"false", but it still would not matter whether there is such a table
inside "a".

> And if you've got a qualified name, you know what schema it's in. If
> you are concerned about a.b, nothing keeps you from writing a test
> against schema a's permissions as well as a test against table a.b's
> permissions. But on the other hand, if for some reason you want to
> know about pg_class.relacl specifically, then having the function
> consider both that and the schema's ACL could be awkward.

Mmm ... maybe, but I don't think that exactly holds water either, given
that the current behavior is to fail if you lack permission on schema a.
Yes, you can write "case when has_schema_privilege() then
has_table_privilege() else false end", but if you're worried that you
might possibly lack schema privilege, then the current behavior of
has_table_privilege is useless to you: it doesn't matter whether or not
you would like to know about pg_class.relacl specifically, because you
won't be allowed to find out.

Also, in some use-cases the extra test would require writing code that can
split a qualified name into pieces, which isn't really all that easy in
SQL.

There's a backwards-compatibility argument for not changing this behavior,
sure, but I don't buy the other arguments you made here. And I don't
think there's all that much to the backwards-compatibility argument,
considering that the current behavior is to fail.

regards, tom lane