PostgreSQL security¶

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;

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;

SSL connections¶

To use SSL connections, both your client and server must support SSL. OpenGeo Suite 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

3. Copy the server.crt and server.key into the OpenGeo Suite PostgreSQL installation folder’s data directory (pgdata).

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

   

   Enabling SSL support

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

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

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

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

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.

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

2. Test the encryption function.

-- encrypt a string using blowfish (bf)
SELECT encrypt('this is a test phrase', 'mykey', 'bf');

3. Ensure the encryption is reversible.

-- round-trip a string using blowfish (bf)
SELECT decrypt(encrypt('this is a test phrase', 'mykey', 'bf'), 'mykey', 'bf');