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

On Fri, Aug 24, 2018 at 5:31 AM, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
> Although the behavior of the original function would reflect pg_class.relacl, it seems to
> me that the function fixed in my patch is more useful for users because this reflects
> the actual accessibility during query execution.

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.
For instance, suppose bob has search_path = a, b and has usage
permission on b but not on a. In that case, if both a.x and b.x
exist, bob's reference to x will latch onto b.x, ignoring a.x
completely. So, if I'm not mistaken, this change will never make any
difference for an unqualified name, because if you don't have usage
permission on the schema, you'll never decide that an unqualified name
references something in that schema in the first place. So I think
this only matters for qualified names.

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.

Also, the current system generally tries not to reveal any information
about the contents of schemas for which you have no permissions. For
instance, suppose there is a table a.x:

rhaas=> select has_table_privilege('a.x', 'select');
ERROR: permission denied for schema a
rhaas=> select has_table_privilege('a.nonexistent', 'select');
ERROR: permission denied for schema a

With this change, you could potentially learn something about which
tables exist in a schema to which you have no access. You could argue
that this is harmless; after all, right now, an unprivileged user can
run 'select * from pg_class', so the jig is up anyway. But that might
be something upon which we'd like to improve someday.

I admit that these are only mild disadvantages, but I think we should
reject breaking backward compatibility unless the result is a clear
improvement, and if anything this seems slightly worse to me. YMMV,
of course....

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company