9

On the AWS RDS documentation I see that rds_superuser can:

Grant and revoke the replication attribute onto all roles that are not the rds_superuser role. For more information, see this section in the PostgreSQL documentation.

source: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html

But, if I try, while logged in as my rds_superuser:

ALTER ROLE ROLENAME WITH REPLICATION;

I get an error stating that I cannot perform that action due to not being a superuser.

Do you guys know how to set the replication attribute to a user?

2
  • I think this is a documentation problem. Having REPLICATION makes you nearly a superuser, which would defy the logic behind the existence of rds_superuser, so I was really surprised to read this passage of the docs. Furthermore, it points to the wrong page of the Postgres docs, and uses the wrong terminology. Sep 14, 2017 at 22:21
  • but rds_superuser cannot be used either am i right? so no way to set up a replication user? Sep 19, 2017 at 16:16

1 Answer 1

13

We hit this head on too, and after much frustration discovered:

CREATE ROLE some-user WITH LOGIN PASSWORD 'long-password'; grant rds_replication to user;

This seems to be working for logical replication.

UPDATE: Note that the user on the publisher needs to have SELECT access too!

2
  • how long does it take for this to replicate?
    – conterio
    Feb 14, 2023 at 0:49
  • 1
    Generally replication is very fast if you don't have a pile of changes that need to replicate. If you do have a pile of old changes, then those have to go through before any other changes can. I'm not sure if permissions are replicated. I've never tried that, and I could see postgres just not doing so for security reasons (or maybe it does for consistency reasons!). No clue there.
    – mlissner
    Feb 15, 2023 at 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.