I'm trying to setup streaming replication. On my master I've removed connect priviliges for public:

REVOKE ALL ON SCHEMA public FROM public;

For User repl I've tried to allow access everywhere again

GRANT pg_read_all_data TO repl;
GRANT pg_write_all_data TO repl;
GRANT ALL PRIVILEGES ON SCHEMA public FROM public to repl;

Users currently look like this

postgres=# \du;
                                                 List of roles
 Role name |                         Attributes                         |              Member of               
----------- ------------------------------------------------------------ --------------------------------------
 pgpool    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repl      | Replication                                                | {pg_read_all_data,pg_write_all_data}

Now on my slave a connection test fails with:

psql -h db-master-p 5432  -U repl -d template1 
psql: error: connection to server at "db-master", port 5432 failed: FATAL:  permission denied for database "template1" 
DETAIL:  User does not have CONNECT privilege.

Which permissions are needed for User repl and how to correctly apply them?

CodePudding user response：

You seem to be mixing up schemas and databases, and the CREATE and CONNECT privileges.

A user for streaming replication does not not privileges on any database objects like schemas and tables, since it only consumes WAL. The user only needs to have the REPLICATION property.

There are two types of connections in PostgreSQL: normal client-server connections and replication connections. Your test with psql tests the former, which requires the CONNECT privilege on the database.

To establish a replication connection with psql, user the replication connection string property:

psql 'host=db-master port=5432 user=repl replication=on'

You cannot specify a database with a replication connection.