There are some important gotchas that you have to take into consideration when you manage Aurora PostgreSQL.This is part -1 of the series where we showcase four important differences between standard PostgreSQL and Aurora PostgreSQL
1) Shared_buffers config is different
Standard PostgreSQL runs on a file system and depends on the file system cache in addition to the PostgreSQL buffer cache. We typically allocate 25% of RAM to shared_buffers in standard PostgreSQL. But in Aurora PostgreSQL the standard is 75% of RAM (Defaults to 75% when you provision the instance) . There is no concept of double buffering in Aurora PostgreSQL
2) Pg_hba file can not be updated
In Standard PostgreSQL we use pg_hba.conf to restrict access . In Aurora we cannot make any changes to “pg_hba.conf” neither directly nor via any tables which we can update to edit “pg_hba.conf”.
So what is the substitute in Aurora then ?
- Security groups – We define inbound and outbound rules for the traffic coming from/to the RDS instance.
- NACLs – If you want to control traffic in and out of one or more subnets then you can use Network ACL.
One thing to note here is that in hba file , we use user+database+ip+authmethod,.. combination to restrict traffic . With security group you can’t define similar combinations
We still have system catalog view pg_hba_file_rules in Aurora . Below is default view in version 12
postgres=> select * from pg_hba_file_rules ;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------------------+---------------+------------+---------------+---------+-------------+---------+-------
4 | local | {all} | {all} | | | md5 | |
10 | host | {all} | {rdsadmin} | samehost | | md5 | |
11 | host | {all} | {rdsadmin} | all | | reject | |
12 | host | {rdsadmin} | {all} | all | | reject | |
13 | host | {all} | {all} | all | | md5 | |
14 | host | {replication} | {all} | samehost | | md5 | |
(6 rows)
Once we modify Aurora PostgreSQL Cluster to enable “force ssl” Line_number 13 was updated for ssl
postgres=> select * from pg_hba_file_rules ;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------------------+---------------+------------+---------------+---------+-------------+---------+-------
4 | local | {all} | {all} | | | md5 | |
10 | host | {all} | {rdsadmin} | samehost | | md5 | |
11 | host | {all} | {rdsadmin} | all | | reject | |
12 | host | {rdsadmin} | {all} | all | | reject | |
13 | hostssl | {all} | {all} | all | | md5 | |
14 | host | {replication} | {all} | samehost | | md5 | |
(6 rows)
3) Max_connection defaults are different
4) Pg_stat_statements rest might not work
When you try to invoke pg_stat_statements_reset you might get “permission denied “ error .
postgres=> SELECT pg_stat_statements_reset();
ERROR: permission denied for function pg_stat_statements_reset
‘pg_stat_statements_reset’ function needs to be granted to the rds_superuser role.
grant all on function pg_stat_statements_reset to rds_superuser;
So once you add required permissions you can see that reset works (See screenshot above)
There are other roles like rds_replication(Logical replication) , rdsadmin(Used internally by Aurora) that you might notice when working with Aurora
Please stay tuned and we will post more gotchas in part 2