Especially in today's world, security is top of mind for app developers, DBAs, and CIOs alike. One of the benefits that VMware strives to include in every product is a system of reasonable defaults for security. This generally means that users should expect a reasonably secure middleware application when they deploy a VMware app by default.
vFabric Postgres (vPostgres) is no different. There are not that many security settings in vFabric Postgres. However, there are a few things you can look at as options to further harden your deployment, and of course, the virtual machine that you are deploying them on, particularly if it is exposed to an external environment.
SSL Connection Restrictions
vFabric Postgres has as default users postgres and root, and both can connect to the virtual machine with SSL. If you want to restrict access to the virtual machine for certain users or a group of users, here is some advice to follow:
1. In order to restrict SSL connection only to the members of the group vFabric (the user postgres is a member of this group by default), add this line at in
2. If you really want to be more severe and restrict the access of ssh only to user postgres, add this line in
Now, only user postgres is able to connect with ssh.
You can of course do far more things like restricting the number of connections, blacklisting IPs, etc. Look at man ssh for more information and edit
Database Instance Security
Database connection security can be controlled directly with
pg_hba.conf, which is located in folder
/var/vmware/vpostgres/current/pgdata/. You can refer to the official documentation of PostgreSQL 9.2 for a detailed explanation of connection restriction settings in vFabric Postgres. However, there are a couple of recommended settings you should use inside your virtual database appliance.
As a default during initialization, only the database superuser postgres is available. We recommended you manage your database appliance with several layers of database users to have a good control of who can do what. If you don’t the scenarios can get ugly—let's imagine that you do not have layers of security and the user of a given application, who performs only SELECTs for a given application, has superuser rights. In this case, your application lacks in security. So, the database user doing only SELECT operations might also do an SQL injection and manipulate the complete database instance. Fortunately, vFabric Postgres provides the necessary tools to create secured layers of users.
If you look at the default security level used in vFabric Postgres, you will find the following line:
host all all 0.0.0.0/0 md5
This means that all the users performing an md5 authentication are able to connect to the database appliance from all IPs.
However you can be *more* restrictive. Let's add the following lines in
host all postgres 0.0.0.0/0 md5
host foo all 0.0.0.0/0 md5
The first line means that only the user postgres is able to connect to all the databases—this is with md5 and without any restriction of origin IPs. The second line means that all the users can connect to the database foo (useful if you want to create a database people can play with freely). If you want to load some settings modified within
pg_hba.conf, remember to restart the database server with something similar to that in the VM:
/opt/vmware/vpostgres/current/bin/pg_ctl restart -D /var/vmware/vpostgres/current/pgdata/
You can also reboot your machine entirely.
To extend this example, let's create the database foo with a new user userfoo who is able to login to the database.
psql -h 192.168.11.3 -U postgres postgres
Type "help" for help.
postgres=# create database foo;
postgres=# CREATE USER userfoo LOGIN;
Now, let’s test the connections for the new user.
$ psql -h 192.168.11.3 -U userfoo postgres
psql: FATAL: no pg_hba.conf entry for host "192.168.11.3", user "userfoo", database
"postgres", SSL off
As expected, userfoo cannot connect to database postgres.
But he can do it for database foo.
>$ psql -h 192.168.11.3 -U userfoo foo
Password for user userfoo:
psql (9.3devel, server 9.2.2)
Type "help" for help.
foo=> create table ab (a int);
Be sure to check the system catalog for
pg_roles—these summarize the current roles in your database appliance.
You can have better granularity control on permissions by using
REVOKE to allow or restrict SQL-specific operations for some users. It’s a great practice to ensure applications have really-well designed user controls.
Lastly, remember that once settings are done, you can always take a dump of the database with
pg_dump. So, you do not need to do that once again. Backups are your friends!
|About the Author: Michael Paquier is a member of PostgreSQL technical staff at VMware. He is involved for many years with community development of PostgreSQL and Postgres-XC, and has worked on multi-master database technologies. He has also interest in parallel query processing and concurrent SQL processing.|