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 · Accepted Answer · 2020-05-20 12:10:19Z

14

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

answered May 20, 2020 at 12:10

Thom Brown

2,0698 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Liav Over a year ago

Thanks Thom for your reply :-) but is it possible to use 'replication' instead of 'rds_replication' role? are these 2 roles behave exactly the same ?

Thom Brown Over a year ago

RDS controls the roles differently to regular PostgreSQL as it part of its managed service. rds_replication is used to allow for logical replication. If you want regular streaming replication, that's managed as part of the RDS service, so managing user permissions as part of it isn't something you can do.

Liav Over a year ago

Thanks a lot Thom for your reply. I managed to work around this issue

Naveen Over a year ago

After running this command '''GRANT rds_replication TO <user_name>;''', do I need to restart my RDS?

Ksenia Rogozhina · Accepted Answer · 2023-08-04 15:51:52Z

2

One small detail that was not initially obvious to me: with rds_replication granted, your user still won't have "Replication" enabled in e.g. Idea database GUI, rolreplication in pg_roles will still be false, but you will be able to create publication/subscription according to the documentation (https://aws.amazon.com/ru/blogs/database/using-logical-replication-to-replicate-managed-amazon-rds-for-postgresql-and-amazon-aurora-to-self-managed-postgresql/). And as far as I understood, it's not possible to get real superuser in AWS RDS, but for the purpose of setting up logical replication you just don't need it.

answered Aug 4, 2023 at 15:51

Ksenia Rogozhina

1051 silver badge10 bronze badges

Comments

dz902 · Accepted Answer · 2022-01-05 09:22:02Z

1

If you encounter must be superuser to alter replication users when using Aurora, then understand that SUPERUSER and REPLICATION attributes are not available to rds_superuser, which is the group your master user belongs to.

There is a separate role called rdsadmin who has all the permissions but is not usable by RDS users.

Replication on Aurora Pgsql is only possible with the CREATE PUBLICATION and CREATE SUBSCRIPTION commands, available from Pgsql v10 (v10.6 in Aurora). You cannot use the old way with ALTER USER some_user REPLICATION.

answered Jan 5, 2022 at 9:22

dz902

6,01345 silver badges48 bronze badges

Collectives™ on Stack Overflow

postgres AWS RDS - failed to create replication users

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related