pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to limit access only to certain records?
7+ messages / 7 participants
[nested] [flat]
* How to limit access only to certain records?
@ 2012-06-22 11:36 Andreas <maps.on@gmx.net>
0 siblings, 5 replies; 7+ messages in thread
From: Andreas @ 2012-06-22 11:36 UTC (permalink / raw)
To: pgsql-sql
Hi,
is there a way to limit access for some users only to certain records?
e.g. there is a customer table and there are account-managers.
Could I limit account-manager #1 so that he only can access customers
only acording to a flag?
Say I create a relation cu_am ( customer_id, account_manager_id ).
Could I let the database control that account-manager #1 can only see
customers who are assigned to him in the cu_am-relation?
For now I do this in the front-end but this is easily circumvented for
anyone who has a clue and uses some other client like psql.
Regards
Andreas
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to limit access only to certain records?
@ 2012-06-22 12:32 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Andreas <maps.on@gmx.net>
4 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2012-06-22 12:32 UTC (permalink / raw)
To: pgsql-sql
Andreas <maps.on@gmx.net> wrote:
> Hi,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers
> only acording to a flag?
Yea, it's possible.
Write functions to access to the table (for select, for insert and so
on) as superuser, with secutity definer, revoke all rights from the
user.
Users can only access to the table with the functions, within this
functions check if the current_user has rights for the record.
There are some examples how to do that, please use google ;-)
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." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to limit access only to certain records?
@ 2012-06-22 12:35 Jov <zhao6014@gmail.com>
parent: Andreas <maps.on@gmx.net>
4 siblings, 0 replies; 7+ messages in thread
From: Jov @ 2012-06-22 12:35 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
no,I think there is no such way.
what about create view for the user you want to limit,and revoke select
privilege from the base table ?
2012/6/22 Andreas <maps.on@gmx.net>
> Hi,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers only
> acording to a flag?
>
> Say I create a relation cu_am ( customer_id, account_manager_id ).
> Could I let the database control that account-manager #1 can only see
> customers who are assigned to him in the cu_am-relation?
>
> For now I do this in the front-end but this is easily circumvented for
> anyone who has a clue and uses some other client like psql.
>
>
> Regards
> Andreas
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql;
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to limit access only to certain records?
@ 2012-06-22 13:03 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Andreas <maps.on@gmx.net>
4 siblings, 0 replies; 7+ messages in thread
From: Jayadevan M @ 2012-06-22 13:03 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
HI,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers
> only acording to a flag?
>
> Say I create a relation cu_am ( customer_id, account_manager_id ).
> Could I let the database control that account-manager #1 can only see
> customers who are assigned to him in the cu_am-relation?
>
> For now I do this in the front-end but this is easily circumvented for
> anyone who has a clue and uses some other client like psql.
Using a VIEW?
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to limit access only to certain records?
@ 2012-06-22 14:52 hari.fuchs@gmail.com
parent: Andreas <maps.on@gmx.net>
4 siblings, 0 replies; 7+ messages in thread
From: hari.fuchs@gmail.com @ 2012-06-22 14:52 UTC (permalink / raw)
To: pgsql-sql
Andreas <maps.on@gmx.net> writes:
> Hi,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers
> only acording to a flag?
Maybe something like the following:
CREATE TABLE test1 (
id serial NOT NULL,
val text NOT NULL,
_user text NOT NULL,
PRIMARY KEY (id)
);
COPY test1 (val, _user) FROM stdin;
for user1#1 user1
for user1#2 user1
for user2#1 user2
\.
CREATE VIEW test1v AS
SELECT id, val
FROM test1
WHERE _user = current_user;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to limit access only to certain records?
@ 2012-06-24 06:58 Craig Ringer <ringerc@ringerc.id.au>
parent: Andreas <maps.on@gmx.net>
4 siblings, 1 reply; 7+ messages in thread
From: Craig Ringer @ 2012-06-24 06:58 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On 06/22/2012 07:36 PM, Andreas wrote:
> Hi,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers
> only acording to a flag?
What you describe is called row-level access control, row level
security, or label access control, depending on who you're talking to.
It's often discussed as part of multi-tenant database support.
As far as I know PostgreSQL does not currently offer native facilities
for row-level access control (except possibly via SEPostgreSQL
http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction). There's
discussion of adding such a feature here
http://wiki.postgresql.org/wiki/RLS .
As others have noted the traditional way to do this in DBs without row
level access control is to use a stored procedure (in Pg a SECURITY
DEFINER function), or a set of access-limited vies, to access the data.
You then REVOKE access on the main table for the user so they can *only*
get the data via the procedure/views.
See:
http://www.postgresql.org/docs/current/static/sql-createview.html
<http://www.postgresql.org/docs/9.1/static/sql-createview.html;
http://www.postgresql.org/docs/
<http://www.postgresql.org/docs/9.1/static/sql-createfunction.html>current
<http://www.postgresql.org/docs/9.1/static/sql-createview.html>/static/sql-createfunction.html
<http://www.postgresql.org/docs/9.1/static/sql-createfunction.html;
http://www.postgresql.org/docs/current/static/sql-grant.html
<http://www.postgresql.org/docs/9.1/static/sql-grant.html;
http://www.postgresql.org/docs/current/static/sql-revoke.html
<http://www.postgresql.org/docs/9.1/static/sql-revoke.html;
Hope this helps.
--
Craig Ringer
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to limit access only to certain records?
@ 2012-06-24 20:00 Dickson S. Guedes <listas@guedesoft.net>
parent: Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 0 replies; 7+ messages in thread
From: Dickson S. Guedes @ 2012-06-24 20:00 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: Andreas <maps.on@gmx.net>; pgsql-sql
2012/6/24 Craig Ringer <ringerc@ringerc.id.au>:
> As far as I know PostgreSQL does not currently offer native facilities for
> row-level access control (except possibly via SEPostgreSQL
> http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction).
Yes. Row-level access was in SEPostgreSQL's drafts but after many discussions
the conclusion is that since PostgreSQL doesn't support row-level access
sepgsql, that is in contrib [1] nowadays, does not support it either.
[1] http://www.postgresql.org/docs/9.1/static/sepgsql.html
Regards.
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-06-24 20:00 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-22 11:36 How to limit access only to certain records? Andreas <maps.on@gmx.net>
2012-06-22 12:32 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-06-22 12:35 ` Jov <zhao6014@gmail.com>
2012-06-22 13:03 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2012-06-22 14:52 ` hari.fuchs@gmail.com
2012-06-24 06:58 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-24 20:00 ` Dickson S. Guedes <listas@guedesoft.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox