I need advice, and a lot of it, from those with more experience, since I have practically none. Please, let me know your opinion on the following.
We have an in-house Postgres database that we would like to make publicly accessible via a password-less login (user: anonymous). (We already have a web front-end for this database, but we have had a lot of requests to allow programmatic access in a way that does not require scraping web pages; FWIW, web scraping of this site is already disallowed in our TOS.) The problem is how to make this database publicly accessible in a way that protects our server from abuse, and at the same time minimizes the amount of time we have to spend policing it. One possible solution (that I like) would be to require some sort of host registration[1] from potential users, so that password-less connection attempts from only these hosts would be allowed. The principal objective here would be to simplify the process of identifying and neutralizing any abuse to the system. My thought would be to set up a registration webpage page with safeguards to prevent robots from registering, maybe perform other checks (such as ensuring that the email address given is legit), and spool the registration requests for processing. (BTW, does anyone know of free software to do this?) Now, supposing we have a fresh batch of host registration requests that have passed all the filters we may impose on them (i.e. they have been "approved" somehow). How best to automate the process of granting access to these host? I suppose that the script/program in charge of this could, in principle, update the pg_hba.conf file, and bounce the server with a suitable "kill -HUP", but I'm queasy about such unsupervised bouncing of the server. I could use some words of wisdom on this topic. More generally, are we even on the right track here? Or is the whole idea of making our database publicly accessible totally foolish? Are there any examples that I may be able to learn from? We are specifically trying to avoid, at this initial stage at least, any solution that would require creating a proxy server for the sole purpose of authenticating and/or validating requests (e.g. ensuring that the request include a unique key, etc.) Our hope is that we may be able to craft a solution using only PostgreSQL's standard security facilities that would be adequate for at least the first several months of operation, if not much longer. Many thanks for your thoughts and opinions on this! kj [1] I realize that it may inconvenient for the anonymous users to be restricted to one or a few hosts, but the kinds of uses of our database that we consider legitimate should be possible even with this inconvenience. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq