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.
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.
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.
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.
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.
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
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)
No comments yet.
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 Icingaweb Icingaweb2 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