Postgresql access restrictions on field/table for select

I am considering using postgresql/postgis for spatial research. I have one table with data, and one table with coordinates. The "data" alone is not sensitive. But as end-users easily can determine the identity of the cases, once combined with the coordinates the "data" is very sensitive.

I currently use the postgis functions to do spatial queries, and return the result to R for statistical analysis.

With postgresq开发者_开发知识库l is it possible to restrict the access to specific fields, so that end-users can use the field(coordinates) and fetch the result from spatial queries. But not able to return/export the field(coordinates) directly via a select statement.

Since 8.4 you can use column level permissions to "lock" out a user from being able to select specific columns like you can do with a view or stored function.

http://andreas.scherbaum.la/blog/archives/577-PostgreSQL-8.4-Column-Permissions.html

You can hide columns from the end user by creating a view that only contains the column the user is allowed to see, and then revoke the SELECT privilege from the underlying table.

But there is no way to prevent a user from locally saving (exporting) the data that he/she obtained by running a SELECT statement.

It is possible to use SECURITY DEFINER function for that.

Revoke select privilege from your table for all users. Create a SECURITY DEFINER function which is owned by table owner and which only returns data of session_user interest.

But remember to write SECURITY DEFINER Functions Safely.

While the above solutions all have their merits, I'd stress the advantages of creating a view.

You can grant a specific user or role permissions on the view but not on the table on which it is based, allowing to you determine exactly which columns of which tables they get access to, and what operations they can perform.

Your view can aggregate and join data before the users see it. You can also define "computed columns" that transform the data in known (and hopefully irreversible) ways to retain the integrity of the sensitive data and still give the users access to the information they need.

Caveat: Not being familiar with PostgreSQL's GIS functionality, this solution may be excluded due to things I am ignorant of.

I'm not aware of anything that will stop the user making any use they want of data obtained from a relational database, so you have no way inside the database to restrict onward use of delivered data.