3

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?

Improve this question
  • 1
    Only way i know is force user to use views. create view test as select * from table limit 100 now, default search_path is $user,public. you could create $user schema ( where $user is username) and create views there with limit and remove users access to other schemas (public) – simplexio Jun 10 '14 at 11:39
  • How users access DB, Is it through a application layer like php... etc? – vedic Jun 13 '14 at 17:33
  • 1
    This will be possible with the upcoming 9.5 through "row level security": postgresql.org/docs/9.5/static/sql-createpolicy.html – a_horse_with_no_name Jul 20 '15 at 20:51
2

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.

Improve this answer
  • Thanks for the reply, but in this case isn't it up to the user to call that function in their query. They are still free to select all without using the function. I want to force the limiting without the need for any intervention from the person executing the query. – user38729 Jun 10 '14 at 12:39
  • 1
    You cannot limit a user if he can execute unrestricted queries. If you are going to restrict users, queries should be done in your application. – klin Jun 10 '14 at 12:49
  • 3
    @klin You definitely can. Our application users are only able to call functions, they have no direct access to any table. The functions are defined with SECURITY DEFINER by a suitable user. – dezso Jul 13 '14 at 11:16
  • @dezso - "Our application users are only able to call functions" - so they cannot execute unrestricted queries. – klin May 23 '20 at 18:41
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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.