pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow many views is ok?
4+ messages / 4 participants
[nested] [flat]
* How many views is ok?
@ 2005-08-12 06:53 Petr Kavan <petr.kavan@marekmicro.cz>
0 siblings, 2 replies; 4+ messages in thread
From: Petr Kavan @ 2005-08-12 06:53 UTC (permalink / raw)
To: pgsql-performance
I have database of company data, and some of them is table of information
about employees. I need each employee to have access only to his own row.
Postgre cannot do this by system of privileges, because that can give
privileges only to whole tables.
Possibility is to create a view for each employee that chooses only his data
and give employee privileges to this view. But I am not sure if such number
of views does not have some performance drawbacks or even if postgre can
support it (I expect i can). I would need several tables protected like this
and it can result in, say 1000 views in maximum.
Because access to DB will go through PHP information system, other
possibility to protect data is to let IS connect as more privileged than
user really is, but let it retrieve only data for that user.
View-approach seems far more clear than this, but im not sure if postgre can
handle it without problems.
Thanks for any reply :-)
-----------------------------------------------------------
Petr Kavan
Database Development
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How many views is ok?
@ 2005-08-15 00:22 John Arbash Meinel <john@arbash-meinel.com>
parent: Petr Kavan <petr.kavan@marekmicro.cz>
1 sibling, 1 reply; 4+ messages in thread
From: John Arbash Meinel @ 2005-08-15 00:22 UTC (permalink / raw)
To: Petr Kavan <petr.kavan@marekmicro.cz>; +Cc: pgsql-performance
Petr Kavan wrote:
> I have database of company data, and some of them is table of
> information about employees. I need each employee to have access only
> to his own row. Postgre cannot do this by system of privileges,
> because that can give privileges only to whole tables.
>
> Possibility is to create a view for each employee that chooses only
> his data and give employee privileges to this view. But I am not sure
> if such number of views does not have some performance drawbacks or
> even if postgre can support it (I expect i can). I would need several
> tables protected like this and it can result in, say 1000 views in
> maximum.
>
> Because access to DB will go through PHP information system, other
> possibility to protect data is to let IS connect as more privileged
> than user really is, but let it retrieve only data for that user.
>
> View-approach seems far more clear than this, but im not sure if
> postgre can handle it without problems.
We do a similar thing tying user to per-row permissions. We have 1 view
per table, and it works fine.
I would recommend that you do something similar. Basically, just make
the view:
CREATE VIEW just_me SECURITY DEFINER AS
SELECT * FROM user_table WHERE username=session_user;
REVOKE ALL FROM user_table;
GRANT SELECT TO just_me TO PUBLIC;
security definer, means that the 'just_me' view will be executed as the
user who created the function (superuser).
The REVOKE ALL (my syntax might be wrong) prevents users from querying
the user tables directly.
The 'session_user' makes the view use the name of the actual connected
user (because of security definer, plain 'user' is the superuser)
This should allow a user to see only their own row in the database.
(Whichever rows that have username matching the connected name).
Now, this only works if the php front end connects specifically as the
given user (our system is setup to do this).
If you don't do it this way, you'll always be stuck with the IS layer
doing the restriction. Even if you create a view per user, if your PHP
layer has the right to look at other tables/views, it doesn't really help.
Good luck,
John
=:->
>
> Thanks for any reply :-)
>
> -----------------------------------------------------------
> Petr Kavan
> Database Development
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
Attachments:
signature.asc (application/pgp-signature, 255B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How many views is ok?
@ 2005-08-15 00:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Petr Kavan <petr.kavan@marekmicro.cz>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-08-15 00:38 UTC (permalink / raw)
To: Petr Kavan <petr.kavan@marekmicro.cz>; +Cc: pgsql-performance
"Petr Kavan" <petr.kavan@marekmicro.cz> writes:
> Possibility is to create a view for each employee that chooses only his data
> and give employee privileges to this view. But I am not sure if such number
> of views does not have some performance drawbacks or even if postgre can
> support it (I expect i can).
Do you really need more than one view? I'd consider something like
create view emp_view as select * from emp where name = current_user;
This requires that your Postgres usernames match up with something in
the underlying table, of course.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How many views is ok?
@ 2005-08-15 05:23 Petr Kavan <kavan.petr@seznam.cz>
parent: John Arbash Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 4+ messages in thread
From: Petr Kavan @ 2005-08-15 05:23 UTC (permalink / raw)
To: John Arbash Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
Hey, that trick with session_user is great! :-) Thank you all very much,
this will certainly help.
-----------------------------------------------------------
Petr Kavan
Database Development
----- Original Message -----
From: "John Arbash Meinel" <john@arbash-meinel.com>
To: "Petr Kavan" <petr.kavan@marekmicro.cz>
Cc: <pgsql-performance@postgresql.org>
Sent: Monday, August 15, 2005 2:22 AM
Subject: Re: [PERFORM] How many views is ok?
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-08-15 05:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-12 06:53 How many views is ok? Petr Kavan <petr.kavan@marekmicro.cz>
2005-08-15 00:22 ` John Arbash Meinel <john@arbash-meinel.com>
2005-08-15 05:23 ` Petr Kavan <kavan.petr@seznam.cz>
2005-08-15 00:38 ` Tom Lane <tgl@sss.pgh.pa.us>
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