Обсуждение: ...

Hi,

I am trying to figure out how to restrict user access to a database to
a few defined functions, so that users could use the database (and update
it) but only through a set of procedures, written for exemple in C and
using the Server Programming
interface.

I am new to PostgreSQL so sorry if this question is irrelevant to this
list.

Thanks in advance,

Emmanuel Motchane

Emmanuel Motchane wrote:
>
> Hi,
>
> I am trying to figure out how to restrict user access to a database to
> a few defined functions, so that users could use the database (and update
> it) but only through a set of procedures, written for exemple in C and
> using the Server Programming
> interface.

If this is some kind of security measure, it won't work because in the
extreme case anybody can just open a socket and send the appropriate
protocol down it (like you can with any client server database).

Otherwise I suggest you ask your user's politely or see if the postgres
grant permissions can do what you want.

One problem you may have with this is that if a function accesses some
table, the user who uses that function must also have permissions on the
table.  I have a similar problem.  I'd like to give permissions on a view,
but not on the table underlying the view (the view serves to filter out
some records the user shouldn't see).  I can't give permission to use view
without giving permission to use the table.

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

On Tue, 23 May 2000, Emmanuel Motchane wrote:

> Hi,
>
> I am trying to figure out how to restrict user access to a database to
> a few defined functions, so that users could use the database (and update
> it) but only through a set of procedures, written for exemple in C and
> using the Server Programming
> interface.
>
> I am new to PostgreSQL so sorry if this question is irrelevant to this
> list.
>
> Thanks in advance,
>
> Emmanuel Motchane
>
>
>
>

On Wed, May 24, 2000 at 12:45:59PM -0500, Travis Bauer wrote:
> One problem you may have with this is that if a function accesses some
> table, the user who uses that function must also have permissions on the
> table.  I have a similar problem.  I'd like to give permissions on a view,
> but not on the table underlying the view (the view serves to filter out
> some records the user shouldn't see).  I can't give permission to use view
> without giving permission to use the table.

Have you tried it? This is one of the things views are for. The view
accesses it's underlying tables as the user who created the view, as far
as I recall. I, for example, have an entire database where every table
has a 'pub' boolean. I've created views that return only rows with pub =
't', and given the anonymous user (which the web server connect as)
select privileges only on the view.

idas=> select count(*) from urls;
count
-----
   23
(1 row)

idas=> select count(*) from urls_p;
count
-----
   23
(1 row)

idas=> select count(*) from urls;
ERROR:  urls: Permission denied.
idas=> \c - anonymous
connecting as new user: anonymous
idas=> select count(*) from urls_p;
count
-----
   23
(1 row)

idas=>


Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Uh, I cut & pasted the transcript in two pieces to get the selects in
the same order, and messed up. The error happens _after_ connecting as
anonymous, not before.

Ross

On Wed, May 24, 2000 at 01:09:58PM -0500, Ross J. Reedstrom wrote:
>
> idas=> select count(*) from urls;
> ERROR:  urls: Permission denied.
> idas=> \c - anonymous
> connecting as new user: anonymous
> idas=> select count(*) from urls_p;
> count
> -----
>    23
> (1 row)
>
> idas=>

Ooops.  I have to withdraw that comment.  I spent hours the other day
beating my head against the wall over this.  I was sure that it didn't
work . . .

Sorry,

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

On Wed, 24 May 2000, Ross J. Reedstrom wrote:

> On Wed, May 24, 2000 at 12:45:59PM -0500, Travis Bauer wrote:
> > One problem you may have with this is that if a function accesses some
> > table, the user who uses that function must also have permissions on the
> > table.  I have a similar problem.  I'd like to give permissions on a view,
> > but not on the table underlying the view (the view serves to filter out
> > some records the user shouldn't see).  I can't give permission to use view
> > without giving permission to use the table.
>
> Have you tried it? This is one of the things views are for. The view
> accesses it's underlying tables as the user who created the view, as far
> as I recall. I, for example, have an entire database where every table
> has a 'pub' boolean. I've created views that return only rows with pub =
> 't', and given the anonymous user (which the web server connect as)
> select privileges only on the view.
>