Mastering PostgreSQL 12 - Third Edition

Improving security

So far, we have seen that data is streamed as a superuser. However, it is not a good idea to allow superuser access from a remote site. Fortunately, PostgreSQL allows us to create a user that is only allowed to consume the transaction log stream, but cannot do anything else.

Creating a user just for streaming is easy:

test=# CREATE USER repl LOGIN REPLICATION;CREATE ROLE

By assigning replication to the user, it is possible to use it just for streaming—everything else is forbidden.

It is highly recommended to not use your superuser account to set up streaming. Simply change the configuration file to the newly created user. Not exposing superuser accounts will dramatically improve security, just like giving the replication ...