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.kla...@aklaver.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@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.kla...@aklaver.com <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
