Re: [BUGS] Bugs with rules on views/tables: permission denied

Donald Fraser Mon, 24 Feb 2003 17:27:02 -0800

----- Original Message -----

Sent: Monday, February 24, 2003 8:29 PM

Subject: Re: [BUGS] Bugs with rules on views/tables: permission denied

> "Donald Fraser" <[EMAIL PROTECTED]> writes:
> > As USER chkrule do:
> > Bugs=3D> INSERT INTO vu_tbl_test (id, s_text) VALUES('1','sometext');
> > ERROR: vu_tbl_test: permission denied
>
> What I get is
> 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

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;

should have read:

REVOKE ALL ON TABLE public.tbl_test2 FROM PUBLIC;

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

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,

compiled by GCC 2.96

I take it you tested yours on at least 7.3.2 or greater?

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.

Functions can perform more sophisticated logic on data input to

tables that the user wouldn't normally have access to. Just like you

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

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

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

>
> > If you give the USER chkrule UPDATE permissions on the view vu_tbl_test the=
> > n the error at Scenario 1 goes away.
>
> Not for me.
>
> 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

modification of tables, via rules, that the user doesn't normally have

access to. The rule creator is granting them that permission by

putting it in the rule.

Regards

Donald Fraser.

Re: [BUGS] Bugs with rules on views/tables: permission denied

Reply via email to