Thank you for the reply.
Even i am able to do manual start backup and stop backup also using backup_admin.
But I am not able to do pg_basebackup using backup_admin .
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 ~]$ psql -U backup_admin -p 5432 -d postgres
psql.bin (9.5.5)
Type "help" for help.
postgres=# create database demo;
CREATE DATABASE
postgres=# create user test_user;
CREATE ROLE
Is there any deference creating backup_admin user below two methods?.
case A)
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
pg_basebackup is not working if i use above syntax to create backup_admin.
Case B) CREATE USER backup_admin password ' XXXXX' SUPERUSER REPLCATION;
pg_basebackup is working if i use above syntax to create backup_admin.
Why case A and case B functionality changes ?.
Is there any Issues/changes in pg_basebackup?
Please provide your inputs.
Thanks,
Chiru
On Sat, Apr 22, 2017 at 5:38 AM, John R Pierce <pierce@xxxxxxxxxxxx> wrote:
On 4/22/2017 2:28 AM, chiru r wrote:
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.
role group membership only inherits object rights, like grant ..... on table ... to role. it doesn't inherit role attributes like SUPERUSER, or REPLICATION. you need to ALTER ROLE to add these to each role.
-- john r pierce, recycling bits in santa cruz
