How to create databases, users (roles) and install extensions in AWS RDS PostgreSQL

Written by - 0 comments

Published on - last updated on May 8th 2023 - Listed in AWS Cloud Postgres Databases


Managing databases and users (roles) in PostgreSQL on AWS RDS is slightly different than using a "normal" PostgreSQL installation, which uses the the local "postgres" user for DBA (database administration).

Here's a quick overview how to create a new PostgreSQL RDS instance, create a database, create database users (roles) and install extensions.

Create a new PostgreSQL RDS instance

Let's start with the obvious: Creating a new PostgreSQL RDS instance on AWS. Logged into the AWS console, search for and select the Amazon RDS service.

Choose the "Standard create" method, select the PostgreSQL engine and below select your preferred PostgreSQL version. For this example I chose the newest available one, PostgreSQL 15.2-R1.

Under "Templates" you can chose what kind of database instance this should be. Production offers high performance and availability options, Dev/Test is less performant but enough for application development and/or tests and the "Free tier" option allows you to play around with RDS for free - at least for a limited time and with very limited resources.

Depending on the template selected above, the "Availability and durability" options might be limited or disappear.

The "Settings" box below is very important. First enter the instance identifier. This can be the name of an application or in general a usage description. This name will be used in the FQDN/URL for the DB connection. After this you need to enter the "Master username", or database administrator user. By default this is set to postgres, but you can chose any username (except rdsadmin, this is reserved for AWS internal database management). And you should set a password to access the database instance once created.

RDS settings

Under "Instance configuration" and "Storage" the sizing of the PostgreSQL instance can be defined. Of course this heavily depends on the application. The "instance class" and the available storage have a major impact on monthly costs. These costs should not be underestimated, they go up very quickly if you want a decent performance and big storage. A helpful tool is the AWS Cost Calculator which gives some estimates about the prices, depending on the selected instance/sizes.

The last important setting is the "Connectivity" box, in which you need to decide whether to make the PostgreSQL database publicly available or only within your VPC.

Once all settings were selected/defined, the database can be created. This will take a couple of minutes. At the end the new PostgreSQL instance is listed under RDS -> Databases. With a click on it you get the endpoint URL, needed to connect to the database.

Connect to the PostgreSQL instance

You could now use the pgAdmin GUI to connect to the database using the public endpoint (URL) and the master user credentials, or, as I prefer, use the psql command. This command can be installed from the postgresql-client package. 

ck@mint ~ $ psql -h myuniquepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com -U postgres -W postgres
Password: **********
psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1), server 15.2)
WARNING: psql major version 12, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> 

This means we're logged in with the master user (postgres), currently connected to the postgres database.

Create a DB USER (ROLE)

Here's where we come to one of the slight differences PostgreSQL on AWS RDS vs. self-hosted PostgreSQL. On a self-hosted PostgreSQL you would most likely use the createuser command (executed as postgres user), this is the recommended way to create database users (roles) these days. But in AWS RDS, we create the database user through psql:

postgres=> CREATE ROLE appuser WITH PASSWORD 'verysecret' LOGIN;
CREATE ROLE

Note: I could have used CREATE USER, too, but that's just an alias for CREATE ROLE with an automatically added LOGIN capability. Better get used to the correct SQL command.

Now let's try to connect to the database using the just created role:

ck@mint ~ $ psql -h myuniquepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com -U appuser -W postgres
Password: **********
psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1), server 15.2)
WARNING: psql major version 12, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> 

Login works, great.

Now let's go ahead and create the database for the application.

Create a database

Let's create the database for the application (using the DBA master credentials). The database owner should be the previously created role "appuser":

postgres=> CREATE DATABASE application OWNER appuser;
ERROR:  must be member of role "appuser"

Oops.. Error? This is another difference you may encounter on an AWS RDS instance. This error wouldn't have shown up in a self-hosted PostgreSQL. The reason for the error is nicely explained on this Stackoverflow answer:

When using Amazon RDS, the user issuing the CREATE DATABASE must be a member of the role that will be the owner of the database.

This means, we first need to append the "role" appuser to our own DBA master user (postgres). The current database roles can be listed using \du inside psql:

postgres=> \du 
-----------------+------------------------------------------------------------+---------------------------------------
 postgres        | Create role, Create DB                    +| {rds_superuser}
                 | Password valid until infinity              |
 rds_ad          | Cannot login                               | {}
 rds_iam         | Cannot login                               | {}
 rds_password    | Cannot login                               | {}
 rds_replication | Cannot login                               | {}
 rds_superuser   | Cannot login                               | {pg_read_all_data,pg_write_all_data,pg_monitor,pg_signal_backend,pg_checkpoint,rds_replication,rds_password}
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                 | Password valid until infinity              |
 rdsrepladmin    | No inheritance, Cannot login, Replication  | {}
 rdstopmgr       |                                            | {pg_monitor,pg_checkpoint}
 appuser         |                                            | {}

Let's add the role "appuser" to our master user "postgres":

postgres=> GRANT appuser TO pgadmin;
GRANT ROLE

Checking the roles again, we can see that the postgres role now has two roles (rds_superuser and appuser):

postgres=> \du
-----------------+------------------------------------------------------------+---------------------------------------
 postgres        | Create role, Create DB                    +| {rds_superuser,visgispoc}
                 | Password valid until infinity              |
 rds_ad          | Cannot login                               | {}
 rds_iam         | Cannot login                               | {}
 rds_password    | Cannot login                               | {}
 rds_replication | Cannot login                               | {}
 rds_superuser   | Cannot login                               | {pg_read_all_data,pg_write_all_data,pg_monitor,pg_signal_backend,pg_checkpoint,rds_replication,rds_password}
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                 | Password valid until infinity              |
 rdsrepladmin    | No inheritance, Cannot login, Replication  | {}
 rdstopmgr       |                                            | {pg_monitor,pg_checkpoint}
 appuser         |                                            | {}

Creating the database should now work:

postgres=> CREATE DATABASE application OWNER appuser;
CREATE DATABASE

Let's check if the database was created:

postgres=> \l
                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+-----------+----------+-------------+-------------+-----------------------
 postgres  | pgadmin   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rdsadmin  | rdsadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin+
           |           |          |             |             | rdstopmgr=Tc/rdsadmin
 template0 | rdsadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +
           |           |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | pgadmin   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgadmin           +
           |           |          |             |             | pgadmin=CTc/pgadmin
 application | appuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

Yes, it's there! Now we make sure to grant all the privileges to the appuser (which should already be the case as its the db owner) but let's do this for the sake of completeness:

postgres=> GRANT ALL PRIVILEGES ON DATABASE application TO appuser;
GRANT

Install PostgreSQL extensions

Again as DBA/master user, PostgreSQL extensions can be installed through psql. In the following example, a couple of POSTGIS extensions are installed:

postgres=> CREATE EXTENSION postgis;
CREATE EXTENSION
postgres=> CREATE EXTENSION postgis_raster;
CREATE EXTENSION
postgres=> CREATE EXTENSION postgis_topology;
CREATE EXTENSION

The extensions can be listed in psql using the \dx sub-command:

postgres=> \dx
                                     List of installed extensions
       Name       | Version |   Schema   |                        Description                         
------------------+---------+------------+------------------------------------------------------
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis          | 3.3.2   | public     | PostGIS geometry and geography spatial types and functions
 postgis_raster   | 3.3.2   | public     | PostGIS raster types and functions
 postgis_topology | 3.3.2   | topology   | PostGIS topology spatial types and functions
(4 rows)

Note that PostgreSQL extensions need to be installed for each database, extensions are not installed "globally" and enabled on all databases.


Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder