Обсуждение: [GENERAL] pg_basebackup issue
Hello,
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 '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@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/2000028 on timeline 1
transaction log end point: 0/2000130
pg_basebackup: base backup completed
Thanks,
Chiru
On 4/22/2017 2:28 AM, chiru r wrote:
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.
role group membership only inherits object rights, like grant ..... on table ... to role. it doesn't inherit role attributes like SUPERUSER, or REPLICATION. you need to ALTER ROLE to add these to each role.
-- john r pierce, recycling bits in santa cruz
On 04/22/2017 02:38 AM, John R Pierce wrote:
> On 4/22/2017 2:28 AM, chiru r wrote:
>> 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.
>
>
> role group membership only inherits object rights, like grant ..... on
> table ... to role. it doesn't inherit role attributes like SUPERUSER,
> or REPLICATION. you need to ALTER ROLE to add these to each role.
The OP did that:
...
alter user backup_admin set role to dba_admin;
When I followed their steps I got:
aklaver@tito:~> psql -d test -U backup_admin
Null display is "NULL".
psql (9.6.2)
Type "help" for help.
test=# select session_user, current_user;
session_user | current_user
--------------+--------------
backup_admin | dba_admin
(1 row)
test=# \drds
List of settings
Role | Database | Settings
--------------+----------+----------------
backup_admin | | role=dba_admin
aklaver@tito:~> pg_basebackup -p 5432 -h 127.0.0.1 -U backup_admin -D
pg_test/
Password:
pg_basebackup: could not connect to server: FATAL: must be superuser or
replication role to start walsender
So I would also be interested in knowing why it does not work?
>
>
> --
> john r pierce, recycling bits in santa cruz
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Thank you for the reply.
Even i am able to do manual start backup and stop backup also using backup_admin.
But I am not able to do pg_basebackup using backup_admin .
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@pgserver ~]$ psql -U backup_admin -p 5432 -d postgres
psql.bin (9.5.5)
Type "help" for help.
postgres=# create database demo;
CREATE DATABASE
postgres=# create user test_user;
CREATE ROLE
Is there any deference creating backup_admin user below two methods?.
case A)
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
pg_basebackup is not working if i use above syntax to create backup_admin.
Case B) CREATE USER backup_admin password ' XXXXX' SUPERUSER REPLCATION;
pg_basebackup is working if i use above syntax to create backup_admin.
Why case A and case B functionality changes ?.
Is there any Issues/changes in pg_basebackup?
Please provide your inputs.
Thanks,
Chiru
On Sat, Apr 22, 2017 at 5:38 AM, John R Pierce <pierce@hogranch.com> wrote:
On 4/22/2017 2:28 AM, chiru r wrote: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.
role group membership only inherits object rights, like grant ..... on table ... to role. it doesn't inherit role attributes like SUPERUSER, or REPLICATION. you need to ALTER ROLE to add these to each role.
-- john r pierce, recycling bits in santa cruz
On Saturday, April 22, 2017, chiru r <chirupg@gmail.com> wrote:
Thank you for the reply.It is inheriting role attributes. I am able to do CREATEDB,CREATEROLE... etc .Even i am able to do manual start backup and stop backup also using backup_admin.
So I've recently read that while some role attributes are inherited SUPERUSER is not. Everything you are seeing work or not works appears to be due to superuser privileges differences.
David J.
But, SUPERUSER privileges are working, you can see above I am able to do CREATEUSER and CREATEDB.
What is the differences in case A and case B for Superuser?
Thanks,
Chiru
On Sat, Apr 22, 2017 at 10:45 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Saturday, April 22, 2017, chiru r <chirupg@gmail.com> wrote:Thank you for the reply.It is inheriting role attributes. I am able to do CREATEDB,CREATEROLE... etc .Even i am able to do manual start backup and stop backup also using backup_admin.So I've recently read that while some role attributes are inherited SUPERUSER is not. Everything you are seeing work or not works appears to be due to superuser privileges differences.David J.
On 04/22/2017 08:13 AM, chiru r wrote: > But, SUPERUSER privileges are working, you can see above I am able to do > CREATEUSER and CREATEDB. > > What is the differences in case A and case B for Superuser? I had flash of insight while driving. The insight being that the problem is down to this: https://www.postgresql.org/docs/9.6/static/app-pgbasebackup.html --dbname=connstr " Specifies parameters used to connect to the server, as a connection string. See Section 32.1.1 for more information. The option is called --dbname for consistency with other client applications, but because pg_basebackup doesn't connect to any particular database in the cluster, database name in the connection string will be ignored. " Have not looked into the source to see how pg_basebackup connects, however I am guessing what ever it does bypasses the mechanism for doing SET ROLE on connection. In pg_hba the 'dummy' database replication is used for replication connections, which is what pg_basebackup does. Since it is not an actual database there would seem to be no way for the alter user backup_admin set role to dba_admin setting to take effect. > > Thanks, > Chiru > > > On Sat, Apr 22, 2017 at 10:45 AM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Saturday, April 22, 2017, chiru r <chirupg@gmail.com > <mailto:chirupg@gmail.com>> wrote: > > Thank you for the reply. > > It is inheriting role attributes. I am able to do > CREATEDB,CREATEROLE... etc . > Even i am able to do manual start backup and stop backup also > using *backup_admin.* > ** > > > So I've recently read that while some role attributes are inherited > SUPERUSER is not. Everything you are seeing work or not works > appears to be due to superuser privileges differences. > > David J. > > -- Adrian Klaver adrian.klaver@aklaver.com
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.
Thanks,
Chiru
On Sat, Apr 22, 2017 at 12:34 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 04/22/2017 08:13 AM, chiru r wrote:But, SUPERUSER privileges are working, you can see above I am able to do
CREATEUSER and CREATEDB.
What is the differences in case A and case B for Superuser?
I had flash of insight while driving. The insight being that the problem is down to this:
https://www.postgresql.org/docs/9.6/static/app-pgbasebackup. html
--dbname=connstr
"
Specifies parameters used to connect to the server, as a connection string. See Section 32.1.1 for more information.
The option is called --dbname for consistency with other client applications, but because pg_basebackup doesn't connect to any particular database in the cluster, database name in the connection string will be ignored.
"
Have not looked into the source to see how pg_basebackup connects, however I am guessing what ever it does bypasses the mechanism for doing SET ROLE on connection. In pg_hba the 'dummy' database replication is used for replication connections, which is what pg_basebackup does. Since it is not an actual database there would seem to be no way for the alter user backup_admin set role to dba_admin setting to take effect.
Thanks,
Chiru
On Sat, Apr 22, 2017 at 10:45 AM, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
On Saturday, April 22, 2017, chiru r <chirupg@gmail.com
<mailto:chirupg@gmail.com>> wrote:
Thank you for the reply.
It is inheriting role attributes. I am able to do
CREATEDB,CREATEROLE... etc .
Even i am able to do manual start backup and stop backup also
using *backup_admin.*
**
So I've recently read that while some role attributes are inherited
SUPERUSER is not. Everything you are seeing work or not works
appears to be due to superuser privileges differences.
David J.
--
Adrian Klaver
adrian.klaver@aklaver.com
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@aklaver.com
Thanks you. The FATAL message is not clear. Yes, It is helpful if someone with more knowledge of the connection code.
[postgres@pgserver ~]$ /opt/PostgreSQL/9.5/bin/pg_
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@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@aklaver.com
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@aklaver.com <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@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
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@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/posti nit.c;h=0a4295b418132758ebd539 f00175c32ac0db92d5;hb=6a18e4bc 2d13d077c52cf90a4c6ec68343808b a7
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@aklaver.com <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@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Saturday, April 22, 2017, chiru r <chirupg@gmail.com> wrote:
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.
It does not seem wise to introduce inheritance of such powerful capabilities when for many years now we have not done so. It seems like reality could be better documented but the present behavior should stay. I also find the original choice to be quite sane regardless.
David J.
Use case: Want to control database privileges/default roles by creating roles instead of granting directly to users.
So that we can manage database access control easily.
Thanks,
Chiru
On Sat, Apr 22, 2017 at 10:03 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Saturday, April 22, 2017, chiru r <chirupg@gmail.com> wrote: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.It does not seem wise to introduce inheritance of such powerful capabilities when for many years now we have not done so. It seems like reality could be better documented but the present behavior should stay. I also find the original choice to be quite sane regardless.David J.
On 04/22/2017 08:04 PM, chiru r wrote: > Use case: Want to control database privileges/default roles by creating > roles instead of granting directly to users. > So that we can manage database access control easily. Which you can do. However, pg_basebackup is a cluster wide command not tied a particular database, so database privileges do not apply. You can still manage it by restricting the roles able to connect to 'replication' in pg_hba.conf and creating roles that match that have only the replication attribute. It is why the replication attribute was added to role creation. > > Thanks, > Chiru > > On Sat, Apr 22, 2017 at 10:03 PM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Saturday, April 22, 2017, chiru r <chirupg@gmail.com > <mailto:chirupg@gmail.com>> wrote: > > 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. > > > It does not seem wise to introduce inheritance of such > powerful capabilities when for many years now we have not done so. > It seems like reality could be better documented but the present > behavior should stay. I also find the original choice to be quite > sane regardless. > > David J. > > -- Adrian Klaver adrian.klaver@aklaver.com
Thank you for your reply.
looking to create users(LOGIN) only and Grant all the privileges through roles like below.
Steps:
Create User
Create role
GRANT required privileges/default attributes(SUPERUSER,REPLICATION etc) to Role
GRANT role to Users.
So,If I follow above process I am not able to do pg_basebackup.
Now I have only options left either I have to use postgres user or create a user directly with replication role(Which we are not interested in).
In future is there possibility to allow a user(using above steps) to do pg_basebackup?.
Thanks,
Chiru
On Sun, Apr 23, 2017 at 12:10 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 04/22/2017 08:04 PM, chiru r wrote:Use case: Want to control database privileges/default roles by creating
roles instead of granting directly to users.
So that we can manage database access control easily.
Which you can do. However, pg_basebackup is a cluster wide command not tied a particular database, so database privileges do not apply. You can still manage it by restricting the roles able to connect to 'replication' in pg_hba.conf and creating roles that match that have only the replication attribute. It is why the replication attribute was added to role creation.
Thanks,
Chiru
On Sat, Apr 22, 2017 at 10:03 PM, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
On Saturday, April 22, 2017, chiru r <chirupg@gmail.com
<mailto:chirupg@gmail.com>> wrote:
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.
It does not seem wise to introduce inheritance of such
powerful capabilities when for many years now we have not done so.
It seems like reality could be better documented but the present
behavior should stay. I also find the original choice to be quite
sane regardless.
David J.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Sat, Apr 22, 2017 at 8:03 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Saturday, April 22, 2017, chiru r <chirupg@gmail.com> wrote: >> >> 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. >> > > It does not seem wise to introduce inheritance of such powerful capabilities > when for many years now we have not done so. It seems like reality could be > better documented but the present behavior should stay. I also find the > original choice to be quite sane regardless. > > David J. I would point out that these roles are already inheritable elsewhere, so it's not like adding it to the connection coming from another machine for basebackup is somehow a new thing really, just making it act the same in both places.
On 04/23/2017 04:52 AM, chiru r wrote: > Thank you for your reply. > > I agree, However we are planning, users not to have any direct > privileges on database objects or default roles(like > SUPERUSER,CREATEDB,REPLICATION..etc) in postgres. > > looking to create users(LOGIN) only and Grant all the privileges through > roles like below. > > *Steps: * > Create User > Create role > GRANT required privileges/default attributes(SUPERUSER,REPLICATION etc) > to Role > GRANT role to Users. > > So,If I follow above process I am not able to do pg_basebackup. > Now I have only options left either I have to use postgres user or > create a user directly with replication role(Which we are not interested > in). > > In future is there possibility to allow a user(using above steps) to do > pg_basebackup?. That would be a Postgres developer question/answer. Many of the developers are on this list and may respond at some point. If you want to put the question directly to the developers I would suggest either: 1) Posting to --hackers (https://www.postgresql.org/list/pgsql-hackers/) 2) Filing a bug report: https://www.postgresql.org/account/login/?next=/account/submitbug/ > > > Thanks, > Chiru > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 04/23/2017 04:52 AM, chiru r wrote: > Thank you for your reply. > > I agree, However we are planning, users not to have any direct > privileges on database objects or default roles(like > SUPERUSER,CREATEDB,REPLICATION..etc) in postgres. > > looking to create users(LOGIN) only and Grant all the privileges through > roles like below. > > *Steps: * > Create User > Create role > GRANT required privileges/default attributes(SUPERUSER,REPLICATION etc) > to Role > GRANT role to Users. Yes, but in your original setup: 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 the practical effect is to have a user(backup_admin) with SUPERUSER REPLICATION attributes every time they logged in(the exception being in the pg_basebackup case, as well as other commands that follow the same path). Not sure how that is different from just setting up a user with the REPLICATION attribute. > > So,If I follow above process I am not able to do pg_basebackup. > Now I have only options left either I have to use postgres user or > create a user directly with replication role(Which we are not interested > in). > > In future is there possibility to allow a user(using above steps) to do > pg_basebackup?. > > > Thanks, > Chiru > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sunday, April 23, 2017, chiru r <chirupg@gmail.com> wrote:
If we decided to not be so nanny-ish we could add a "inherit-role-attributes" option to create user and then you could define roles with that option.
Thank you for your reply.I agree, However we are planning, users not to have any direct privileges on database objects or default roles(like SUPERUSER,CREATEDB,REPLICATION..etc) in postgres. looking to create users(LOGIN) only and Grant all the privileges through roles like below.Steps:Create UserCreate roleGRANT required privileges/default attributes(SUPERUSER,REPLICATION etc) to Role GRANT role to Users.So,If I follow above process I am not able to do pg_basebackup.Now I have only options left either I have to use postgres user or create a user directly with replication role(Which we are not interested in).In future is there possibility to allow a user(using above steps) to do pg_basebackup?.
The choice to make this not work is now well established so I would have say no, there isn't.
While making the " alter role ... set role to ... " has hope the only reason to do it is as a hack for the very case of not having superuser/replication inherited. On that basis I'd say it's unlikely - I suspect we'd rather do the next thought.
That's my opinion at least. At this moment you'd be waiting at least 18 months for it to appear in version 11.
David J.