In the last tow posts we had a look at DB Parameter Groups and Subnet Groups as these need to be ready when you want to deploy a RDS PostgreSQL instance on AWS. In this post we’ll use these two building blocks to bring up a high available PostgreSQL instance using a master instance in one, and a replica in another availability zone. This is usually what you want when you want a production deployment in AWS.
As usual, with the AWS console, creating a new database is quite easy:
In our case we want to go for PostgreSQL:
The latest version as of today is 12.2, we want to go for production and we need to provide a name:
We sill stick with the default for the master username as that is postgres usually anyway:
We’ll go with the defaults for the storage section as well:
For production deployments you usually want to go for one or more standby instances. For the scope of this little demo it is not required:
Now it comes to the connectivity and this is where we need the subnet group we created in the last post:
For the security I’ve chosen my default which also allows incoming traffic to the standard PostgreSQL port 5432. We’ll let the default for authentication:
.. but will use the DB Parameter Group we’ve created in the first post:
We’ll keep the defaults for the remaining sections (backup, monitoring, encryption) and will create the RDS PostgreSQL instance:
The deployment will take some minutes and once it is ready you get an overview in the console:
If you are looking for the reference to the DB Parameter Group you can find that in the “Configuration” section:
From now on we can use psql to connect to our PostgreSQL instance:
[ec2-user@ip-10-0-1-51 ~]$ psql -h dwe-pg.xxx.eu-central-1.rds.amazonaws.com -p 5432 -U postgres postgres
psql (12.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=> select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)
As you already can see the connection is encrypted and this is because AWS configured PostgreSQL for SSL out of the box and we saw that already when we created the DB Parameter Group:
postgres=> show ssl;
ssl
-----
on
(1 row)
postgres=> show ssl_cert_file ;
ssl_cert_file
-----------------------------------------
/rdsdbdata/rds-metadata/server-cert.pem
(1 row)
postgres=> show ssl_key_file ;
ssl_key_file
----------------------------------------
/rdsdbdata/rds-metadata/server-key.pem
(1 row)
As we are now using a managed service, do not expect that you can use PostgreSQL as you can do that when you install for your own:
postgres=> select pg_ls_dir('.');
ERROR: permission denied for function pg_ls_dir
postgres=> \du+
List of roles
Role name | Attributes | Member of | Description
---------------------------+------------------------------------------------+--------------------------------------------------------------+-------------
pg_execute_server_program | Cannot login | {} |
pg_monitor | Cannot login | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} |
pg_read_all_settings | Cannot login | {} |
pg_read_all_stats | Cannot login | {} |
pg_read_server_files | Cannot login | {} |
pg_signal_backend | Cannot login | {} |
pg_stat_scan_tables | Cannot login | {} |
pg_write_server_files | Cannot login | {} |
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_monitor,pg_signal_backend,rds_replication,rds_password} |
rdsadmin | Superuser, Create role, Create DB, Replication+| {} |
| Password valid until infinity | |
rdsrepladmin | No inheritance, Cannot login, Replication | {} |
Our “postgres” user is not a real super user anymore, but it is assigned to the “rds_superuser” role and this one has limited permissions. The real super is “rdsadmin” and we do not have access to this user. A lot of stuff is not possible, e.g.:
postgres=> select * from pg_hba_file_rules ;
ERROR: permission denied for view pg_hba_file_rules
postgres=>
postgres=> select * from pg_read_file('/var/tmp/aa');
ERROR: permission denied for function pg_read_file
… and this is normal as all these would interact with the operating system, and that is hidden completely and not allowed at all. Changing parameters on the instance level does not work as well:
postgres=> alter system set work_mem='1MB'; ERROR: must be superuser to execute ALTER SYSTEM command
You will need to do this by adjusting the DB Parameter group. What surprised me is that you can create a tablespace:
postgres=> create tablespace t1 location '/var/tmp';
CREATE TABLESPACE
postgres=> \db
List of tablespaces
Name | Owner | Location
------------+----------+---------------------------------------
pg_default | rdsadmin |
pg_global | rdsadmin |
t1 | postgres | /rdsdbdata/db/base/tablespace/var/tmp
(3 rows)
The location is remapped in the background. The reason is in the documentation and it is only supported for compatibility reasons, not to spread I/O as you do not know what the storage looks like anyway. Using RDS for PostgreSQL (or any other RDS service) will change the way you will be working with the database instance. You carefully need to check your application if you want to migrate, maybe it is using functionality that is not supported in RDS. Setting parameters can not be done directly, but by using DB Parameter Groups.
In the next post we’ll look at how you can change parametes by modifying the DB Parameter Group.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)