pg_basebackup issue

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

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

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

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

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

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

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

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

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

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

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 <adrian.klaver@aklaver.com>
wrote:

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.

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

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

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

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

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

On Sunday, April 23, 2017, chiru r <chirupg@gmail.com> 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.