No doubt that PostgreSQL (PG) privilege system is very flexible one but at the same time, due to the architectural differences between PG and Oracle, it is also substantially different from patterns used in Oracle. Considering myself as a total PostgreSQL newbie, I thought it would be a good start for me to write down some basic PG security approaches (patterns?) in a form of a blog article.
Of course, since I’m a PG newbie, don’t take anything you read here for granted and please, don’t blindly follow my recommendations without trying this on your own — I’m sure some things could be done differently, more efficient or more secure. If you think I’m dead wrong on something I said here, please contact us and I’ll certainly correct the content with proper attribution.

Here is a business scenario that I’ll be referencing during this short “how-to” tutorial.

Acme Ltd. was an Oracle customer for many years. Recent economic downturn caused a serious cut in yearly IT budged expenses. Top management decided to deploy all newly build applications on open and free software stack. PostgreSQL was first and only choice for database layer by DBA team, developers where less enthusiastic, so they asked DBA team to prepare some proof of concept working environment, where they can try out PG features and test security framework.

Separating development, test and production databases

We decided to create three databases in PG cluster: PG1 (dev), PG2 (test), PG3 (prod)

As superuser:

-- PG1 - development database 
CREATE DATABASE pg1
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'sl_SI.UTF8'
       LC_CTYPE = 'sl_SI.UTF8'
       CONNECTION LIMIT = 20;
       
REVOKE CONNECT, TEMPORARY ON DATABASE pg1 FROM public;

-- PG3 - test database 

CREATE DATABASE pg2
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'sl_SI.UTF8'
       LC_CTYPE = 'sl_SI.UTF8'
       CONNECTION LIMIT = 50;

REVOKE CONNECT, TEMPORARY ON DATABASE pg2 FROM public;

-- PG3 - production database 

CREATE DATABASE pg3
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'sl_SI.UTF8'
       LC_CTYPE = 'sl_SI.UTF8'
       CONNECTION LIMIT = 50;

REVOKE CONNECT, TEMPORARY ON DATABASE pg3 FROM public;

We think that default PG privileges are unreasonably generous on public schema.
So immediately after db creation we revoked privileges granted to PUBLIC schema. As superuser connect to each newly created database and revoke privileges:

$ psql -U alesk pg1

pg1=# REVOKE ALL ON SCHEMA public FROM public;

$ psql -U alesk pg2

pg2=# REVOKE ALL ON SCHEMA public FROM public;

$ psql -U alesk pg3

pg3=# REVOKE ALL ON SCHEMA public FROM public;

Separating core application objects from reporting and ETL processes

Schemas are perfectly suitable to achieve this goal. In each database we created three schemas, ACME (core application), ACMEREP (reporting), ACMELOAD (ETL stuff). Note that we deliberately specified postgres superuser as schema owner – for now – we’ll change this later on. We also created single demo table in each schema, solely for testing purposes.
Connect as superuser to each database (pg1, pg2, pg3) and run:

create schema acme authorization postgres;
create schema acmerep authorization postgres;
create schema acmeload authorization postgres;

create table acme.t1 (i int);
insert into acme.t1 values (42);

create table acmerep.t1 (i int);
insert into acmerep.t1 values (42);

create table acmeload.t1 (i int);
insert into acmeload.t1 values (42);

Manage roles and connections

In PG we have two kind of roles, login and nologin roles. Roles are global across PG cluster and thus shared by all databases in the cluster. Think about login roles as being users and nologin roles as being groups. With users we try to identify people (and/or applications) accessing our database and with groups we share permissions between people. It was not always like that, in PG versions before 8.1 there were two strictly separated entities; USER and GROUP, that’s why some old statements still work in PG 9.x, but it’s better to stick with the new terminology and syntax.
In this section we’ll create both kind of roles. Before PG allows user to connect to the database it checks pg_hba.conf file if there is a rule that approves user connection or not. PG is always processing rules in pg_hba.conf from top to bottom, the moment it finds satisfying rule it stops. So, it’s very important to carefully edit this file, because you can easily open the door to someone you don’t want to. When I first read about the concept behind pg_hba.conf I said, ok, it’s a simple and flexible way of controlling who can access the databases in PG cluster, but at the same time it’s also cumbersome and error prone to rely on DBA to properly edit configuration file for each new user account. There must be a better way and hopefully there is — we can manage connections to PG with standard permissions on database (granting “connect” role), pretty much like we’re used on Oracle.

Let’s create some roles. Connect to the PG as superuser…

For each database (pg1, pg2, pg3) create “special” group role that will control who can connect to particular database. We’ll add these roles to pg_hba.conf later on.

create role pg1_connect nologin;
create role pg2_connect nologin;
create role pg3_connect nologin;

grant connect on database pg1 to pg1_connect;
grant connect on database pg2 to pg2_connect;
grant connect on database pg3 to pg3_connect;
--

Now we should edit pg_hba.conf. Personally, I want to edit this file just once per database, not once for each user. Ideally, this file should not contain any users (login roles) at all, but we’ll make an exception when creating rule for webapp login and this is fine. As in Oracle I would like to administer rights to connect to the database exclusively from psql, granting/revoking “connect” role.

# TYPE  DATABASE        USER            ADDRESS               METHOD
host	pg1             +pg1_connect    0.0.0.0/0               md5
host    pg2             +pg2_connect    0.0.0.0/0               md5
host    pg3             +pg3_connect    0.0.0.0/0               md5
--

The plus sign in front of pg1_connect role is the crucial trick that I was not familiar with until I read blog post by Shaun Thomas [3]. It means that rule will match *any* role that is *directly* OR *indirectly* member of this role. Without + the rule would match only that specific role.

Now, we need to create some login roles for developers. We decided that we’ll keep Oracle kind of schema pattern, where application is installed in dedicated application schema. Developers who work together will also share same login role that’ll give them access to particular schema.
Let’s first create login roles according to our application schema’s (ACME, ACMEREP, ACMELOAD), as super user login to PG cluster:

create role acme login password 'acmepwd';
grant pg1_connect to acme;
grant pg2_connect to acme;

create role acmerep login password 'acmereppwd';
grant pg1_connect to acmerep;
grant pg2_connect to acmerep;

create role acmeload login password 'acmeloadpwd';
grant pg1_connect to acmeload;
grant pg2_connect to acmerep;

Note that by default we allowed developers to connect to development and test database by granting them both connect roles (pg1_connect, pg2_connect). Developers are not allowed to connect to production database pg3.

Next, we need to transfer ownership of applications schema’s from postgres to newly created login roles. Connect as superuser to each database (pg1, pg2, pg3) and execute:

alter schema acme owner to acme;
alter schema acmerep owner to acmerep;
alter schema acmeload owner to acmeload;

Since we already created test table t1 in each schema that is owned by postgres, we would like to change table ownership on t1 tables from postgres to application schema’s login roles as well. We can issue individual SQL statement:

alter table acme.t1 owner to acme;

…or better yet, we can generate statements from information schema meta data:

select 'ALTER TABLE '||table_schema||'.'||table_name
 ||' OWNER TO '|| CASE when table_schema = 'acme' then 'acme'
                       when table_schema = 'acmerep' then 'acmerep'
                       when table_schema = 'acmeload' then 'acmeload'
                       else table_schema
                   END ||';'
from information_schema.tables 
where table_schema in('acme','acmerep','acmeload');

which will result in:

ALTER TABLE acmeload.t1 OWNER TO acmeload;
ALTER TABLE acmerep.t1 OWNER TO acmerep;
ALTER TABLE acme.t1 OWNER TO acme;

At this point we have:

• three databases in PG cluster, separating development from test and production environment
• we removed default privileges on database from public as well as those granted on public schema
• in each database we created three schema’s to separate three distinct application modules
• we deliberately created login roles with the same name as application schema’s to resemble Oracle pattern of work for developers
• we transferred ownership on application schema’s to login roles dedicated to developers working together on particular application module (acme, acmerep or acmeload)
• we’re controlling connection privileges to the databases with pg_connect roles, rather than by editing pg_hba.conf file

So far so good, but there are still things to configure. We need to create dba account that’ll have enough privileges to manage all three databases at database level. Web application will also need special (limited) account with privileges on acme and acmerep schemas on PG2 and PG3 databases. Access from application server should be restricted to specific IP’s.
Another thing that needs to be done is limiting superusers connectivity. In production environment it’s a crucial protection step to disallow superuser’s to connect to PG cluster over the network — if superuser needs to do something on PG cluster she/he must first connect to server either with ssh or locally.

Setup roles for DBA and web application

Let’s begin with application “dba” login role. We have user Jack who is responsible for managing databases pg1-pg3.
As DBA he sometimes needs to create his own procedures, tables etc., that’s why he also needs his own schema. First login to PG cluster as superuser and execute:

create role pg1_dba nologin;
create role pg2_dba nologin;
create role pg3_dba nologin;

create role jack with login password 'jackpwd';
alter role jack set search_path to jack,public;

grant pg1_connect, pg2_connect, pg3_connect to jack;

-- give Jack ownership rights on application schema's
-- by granting him roles that're owners of those schema's!
grant acme, acmerep, acmeload to jack;

-- connect to each database (pg1, pg2, pg3) as superuser and run
create schema jack authorization jack;

Notice that we granted three existing application login roles (acme, acmerep and acmeload)
to Jack, thus allowing him to manage all objects that’ll be created by developers in application schemas using those three login roles. He can not create new databases and/or roles – these privileges remain in the domain of superusers. Finally, he has his own schema to keep his own objects away from the others.

Production application server is allowed to connect to test (pg2) and production (pg3) databases from specific IP only. Application uses data from schema acme with certain read/write privileges on schema acme and read-only privileges on schema acmerep. Total number of connections for webapp user should be limited to 20. Connect as superuser to PG cluster and run:

create role webapp login password 'webapppwd' connection limit 20;
grant pg2_connect, pg3_connect to webapp;

create role pg2_acme_edit nologin;
create role pg2_acmerep_read nologin;

create role pg3_acme_edit nologin;
create role pg3_acmerep_read nologin;

grant pg2_acme_edit, pg2_acmerep_read to webapp;
grant pg3_acme_edit, pg3_acmerep_read to webapp;
--

At this point webapp user has been granted roles with necessary privileges on objects in both schema’s, acme and acmerep. For now it’s just a single table, but additional privileges will be granted to edit/read roles by developers later. However, before webapp login role can access data in both schemas it needs one additional privilege: USAGE on both schema’s. We decided to create separate role just for this privilege. This’ll allows us to easily remove access to all objects in application schema’s with single revoke statement.

-- as superuser create nologin usage role in pg2 and pg3,
-- assuming that we

create role pg2_webapp_usage nologin;
create role pg3_webapp_usage nologin;

\c pg2 

grant usage on schema acme to pg2_webapp_usage;
grant usage on schema acmerep to pg2_webapp_usage;

\c pg3

grant usage on schema acme to pg3_webapp_usage;
grant usage on schema acmerep to pg3_webapp_usage;
--

To allow webapp login role to access application schema’s on test and production databases:

grant pg2_webapp_usage to webapp;

grant pg3_webapp_usage to webapp;
--

Last but not least, we want to control from which IP’s can webapp user connect to the database. At the same time, we’ll configure pg_hba.conf in a way that’ll prevent superusers to connect to PG cluster from network.

Let’s start with the superuser. We’ll use the same trick as before, combining PG role with (+) entry in the pg_hba.conf. Connect to PG cluster with superuser and create special role wheel.

create role wheel nologin;

-- grant this role to every superuser
grant wheel to alesk, postgres;

…then all that we need is to allow local connection for wheel members and prevent connections from network by editing pg_hba.conf file:

# TYPE  DATABASE        USER           ADDRESS              METHOD
local   all             +wheel                                md5
host    all             +wheel         0.0.0.0/0              reject

Securing connection by webapp user is done in a similar way, with the help from pg_hba.conf.

host    pg1             webapp          10.10.10.11/24            md5
host    pg2,pg3         webapp          10.10.10.10/24            md5
host    all             webapp          0.0.0.0/0                 reject

We’re allowing connection to pg2,pg3 databases with user webapp only from 10.10.10.10/24. Webapp can also connect to pg1 from 10.10.10.11 which is application server dedicated to development. From all other IP’s connection attempts will be rejected.
Content of pg_hba.conf file on my laptop is shown in the following picture, where I grouped together different sections.

Note that the line using “trust” is there only for my own convenience when playing with PG on my laptop. This line should be removed from config file on production PG cluster!

I’ll end my “article” with pictures showing the end result of above steps on my laptop in pgAdmin:

To recap:

• We have three separate databases for development, test and production with restricted permissions on schema public.
• In each database we created three schema’s dedicated to application.
• Each application schema is owned by login role with the same name. This login role is shared by all developers working on that particular module/schema.
• Application DBA for AcmeApp has rights on all three application schema’s, but otherwise lacks any superuser rights. Oracle variant of DBA (SYSDBA) is reserved for superusers.
• Superusers can’t work over the network, they can login locally only.
• We’re managing rights to connect to the particular database with database “connect” roles, rather than by tweaking pg_hba.conf for each user.
• Web application server can connect to PG cluster from restricted IP’s only.

There’re still things to be done to enhance PG cluster security, but that’s something I’ll leave for some post in the future — after all, I’m still in the early learning stage…:)

References:

• [1] PostgreSQL Official Documentation”
• [2] Securing your PostgreSQL database
• [3] Make pg_hba.conf Redundant by Using pg_hba.conf
• [4] Total security in a PostgreSQL database
• [5] Managing rights in PostgreSQL