Re: Bugs with rules on views/tables: permission denied - Mailing list pgsql-bugs

----- Original Message -----=20
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Donald Fraser" <demolish@cwgsy.net>
Cc: "[BUGS]" <pgsql-bugs@postgresql.org>
Sent: Monday, February 24, 2003 8:29 PM
Subject: Re: [BUGS] Bugs with rules on views/tables: permission denied=20


> "Donald Fraser" <demolish@cwgsy.net> writes:
> > As USER chkrule do:
> > Bugs=3D3D> INSERT INTO vu_tbl_test (id, s_text) VALUES('1','sometext');
> > ERROR:  vu_tbl_test: permission denied
>=20
> What I get is=20
> ERROR:  test_func: permission denied

This is really weird... starting to pull my hair out!
I cut and paste everything straight out of my email=20
and the first error I got was definitely:
ERROR:  vu_tbl_test: permission denied

I noticed that I had made a typo on line 17 of SQL:
REVOKE ALL ON TABLE public.tbl_test1 FROM PUBLIC;=20
should have read:
REVOKE ALL ON TABLE public.tbl_test2 FROM PUBLIC;=20

So I changed this and tried again - this time I got
ERROR:  tbl_test2: permission denied

It doesn't matter what I do now, I tried putting line 17 back
how it was (shouldn't have mattered anyway), vacuuming,
restarting the server and the first error message I get now=20
is always:
ERROR:  tbl_test2: permission denied

I cannot get the original message:
ERROR:  vu_tbl_test: permission denied.

Not sure what is going on with that one?

I'm running PostgreSQL 7.3.1 on i686-pc-linux-gnu,=20
compiled by GCC 2.96
I take it you tested yours on at least 7.3.2 or greater?=20
Were there some changes between these versions that could effect
this scenario? I didn't see any in the readme...

> and the needed fix is to grant execute privilege on the function to the
> calling user.

In this case I have to draw an annalogy between functions and tables,
albeit that they are completely different.=20
Functions can perform more sophisticated logic on data input to=20
tables that the user wouldn't normally have access to. Just like you=20
wouldn't give them access to those tables in the first place.
Hence if you give them access to the function, you may as well give
them access to the tables which is not the goal of views.
It's probably a debatable point, of which I can see how Postgres
has arrived at the current point of logic given it's past history with
function permissions.

At the moment to get around this I only allow access to tables
through views (which is generally good database design) so
when it comes to functions that modify tables, it doesn't matter
too much because if a user calls a function directly they=20
still want have the permissions. They can only be given such
persmission via views.

It just seems a bit untidy that I can't stop a user calling the=20
functions directly in the first place.
I'm sure one day there will be the scenario where I will want
a function to do something other than just table modifications
and then I will be stuck with the "how do I stop them calling
it directly in an uncontrolled manner".

>=20
> > If you give the USER chkrule UPDATE permissions on the view vu_tbl_test=
 the=3D
> > n the error at Scenario 1 goes away.
>=20
> Not for me.
>=20
> The reason it acts this way is that rule permissions apply to tables
> mentioned in the rule, not to functions.  This is at least partly for
> historical reasons: functions didn't have permissions to check, back
> when the rule system was designed.  But I'm not sure that it's wrong.
> In your example, the end user is controlling the arguments given to
> test_func.  If the rule allowed him to call test_func without having
> permission to do so, there'd be the potential for security breaches.

In my opinion this is no more of a security breach than allowing the=20
modification of tables, via rules, that the user doesn't normally have=20
access to. The rule creator is granting them that permission by=20
putting it in the rule.

Regards
Donald Fraser.