postgres AWS RDS - failed to create replication users

Question

I'm new to postgres. I created a postgres amazon RDS database. after creation I connected to instance via the "postgres" login and the master password I supplied while creating the RDS.

I'm trying to run this command

CREATE ROLE ohdsi_admin
  CREATEDB REPLICATION
   VALID UNTIL 'infinity';
COMMENT ON ROLE ohdsi_admin
  IS 'Administration group for OHDSI applications';

Getting this error: ERROR: must be superuser to create replication users SQL state: 42501

But to my understanding I'm super user now, with weak privilages (cause of RDS) How can I make the above command to work ?

Thom Brown Thom Brown · Accepted Answer · 2020-05-20T12:10:19

With AWS, you need to grant rds_replication to the user instead.

For example

CREATE ROLE ohdsi_admin;
GRANT rds_replication TO ohdsi_admin;

There is also information about this on the RDS documentation: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html

postgres AWS RDS - failed to create replication users

1 Answers