Info2soft use cookies to help you have a superior and more admissible browsing experience on our website. Privacy Policy
Loading...
By:
A replication user is a dedicated database role created with the REPLICATION attribute. This lets the role connect in replication mode and stream Write-Ahead Logs (WAL) through the physical or logical replication protocol, instead of running SQL queries against tables like a standard LOGIN role.
Teams create a separate replication role rather than using the postgres superuser to follow the principle of least privilege. This distinction matters for security:
postgres superuser can read, modify, or drop any object in the database, making its credentials highly sensitive.A common point of confusion is the CREATEROLE privilege. As PostgreSQL 18 documentation confirms, holding CREATEROLE does not let a role create replication users, nor does it allow granting or revoking the REPLICATION privilege for others. Only true superusers, or roles already granted REPLICATION themselves, can provision new replication accounts.
Before creating a replication user, a few parameters in postgresql.conf need to be set correctly. Skipping these will prevent the standby server from establishing a replication stream.
Check the following on the primary database instance:
wal_level to replica, or logical if you plan to use logical replication instead of streaming physical replication. This controls how much detail gets written to the Write-Ahead Logs.max_wal_senders high enough to cover every concurrent replication connection you plan to run. Each standby server or backup tool uses one sender slot.max_replication_slots has room for a dedicated slot per standby server. Slots keep a replica from falling too far behind by tracking its current log position.CREATEROLE plus an explicit replication grant.Setting up a replication role involves two layers: the role itself inside PostgreSQL, and the authentication rule that lets it connect over the network. Follow these three steps on the primary server.
Connect to the primary instance as the postgres superuser and run the creation command. Give it a strong password, along with the REPLICATION attribute so it can stream WAL.
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'your_secure_password';CREATE USER includes LOGIN by default, so there’s no need to add it separately. If you use CREATE ROLE instead, add LOGIN explicitly, since CREATE ROLE does not assume it.
Creating the role inside PostgreSQL is not enough. The server also needs to be told to accept the connection. Open pg_hba.conf and add a line scoped to the standby server’s IP address.
host replication replicator 192.168.1.50/32 scram-sha-256As of PostgreSQL 14, scram-sha-256 is the default authentication method and should be used instead of the older, weaker md5 method. The database field is set to the literal value replication, which tells PostgreSQL this rule applies to replication connections rather than regular database access.
Changes to pg_hba.conf take effect on reload, so a full restart isn’t needed. Reload from inside psql:
SELECT pg_reload_conf();Or from the system terminal:
pg_ctl reload -D /var/lib/postgresql/dataOnce reloaded, confirm the replication user exists with the correct attributes.
In psql, run \du to list all roles. Look for replicator and check that Replication appears under Attributes. You can also query the catalog directly:
SELECT rolname, rolreplication, rollogin FROM pg_roles WHERE rolname = 'replicator';To check that the pg_hba.conf line was parsed without errors, query pg_hba_file_rules:
SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE user_name = 'replicator';
A NULL value in the error column means PostgreSQL parsed the new rule successfully.
Managed PostgreSQL services don’t give you shell access, so pg_hba.conf and postgresql.conf can’t be edited directly. Each platform grants replication privileges through its own system role instead of raw superuser access.
| Managed Platform | Configuration Method | Default System Role | How Network Access Is Scoped |
|---|---|---|---|
| AWS RDS / Aurora | SQL grant | rds_replication |
Security groups / VPC peering |
| Azure Database | SQL grant | azure_pg_admin |
Connection security / firewall rules |
| Google Cloud SQL | SQL creation + console | cloudsqlsuperuser |
Authorized networks / Cloud SQL Auth Proxy |
Amazon RDS and Aurora: Grant the built-in role to enable replication:
GRANT rds_replication TO your_replication_user;This works without superuser access, and it’s also the foundation that automatic failover in RDS relies on to promote a standby when the primary goes down.
Azure Database for PostgreSQL (Flexible Server): Grant membership in Azure’s administrative role:
GRANT azure_pg_admin TO your_replication_user;You can also assign REPLICATION directly if you’re connected as the primary administrator account.
Google Cloud SQL: Create the user under Cloud SQL’s elevated management role:
CREATE USER replicator WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'your_secure_password';Manage network access through the Google Cloud Console or gcloud, or use the Cloud SQL Auth Proxy to skip IP allowlisting entirely.
Even with the right steps, setting up replication can still trigger authentication or authorization errors. Here’s how to resolve the ones you’re most likely to run into.
This means you’re not connected as a superuser, or you’re using a role with CREATEROLE that hasn’t been explicitly granted replication rights.
Connect as the postgres superuser or another superuser role. If you need to use a non-superuser with CREATEROLE, remember that this privilege alone doesn’t grant the ability to create or manage REPLICATION roles. A superuser has to delegate that authority separately.
The primary’s authentication file is blocking the standby’s connection, usually because a rule is missing, has a typo, sits in the wrong order, or the config was never reloaded.
Check pg_hba.conf and confirm a rule matches the standby’s IP address, the user name, and the literal database value replication. After fixing the line, run SELECT pg_reload_conf(); on the primary, then query pg_hba_file_rules to confirm there are no parsing errors.
This is usually a hashing mismatch: the password was stored using the older md5 format, but pg_hba.conf requires scram-sha-256, or the reverse.
Check the active encryption setting with SHOW password_encryption;. If it’s not set to scram-sha-256, update it, then re-run ALTER USER replicator WITH PASSWORD 'your_password'; to re-hash the password under the new setting. Make sure the matching pg_hba.conf line also uses scram-sha-256.
Despite the wording, this is rarely about database roles or replication privileges. It’s usually a filesystem issue, where the operating system user running the command doesn’t have write access to the target directory, or files in the primary’s data directory have the wrong ownership.
Make sure the destination directory on the standby is empty, then set the correct owner:
chown -R postgres:postgres /var/lib/postgresql/dataAdjust the path for the actual deployment. Also check that no stray lock files or external tools are holding a lock on the primary’s data directory.
Setting up a replication user is only the first step. Keeping that replication stream healthy, monitored, and ready to fail over when the primary database goes down is a separate challenge, and one that manual PostgreSQL configuration doesn’t fully solve on its own.
i2Availability is built for exactly this gap. It provides byte-level real-time data replication between a production database and its standby, so changes on the primary are captured and sent to the backup continuously rather than on a schedule. This keeps RPO close to zero, since the standby is never far behind the source.
Key features include:
The choice between synchronous and asynchronous replication affects how much lag a standby can tolerate before failover becomes risky, and i2Availability’s real-time replication is designed to keep that gap as small as possible regardless of which mode fits the environment.
For teams also managing full-machine or file-based replication alongside their database, i2Migration extends similar real-time synchronization to broader migration and disaster recovery scenarios.
Q1: Is postgres the same as a replication user?
No. The postgres superuser bypasses all permission checks, including replication, but it’s meant for administrative tasks, not for streaming WAL. A dedicated replication user with only the REPLICATION attribute limits what a compromised credential can access.
Q2: Do I need to restart PostgreSQL, or is reload enough?
A reload is enough for changes to pg_hba.conf. Run SELECT pg_reload_conf(); or pg_ctl reload, and the new authentication rule takes effect without dropping existing connections.
Q3: CREATE ROLE vs CREATE USER, is there a difference?
CREATE USER is equivalent to CREATE ROLE, except it includes LOGIN by default. CREATE ROLE does not, so you need to add LOGIN explicitly if the role should be able to connect.
Q4: Can I use md5 instead of scram-sha-256?
Yes, but it’s not recommended. PostgreSQL has defaulted to scram-sha-256 since version 14 because it’s more resistant to password sniffing. Use md5 only if an older client can’t support scram-sha-256.
Creating a PostgreSQL replication user is straightforward once the role, authentication, and network layers are configured correctly. Getting the REPLICATION attribute, pg_hba.conf entry, and password hashing method aligned from the start avoids most of the errors covered in this guide.
For teams running PostgreSQL alongside other databases and virtualized systems, keeping replication healthy at scale often calls for more automation than manual configuration alone. Info2soft builds tools that extend this kind of real-time protection across broader infrastructure, from database replication to full-system high availability.
· Enterprise & Mid-market Customers Worldwide
· Support team available to assist you throughout your trial
· Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
