Thread: Making the DB secure
Hi,
we plan to make available our database from the internet (direct tcp/ip
based connections). We want to make it as secure as possible. There are a
few users who could access the database, but we want to block any other
users to access.
Our plans are:
- using encripted (ssl) connections - since sensitive (medical) personal
information are stored.
(How to setup this? What do we need on server side, and what on client
side?)
- using pg_hba.conf to configure authentication method and IP filters
- forcing our users to change their passwords frequently
- applying strong password policy (long pw, containing upper/lowercase
characters and numbers)
Could anybody suggest us something more valuable features in postgres to
improve the security?
Regarding SSL, I'd like to know how to use it correctly. What we have to do
on the server to accept ssl connections, and what kind of client softwares
are required.
Many thanks,
-- Csaba Együd
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
On Jun 17, 2005, at 8:49 AM, Együd Csaba wrote: > Hi, > we plan to make available our database from the internet (direct tcp/ip > based connections). We want to make it as secure as possible. There > are a > few users who could access the database, but we want to block any other > users to access. > > Our plans are: > - using encripted (ssl) connections - since sensitive (medical) > personal > information are stored. > (How to setup this? What do we need on server side, and what on > client > side?) > - using pg_hba.conf to configure authentication method and IP filters > - forcing our users to change their passwords frequently > - applying strong password policy (long pw, containing > upper/lowercase > characters and numbers) > > Could anybody suggest us something more valuable features in postgres > to > improve the security? > Regarding SSL, I'd like to know how to use it correctly. What we have > to do > on the server to accept ssl connections, and what kind of client > softwares > are required. > > Many thanks, > > -- Csaba Együd It sounds like you might want to think about hiring a consultant to help out here--what do others think? With medical information, this is not something you want to get wrong. Sean
Dear Sean, yes, maybe. But actually I thought some suggestions. The only real problem of mine is to configure the SSL. I am able configure the pg_hba.conf (at least I think so now), we have a firewal on the server too. I meant that maybe somebody can suggest something to make the thing more secure. Thank you, -- Csaba -----Original Message----- From: Sean Davis [mailto:sdavis2@mail.nih.gov] Sent: Friday, June 17, 2005 3:09 PM To: Együd Csaba Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Making the DB secure On Jun 17, 2005, at 8:49 AM, Együd Csaba wrote: > Hi, > we plan to make available our database from the internet (direct > tcp/ip based connections). We want to make it as secure as possible. > There are a few users who could access the database, but we want to > block any other users to access. > > Our plans are: > - using encripted (ssl) connections - since sensitive (medical) > personal information are stored. > (How to setup this? What do we need on server side, and what on > client > side?) > - using pg_hba.conf to configure authentication method and IP filters > - forcing our users to change their passwords frequently > - applying strong password policy (long pw, containing > upper/lowercase characters and numbers) > > Could anybody suggest us something more valuable features in postgres > to improve the security? > Regarding SSL, I'd like to know how to use it correctly. What we have > to do on the server to accept ssl connections, and what kind of client > softwares are required. > > Many thanks, > > -- Csaba Együd It sounds like you might want to think about hiring a consultant to help out here--what do others think? With medical information, this is not something you want to get wrong. Sean -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
Együd Csaba <csegyud@vnet.hu> writes: > Hi, > we plan to make available our database from the internet (direct tcp/ip > based connections). We want to make it as secure as possible. There are a > few users who could access the database, but we want to block any other > users to access. Honestly, I wouldn't do it this way unless I had no alternative. I'd build a middle-tier application layer that takes requests from the clients (via raw sockets, XML-RPC or whatever) and then translates those calls into SQL queries. It just gives you a lot more control over what queries hit your system. -Doug
Együd Csaba wrote: > Hi, > we plan to make available our database from the internet (direct tcp/ip > based connections). We want to make it as secure as possible. There are a > few users who could access the database, but we want to block any other > users to access. > > Our plans are: > - using encripted (ssl) connections - since sensitive (medical) personal > information are stored. > (How to setup this? What do we need on server side, and what on client > side?) > - using pg_hba.conf to configure authentication method and IP filters > - forcing our users to change their passwords frequently > - applying strong password policy (long pw, containing upper/lowercase > characters and numbers) > > Could anybody suggest us something more valuable features in postgres to > improve the security? > Regarding SSL, I'd like to know how to use it correctly. What we have to do > on the server to accept ssl connections, and what kind of client softwares > are required. It sounds to me like you plan to put the database server on the internet. I hope not. It should at the very least be in a dmz: database server <-> web server <-> firewall <-> internet -- Until later, Geoffrey
Együd Csaba wrote: > Dear Sean, > yes, maybe. > But actually I thought some suggestions. The only real problem of mine is to > configure the SSL. I am able configure the pg_hba.conf (at least I think so > now), we have a firewal on the server too. > > I meant that maybe somebody can suggest something to make the thing more > secure. If you want security, your database should be on one machine, your web server on another and all behind your firewall which would be yet another machine. I tend to agree with a Sean. If you don't know what you're doing, you should get a consultant who does. Production is not the place to learn how to secure a box. -- Until later, Geoffrey
On 06/17/2005 07:49:07 AM, Együd Csaba wrote:
> Hi,
> we plan to make available our database from the internet (direct
> tcp/ip
> based connections). We want to make it as secure as possible. There
> are a
> few users who could access the database, but we want to block any
> other
> users to access.
Disclamer: Yup, the other responders are right. You don't want to
make a mistake with security.
Rather than put your database on the net you'd be much better served
by using a VPN. IPSEC and OpenVPN come to mind. At the
very least use a ssh tunnel.
You want security, use a tool designed to give you security.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Hi,
thank you very much. These are very good ideas, I think.
I forgot one thing to mention. We will have very few clients (max. 20) and
all clients will be required to have a fix IP address. Fix IP addresses can
be listed in pg_hba.conf to filter incoming IPs very efficiently. With this
note, do you think we need VPN or other enhancement?
Bye,
-- Csaba
-----Original Message-----
From: Karl O. Pinc [mailto:kop@meme.com]
Sent: Saturday, June 18, 2005 12:23 AM
To: Együd Csaba
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Making the DB secure
On 06/17/2005 07:49:07 AM, Együd Csaba wrote:
> Hi,
> we plan to make available our database from the internet (direct
> tcp/ip based connections). We want to make it as secure as possible.
> There are a few users who could access the database, but we want to
> block any other users to access.
Disclamer: Yup, the other responders are right. You don't want to make a
mistake with security.
Rather than put your database on the net you'd be much better served by
using a VPN. IPSEC and OpenVPN come to mind. At the very least use a ssh
tunnel.
You want security, use a tool designed to give you security.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
t hub.org
X-Spam-Status: No, hits=05 tagged_above=required=tests=L, FORGED_RCVD_HELO
X-Spam-Level:
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org
X-Antivirus: AVG for E-mail 7.0.323 [267.7.7]
On 05/26/2005 06:08:00 PM, Devrim GUNDUZ wrote:
>
> Hi,
>
> AFAICS from the user requests, many people is not aware about the
> compatibility RPM we built:
>
> http://developer.PostgreSQL.org/~devrim/compat-postgresql-libs-3-2PGDG
> .i686.rpm
>
> is the compatibility RPM that fixes the problem which arose with
> PostgreSQL 8.0.2 . With 8.0.2, the major number of libpq was bumped,
> and all software that depends on libpq.so.3 failed/or needed to be
> recompiled.
I would like to compile this for my architecture from source.
Could you please tell me where the source rpm is?
(I can't seem to find a pointer to this rpm anywhere but in this email. Is
there a web page/ftp site?)
Thanks.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
On 6/17/05, Együd Csaba <csegyud@vnet.hu> wrote: > Hi, <snip> couple of things: 1. make sure postgresql is in a very tight chroot environment. without shell or anything else. 2. make sure you have list of all client ips, and use firewall to allow them, and only them to access postgresql port. all other connections should be rejected on firewall level, not application level!. 3. accept connections only through ssl (as for what you need - both client and server has to be build and linked agains openssl library). 4. if above is not possible - enforce usage of vpn. i would suggest openvpn - it's much easier to setup then ipsec based vpns, and yet it still holds all security measures. 5. define unique users inside of database for each real user. give them only the rights they need to have - not even a single "SELECT" more. 6. when possible - revoke select access to tables, grant select access to views 7. when possible - revoke all access to tables, grant execute access to functions which will be modifying database (just define them with "security definer" flag). 8. log all queries to files. with postgresql 8 it's easier then it ever was. dont log through syslog! 9. log all system activities - you can use grsecurity patches for extended auditing. 10. using grsecurity or selinux or lids or ... - block unnecessary accesses on system level (acls) 11. pray. this is what we do commercially, and as for now - it works great. best regards, depesz
On 06/20/2005 12:32:12 PM, Együd Csaba wrote:
> Hi,
> thank you very much. These are very good ideas, I think.
> I forgot one thing to mention. We will have very few clients (max. 20)
> and
> all clients will be required to have a fix IP address. Fix IP
> addresses can
> be listed in pg_hba.conf to filter incoming IPs very efficiently. With
> this
> note, do you think we need VPN or other enhancement?
You want to consider the failure modes. LANs can be
ARP spoofed to redirect traffic to elsewhere than their
destination IP, or what happens if the client does not request
encryption or the server is restarted without encryption
enabled, etc. You don't want to be allowing insecure
communication by accident and unawares. VPNs are
designed to disable communication
on failure, and the designers have presumably thought
of all the senarios. When you roll your own security
you need to be the one that thinks of everything.
(Of course, some VPN products are much less secure
than others.)
And maintaining things over time is always an issue.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Hi Depesz, thank you. Most of your suggestions are in our plans. We plan to grant only the necessary rights, filtering explicit IP addresses and so on. We are going to consider applying a VPN. Also we are going to consider logging all the querie executed by the server. You mentioned that pg8 provides some functions regarding this. Where to serch for more info? How thay name this feature? Many thanks, -- Csaba -----Original Message----- From: hubert depesz lubaczewski [mailto:depesz@gmail.com] Sent: Monday, June 20, 2005 7:58 PM To: Együd Csaba Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Making the DB secure On 6/17/05, Együd Csaba <csegyud@vnet.hu> wrote: > Hi, <snip> couple of things: 1. make sure postgresql is in a very tight chroot environment. without shell or anything else. 2. make sure you have list of all client ips, and use firewall to allow them, and only them to access postgresql port. all other connections should be rejected on firewall level, not application level!. 3. accept connections only through ssl (as for what you need - both client and server has to be build and linked agains openssl library). 4. if above is not possible - enforce usage of vpn. i would suggest openvpn - it's much easier to setup then ipsec based vpns, and yet it still holds all security measures. 5. define unique users inside of database for each real user. give them only the rights they need to have - not even a single "SELECT" more. 6. when possible - revoke select access to tables, grant select access to views 7. when possible - revoke all access to tables, grant execute access to functions which will be modifying database (just define them with "security definer" flag). 8. log all queries to files. with postgresql 8 it's easier then it ever was. dont log through syslog! 9. log all system activities - you can use grsecurity patches for extended auditing. 10. using grsecurity or selinux or lids or ... - block unnecessary accesses on system level (acls) 11. pray. this is what we do commercially, and as for now - it works great. best regards, depesz -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
Hi Karl,
OK, I see the point. We are going to look around the VPN. So as a
conclusion: can we state, that, in addition to all the security features
postgres provides, applying a VPN - with SSL and firewal - is enough to
provide the necessary security?
The server will be a Linux(??) based system. The clients will run Windows
XP.
Are Windows based clients able to cooperate with these kind of Linux
servers?
Thanks,
-- Csaba
-----Original Message-----
From: Karl O. Pinc [mailto:kop@meme.com]
Sent: Monday, June 20, 2005 8:51 PM
To: Együd Csaba
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Making the DB secure
On 06/20/2005 12:32:12 PM, Együd Csaba wrote:
> Hi,
> thank you very much. These are very good ideas, I think.
> I forgot one thing to mention. We will have very few clients (max. 20)
> and all clients will be required to have a fix IP address. Fix IP
> addresses can be listed in pg_hba.conf to filter incoming IPs very
> efficiently. With this note, do you think we need VPN or other
> enhancement?
You want to consider the failure modes. LANs can be ARP spoofed to redirect
traffic to elsewhere than their destination IP, or what happens if the
client does not request encryption or the server is restarted without
encryption enabled, etc. You don't want to be allowing insecure
communication by accident and unawares. VPNs are designed to disable
communication on failure, and the designers have presumably thought of all
the senarios. When you roll your own security you need to be the one that
thinks of everything.
(Of course, some VPN products are much less secure than others.)
And maintaining things over time is always an issue.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.7/20 - Release Date: 2005.06.16.
On 06/20/2005 01:45:48 PM, Együd Csaba wrote:
> Hi Karl,
> OK, I see the point. We are going to look around the VPN. So as a
> conclusion: can we state, that, in addition to all the security
> features
> postgres provides, applying a VPN - with SSL and firewal - is enough
> to
> provide the necessary security?
When it comes to security people are always the biggest problem.
The above should give you the necessary technical means to
call yourself "very secure".
> The server will be a Linux(??) based system. The clients will run
> Windows
> XP.
> Are Windows based clients able to cooperate with these kind of Linux
> servers?
Yes. We're getting off topic for the list here. I can't say
I know enough offhand to do more than blather, so I'll do that. :)
IIRC IPSec is built into the Linux kernel,
and in theory XP has a built-in client, but I believe the
client's interface is "unfriendly". If you find a good free IPSec
XP client please let me know. IIRC, OpenVPN has XP clients.
Your firewall may have clients. You could probably whip up
a cygwin based openssh client to tunnel your traffic, although
I don't know why you would given OpenVPN. There are many choices
(including non-VPN ones other's have mentioned, like mediating
database access via a web server.)
As a VPN by definition bypasses your firewall don't allow more
traffic than necessary through it.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Együd Csaba wrote: > Hi, > thank you very much. These are very good ideas, I think. > I forgot one thing to mention. We will have very few clients (max. 20) and > all clients will be required to have a fix IP address. Fix IP addresses can > be listed in pg_hba.conf to filter incoming IPs very efficiently. With this > note, do you think we need VPN or other enhancement? YOU NEED A SECURITY CONSULTANT. If you think you can rely on static ips as a security tool, you are clueless. I'm sorry, but the fact that you mentioned that this database contains medical information really disturbs me. A static IP insures NOTHING. A vpn will secure the connection and protect it. -- Until later, Geoffrey
Együd Csaba wrote: > Hi Karl, > OK, I see the point. We are going to look around the VPN. So as a > conclusion: can we state, that, in addition to all the security features > postgres provides, applying a VPN - with SSL and firewal - is enough to > provide the necessary security? No, they must be properly configured. > > The server will be a Linux(??) based system. The clients will run Windows > XP. > Are Windows based clients able to cooperate with these kind of Linux > servers? Yes. -- Until later, Geoffrey
Karl O. Pinc wrote: > > On 06/20/2005 01:45:48 PM, Együd Csaba wrote: > >> Hi Karl, >> OK, I see the point. We are going to look around the VPN. So as a >> conclusion: can we state, that, in addition to all the security >> features >> postgres provides, applying a VPN - with SSL and firewal - is enough >> to >> provide the necessary security?Együd Csaba <csegyud@vnet.hu> > > > When it comes to security people are always the biggest problem. > The above should give you the necessary technical means to > call yourself "very secure". This is not correct. If you purchase a 747, can you fly it? No, you must learn to fly. The biggest problem with security is mis-configured security. You can install all the security apps you want, but if you don't properly configure them, they are useless. Együd Csaba should pursue is security concerns on a security list, not a database list. -- Until later, Geoffrey
Hi All,
I am really sorry for upsetting the list. You are absolutelly right, I do
have applied a network specialist, who stated that the VPN is a must, and we
started to configure and test the server together (using OpenVPN).
Actually this application is going to operate in a test phase for months, so
we have the time to test securty issues.
Dear Geoffrey, please do not worry, we won't misapply any sensitive data.
BTW, I would like to close this thread on this list, because this is rather
off topic here. Thank you very much for opening up my eyes. I really thought
I can find it out alone. I wasn't right. Thanks for you all.
Certainly anybody is wellcome to write me in private.
Regards,
-- Csaba Együd.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Geoffrey
Sent: Tuesday, June 21, 2005 11:41 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Making the DB secure
Együd Csaba wrote:
> Hi,
> thank you very much. These are very good ideas, I think.
> I forgot one thing to mention. We will have very few clients (max. 20)
> and all clients will be required to have a fix IP address. Fix IP
> addresses can be listed in pg_hba.conf to filter incoming IPs very
> efficiently. With this note, do you think we need VPN or other
enhancement?
YOU NEED A SECURITY CONSULTANT. If you think you can rely on static ips as
a security tool, you are clueless. I'm sorry, but the fact that you
mentioned that this database contains medical information really disturbs
me.
A static IP insures NOTHING. A vpn will secure the connection and protect
it.
--
Until later, Geoffrey
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
sive
lock on the index's table, so until you roll back, no other transaction will
be able to touch the table at all. So the whole thing may be a nonstarter
in a production database anyway :-(. You can probably get away with
BEGIN;
DROP INDEX ...
EXPLAIN ...
ROLLBACK;
if you fire it from a script rather than by hand --- but EXPLAIN ANALYZE
might be a bad idea ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.10/25 - Release Date: 2005.06.21.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.10/25 - Release Date: 2005.06.21.