Обсуждение: Restricting access to rows?

We have a growing ASP-hosted application built on PHP/Postgres 8.1, and are
getting requests from clients to manipulate the databases more directly.
However, the structure of our databases prevents this from happening readily.

Assume I have two tables configured thusly:

create table customers (
    id serial unique not null,
    name varchar not null
    );

create table widgets (
    customers_id integer not null references customers(id),
    name varchar not null,
    value real not null default 0
    );

insert into customers (name) values ('Bob');
insert into customers (name) values ('Jane');
insert into widgets (customers_id, name, value) VALUES (1, 'Foo', 100);
insert into widgets (customers_id, name, value) VALUES (1, 'Bar', 50);
insert into widgets (customers_id, name, value) VALUES (2, 'Bleeb', 500);

This leaves us with two customers, Bob who has two widgets worth $150, and
Jane with one widget worth $500.

How can I set up a user so that Bob can update his records, without letting
Bob update Jane's records? Is it possible, say with a view or some other
intermediate data type?

Thanks,

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

Benjamin Smith <lists@benjamindsmith.com> schrieb:
> How can I set up a user so that Bob can update his records, without letting
> Bob update Jane's records? Is it possible, say with a view or some other
> intermediate data type?

You can use a VIEW to select all rows for CURRENT_USER, and then create
RULES for this view to do INSERT, UPDATE and DELETE.

A nice framework for row-level access-control is 'veil':
http://pgfoundry.org/projects/veil


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Benjamin Smith <lists@benjamindsmith.com> writes:

> How can I set up a user so that Bob can update his records, without letting
> Bob update Jane's records? Is it possible, say with a view or some other
> intermediate data type?

It's not hard to give them access to *view* their records using a view. You
just create the view with WHERE customer_id = .. and then grant SELECT access
to that view but not the underlying table.

In theory that would be enough to give them update access as well. However
Postgres doesn't yet support updateable views, at least not automatically.

You would have to write rules for each view to implement updateable views
which isn't hard but would get pretty tiresome if you're doing this for a lot
of tables and a lot of clients.

There was a project around where someone had implemented some scripts to do
this automatically. You might be able to find it searching back through the
lists.

There are also people interested in working on it as a built-in feature for
Postgres, but I don't think there's any time-line on though or even any
preliminary results yet, so I wouldn't depend on it any time soon.

--
greg

Hi,

Are there any plans to make CREATE USER local to a database? (as opposed
to CLUSTER scope, as it is today)

So that in such cases as Benjamin's, the ISP could satisfy customer
requests by createing and handing over the new database instance within
the managed cluster? Even with the unrestricted CREATE USER privileges?

-R

On Fri, 2006-05-26 at 07:39 +0200, Andreas Kretschmer wrote:
> Benjamin Smith <lists@benjamindsmith.com> schrieb:
> > How can I set up a user so that Bob can update his records, without letting
> > Bob update Jane's records? Is it possible, say with a view or some other
> > intermediate data type?
>
> You can use a VIEW to select all rows for CURRENT_USER, and then create
> RULES for this view to do INSERT, UPDATE and DELETE.
>
> A nice framework for row-level access-control is 'veil':
> http://pgfoundry.org/projects/veil
>
>
> HTH, Andreas
--
-R

Greg Stark wrote:
> There are also people interested in working on it as a built-in feature for
> Postgres, but I don't think there's any time-line on though or even any
> preliminary results yet, so I wouldn't depend on it any time soon.

Actually, there is a patch which works for some cases. Not sure if it
will make it into 8.2 though.

--
   Richard Huxton
   Archonet Ltd

Benjamin Smith wrote:
> How can I set up a user so that Bob can update his records, without letting
> Bob update Jane's records? Is it possible, say with a view or some other
> intermediate data type?

I've done something similar using a separate control table where I set
what accounts an user can "see", then I wrote a psql that returns just
the rows for that especific user, it could also be done with pure SQL
joins tough.



--
Sinceramente,
Josué Maldonado.

... "Si me engañas una vez, tuya es la culpa. Si me engañas dos, la
culpa es mía." -- Anaxágoras.

Josue E. Maldonado wrote:

> Benjamin Smith wrote:
>
>> How can I set up a user so that Bob can update his records, without
>> letting Bob update Jane's records? Is it possible, say with a view or
>> some other intermediate data type?
>
>
> I've done something similar using a separate control table where I set
> what accounts an user can "see", then I wrote a psql that returns just
> the rows for that especific user, it could also be done with pure SQL
> joins tough.
>
>
>
You can put in a some triggers that do a few things, and I think a rule
on SELECT will round it off.

on Insert: populate a column with CURRENT_USER
on Update and Delete: refuse unless CURRENT_USER matches the column
on SELECT rules, apply a filter that column = CURRENT_USER

You also may put in an override for all three that if the CURRENT_USER
is in some particular group these filters will not apply.  One level
might be just for selects, a higher level for updates/deletes.

Or you can do the reverse, and say that these filters only apply if the
user is in a certain group.

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> Are there any plans to make CREATE USER local to a database?

No.

There is the db_user_namespace configuration parameter, but it's a bit
of an ugly kluge if you ask me ...

            regards, tom lane

On Fri, 2006-05-26 at 10:25 -0400, Tom Lane wrote:
> There is the db_user_namespace configuration parameter, but it's a bit
> of an ugly kluge if you ask me ...

Haven't noticed that.

But a superuser@dataabase1, still can create a user@database2 - so it's
of no use for privilege separation. Pity.

--
-R

You are apparently dealing with the downside of co-mingling your clients
data... maybe you should seriously consider revising your approach and
giving each client either separate databases or separate schema's within a
given database --

This is why co-mingling should be avoided...

I'd push for the former -- that way -- you can use a template database
instead of hoping that all of your filters, rules, views, etc are
accurate...



"Benjamin Smith" <lists@benjamindsmith.com> wrote in message
news:200605252155.52906.lists@benjamindsmith.com...
> We have a growing ASP-hosted application built on PHP/Postgres 8.1, and
> are
> getting requests from clients to manipulate the databases more directly.
> However, the structure of our databases prevents this from happening
> readily.
>
> Assume I have two tables configured thusly:
>
> create table customers (
> id serial unique not null,
> name varchar not null
> );
>
> create table widgets (
> customers_id integer not null references customers(id),
> name varchar not null,
> value real not null default 0
> );
>
> insert into customers (name) values ('Bob');
> insert into customers (name) values ('Jane');
> insert into widgets (customers_id, name, value) VALUES (1, 'Foo', 100);
> insert into widgets (customers_id, name, value) VALUES (1, 'Bar', 50);
> insert into widgets (customers_id, name, value) VALUES (2, 'Bleeb', 500);
>
> This leaves us with two customers, Bob who has two widgets worth $150, and
> Jane with one widget worth $500.
>
> How can I set up a user so that Bob can update his records, without
> letting
> Bob update Jane's records? Is it possible, say with a view or some other
> intermediate data type?
>
> Thanks,
>
> -Ben
> --
> "The best way to predict the future is to invent it."
> - XEROX PARC slogan, circa 1978
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>