Обсуждение: Read only access, via functions only

Hi all

Is there a away to set up a schema such that a certain role has (1)
read only access to (2) all the tables, but (3) must use predefined
functions to use that access?

Items 1 and 2 are so that the end user doesn't stomp on the data.

I want item 3 in order to force the application programmers (PHP) not
to craft their own select statements but to go through the API

Thoughts? I have about given up, but maybe somebody can make it easy for me.

Running version 8.2.something (stupid Linux Distros)

(I can post code if necessary, I am just feeling a little lazy...)

Thanks
W

On Nov 5, 2008, at 6:46 PM, Webb Sprague wrote:

> Hi all
>
> Is there a away to set up a schema such that a certain role has (1)
> read only access to (2) all the tables, but (3) must use predefined
> functions to use that access?
>
> Items 1 and 2 are so that the end user doesn't stomp on the data.
>
> I want item 3 in order to force the application programmers (PHP) not
> to craft their own select statements but to go through the API

What about using views?

>
>
> Thoughts? I have about given up, but maybe somebody can make it easy
> for me.
>
> Running version 8.2.something (stupid Linux Distros)

There is nothing stupid on a Linux distro....

>
>
> (I can post code if necessary, I am just feeling a little lazy...)

Don't be lazy.... ;)
If we are lazy, you wouldn't see a reply.....

Ries

>
>
> Thanks
> W
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



            regards, Ries van Twisk


-------------------------------------------------------------------------------------------------
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?

On Wed, 2008-11-05 at 15:46 -0800, Webb Sprague wrote:
> Hi all
>
> Is there a away to set up a schema such that a certain role has (1)
> read only access to (2) all the tables, but (3) must use predefined
> functions to use that access?

you can set transactions read only but the user can turn them off. Use
views, functions and GRANT.

Joshua D. Drake

--

> you can set transactions read only but the user can turn them off. Use
> views, functions and GRANT.

Views was the key word.  I had tried to do it with functions and GRANT alone.

Thanks to the collective brain that is a listserv.
-W

Webb Sprague wrote:
>> you can set transactions read only but the user can turn them off. Use
>> views, functions and GRANT.
>
> Views was the key word.  I had tried to do it with functions and GRANT alone.

You can also do a lot with SECURITY DEFINER functions - for example, if
you want to grant them the ability to make only very limited changes to
the data set and want to run some checking logic before allowing the change.

--
Craig Ringer