grants and functions - Mailing list pgsql-admin

From Remco Post
Subject grants and functions
Date
Msg-id 47569235.6030701@sara.nl
Whole thread Raw
List pgsql-admin
Hi All,

I've been trying to figure this out and reading this list on this subject.

I have a view that shows a user a lot of intresting info. Now, on a
normal view, that does not call any functions, I can give my user select
rights and he/she can see all the info, without me having to to grant
any rights on underlying tables.

The PUBLIC also has execute rights on all functions I've defined by
default, so far so good. But now, If I grant a USER SELECT on a VIEW
that uses a pl/pgsql function, for some reason, The normal inheritance
of permissions is lost as I've become used to.

Now I could define my FUNCTION with SECURITY DEFINER, which will
probably work around the problem, since this will give the USER database
owner privileges. Also, I could give the USER select rights on the
underlying tables, but there was a reason I only wanted him/her to see
the database via the VIEW I created, and there are a lot of tables involved.

Has anybody found a way around this? Is this a bug, a documented
shortcoming, or just a feature of PostgreSQL (at version 8.1)?

--
Met vriendelijke groeten,

Remco Post

SARA - Reken- en Netwerkdiensten                      http://www.sara.nl
High Performance Computing  Tel. +31 20 592 3000    Fax. +31 20 668 3167
PGP Key fingerprint = 6367 DFE9 5CBC 0737 7D16  B3F6 048A 02BF DC93 94EC

"I really didn't foresee the Internet. But then, neither did the
computer industry. Not that that tells us very much of course - the
computer industry didn't even foresee that the century was going to
end." -- Douglas Adams

pgsql-admin by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Cannot kill autovacuum
Next
From: "Greg Sabino Mullane"
Date:
Subject: Re: Enhancement request