[GENERAL] pg_basebackup issue

[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

[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

Re: [GENERAL] Recover PostgreSQL database folder data

[Magnus Hagander]

On Sat, Apr 22, 2017 at 3:05 AM, Cat  wrote:

> On Fri, Apr 21, 2017 at 08:20:38PM -0300, Edson Lidorio wrote:
> > Ls -la /var/lib/pgsql/9.6/data
> >
> > drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
> > drwx--.  4 root root51 Abr 21 06:33 ..
>
> Ensure that the user 'postgres' has permissions to get to
> this dir from / up. This may either mean changing permissions
> on some directories or changing ownership.
>
> More than likely / /var /lib are a permissions thing (likely
> need to be u+rwx,g+rx,o+rx) and /var/lib/pgsql/ and up is an
> ownership thing (postgres:postgres) but this is not guaranteed
> so take care.


Since this is CentOS, I would also look into if it's selinux things that
are incorrect. The easiest way is to turn it off and see if that fixes it
-- if it does, then read up on the selinux docs for how to figure out what
is wrong and probably use restorecon to get things back in order.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: [GENERAL] Recover PostgreSQL database folder data

[Edson Lidorio]

On 22-04-2017 06:40, Magnus Hagander wrote:



On Sat, Apr 22, 2017 at 3:05 AM, Cat > wrote:


On Fri, Apr 21, 2017 at 08:20:38PM -0300, Edson Lidorio wrote:
> Ls -la /var/lib/pgsql/9.6/data
>
> drwx--. 20 postgres postgres  4096 Abr 21 17:52 .
> drwx--.  4 root root51 Abr 21 06:33 ..

Ensure that the user 'postgres' has permissions to get to
this dir from / up. This may either mean changing permissions
on some directories or changing ownership.

More than likely / /var /lib are a permissions thing (likely
need to be u+rwx,g+rx,o+rx) and /var/lib/pgsql/ and up is an
ownership thing (postgres:postgres) but this is not guaranteed
so take care.


Since this is CentOS, I would also look into if it's selinux things 
that are incorrect. The easiest way is to turn it off and see if that 
fixes it -- if it does, then read up on the selinux docs for how to 
figure out what is wrong and probably use restorecon to get things 
back in order.


--
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Friends,
The problem, was the selinux of CentOS, I disabled the selinux and 
applied the pemissions again and PostgreSQL started normally.


Used Commands:
# sudo /usr/sbin/setenforce 0
# sudo chown postgres /var/lib/pgsql/9.6/
#  sudo chown postgres:postgres /var/lib/pgsql/9.6/data
# chmod 700 /var/lib/pgsql/9.6/
# sudo systemctl start postgresql-9.6

Thank you all

Note: Looking at google, I noticed that there is more people with this 
problem.It's a problem with CentOS and PostgreSQL, which does not go 
down very well.

Re: [GENERAL] pg_basebackup issue

[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

[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

[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

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

[GENERAL] Total ram size study

[Melvin Davidson]

*Has anyone ever done a study on performance increase via ram increase?I
have a client on AWS with 8GB total ram (2GB shared_buffers), and I
amcurious if doubling the ram to 16GB (4GB shared_buffers) will result in
minimizing query response time.*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Total ram size study

[Vick Khera]

I've not done a formal study, but I've always found that throwing hardware
at the problem does wonders. My current database I made faster by bumping
RAM until the entire working set fits in memory. The server has 256GB of
RAM, half of which is used by ZFS for its purposes, and the other half for
Postgres. The prior iteration of the servers only had 64GB of RAM and the
difference was very remarkable.

On Sat, Apr 22, 2017 at 11:27 AM, Melvin Davidson 
wrote:

>
>
>
> *Has anyone ever done a study on performance increase via ram increase?I
> have a client on AWS with 8GB total ram (2GB shared_buffers), and I
> amcurious if doubling the ram to 16GB (4GB shared_buffers) will result in
> minimizing query response time.*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Total ram size study

[Melvin Davidson]

Thanks Vick,

Those were my thoughts as well. Your response gives me something to help
convince the client to kick up the hardware.

On Sat, Apr 22, 2017 at 12:20 PM, Vick Khera  wrote:

> I've not done a formal study, but I've always found that throwing hardware
> at the problem does wonders. My current database I made faster by bumping
> RAM until the entire working set fits in memory. The server has 256GB of
> RAM, half of which is used by ZFS for its purposes, and the other half for
> Postgres. The prior iteration of the servers only had 64GB of RAM and the
> difference was very remarkable.
>
> On Sat, Apr 22, 2017 at 11:27 AM, Melvin Davidson 
> wrote:
>
>>
>>
>>
>> *Has anyone ever done a study on performance increase via ram increase?I
>> have a client on AWS with 8GB total ram (2GB shared_buffers), and I
>> amcurious if doubling the ram to 16GB (4GB shared_buffers) will result in
>> minimizing query response time.*
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_basebackup issue

[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
mailto:david.g.johns...@gmail.com>> wrote:

On Saturday, April 22, 2017, chiru r mailto:chir...@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.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] Total ram size study

[Marcin Giedz]

Is it possible so you can share your postgresql.conf file and system settings 
if any for this 256G ram installation pls?
 Thx Marcin
 Sent from my BlackBerry 10 smartphone. From:  Melvin Davidson Sent:  Saturday, 
April 22, 2017 18:31 To:  Vick Khera Reply To:  Melvin Davidson Cc:  
pgsql-general@postgresql.org Subject:  Re: [GENERAL] Total ram size study
Thanks Vick,

Those were my thoughts as well. Your response gives me something to help
convince the client to kick up the hardware.

On Sat, Apr 22, 2017 at 12:20 PM, Vick Khera  wrote:

> I've not done a formal study, but I've always found that throwing hardware
> at the problem does wonders. My current database I made faster by bumping
> RAM until the entire working set fits in memory. The server has 256GB of
> RAM, half of which is used by ZFS for its purposes, and the other half for
> Postgres. The prior iteration of the servers only had 64GB of RAM and the
> difference was very remarkable.
>
> On Sat, Apr 22, 2017 at 11:27 AM, Melvin Davidson 
> wrote:
>
>>
>>
>>
>> *Has anyone ever done a study on performance increase via ram increase?I
>> have a client on AWS with 8GB total ram (2GB shared_buffers), and I
>> amcurious if doubling the ram to 16GB (4GB shared_buffers) will result in
>> minimizing query response time.*
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] pg_basebackup issue

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

[GENERAL] Memory consumption for Query

[dhaval jaiswal]

How to check how much memory query is consuming.

Is there tool can check of query consuming memory for the execution or output.


Let's say for following query how to calculate memory consumption.


select * from test where id=1;



Sent from Outlook

Re: [GENERAL] pg_basebackup issue

[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] Strange Issue between PSQL 9.3 and Label Zebra Printer?

[Adrian Klaver]

On 04/21/2017 05:14 PM, Periko Support wrote:

Hi guys.

I would to explain my current strange issue with our label printer
-.

Windows 8.1 x64, psqlodbc 9.5x86/x64 drivers tested.

The issue is that every time we print labels that read data from the
DB, the print start printing
with pause on every label.


What application are you doing this through?



Is like, select data, send result to printer, print the label, stop,
request new record start over.


Is each record different information?

Or are you talking about multiple copies of a single record?



PSQL is running under Linux Ubuntu 14.x PSQL 9.3.x

We have other printer from DataMAX which we don't have any issue and
the connection is by LAN.


So what does it do?



Wondering is some one here have in the past issues like this one?

Thanks for your time!!!





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

[GENERAL] Not sure this should be asked here but...

[Ron Ben]

Why to use mailining list rather than forum?
forum gives much more flexablitiy, easy to read and respond, allows to search for other simillar issues, topics can be tagged...
 
 

Re: [GENERAL] Not sure this should be asked here but...

[David G. Johnston]

On Sat, Apr 22, 2017 at 12:04 PM, Ron Ben  wrote:

> Why to use mailining list rather than forum?
> forum gives much more flexablitiy, easy to read and respond, allows to
> search for other simillar issues, topics can be tagged...
>
>
>
​Was this intended to display irony re: "allows to search for other similar
issues"?​

​
https://www.postgresql.org/message-id/CY4PR13MB1751ED37A959456DA0DE115599310%40CY4PR13MB1751.namprd13.prod.outlook.com

"browser interface to forums please?"

​David J.

Re: [GENERAL] Total ram size study

[John R Pierce]

On 4/22/2017 8:27 AM, Melvin Davidson wrote:

*Has anyone ever done a study on performance increase via ram increase?
I have a client on AWS with 8GB total ram (2GB shared_buffers), and I am
curious if doubling the ram to 16GB (4GB shared_buffers) will result in
minimizing query response time.*


entirely dependent on your data set and workload.if the working set 
fits in 2GB, then more memory likely won't do that much.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] pg_basebackup issue

[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

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





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

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

Re: [GENERAL] pg_basebackup issue

[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

[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

[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
mailto:david.g.johns...@gmail.com>> wrote:

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