Re: has_language_privilege returns incorrect answer for non-superuser

On 07/10/2012 12:50 AM, Magnus Hagander wrote:
> On Tue, Jul 10, 2012 at 3:23 AM, Joe Conway <mail(at)joeconway(dot)com> wrote:
>> I verified this behavior on head as well as 9.1 (didn't bother looking
>> any further back). Looks like the reason is that CreateFunction()
>> correctly checks lanpltrusted, whereas pg_language_aclmask() does not.
>>
>> Seems like a bug to me -- opinions?
>
> Definitely seems like a bug to me, yes.
>
> And while I haven't verified that the suggested fix actually fixes it
> for me, it sounds reasonable :)

I realized there is a somewhat analogous situation with schema objects
and schema USAGE permission. I.e. I find this understandable but surprising:

8<----------------------------------
test1=> \c - postgres
You are now connected to database "test1" as user "postgres".
test1=# select has_table_privilege('nobody','sf.foo','select');
has_table_privilege
---------------------
f
(1 row)

test1=# grant select on table sf.foo to nobody;
GRANT
test1=# select has_table_privilege('nobody','sf.foo','select');
has_table_privilege
---------------------
t
(1 row)

test1=# \c - nobody
You are now connected to database "test1" as user "nobody".
test1=> select * from sf.foo;
ERROR: permission denied for schema sf
LINE 1: select * from sf.foo;
8<----------------------------------

So I think this boils down to what we think the output of the various
has_*_privilege() functions *should* tell you:

1) privileges possessed even though they may not
be usable
-or-
2) privileges possessed and usable

Personally I'm interested in answering the latter question -- what are
all the things role X can do and see.

But historically (and perhaps correctly) these functions have always
done the former -- so maybe all we need are some words of warning in the
documentation of these functions?

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support