Thread: Restrict number of connections to specific table
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?
Any help is much appreciated.
Cheers
Sam
Sam Franklin
GIS Manager
survey & geoengineering
27 Gay Street
Bath
BA1 2PD
United Kingdom
T: +44 1225 485800
F: +44 2075 048106
E: Sam.Franklin@senergyworld.com
| ||||||||
SPWLA | IPA | EAGE | ONS | SPE ATCE | PETEX | |||
The information contained in this message is confidential. If you have received this message in error please notify the originator, and remove it from your system, immediately. The unauthorised use, disclosure, copying or alteration of this message is strictly forbidden. This message has not been encrypted. It may therefore be liable to compromise. It is your responsibility to scan this message for viruses. Viruses and compromises of security are inherent risks in relation to email. To the extent permitted by law we do not accept any liability for any virus infection and/or external compromise of security in relation to transmissions by email. Senergy Holdings Limited and its subsidiaries reserves the right to monitor and record e-mail messages sent to and from this address for the purposes of investigating or detecting any unauthorised use of its system and ensuring its effective operation. |
Attachment
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 exclusivelyso 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 firstuser 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
Sam Franklin, 13.05.2014 12:50:> Hi – apologies if this question has a painfully obvious answer. IThere is no configuration option if you mean that.
> 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?
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@postgresql.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
https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql#L39
"Sam Franklin" <Sam.Franklin@senergyworld.com> writes: > I have some licenced geospatial data. The licence terms state that only > "one user can access the data at one time". > 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? Not on a specific table. You could possibly use a dedicated Postgres instance with max_connections set to 1. If you need to serialize access to just this table while allowing concurrent access to some other data in the same DB, then there's no built-in answer. regards, tom lane
Thomas, Keith and Tom - many thanks for your replies. Plenty of ideas to try. Best regards Sam -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: 13 May 2014 15:28 To: Sam Franklin Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Restrict number of connections to specific table "Sam Franklin" <Sam.Franklin@senergyworld.com> writes: > I have some licenced geospatial data. The licence terms state that > only "one user can access the data at one time". > 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? Not on a specific table. You could possibly use a dedicated Postgres instance with max_connections set to 1. If you need to serialize access to just this table while allowing concurrent access to some other data in the same DB, then there's no built-in answer. regards, tom lane
On Tue, May 13, 2014 at 10:27:38AM -0400, Tom Lane wrote: > "Sam Franklin" <Sam.Franklin@senergyworld.com> writes: > > I have some licenced geospatial data. The licence terms state that only > > "one user can access the data at one time". > > > 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? > > Not on a specific table. You could possibly use a dedicated Postgres > instance with max_connections set to 1. If you need to serialize access > to just this table while allowing concurrent access to some other data > in the same DB, then there's no built-in answer. Seems they could set the CONNECTION LIMIT to 1 on CREATE/ALTER DATABASE also, but as you said, it is per database. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +