Skip site navigation (1) Skip section navigation (2)

Re: Separate Sessions?? (View data <-> Query tool)

From: Csaba Együd <csegyud(at)gmail(dot)com>
To: pgadmin-support(at)postgresql(dot)org
Subject: Re: Separate Sessions?? (View data <-> Query tool)
Date: 2008-11-29 05:33:59
Message-ID: ggqk8c$ml2$ (view raw, whole thread or download thread mbox)
Lists: pgadmin-supportpgsql-general
""Willy-Bas Loos"" <willybas(at)gmail(dot)com> a következoket írta üzenetében 
>If I understand well you say that I could isolate data by using separate 
>group roles for each firms and defining updatable views to work on.
yes. it's one way to do it.
As simple as this example is, you might as well make a seperate table for 
each firm. Then you wouldn't need to bother with views and rules.
The views get interesting when you want to allow one record to be selected 
by more than one firm.
Or if you want to be able to do stuff with all of the data in table1.
The disadvantage is that each firm has to use diffent code, they select from 
a different table. If you want to get really crazy you can add a username to 
table1 and make just one view that filters on applicable_roles (all the 
roles granted to the current_role). But that doesn't scale so well i guess.

>This sounds quite promissing.
Well, it only works as long as you use the database authentication (and 
authorisation) system. Meaning that your application logs in to the database 
with a seperate account for each user.
Works great for database clients like pgadmin, but it doesn't make you happy 
in PHP or standard webservice stuff.
In that case you don't need this kind of authorisation in the database 
layer, but you do it in a different application.

>The most strange thing for me is revoking all rights from table1 but I 
>guess this is the heart of it... If I revoke all rights on table1 from the 
>roles how will the user be able to modify the data of table1 trough the 
> I read somewhere that when a user wants to select rows from a view the 
> user needs to have the select permission for the undelaying table as well, 
> and thats the case with updating or inserting too.

The view uses the privileges of its owner, not those of the current_user

So for example:
john has no rights on table1, but can make a view "create view bla as select 
* from table1". There will be a "permission denied" error for anyone who 
selects from the view, even superusers.
the other way around:
fred is a superuser. When he makes the same view, all records will be 
visible to those that (may) select from it.

just try it:
--based on the code from before, you now have the right to select from 
table1 since you created it and are the owner
set role john;
create view bla as select * from table1;
select * from bla;

reset role;
select current_role;

select * from bla;



"Patriotism is the conviction that your country is superior to all others 
because you were born in it." -- George Bernard Shaw


I do not want to make it so complicated. This level of complexity is far 
enough... :)

For a long time, I have tried for always use database roles to log into the 
application instead ot unsing fictive users logging in with a shared 
privilege-packed database role. I'm that kind of paranoid developer who 
thinks that bad guys will try to hack the system. On the other hand there 
may also be bugs in the client software and the unprivileged user could 
reach information which is not for her / him...

I usually develop desktop applications not web apps. So I need this level of 
granularity of the permission system. PostgreSQL is really impressive on 
this field.

Regarding the view rights: I did not know this piece of information and I'm 
thankful for you to making it clear.

Many thanks!

  -- Csaba 

In response to

pgadmin-support by date

Next:From: Guillaume LelargeDate: 2008-11-29 15:48:16
Subject: Re: How to view geometry fields as text?
Previous:From: Stefan KellerDate: 2008-11-28 23:45:42
Subject: How to view geometry fields as text?

pgsql-general by date

Next:From: Siddharth ShahDate: 2008-11-29 06:20:28
Subject: configure options
Previous:From: Willy-Bas LoosDate: 2008-11-28 21:20:20
Subject: Re: Separate Sessions?? (View data <-> Query tool)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group