Re: has_table_privilege for a table in unprivileged schema causesan error - Mailing list pgsql-hackers
From | Yugo Nagata |
---|---|
Subject | Re: has_table_privilege for a table in unprivileged schema causesan error |
Date | |
Msg-id | 20180827183209.0a19e6b4ca4467a2ce3a6264@sraoss.co.jp Whole thread Raw |
In response to | Re: has_table_privilege for a table in unprivileged schema causes an error (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: has_table_privilege for a table in unprivileged schema causes an error
|
List | pgsql-hackers |
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>
pgsql-hackers by date: