Support non-local database

[karlcz]

Our current packaging and deploy mechanism defaults to and assumes a local web stack on a single server. It uses unix domain sockets between the web server and database, as well as scripts invoking psql to connect to the local database server.

The core system has some primitive but under-used support for a non-local database:

• The registry database connection string is stored in ermrest_config.json
• The per-catalog database connection string is stored in each row of the registry table

However, to make the software truly usable with multiple web server instances, e.g. for load-balancing, we need to fill in and test several gaps:

• [x] Ensure that non-local database connection strings actually work for registry and catalog connections
• [ ] Adjust the (re-)deployment process to honor connection strings for registry and catalog connections
• [x] Extend the catalog factory to allow creation of remote catalogs, e.g. interact with a remote database cluster for the database creation step and form the right kind of connection string for the new entry in the registry

Additional enhancements may also be worthwhile when refactoring any of this code:

• [ ] Allow for renaming of the registry DB so that multiple ermrest deployments MAY share a single database cluster
• [ ] Adjust the per-catalog name generation to make it easier to understand the mapping of backing databases to catalogs in a database cluster that might hold more than one ermrest deployment
• [ ] Allow for renaming the ermrest daemon database role so that multiple deployments can have privilege isolation even when sharing the same database cluster

[karlcz]

Updating this old issue with current (manually tested) status. A deployed cluster can be converted to a remote DB with adjustments to the postgres DSN strings in ermrest_config.json and a local ~ermrest/.pgpass file to hold credentials for the connection.

However, the ermrest-deploy script still assumes a local DB cluster and runs a number of psql commands via sudo. This whole process needs more thought. Presumably, the high privileges used during the deploy process should not be available to the normal web service host. So, perhaps the deployment process should remain a local process the DBA runs on the real DB cluster host? Or perhaps we should more clearly separate:

1. High privilege setup (usually done once) on the DB cluster
2. Maintenance setup (done repeatedly/idempotently) via connections as the ermrest role.

In a more complex setup, the maintenance steps might be run from a separate DBA workstation or other client machine, rather than the actual web service host. This holds true for optional cron jobs as well.