Thread: Problem with function permission test in a view
Someone asked me a question about view and function permissions. I assumed all object access done by a view would be based on the permissions on the view, and not the permissions of the objects. While table access done in a view follows this outline, function call access does not. In my tests below, I create a function ftest(), and allow only 'postgres' to execute it. I then create a view and put the function call in the view, and grant permissions on the view to others. However, in the test, you will see that the function call in the view fails. Is this a bug? --------------------------------------------------------------------------- CREATE USER dummyuser;CREATE USERSET SESSION AUTHORIZATION postgres;SETCREATE OR REPLACE FUNCTION ftest() RETURNS INTEGEREXTERNALSECURITY DEFINERAS 'SELECT 1' LANGUAGE 'sql';CREATE FUNCTIONREVOKE EXECUTE ON FUNCTION ftest() FROM PUBLIC;REVOKESELECTftest(); ftest------- 1(1 row)SET SESSION AUTHORIZATION dummyuser;SETSELECT ftest();ERROR: permissiondenied for function ftestSET SESSION AUTHORIZATION postgres;SETCREATE VIEW vv AS SELECT ftest();ERROR: relation"vv" already existsSELECT * FROM vv; ftest------- 1(1 row)GRANT ALL ON vv TO PUBLIC;GRANTSET SESSION AUTHORIZATIONdummyuser;SETSELECT * FROM vv;ERROR: permission denied for function ftest -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Someone asked me a question about view and function permissions. I
> assumed all object access done by a view would be based on the
> permissions on the view, and not the permissions of the objects.
Table references are checked according to the owner of the view, but use
in a view does not change the execution context for function or operator
calls. This is how it's always been done.
> Is this a bug?
Changing it would be a major definitional change (and a pretty major
implementation change too). It might be better, but please don't
pre-judge the issue by labeling it a bug.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Someone asked me a question about view and function permissions. I > > assumed all object access done by a view would be based on the > > permissions on the view, and not the permissions of the objects. > > Table references are checked according to the owner of the view, but use > in a view does not change the execution context for function or operator > calls. This is how it's always been done. > > > Is this a bug? > > Changing it would be a major definitional change (and a pretty major > implementation change too). It might be better, but please don't > pre-judge the issue by labeling it a bug. Well, it sure sounds like a bug. What logic is there that table access use the view permissions, but not function access? Could we just use SECURITY DEFINER for function calls in views? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Well, it sure sounds like a bug. What logic is there that table access
> use the view permissions, but not function access? Could we just use
> SECURITY DEFINER for function calls in views?
You're confusing two distinct questions, I think. One is how we decide
whether it's allowed to call the function (ie, whose USAGE right do we
check). The other is whose ID does the function run under. If the
function is SECURITY DEFINER then the second question is determined by
the function itself, but otherwise it's not; and in any case SECURITY
DEFINER doesn't speak to the first question.
A case can be made that the answer to both questions should be "the view
owner", rather than "the view user" as it is now, but I am not sure that
this is an open-and-shut issue. Both the user and the owner can affect
whether such a function gets called and what arguments it gets called
with (maybe not so much in a view rule, but definitely in ON INSERT and
other action rules). It seems possible that we'd just be switching from
one set of security issues to another.
regards, tom lane
"Bruce Momjian" <pgman@candle.pha.pa.us> wrote: > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Someone asked me a question about view and function permissions. I > > > assumed all object access done by a view would be based on the > > > permissions on the view, and not the permissions of the objects. > > > > Table references are checked according to the owner of the view, but use > > in a view does not change the execution context for function or operator > > calls. This is how it's always been done. > > > > > Is this a bug? > > > > Changing it would be a major definitional change (and a pretty major > > implementation change too). It might be better, but please don't > > pre-judge the issue by labeling it a bug. > > Well, it sure sounds like a bug. What logic is there that table access > use the view permissions, but not function access? Could we just use > SECURITY DEFINER for function calls in views? I already had this problem, look here: http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1 and I had no reply :-( Regards Gaetano Mendola
"Bruce Momjian" <pgman@candle.pha.pa.us> wrote: > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Someone asked me a question about view and function permissions. I > > > assumed all object access done by a view would be based on the > > > permissions on the view, and not the permissions of the objects. > > > > Table references are checked according to the owner of the view, but use > > in a view does not change the execution context for function or operator > > calls. This is how it's always been done. > > > > > Is this a bug? > > > > Changing it would be a major definitional change (and a pretty major > > implementation change too). It might be better, but please don't > > pre-judge the issue by labeling it a bug. > > Well, it sure sounds like a bug. What logic is there that table access > use the view permissions, but not function access? Could we just use > SECURITY DEFINER for function calls in views? I already had this problem, look here: http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1 and I had no reply :-( Regards Gaetano Mendola
I am documenting this behavior in the CREATE VIEW manual page, diff attached. --------------------------------------------------------------------------- Gaetano Mendola wrote: > "Bruce Momjian" <pgman@candle.pha.pa.us> wrote: > > Tom Lane wrote: > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > > Someone asked me a question about view and function permissions. I > > > > assumed all object access done by a view would be based on the > > > > permissions on the view, and not the permissions of the objects. > > > > > > Table references are checked according to the owner of the view, but use > > > in a view does not change the execution context for function or operator > > > calls. This is how it's always been done. > > > > > > > Is this a bug? > > > > > > Changing it would be a major definitional change (and a pretty major > > > implementation change too). It might be better, but please don't > > > pre-judge the issue by labeling it a bug. > > > > Well, it sure sounds like a bug. What logic is there that table access > > use the view permissions, but not function access? Could we just use > > SECURITY DEFINER for function calls in views? > > I already had this problem, look here: > > http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1 > > and I had no reply :-( > > Regards > Gaetano Mendola > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/ref/create_view.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/create_view.sgml,v retrieving revision 1.24 diff -c -c -r1.24 create_view.sgml *** doc/src/sgml/ref/create_view.sgml 12 Sep 2003 00:12:47 -0000 1.24 --- doc/src/sgml/ref/create_view.sgml 26 Sep 2003 23:53:34 -0000 *************** *** 118,123 **** --- 118,129 ---- CREATE VIEW vista AS SELECT text 'Hello World' AS hello; </programlisting> </para> + + <para> + While access to tables in the view is controlled entirely by permissions + on the view, functions called by the view are checked independently. + </para> + </refsect1> <refsect1>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am documenting this behavior in the CREATE VIEW manual page, diff
> attached.
> + <para>
> + While access to tables in the view is controlled entirely by permissions
> + on the view, functions called by the view are checked independently.
> + </para>
That seems a tad vague, not to say content-free. Perhaps instead say
"Access to tables referenced in the view is determined by permissions of
the view owner. However, functions called in the view are treated the
same as if they had been called directly from the query using the view.
Therefore the user of a view must have permissions to call all functions
used by the view."
As I said earlier, it's quite possible that we should consider this a
mistake. But it's way too late to consider fixing it for 7.4, even if
we had consensus that it should be changed, which I don't think we do
yet. In the meantime we should document the behavior clearly.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I am documenting this behavior in the CREATE VIEW manual page, diff > > attached. > > > + <para> > > + While access to tables in the view is controlled entirely by permissions > > + on the view, functions called by the view are checked independently. > > + </para> > > That seems a tad vague, not to say content-free. Perhaps instead say > "Access to tables referenced in the view is determined by permissions of > the view owner. However, functions called in the view are treated the > same as if they had been called directly from the query using the view. > Therefore the user of a view must have permissions to call all functions > used by the view." > > As I said earlier, it's quite possible that we should consider this a > mistake. But it's way too late to consider fixing it for 7.4, even if > we had consensus that it should be changed, which I don't think we do > yet. In the meantime we should document the behavior clearly. I like your text much better --- added. I will throw this email in the 7.5 queue and we can decide if it is a bug then. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > I like your text much better --- added. I will throw this email in the > 7.5 queue and we can decide if it is a bug then. If is a bug is better have a patch for the 7.4 May be is only a missing feature. For sure for us was and is actually a nightmare imagine : V1 -> V2 -> F / V3 -> V4 This mean that if a group A can use the view V1 and a group B can use a view V3 whe shall back trace that the function F must be executable by group A due V1 and by group B due V3. Fortunatelly Postgres now take trace of dependencies so we can ( not easily ) analyze each function and see if is used inside a view or not, and choose the right permission. We have ~ 100 View and ~ 100 functions :-( Regards Gaetano Mendola PS: I vote that is a bug :-)