[HACKERS] pg_basebackup issue - Mailing list pgsql-hackers

Hi Team,

I am using Postgresql 9.5 and I have created backup_admin user and created dba_admin ROLE with SUPERUSER and REPLICATION ,after that GRANT dba_admin  role   to backup_admin user and executed  pg_basebakup utility with backup_admin user. 

But I am not able to use the pg_basebackup utility using backup_admin user and got below FATAL.

pg_basebackup: could not connect to server: FATAL:  must be superuser or replication role to start walsender

However I have observed only issue with backup_admin  user to use pg_basebackup utility.

Please help me to understand why pg_basebackup is throwing FATAL when I use backup_admin?.

Is there any limitation with pg_basebackup utility ?

The process i am following for backup_admin user :

postgres=# select version();

                                                 version

----------------------------------------------------------------------------------------------------------

 PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit

(1 row)

postgres=#

postgres=# create user backup_admin password 'XXXXX';

CREATE ROLE

postgres=# create role dba_admin SUPERUSER REPLICATION;

CREATE ROLE

postgres=# grant dba_admin to backup_admin;

GRANT ROLE

postgres=# alter user backup_admin set role to dba_admin;

ALTER ROLE

postgres=# \du

                                           List of roles

    Role name     |                         Attributes                         |     Member of

------------------+------------------------------------------------------------+--------------------

 backup_admin     |                                                            | {dba_admin}

 dba_admin        | Superuser, Cannot login, Replication                       | {}

 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

[postgres@pgserver ~]$ mkdir online_backups1

[postgres@pgserver ~]$ /opt/PostgreSQL/9.5/bin/pg_basebackup  --format=t   --pgdata=online_backups1 -p 5432 -U backup_admin  -x -z  --verbose

pg_basebackup: could not connect to server: FATAL:  must be superuser or replication role to start walsender

For information the pg_basebackup is working fine for Postgres user and it is successful.

[postgres@pgserver ~]$ /opt/PostgreSQL/9.5/bin/pg_basebackup  --format=t   --pgdata=online_backups -p 5432 -U postgres  -x -z  --verbose

transaction log start point: 0/2000028 on timeline 1

transaction log end point: 0/2000130

pg_basebackup: base backup completed

Thanks,

Chiru