subject:"\[GENERAL\] pg_basebackup issue"

Re: [GENERAL] pg_basebackup issue

2017-04-23 Thread David G. Johnston

On Sunday, April 23, 2017, 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?.
>

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.

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.

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.

Re: [GENERAL] pg_basebackup issue

2017-04-23 Thread Adrian Klaver


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 '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

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-23 Thread Adrian Klaver


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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-23 Thread Scott Marlowe

On Sat, Apr 22, 2017 at 8:03 PM, David G. Johnston
 wrote:
> On Saturday, April 22, 2017, chiru r  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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-23 Thread chiru r

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?.

Thanks,
Chiru

On Sun, Apr 23, 2017 at 12:10 AM, Adrian Klaver 
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
>> > wrote:
>>
>> On Saturday, April 22, 2017, chiru r > > 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.kla...@aklaver.com
>

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread Adrian Klaver


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
> wrote:

On Saturday, April 22, 2017, chiru r > 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread chiru r

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.johns...@gmail.com> wrote:

> On Saturday, April 22, 2017, chiru r  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.
>

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread David G. Johnston

On Saturday, April 22, 2017, chiru r  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.

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread chiru r

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 '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

Thanks,
Chiru

On Sat, Apr 22, 2017 at 6:00 PM, Adrian Klaver 
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
>> > 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 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread Adrian Klaver

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread chiru r

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_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 
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
>

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread Adrian Klaver


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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread chiru r

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 
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
>> > wrote:
>>
>> On Saturday, April 22, 2017, chiru r > > 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.kla...@aklaver.com
>

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread Adrian Klaver


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
> wrote:

On Saturday, April 22, 2017, chiru r > 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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread chiru r

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.johns...@gmail.com> wrote:

> On Saturday, April 22, 2017, chiru r  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.
>

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread David G. Johnston

On Saturday, April 22, 2017, chiru r  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.

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread chiru r

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.*

But I am not able to do  *pg_basebackup *using *backup_admin ** .*

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 ~]$ 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 '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

pg_basebackup is * not working* if i use above syntax to create
backup_admin.

*Case B)* CREATE USER backup_admin password ' X' 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  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
>
>

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread Adrian Klaver


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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_basebackup issue

2017-04-22 Thread John R Pierce


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

[GENERAL] pg_basebackup issue

2017-04-22 Thread chiru r

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 '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

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

Re: [GENERAL] pg_basebackup issue

[GENERAL] pg_basebackup issue

20 matches

Site Navigation

Mail list logo

Footer information