Re: pg_basebackup issue

Thank you Adrian.

It seems the code is allowing only who has Superuser/Replication role
directly.

Is there any possibility in future releases they allow both case A & B
Users able to use pg_basebackup.

Working:

A) CREATE USER backup_user SUPERUSER;

Not working:

B) 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

Thanks,
Chiru

On Sat, Apr 22, 2017 at 6:00 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 04/22/2017 12:56 PM, chiru r wrote:
>
>> Thanks you. The FATAL message is not clear. Yes, It is helpful
>> if someone with more knowledge of the connection code.
>>
>
> Well if your interested in looking for yourself look here:
>
> src/backend/utils/init/postinit.c
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;
> f=src/backend/utils/init/postinit.c;h=0a4295b418132758ebd539
> f00175c32ac0db92d5;hb=6a18e4bc2d13d077c52cf90a4c6ec68343808ba7
>
> Lines 781-879:
>
> ...
>
> if (!superuser() && !has_rolreplication(GetUserId()))
> ereport(FATAL,
> (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
> errmsg("must be superuser or replication role to
> start walsender")));
>
> ...
>
> /*
> * If this is a plain walsender only supporting physical replication, we
> * don't want to connect to any particular database. Just finish the
> * backend startup by processing any options from the startup packet, and
> * we're done.
> */
>
> ....
>
> /*
> * If this is a background worker not bound to any particular
> * database, we're done now. Everything that follows only makes sense
> * if we are bound to a specific database. We do need to close the
> * transaction we started before returning.
> */
>
>
> To me it looks like settings are handled by process_settings at line 1101
> and that is only called if an actual database is being connected to.
>
>
>>
>> [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*
>> *
>> *
>>
>> On Sat, Apr 22, 2017 at 2:00 PM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 04/22/2017 10:04 AM, chiru r wrote:
>>
>> Thanks for the reply,
>>
>> Actually I am not setting Role for database specific,I did set
>> Role to
>> user.
>> Since users and roles are global for all databases in PostgreSQL,I
>> believe it should work for replication pesudo database.
>>
>>
>> Except for the part where the 'replication' database does not
>> actually exist, so I am not seeing how a connection could be
>> established to it. Now whether a replication connection is supposed
>> to honor SET ROLE is something someone with more knowledge of the
>> connection code will have to answer. The evidence says it does not.
>>
>>
>> Thanks,
>> Chiru
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>