Re: has_table_privilege for a table in unprivileged schema causesan error

On Sat, 25 Aug 2018 23:29:27 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.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.

Yes. I consider only the cases of qualified names and the patch doesn't 
change any behavior about unqualified name cases.

> > 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".

Yes, Tom's explanation is right. The proposal functions doesn't reveal
any information about the contents of unprivileged schemas, either.

> 
> > 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.

This is a reason why we proposed to fix the function. However, with regard to
splitting a qualified name, making a new SQL function to do this might resolve
it, for example, as below.
 
 select case when has_schema_privilege(x.nspname) 
   then has_table_privilege(x.objname)
   else false end
  from pg_split_qualified_name(tablename) x;

> 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.

With regarding to keeping the backwards-compatibility, to add a new paramater
to has_*_privilege functions is a solution as proposed previously.

 has_table_privilege(user, table, privilege[, consider_schema=false]) 

How do you think this proposal?

Regards,

-- 
Yugo Nagata <nagata@sraoss.co.jp>