Is there a way to revoke permission to join two or more tables, even for
users who have all other permissions (e.g., select, insert, update,
delete) on those tables?

Heather Johnson
Senior Database Programmer
New York Post

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Heather Johnson wrote:
> Is there a way to revoke permission to join two or more tables, even for
> users who have all other permissions (e.g., select, insert, update,
> delete) on those tables?

I don't think you can, and I'm not sure it makes sense to. If I can
select from tables ta,tb then I can match them up in my client - if
needs be I can save the data and import it into a local database.

Could you hide the column(s) being joined on? If so, then you could
create two views and just grant access to those.
CREATE TABLE ta (id_a int, notes_a text, joinval_a int)
CREATE TABLE tb (id_b int, notes_b text, joinval_b int)
CREATE VIEW va AS SELECT id_a,notes_a FROM ta
CREATE VIEW vb AS SELECT id_b,notes_b FROM tb
GRANT ALL ON VIEW va TO ...
GRANT ALL ON VIEW vb TO ...
REVOKE ALL ON TABLE ta FROM ...
REVOKE ALL ON TABLE tb FROM ...
You'll want to add rules for updating/inserting, assuming that can be
done while concealing the existence of joinval_a/b

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Tue, Feb 22, 2005 at 02:51:09PM -0500, Heather Johnson wrote:

> Is there a way to revoke permission to join two or more tables, even for
> users who have all other permissions (e.g., select, insert, update,
> delete) on those tables?

For what purpose? If this were possible, then users could still
do joins on the client side if they had access to all the data.
Are you just trying to prevent potentially large queries? What
problem are you trying to solve?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

One of the tables contains personally identifiable information (PII)
(e.g., email, first and last name, etc.). The other contains "click
stream" data (information about where on our website users have gone).
In order to be sensitive to users privacy, we don't want to ever (even
accidentally) run queries that would combine PII and click stream data.
So we're looking for ways to put "walls" up against combining the data.
We understand that anyone with ample access to the database can
deliberately combine the data if they chose to do so in blatent
violation of our policies. But we want to set something up that would
put an obstacle or two in the path of anyone who might accidentally run
such a query.

The ideas about setting up views might work. I guess we'd just have to
change the permissions of users who have access to the db so that they
can only use the views and not query the tables directly.

Michael Fuhr wrote:
> On Tue, Feb 22, 2005 at 02:51:09PM -0500, Heather Johnson wrote:
>
>
>>Is there a way to revoke permission to join two or more tables, even for
>>users who have all other permissions (e.g., select, insert, update,
>>delete) on those tables?
>
>
> For what purpose? If this were possible, then users could still
> do joins on the client side if they had access to all the data.
> Are you just trying to prevent potentially large queries? What
> problem are you trying to solve?
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, 2005-02-22 at 14:30, Heather Johnson wrote:
> One of the tables contains personally identifiable information (PII)
> (e.g., email, first and last name, etc.). The other contains "click
> stream" data (information about where on our website users have gone).
> In order to be sensitive to users privacy, we don't want to ever (even
> accidentally) run queries that would combine PII and click stream data.
> So we're looking for ways to put "walls" up against combining the data.
> We understand that anyone with ample access to the database can
> deliberately combine the data if they chose to do so in blatent
> violation of our policies. But we want to set something up that would
> put an obstacle or two in the path of anyone who might accidentally run
> such a query.
>
> The ideas about setting up views might work. I guess we'd just have to
> change the permissions of users who have access to the db so that they
> can only use the views and not query the tables directly.

Why not change the keys that currently connect them to something
different (i.e. random noise) and make a NEW table that could join them
with those random keys that is restriced access wise to only the chosen
few.

Or do you need to actually ever re-reference the two datasets? If not,
then just lose the connecting data when you insert the rows.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Scott Marlowe wrote:

> Why not change the keys that currently connect them to something
> different (i.e. random noise) and make a NEW table that could join them
> with those random keys that is restriced access wise to only the chosen
> few.

This might work rather nicely. It would enable us to restrict direct
access to only a single table---a table with no purpose other than to
faciliate a join of these two other tables. And staff that needs direct
access to the original tables can continue to have it.

> Or do you need to actually ever re-reference the two datasets? If not,
> then just lose the connecting data when you insert the rows.

Yes, unfortunately we do need to re-reference them. But I think the idea
above will work out pretty well. Thank you for your help!

Heather

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)