Thread: [HACKERS] pg_basebackup issue
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
Please help me why pg_basebackup is throwing FATAL when I use backup_admin?.
Is there any limitation in pg_basebackup utility ?
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
For reference this has been asked, and eventually answered on -general at:
Further comments below; partly a rehash of the conclusion drawn by Adrian Klaver on that thread.
On Sun, Apr 23, 2017 at 11:55 AM, chiru r <chirupg@gmail.com> wrote:
postgres=#postgres=# create user backup_admin password 'XXXXX';CREATE ROLEpostgres=# create role dba_admin SUPERUSER REPLICATION;CREATE ROLEpostgres=# grant dba_admin to backup_admin;GRANT ROLEpostgres=# alter user backup_admin set role to dba_admin;ALTER ROLEpostgres=# \duList of rolesRole 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 walsenderPlease help me why pg_basebackup is throwing FATAL when I use backup_admin?.
The pg_basebackup is dying because the role specified, -U backup_admin, has neither SUPERUSER nor REPLICATION privileges since those two privileges are not programmed to be passed down via inheritance. This is a feature. As noted on the other thread one could ask for another feature (via another role attribute) that tells PostgreSQL to pass down those privileges via inheritance. That seems like the most useful solution if one believes that having such an attribute would be an improvement over explicitly defining whether specific login roles are replication or, the all-inclusive, superuser.
The reason the "ALTER USER .. SET ROLE TO" doesn't make any difference here is because pg_backup doesn't specify a database and the table pg_db_role_setting, which where that command stores its data, is only consulted after a successful connection to a specific database has been established. That doesn't happen here.
Is there any limitation in pg_basebackup utility ?
I suppose...
if you look at it from the standpoint that pg_basebackup operates as the physical data files level and not the SQL level.Other's with more authority will voice their own opinions but I'm not where changing the inheritance behavior is an option at this point. Making ALTER USER ... SET ROLE work here is plausible but hackish. The new role attribute just seems messy.
While I guess I get the appeal of having everything defined via group roles and implicit inheritance it still sounds like a purely aesthetic dynamic which is contrary to existing design decisions.
David J.