Home > Blogs > VMware vFabric Blog

Securing your vFabric PostgreSQL VM

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 /etc/ssh/sshd_config.

AllowGroups vfabric

2. If you really want to be more severe and restrict the access of ssh only to user postgres, add this line in /etc/ssh/sshd_config.

AllowUsers postgres

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 sshd_config.

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         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 pg_hba.conf.

host    all             postgres       md5
host    foo             all          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 -U postgres postgres
psql.bin (9.2.2)
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 -U userfoo postgres
psql: FATAL:  no pg_hba.conf entry for host "", user "userfoo", database
"postgres", SSL off

As expected, userfoo cannot connect to database postgres.

But he can do it for database foo.

>$ psql -h -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 GRANT and 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.

22 thoughts on “Securing your vFabric PostgreSQL VM

  1. Jacklyn

    It’s aсtually a cool and helpful piece
    of information. I’m glad tht you shared this useful info
    with us. Plеase stay us up to date lіke this. Thank youu for

    My homepage … hotels in pachmarhi, Jacklyn,

  2. baggage claim

    Nice weblog here! Also your web site quite a bit up fast!
    What web host are you the use of? Can I am getting your associate link in your host?

    I wish my site loaded up as quickly as yours

  3. kids and toddlers recipes

    The expert teachersalso moldyour child by training them
    to possess efficientcommunication skills. The
    main point to remember throughout, for increasing the profitability of running a day care
    business, is that you need maintain a strong base of kids.
    Nor do their jobs enable them to have enough income to attain quality affordable child care.

  4. Petra

    Amazing! I’m really enjoying the style
    and design of your site. Are yoou using a custom made theme or is this reeadily available to all
    users? If youu don’t want to sayy the name of it out in the
    general public, please make sure to e-mail me at:
    petra.knoll@gmail.com. I’d love to get my
    hands on this template! Many thanks.


Leave a Reply

Your email address will not be published. Required fields are marked *