Обсуждение: Securing a remotely accessible PostgreSQL server

Hi All,
I am looking for suggestions on how best to secure a server that is
accessible via the internet. Even account creation for the database is
open to the world. Does anybody have any extra changes they would make
to postgresql.conf or OS changes they would suggest? Perhaps some
default permissions that would be best revoked?

The system setup is currently a Linux box running PostgreSQL 8.4
My pg_hba.conf already limits remote connections to one database and one
particular role.

I have been a bit hesitant to post this in the past as I believed many
would just give the answer of "Don't". Please just humor me and give
suggestions assuming it is the only way. Consider it a fun challenge.

Thanks for your help,
Josh

Josh <josh@saucetel.com> wrote:

> I am looking for suggestions on how best to secure a server that
> is accessible via the internet. Even account creation for the
> database is open to the world. Does anybody have any extra changes
> they would make to postgresql.conf or OS changes they would
> suggest? Perhaps some default permissions that would be best
> revoked?
>
> The system setup is currently a Linux box running PostgreSQL 8.4
> My pg_hba.conf already limits remote connections to one database
> and one particular role.

The role can create databases but not access them?  Odd.

In no particular order, these come to mind:

* Only allow SSL connections.

* Use a non-standard port, to obscure what the service is.

* Put the machine behind a firewall which only allows packets
through to the desired port.

* Make sure you *don't* run the database service as root.

* Make sure that the user which does run the database server doesn't
have access to anything more than it absolutely needs, directly or
through group membership.  (In particular, sudo rights should be
carefully limited or non-existent.)

* Turn on logging of connections and disconnections.  Save the logs
for a while.

* Limit permissions for the role to the bare minimum needed.  You
haven't told us enough to know what the would mean, exactly; but not
having rights to create functions in untrusted languages would be a
start.

* If you can limit the IP addresses which need to connect, do so (in
the firewall and/or pg_hba.conf).  Be prepared to block IP ranges
which are the source of attacks.

* Stay up-to-date on PostgreSQL minor releases and OS security
updates.

-Kevin

On Wed, Dec 22, 2010 at 3:30 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Josh <josh@saucetel.com> wrote:
>
>> I am looking for suggestions on how best to secure a server that
>> is accessible via the internet. Even account creation for the
>> database is open to the world. Does anybody have any extra changes
>> they would make to postgresql.conf or OS changes they would
>> suggest? Perhaps some default permissions that would be best
>> revoked?
>>
>> The system setup is currently a Linux box running PostgreSQL 8.4
>> My pg_hba.conf already limits remote connections to one database
>> and one particular role.
>
> The role can create databases but not access them?  Odd.
>
> In no particular order, these come to mind:
>
> * Only allow SSL connections.
>
> * Use a non-standard port, to obscure what the service is.
>
> * Put the machine behind a firewall which only allows packets
> through to the desired port.
>
> * Make sure you *don't* run the database service as root.
>
> * Make sure that the user which does run the database server doesn't
> have access to anything more than it absolutely needs, directly or
> through group membership.  (In particular, sudo rights should be
> carefully limited or non-existent.)

In fact, I'd chroot / jail the postgres server in this instance.  If
they get in, you just copy back over the chrooted directory and you're
up and running in minutes.

> Josh<josh@saucetel.com>  wrote:
>
> I am looking for suggestions on how best to secure a server that
> is accessible via the internet. Even account creation for the
> database is open to the world. Does anybody have any extra changes
> they would make to postgresql.conf or OS changes they would
> suggest? Perhaps some default permissions that would be best
> revoked?
>
> The system setup is currently a Linux box running PostgreSQL 8.4
> My pg_hba.conf already limits remote connections to one database
> and one particular role.

You don't give any details about your users or how/why they need this access so it's hard to give good advice.  But one
possibilityis to use SSH tunneling, so that your users have to log in to your server first using a protocol that's
prettysecure. 

    ssh -L5432:localhost:5432 user@host.com

Then the user connects locally instead of directly.  On the user's computer:

    psql -h localhost dbname

We've used this technique when a developer had to work from a remote location.  There is no direct access to Postgres
atall, yet you can work remotely and securely. 

Craig