Grant access to subset of table to user on PostgreSQL

Question

I know that I can use views to grant access to a subset of attributes in a table. But how can I grant access to particular tuples only? Say I have a table of registered students, a username attribute and then some other like degree_status, how do I grant access so that user A can only select from the table a tuple corresponding to username A ? I have a database exam and I'm studying some past papers and I came across this question but I don't know how to answer it and I cant find how to do it from my book "Dtabase System: A practical Approach to Database Design, Implementation and Management'

Thanks any help is much appreciated!

Matt

I've been doing some research for some stuff I'm thinking of doing and came across http://archives.postgresql.org/pgsql-hackers/2009-10/msg01346.php ; 'tis a pretty interesting point to add to the answers below... — alex, Feb 04 '12 at 10:30

score 3 · Accepted Answer · answered May 14 '11 at 21:18

3

Say that you got :

Table items (item_id, ...)
Table users (user_id, ...)
Table users_permissions( user_id, item_id, perm_type )

You could create a VIEW like this :

SELECT i.*, p.perm_type 
FROM items JOIN users_permissions USING (item_id) 
WHERE user_id = get_current_user_id();

Users can select from this view but not remove the WHERE and JOIN restricting the permissions.

The get_current_user_id() function is likely to be the major problem ;)

answered May 14 '11 at 21:18

bobflux

11,123
3
27
27

1

Should be easy if every "real" user does use a different Postgres user account, then `get_current_user_id()` is simply `current_user` – May 14 '11 at 22:13
Yeah. is a PITA, but on the other hand, it's the only way to have the DB enforce permissions. Having a zillion DB users is the major problem. If you have 10 users, go for it. – bobflux May 14 '11 at 22:42
Thanks this is exactly what I needed. I didn't know about this function, very userful :) – matthewbpt May 15 '11 at 07:42

score 2 · Answer 2 · answered May 14 '11 at 21:58

2

Along the lines of peufeu's answer, in Postgresql the current user name is available through the function current_user. So a view

CREATE VIEW available_bigtable AS
SELECT * FROM bigtable
WHERE username = current_user;

looks like it does what you need. Grant SELECT to everyone on the view, but to no one (except admins) on the underlying bigtable.

answered May 14 '11 at 21:58

Andrew Lazarus

18,205
3
35
53

Very useful thanks for the help, this current_user function is exactly what I need :) – matthewbpt May 15 '11 at 07:43
1

The 'Accept' button must be there somewhere. :) – Andrew Lazarus May 15 '11 at 21:53

score 1 · Answer 3 · answered May 14 '11 at 23:08

1

The Veil project provides a framework for row-level access control in PostgreSQL.

answered May 14 '11 at 23:08

Peter Eisentraut

35,221
12
85
90

shesek · Answer 4 · 2012-11-25T12:35:44.420

How about creating a function that takes the user id and returns the subset of rows he has access to?

CREATE FUNCTION user_items(integer) RETURNS SETOF items AS $$
    SELECT * FROM items WHERE user_id = $1
$$ LANGUAGE SQL;

SELECT * FROM user_items(55); # 55 being the user id

edit Thinking about it more, this could cause quite a performance hit, as the user_id condition would be applied to the whole data set, prior to any other "user-land" conditions.

For example, SELECT * FROM user_items(55) WHERE id=45 would first filter the entire table for user items, and only than find the ID on that subset.

With views, the query planner can decide on the optimal order to evaluate the conditions (where he'll probably filter for the ID first, than for user id). When using a function like I suggested, postgres can't do that.

Grant access to subset of table to user on PostgreSQL

4 Answers4

Linked