Re: pg_basebackup issue

For reference this has been asked, and eventually answered on -general at:

https://www.postgresql.org/message-id/flat/CAKFQuwZDS7nA0SvVnumwjHBxz4CWKQm3bVNTHVeWdtAW_oXNJg%40mail(dot)gmail(dot)com#CAKFQuwZDS7nA0SvVnumwjHBxz4CWKQm3bVNTHVeWdtAW_oXNJg(at)mail(dot)gmail(dot)com

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(at)gmail(dot)com> wrote:

>
> 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(at)pgserver ~]$ mkdir online_backups1
> [postgres(at)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?.*
>
>
​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.