Thread: Opened ports vs. Packages...

Opened ports vs. Packages...

From
Durumdara
Date:
Hi!

I want to ask about PGSQL-crack/hack-web-database.

So:

We have a web site with python.  The DB engine is PGSQL.

The first theory was that PGSQL is locally used, and each of other operations are realized with encoded XML packages.

These operations are: fill up partner data, fill up offer data.

The client application is Delphi based. That was a little hard to convert local data (DataSets, etc) to XML, and recode in the server side (in python).

The new theory is need more flexibility: we can fillup more data and read some (long) query result.

The main problem that I need to open the PGSQL's port to the net with login possibility.

1.) Auth. - password trying.
The clients are access PGSQL by Zeos, or by PGDAC. I don't know what auth. methods they are support, but I think that md5 and plain text is not enough here...

2.) Opened port -  PGSQL hack/crack possibilty.
I don't know about PGSQL hack/crack on ports, but everything is possible. If they are hack the PGDB without knowing password (with some special code injection), we are in problem...

3.) Server overloading with DOS. (Example: many-many requests to login)

4.) Lost connections? How to handle when connection lost on wrong web, or temp. down?

If we don't want to open the port, we need to make a complete interface that know same thing as Zeos.

Zeos (as I know) supports data packages - the data loaded in packages, and if you scroll to the end of the table, you can get the next package.

We need to develop same thing to load all info we want.
Need to make an update mechanism to reflect changes, etc.

We need to create a mechanism that read Queries, process them, and rewrite the result packages...

And these infos are changing - because if I open a query with web server at X, and got a new request at Y to make next package, may the query data changes... 

For example:
select * from data
1.) A....D (select * from partner top 0 range 1000)
2.) C...F  (select * from partner top 1001 range 2000)

because between these requests we have a big modification... (in the webserver all request a new cursor).

This is hard work.

So... what is the magic we can use? And what you heard about opened ports/hack/connections?

Thanks:
   dd




Re: Opened ports vs. Packages...

From
Craig Ringer
Date:
On 3/05/2010 7:58 PM, Durumdara wrote:

> 1.) Auth. - password trying.
> The clients are access PGSQL by Zeos, or by PGDAC. I don't know what
> auth. methods they are support, but I think that md5 and plain text is
> not enough here...

Just make sure you have an ssl certificate for your server, and require
clients to validate it. Otherwise anyone malicious on the network path
(or able to steal packets - think wifi or arp spoofing) can steal the
password and/or your data.

You should configure the client to verify the server against a CA cert
or a pre-installed copy of the server cert. Unfortunately, by default
psql seems to accept self-signed certificates as trustworthy, but that's
really not a good idea.

> 2.) Opened port -  PGSQL hack/crack possibilty.
> I don't know about PGSQL hack/crack on ports, but everything is
> possible. If they are hack the PGDB without knowing password (with some
> special code injection), we are in problem...

Remote security holes are rarely reported for PostgreSQL. That doesn't
mean they aren't there.

> 3.) Server overloading with DOS. (Example: many-many requests to login)

Unless I'm badly mistaken PostgreSQL starts a backend to do auth, which
is an expensive process. A continuous load-based DoS would therefore
probably be very easy to do with relatively little bandwidth.

It's fairly easy with linux iptables and the like to make the database
port inaccessible to a particular IP until the application successfully
accesses some other more lightweight service. That might help if you're
really worried.

Personally I'm not bothering in my deployment. I run Pg on a
non-standard port and require ssl connections with an authenticated
*client* certificate, but don't worry too much about DoS. I don't have
the capacity to resist an even vaguely determined DoS anyway, and Pg
won't make that any worse.

> 4.) Lost connections? How to handle when connection lost on wrong web,
> or temp. down?

Use tcp keepalives to work around braindead nat routers. Set short
timeouts (see postgresql.conf tcp_ settings) so the server notices dead
connections quickly. Be robust in the face of dangling dead connections.

--
Craig Ringer