Setup a new PostgreSQL database on AWS-RDS

postgresql aws aws-rds

There are some differences between self-hosted Postgres and an AWS-RDS instance. Show how to setup a new database with a separate “owner” user.

TL;DR

Delegating ownership of a database to a separate owner does not work the same way on RDS as on a local server. First: postgres is not a superuser. Second: RDS databases are only reachable via the network and must therefore deal with remote users. A local database which is not reachable via a network has a smaller attack surface by using only local users.

The following commands create a new database owned by a new user and restrict access to that user:

-- create DB, delegate ownership
create user foo with password 'foo' role postgres;
create database foo with owner foo;
\connect foo
-- tighten up DB and public schema
revoke all on database foo from public;
revoke all on schema public from public;
-- delegate ownership of public schema
alter schema public owner to foo;
-- optional: withdraw own rights
revoke foo from postgres;

These commands protect only the new database! The standard databases postgres and template1 are still unprotected! So execute these command once per RDS instance:

revoke all on database template1 from public;
revoke all on database postgres from public;

Long Story

A small project might use a self-hosted PostgreSQL on the same machine. By default PostgreSQL does not listen on public network interfaces - therefore only a small set of local users must be considered. On RDS that is exactly the opposite: There are only network connections and therefore all users are remote users – these should be separated more strictly.

An RDS instance which was created for one project is prone to be “reused” for other small projects. This also calls for strict user separation.

The simplest form of user separation is this: Each project has its own database1 and it own user. This user has complete control over the database and no access to anything else. Basically the DB-superuser delegates the management for a database to another user/project.

Of course this is a very simple kind of delegation! In important productive systems it is tradition to restrict the application to the DML stuff like INSERT, UPDATE, DELETE and forbid the DDL commands. On the other hand it is quite convenient to allow the application to migrate the schema from one version to next on its own account – YMMV.

Several defaults of PostgreSQL are not suitable for this scenario:

These points are addressed in the following sections. While the gist is the same for local PostgreSQL and RDS some local commands must be rephrased for RDS.

Restrict access to default databases

A local PostgreSQL cluster has by default the databases template0, template1 and postgres 3. An RDS instance has the additional database rdsadmin, but that one is already restricted.

These commands restrict access to template1 and postgres – only the owner (postgres) can connect:

revoke all on database template1 from public;
revoke all on database postgres from public;

The only remaining database template0 is “protected” by other means: Its entry in pg_database says datallowconn = false – therefore PostgreSQL rejects any connection outright.

These commands need to be executed only once per RDS instance.

Create a database with another owner

The simplest setup of a new database in a local Postgres cluster for some new project is like this:

$USER> sudo -u postgres psql
postgres=# create user foo with password 'foo';
CREATE ROLE
postgres=# create database foo owner foo;
CREATE DATABASE

Now you have a new database foo owned by a separate user foo. That user can setup the DB schema and the application can use the same credentials without distributing the the superuser credentials.

But that does not work on AWS-RDS:

ec2-user> psql -h database-1.XXX.XXX.rds.amazonaws.com postgres postgres
postgres=> create user foo with password 'foo';
CREATE ROLE
postgres=> create database foo owner foo;
ERROR:  must be member of role "foo"

The first faint hint is the different prompt in psql: It is =# on the local cluster but => on the RDS instance.

On RDS the database user postgres is not the superuser – the RDS User Guide describes that in a PostgreSQL specific section:

When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is a predefined Amazon RDS role similar to the PostgreSQL superuser role (customarily named postgres in local instances), but with some restrictions. As with the PostgreSQL superuser role, the rds_superuser role has the most privileges for your DB instance. AWS RDS User Guide – Creating Roles

But alas - who reads this kind of documentation upfront!

The roles on the local instance look like this:

postgres=# \du postgres
                       List of roles
 Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
 postgres  | Superuser, Create role, Create DB | {}

and like this on the RDS instance (showing all roles):

postgres=> \du
                                                               List of roles
    Role name    |                         Attributes                         |                          Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
 postgres        | Create role, Create DB                                    +| {rds_superuser}
                 | Password valid until infinity                              |
 rds_password    | Cannot login                                               | {}
 rds_replication | Cannot login                                               | {}
 rds_superuser   | Cannot login                                               | {pg_monitor,pg_signal_backend,rds_replication,rds_password}
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                 | Password valid until infinity                              |

So the only real superuser is rdsadmin but no other role is a member of that one and only AWS has its password. postgres has CREATEROLE and CREATEDB and inherits - via rds_superuser - some more like rds_password and pg_monitor (a system role not displayed here, use \duS to display).

But PostgreSQL itself considers the CREATEROLE as “almost-superuser”:

Be careful with the CREATEROLE privilege. There is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges). For example, if the role “user” has the CREATEROLE privilege but not the CREATEDB privilege, nonetheless it can create a new role with the CREATEDB privilege. Therefore, regard roles that have the CREATEROLE privilege as almost-superuser-roles. PostgreSQL - CREATE ROLE

And since the user postgres has the CREATEROLE privilege it is indeed quite powerful.

Back to business:

The magical incantation for RDS is this:

create user foo with password 'foo' role postgres;
create database foo with owner foo;
revoke foo from postgres;

The first command creates a new user. The clause role postgres immediately makes postgres a member of foo - it is a shortcut for these two commands:

create user foo with password 'foo';
grant foo to postgres;

Do not confuse the clause ROLE postgres with IN ROLE postgres – that reverses the direction and one makes foo a member of postgres! See CREATE ROLE for details.

This temporary role association is reverted by the third command but that’s optional.

Restrict the database

All this was “only” about creating a new database an delegating ownership of that database to a separate user. The next step is to revoke public access to that database in the same manner as above for the existing databases:

-- tighten up access to DB
revoke all on schema public from public;

Now the database itself is restricted and owner by the user. But that user does not own everything inside the database.

Hand over public schema

The public schema of a new database is owned by postgres. By default any user which can connect to the database is allowed to create stuff in the public schema. Currently only the owner of the database and postgres can connect – so the database is secure enough. But it is better to hand over the public schema to the database owner. After that the user can manage access to the database and the schema without the help postgres.

\connect foo
-- tighten up public schema
revoke all on schema public from public;
-- delegate ownership of public schema
alter schema public owner to foo;

After that the user foo is the king of the castle.

Summary

The network access of PostgreSQL on RDS requires some minimal security precautions – stuff that’s not bad for a local database, too. Most of the required commands can be copied verbatim from the local DB to RDS. The rest (creating a user and making postgres a member of that user) can be changed to work in both environments, too.

AWS RDS:

More on securing PostgreSQL on RDS:

PostgreSQL:

  1. Remember the official PostgreSQL terminology: a database cluster is the server process which manages multiple databases each containing multiple schemas each containing multiple tables and other stuff. ↩︎

  2. Therefore the owner cannot restrict access to that schema. ↩︎

  3. Generally: There is a database with the same name as the DB superuser. ↩︎