I have a single PostgreSQL database with multiple schemata, each schema corresponding to a single town and its geodata. In every schema/town, there are the same type of geodata stored, and for every town, I have very similar QGIS project files that display each town in a similar fashion. For every schema, I have a distinct user/role in my PostgresSQL Database whose search path is limited to its corresponding schema.
I want all of those towns to be featured on the same QGIS Web Client landing page (index.php), so that a user can click on any of the towns name and find the corresponding geodata in the QWC GUI.
BUT: Using the search.wsgi and qwc_connect.py modules to allow users to search for addresses in every town, I want to restrict each towns own QWC-GUI to be able to only search within their corresponding schema. That was the reason I implemented the different schemata for different towns, and created distinct roles that can only access their corresponding schema. Otherwise, I feared, a user might query for a very common street name and get the result from an other town that is not the one he clicked on the landing page. In the '/wsgi/qwc_connect.py' -file, I have to specify a database connection. And as it presents itself, I can only specify a single database role to login to the DB:
#!/usr/bin/python # -*- coding: utf-8 -*- # Script to connect to a PostgreSQL database. Connection object to be used in all wasgi scripts import psycopg2 #PostgreSQL DB Connection # configure your DB connection here DB_CONN_STRING="host='localhost' dbname='mydb' port='5432' user='myrole' password='************'" def getConnection(environ, start_response): #SQL database connection try: conn = psycopg2.connect(DB_CONN_STRING) return conn except: errorText = 'error: database connection failed!' # write the error message to the error.log print >> environ['wsgi.errors'], "%s" % errorText response_headers = [('Content-type', 'text/plain'), ('Content-Length', str(len(errorText)))] start_response('500 INTERNAL SERVER ERROR', response_headers) return None
How I understood it, the variable 'searchtables' in the file '/wsgi/search.wsgi' will access a indexed table that is stored within the schema that was specified by the QGIS project file. So there is no need for editing code there, if I give the searchtables in every towns schema the same name, and restrict the access to each schema to the corresponding role (which is also the role used to establish the DB-connection the QGIS project file). But how do I specify multiple roles in qwc_connect.py, so that only the role that is currently used by the QGIS project file to access its own schema is also used for querying the database?
I was thinking about distinct qwc_connect.py files for every QGIS project file, but don't know how to link them. Nor do I know how to rewrite the code above to include multiple db-connections that become active depending on the QGIS project file from where the query originated. Can you show me how it could be done?
What I don't want is to create separate instances of QWC, or separate databases for every town. That would be an easy solution right now, but uncomfortable to manage in the future and difficult to maintain when more and more towns are added to the mix.