Re: [HACKERS] pg_basebackup issue

[David G. Johnston]

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

https://www.postgresql.org/message-id/flat/CAKFQuwZDS7nA0SvVnumwjHBxz4CWKQm3bVNTHVeWdtAW_oXNJg%40mail.gmail.com#cakfquwzds7na0svvnumwjhbxz4cwkqm3bvnthvewdtaw_ox...@mail.gmail.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  wrote:

>
> postgres=#
> postgres=# create user backup_admin password 'X';
> 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?.*
>
>
​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.

[HACKERS] pg_basebackup issue

[chiru r]

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 'X';
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/228 on timeline 1
transaction log end point: 0/2000130
pg_basebackup: base backup completed

Thanks,
Chiru