Limiting read access in postgres to few rows

Question

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any select query fired by a particular user.

Is this possible in PostgreSQL? If so, how can this be done?

Answer 1

You can define a function that returns required limit for some users. The function below set limit for users joe and ben.

Note that limit null is equivalent to limit all, so all other users will have no limits.

create or replace function set_limit()
returns integer language sql as $$
    select case current_user 
        when 'joe' then 100
        when 'ben' then 200
        else null
    end
$$;

select * from some_table limit set_limit();

Edit:

So basically we would like to append a limit clause to any select query fired by a particular user.

Once function set_limit() is created you can append limit set_limit() to any select query.

Answer 2

In PostgreSQL, when a user writes SELECT * FROM thetable, and thetable is a base table (not a view), it is not possible to restrict number of rows returned.

It is possible for UPDATE/INSERT/DELETE (with triggers and shared memory).