[Bucardo-general] General clarifications about users permissions

[David Christensen]

> On Jul 5, 2020, at 9:56 PM, Rodrigo Rodovalho <rodrigo.rodovalho at gmail.com> wrote:
> 
> Hi,
> 
> I'm having a little trouble setting up the correct postgres permissions of the databases involved in a two element multi-master environment. I would like to describe here my understanding and gently ask for clarifications in what is wrong. In my tests, I was able to make it work while the tests were made in a single machine using pgbench. Now, with two, I'm having constant authentications failures.

[snip]

> My commands so far, on Master-1:
>     $ bucardo add db database_local dbname=database_name
>     $ bucardo add db database_remote dbname=database_name port=5432 host=master_2_ip user=user_name password=password_2 <---- This connection works fine!
>     $ bucardo add dbgroup database_group database_local:source database_remote:source
>     $ bucardo add all tables relgroup=tables_group  <---- ISSUE HERE: "FATAL:  Peer authentication failed for user "bucardo" at line 64" (it really says *peer*)
>     $ bucardo add sync sync_name telgroup=tables_group dbgroup=database_group

It looks like there might be an earlier line in `pg_hba.conf` which is requiring peer authentication (probably for all local users).

One thing to know when setting up connections here is to know that there are two potential connections involved:

1) Connections initiated by the `bucardo` program.  These will be using the environment of the user who executes the program itself.

2) Connections initiated by the `postgres` database itself.  These are things like plperl functions which talk to the other databases in the cluster (example: validate_sync()).

You will want to check and see if there are pg_hba.conf configurations which are preventing things from working properly.

I find that it is helpful to put a .pgpass or pg_service file with the actual passwords for the bucardo user in ~postgres and whatever user runs your service account for bucardo just to make the authentication more concise.

HTH,

David
--
David Christensen
Senior Software and Database Engineer
End Point Corporation
david at endpoint.com
785-727-1171



-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20200706/732f7ce0/attachment.sig>