How to grant set the replication attribute on RDS PostgreSQL?

Question

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?

• András Váczi: 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.
• Juan Sebastian: but rds_superuser cannot be used either am i right? so no way to set up a replication user?

Answer 1

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!

• conterio: how long does it take for this to replicate?
• mlissner: 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.