Re: [HACKERS] pg_basebackup issue - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: [HACKERS] pg_basebackup issue |
| Date | |
| Msg-id | CAKFQuwYEv6zW-tdD2YOtvLMhBrisLrpVa=m4ABvXgnmXqUV8uw@mail.gmail.com Whole thread Raw |
| In response to | [HACKERS] pg_basebackup issue (chiru r <chirupg@gmail.com>) |
| List | pgsql-hackers |
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.
pgsql-hackers by date: