PostgreSQL security

PostgreSQL has a flexible permissions system, with the ability to assign specific privileges to specific roles, and assign users to one or more of those roles. In addition, as the PostgreSQL server supports a number of methods for authenticating users, the database can use the same authentication infrastructure as other system components. This helps reduces the maintenance overhead by simplifying password management.

Users and roles

Instead of creating users and individually granting the necessary access rights to each user, a simpler solution is to define a number of database roles with the requisite permissions and then assign users to those roles. When new users are created, they can be assigned to existing roles. When users are deleted from the database, the roles will remain.

Creating roles

Conceptually, a role is a user and a user is a role. The only difference is that a user is a role with the login privilege. Functionally, the two SQL statements below are the same—they both create a role with the login privilege or in other words, a user.

CREATE ROLE user1 LOGIN;

CREATE USER user1;

Read-only users

For users who only need to query but not update or delete data, read-only access to the database can be granted. For example, if a web publishing application requires access to the nyc_streets table, read-only access to that specific table can be granted. The applications could also inherit the necessary system access for PostGIS operations from a postgis_reader role.

The following example will create a new user app1 with SELECT privileges on the nyc_streets table and assign the user to the postgis_reader role.

CREATE USER app1;

GRANT SELECT ON nyc_streets TO app1;

CREATE ROLE postgis_reader INHERIT;

GRANT postgis_reader TO app1;

When the user app1 logs in to the database, they can now select rows from the nyc_streets table.

SELECT * FROM nyc_streets LIMIT 1;

However, the app1 user cannot run an ST_Transform command.

SELECT ST_AsText(ST_Transform(geom, 4326))
  FROM nyc_streets LIMIT 1;
ERROR:  permission denied for relation spatial_ref_sys
CONTEXT:  SQL statement "SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1"

Although the app1 user can view the contents of the nyc_streets table, they do not have permission to view the contents of spatial_ref_sys, so executing the ST_Transform command fails.

To resolve this, grant SELECT privilege on all the PostGIS OpenGIS metadata objects to the postgis_reader role as follows:

GRANT SELECT ON geometry_columns TO postgis_reader;
GRANT SELECT ON geography_columns TO postgis_reader;
GRANT SELECT ON spatial_ref_sys TO postgis_reader;

The postgis_reader role can be assigned to any user who needs read access to the PostGIS metadata tables.

Read-write users

There are two kinds of read/write scenarios to consider:

  • Web-based and other applications that need to modify existing data or create new data.
  • Developers or analysts who need to create new tables and geometry columns.

For web applications that require write access to data tables, simply grant the extra permissions (INSERT, UPDATE, and DELETE) to the specific tables themselves, and continue to use the postgis_reader role.

GRANT INSERT,UPDATE,DELETE ON nyc_streets TO app1;

Note

These database access privileges would be required for a read/write WFS service.

For developers and analysts, a postgis_writer role with read/write access to the PostGIS metadata objects, is required. This new role should inherit the access rights already assigned to the postgis_reader role and have additional INSERT, UPDATE, and DELETE privileges granted on the metadata objects.

CREATE ROLE postgis_writer;

GRANT postgis_reader TO postgis_writer;

GRANT INSERT,UPDATE,DELETE ON spatial_ref_sys TO postgis_writer;

GRANT postgis_writer TO app1;

Encryption

PostgreSQL provides a number of encryption facilities. Some of these facilities are enabled by default, while others are optional.

All passwords are MD5 encrypted by default. The client/server handshake double encrypts the MD5 password to prevent re-use of the hash by anyone who intercepts the password. SSL connections (Secure Sockets Layer) are optionally available between the client and server, to encrypt all data and login information. SSL certificate authentication is also available when SSL connections are used.

Database columns can be encrypted using the pgcrypto module, which includes hashing algorithms, direct ciphers (blowfish, aes) and both public key and symmetric PGP encryption.

SSL connections

To use SSL connections, both your client and server must support SSL. Boundless Server provides PostgreSQL with SSL support, but SSL is not enabled by default. To enable SLL support:

  1. Shut down the PostgreSQL service.
  2. Acquire or generate an SSL certificate and key. The certificate must not include a passphrase otherwise the database server won’t be able to start up. To generate a self-signed key, enter the following:
# Create a new certificate, completing the certification info as prompted
openssl req -new -text -out server.req

# Strip the passphrase from the certificate
openssl rsa -in privkey.pem -out server.key

# Convert the certificate into a self-signed cert
openssl req -x509 -in server.req -text -key server.key -out server.crt

# Set the permission of the key to private read/write
chmod og-rwx server.key
  1. Copy the server.crt and server.key into the Boundless Server PostgreSQL installation folder’s data directory (pgdata).

  2. Enable SSL support in the postgresql.conf file and set the ssl Value to on.

    ../../_images/ssl_conf.png

    Enabling SSL support

  3. Restart the PostgreSQL service to activate support for SSL.

  4. To add an encrypted server connection, on the pgAdmin main menu click File and click Add Server to open the New Server Registration dialog box.

  5. Enter the server properties and click the SSL tab.

  6. Set the SSL parameter to require and click OK to create the connection.

../../_images/ssl_create.png

Setting the SSL parameter in pgAdmin

Once you connect to the database using the new connection, check the connection properties to confirm SSL encryption is used.

../../_images/ssl_props.png

SSL-encrypted connection

Since the default SSL connection mode is prefer, you don’t have to specify an SSL preference when connecting. A connection made using the command line psql tool will read the SSL option and use it by default:

psql (8.4.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

Data encryption

There are many encryption options available with the pgcrypto module. One of the simplest examples is encrypting a column of data using a symmetric cipher. To set this up, complete the following steps:

  1. Enable pgcrypto by loading the pgcrypto.sql file, either using pgAdmin or psql.
pgsql/9.1/share/postgresql/contrib/pgcrypto.sql
  1. Test the encryption function.
-- encrypt a string using blowfish (bf)
SELECT encrypt('this is a test phrase', 'mykey', 'bf');
  1. Ensure the encryption is reversible.
-- round-trip a string using blowfish (bf)
SELECT decrypt(encrypt('this is a test phrase', 'mykey', 'bf'), 'mykey', 'bf');

Authentication

PostgreSQL supports a number of authentication methods, to allow easy integration into existing enterprise architectures. In production systems, the following methods are commonly used:

  • Password—Passwords are stored by the database with MD5 encryption
  • Kerberos—Enterprise authentication method used by both the GSSAPI and SSPI schemes in PostgreSQL. With SSPI, PostgreSQL can authenticate against Windows servers.
  • LDAP—Common enterprise authentication method. The OpenLDAP server bundled with most Linux distributions provides an open source implementation of LDAP.
  • Certificate—Works with client connections made via SSL (assumes clients can manage the distribution of keys)
  • PAM—Supports Linux or Solaris PAM scheme for transparent authentication provision

Authentication methods are controlled by the pg_hba.conf file. The hba in the file name stands for host based access, as in addition to allowing you to specify the authentication method to use for each database, it allows you to limit host access using network addresses.

To edit the settings in the pg_hba.conf file, on the pgAdmin main menu click File and click Open pg_hba.conf to open the file in the Backend Access Configuration Editor.

../../_images/pg_hba.png

Accessing the pg_hba.conf file

The pg_hba.conf file includes the following:

  • Type—Determines the type of access, either local for connections from the same server or host for remote connections
  • Database—Which database the access rule refers to or “all” for all databases
  • User—Which users the access rule refers to or “all” for all users
  • IP-Address—Network limitations for remote connections using network/netmask syntax
  • Method—Authentication protocol to use. Trust skips authentication entirely and simply accepts any valid user name without challenge.
  • Option—Specifies options for the selected authentication method

Generally local connections are trusted, since access to the server itself is usually privileged. Remote connections are disabled by default when PostgreSQL is installed. If you want to connect from remote machines, you must add the appropriate entry to the file.

To add a new entry, double-click the last empty row in the list of entries to open the Client Access Configuration dialog box.

../../_images/pg_hba_new.png

Adding a new remote access entry

This new entry is an example of a remote access connection, allowing LDAP authenticated access only to machines on the local network (in this case the 192.168.1. network) and only to the nyc database.

Implementing the various authentication rules in a production system will largely depend on the security requirements of your network.