Re: pg_basebackup issue

[chiru r <chirupg@xxxxxxxxx>]

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.klaver@xxxxxxxxxxx> 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=0a4295b418132758ebd539f00175c32ac0db92d5;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@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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx