Thread: Is PGSQL enough safe for internet?
Hi!
I need to suggest a DataBase server for my friend. It have Win2003
server machine.
He finished his web application (Python), but he need to do some remote
operations.
His project is inherited by another developer with some limitations :-(.
The user have an application.exe (Delphi) to define some informations,
make some decisions, and manage the products.
When the user finished with his job, he/she can upload the new elements
to the server.
The web application working in the server, it is a shop like thing.
But it don't have web admin interface. The admin interface realized with
the Delphi application, and with direct connection to the DB server.
So the shop admin need to upload the modifications, and it need to
download the statistics, and other informations from the DB to the
admin's machine.
This old project uses DBISAM. But the new developer want to port to PG,
because DBISAM server don't like Linux.
The web interface is done, but he need to determine, how to realize the
managing and the statistics making.
He don't want to rewrite the client application in web interface. It
want to solve only the download and upload problems.
In the old project these operations are use DBISAM Queries with direct
connections to the DB server.
The new developer want to make a simple solution, and first this can be
only a direct DB server connection. He asked me, which DB I suggest.
I know that Zeos can access PG DataBases from Delphi, but I don't know,
is PGSQL enough safe for opening it's main port to the net. It is a
dangerous thing (I think), because this PGSQL server serve more,
important DataBases which we don't want to publish.
Possible he can install an another pgsql service that can be opened to
the net and that can usable for ONLY THIS PROJECT. But I don't know,
that is possible or not; and how to upgrade later, if needed... :-(
Can I define in PGSQL, which DataBases can read from the localhost, and
which can from the net?
Can I separate these databases to decreasing the dangers? Not phisically
- in other way to hide the private DBs.
Can I use some functions in PGSQL to make some protecting rules? Like in
Mail Servers: if the IP is same and pwd is wrong in X times, the IP
blocked for N hours...
Last idea, if the port opening is not too good to convert
uploads/downloads to xml, and the client/server can read/write these
packages.
But this is hard to realize (convert all informations to same schema),
and it need to protect also...
Please help me (to I can help to my friend)!
Thanks for it!
dd
durumdara wrote: > > Can I define in PGSQL, which DataBases can read from the localhost, and > which can from the net? Yes. See "authentication" in the manuals for details on pg_hba.conf and also "grant connect". Only allow password-protected connection over an encrypted ssl channel. Only allow connection from a specified IP address. Add a rule to your firewall to only allow connection from that address too. > Can I separate these databases to decreasing the dangers? Not phisically > - in other way to hide the private DBs. You can't hide their existence, but you can restrict which users can connect. You can have multiple versions of PostgreSQL running - the default on port 5432 and another on e.g. port 5433. Lots of people do this, often so they can have different versions of PG installed on the same machine. > Can I use some functions in PGSQL to make some protecting rules? Like in > Mail Servers: if the IP is same and pwd is wrong in X times, the IP > blocked for N hours... Not that I know of. On linux there is a tool called fail2ban which will do this for you, but I don't know of anything similar for Windows. > Last idea, if the port opening is not too good to convert > uploads/downloads to xml, and the client/server can read/write these > packages. > But this is hard to realize (convert all informations to same schema), > and it need to protect also... Not sure that gains you anything. If you have a user specifically for upload/download purposes you can restrict what they can do. You could even have a separate set of import tables that they write to and then they call a function to transfer to the live tables. That function could do additional checking if you think it necessary. HTH -- Richard Huxton Archonet Ltd
On Wed, 18 Mar 2009 09:32:56 +0100 durumdara <durumdara@gmail.com> wrote: > Possible he can install an another pgsql service that can be > opened to the net and that can usable for ONLY THIS PROJECT. But I > don't know, that is possible or not; and how to upgrade later, if > needed... :-( If you can't use another DB... or you don't want to use another DB (this depends on how much separation/security you really think is suited...) you can configure postgresql to respond just to certain IP over SSL to access just certain DB even on a different port. http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html http://www.postgresql.org/docs/8.3/interactive/client-authentication.html > Can I use some functions in PGSQL to make some protecting rules? > Like in Mail Servers: if the IP is same and pwd is wrong in X > times, the IP blocked for N hours... I wouldn't recommend this approach. Someone may just close you out from your own home. Unless you're accessing the server from static IP and you can use white listing. -- Ivan Sergio Borgonovo http://www.webthatworks.it