Re: Restrict number of connections to specific table

On Tue, May 13, 2014 at 7:29 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> Sam Franklin, 13.05.2014 12:50:
> > Hi – apologies if this question has a painfully obvious answer. I
> > put the N in novice regarding postgresql.
> >
> > I use postgres with the spatial extension, postgis.
> >
> > I have some licenced geospatial data. The licence terms state that
> > only “one user can access the data at one time”.
> >
> > I want to add this data to the collection of spatially-enabled
> > postgis tables that I serve to my enterprise, where users access and
> > consume postgis data tables via GIS clients.
> >
> >
> > Can I put a constraint on a specific table to restrict the number of
> > concurrent connections to 1, which will allow me to adhere to the
> > data licencing terms?
> >
> > If not, is there an alternative solution to serve up the data via a
> > PostgreSQL table and still keep within the data licence terms?
>
> There is no configuration option if you mean that.
>
> One thing I can think of is to allow access to that table only through a
> function. Inside that function lock the table exclusively so that no other
> session can access it.
>
> The question then is: _when_ do you release this lock?
>
> If you release the lock after the select has finished, two users could
> query the data one after the other. But if the first user doesn't clear the
> results within the tool that is used to display the data you would still
> wind up with two users "accessing" the data at the same time.
>
> Thomas
>
>
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

Try looking into advisory locks. Use Thom's suggestion of only allowing
access to the table via a function, but have the call to that function use
an advisory lock so that it can only be called by a single session at a
time.

http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS
http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

Use the "try" functions to see if the lock is available. If it returns
false, you can have calls to the function either error out or exit cleanly.
Be aware that if you don't use the transaction level advisory locks, you
must explicitly release the advisory lock. Otherwise it will be held
forever.

Here's an example of how I've used it to control a function not being
allowed to run concurrently:
https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql#L39