can we haz this, please? :)

As mentioned in D1516 I don't think DNS provides the proper granularity for this; somerset is a "database replica" server only coincidentally; all its databases that aren't the replica of the main database are actually primaries.

I think providing a pgbouncer instance which would allow access to all database instances (with aliases) from anywhere would be a more sensible move.

[ moving here my feedback from D1516 ]

In D1516#34010, @olasd wrote:

I'm not convinced this is such a good idea; this machine is way more than a "db replica" server (it only has one replica, most its databases are actually primary) and I don't think DNS provides the appropriate granularity level to record this information.

Right, the machine does multiple things, but CNAMEs are not mutually exclusive.

I think configuring pgbouncer to have transparent access to read-only/read-write databases would be more appropriate and flexible than doing dns hacks.

Can we have both?

I think providing a pgbouncer instance which would allow access to all database instances (with aliases) from anywhere would be a more sensible move.

For information, a pgbouncer got installed manually on the new belvedere machine (reflecting as far as i understood @ftigeot the one we had on prado).
I'm pushing towards puppetizing it (i will work on it today).

Can we have both?

I agree with @olasd that somerset is more than a replica [1] (got primary db indexer, occurrences cluster for retro-compatibility work for @grouss, testdedup cluster from @seirl's work, ...).

I'm waiting on olasd's ack/nack to merge or not the diff.

[1] https://intranet.softwareheritage.org/wiki/Databases_inventory#somerset

Cheers,

Discussing a bit more with @zack, the need is actually to be able to transparently connect from any machine to the db services (without the hassle of configuring) [1]

Something like the following would be enough (pg_service.conf, .pgpass):

psql service=swh
psql service=swh-replica
...

That means, puppetize the ~/.pg_service.conf mentioned in doc.
The requisite is that it's available for all users connecting to swh nodes.
Implementation wise, we need to look on how to install it in a centralized manner per node (/etc/pg_service.conf or some such).

Probably:

• we only want that for guest user (read only user). Or at least, we want that as a first incremental step.
• ~/.pgpass might be not necessary here if we have pgbouncer setuped (done).

Personally, i've dealt with this declaratively on my machines (with home-manager) and my use is simple:

• service="<db>": guest access to db (for example: swh, swh-deposit, swh-scheduler, etc...)
• service="admin-<db>": main user which has write access (for example: admin-swh, admin-swh-deposit, etc...)

[1] https://www.postgresql.org/docs/11/libpq-pgservice.html

We already have something that tries to bootstrap that [1]

It needs to be completed and actually deployed.

[1] https://forge.softwareheritage.org/source/puppet-swh-site/browse/production/site-modules/profile/manifests/devel/postgres.pp$8

Pushed in production.

To install it, a node's role needs to be updated to have the following instructions in its role include ::profile::devel::postgres.

Then it will be working for guest connection.

psql service=swh
psql service=swh-replica

This is great! Thanks a lot.

Question : is it just swh and swh-replica or there are other DBs? Can we have all DBs we use for development in there and document the names somewhere?

Also, can we add this role to *all* our hosts?

Question : is it just swh and swh-replica or there are other DBs? Can we have all DBs we use for development in there and document the names somewhere?

It's all dbs listed in the defaults.yaml (so mostly all except the azure replicas)

Can we have all DBs we use for development in there and document the names somewhere?

I worked according to the comments i described T1234#33001.

so we have the bare alias, for example:

• "swh": read access (read-only with associated guest access -> for people)
• "admin-swh": read/write access (with the associated owner user -> for services)

root@db0:~# grep "\\[" /etc/postgresql-common/pg_service.conf
[swh]
[admin-swh]
[swh-deposit]
[admin-swh-deposit]
[swh-scheduler]
[admin-swh-scheduler]
[swh-vault]
[admin-swh-vault]
[swh-lister]
[admin-swh-lister]
[swh-replica]
[admin-swh-replica]
[swh-indexer]
[admin-swh-indexer]
#### this is for the staging part so don't mind them
[staging-swh]
[admin-staging-swh]
[staging-swh-indexer]
[admin-staging-swh-indexer]
[staging-swh-scheduler]
[admin-staging-swh-scheduler]

Also, can we add this role to *all* our hosts?

Possibly, yes.
I'm not sure about the impacts of having the postgres dependencies everywhere though.
Do you have a list of the nodes you want first?

Also, I indirectly worked on that to help in the staging area (because i'd like to use that instead of "repeating" our yaml keys which are huge)

I've not yet deployed it everywhere.
Because:

• on the service side, there is a way to use it without too much hassle (i'm working on it)
• there is still some work on people's side though (PGPASSFILE env variable needs to be set)

For the people part, i'm not sure whether i want that automatically or not...
(we don't have user home deployment stanza yet and i don't have much time left for that now ;)

The missing manual step for people, adding the following instruction to their ".shrc" routine:

export PGPASSFILE=/etc/postgresql-common/pgpass.conf

If you think that's enough to do the manual part yourself, please let me know ;)

Cheers,

In the end the PGPASSFILE stuff cannot work with one file globally defined.
Because for some reason, the authors decided that it needs to be only user readable...
Thus having one for root is not usable for other users.

So i ended up (in staging right now) with defining the { ~/.pgpass , ~/.pg_service.conf } files for each user defined in a user list (defaulting to zack and me for now).
I expect that list to be updated for the location/hostname of the services needed it (adding swhstorage for example in the case of the storage service).

In T1234#36008, @ardumont wrote:

In the end the PGPASSFILE stuff cannot work with one file globally defined.
Because for some reason, the authors decided that it needs to be only user readable...

(sigh)

So i ended up (in staging right now) with defining the { ~/.pgpass , ~/.pg_service.conf } files for each user defined in a user list (defaulting to zack and me for now).

we have a swhdev unix group already, just change the user list to "all users in that group" (dynamically expanded, of course, so that we don't duplicate that list) and i think we're good

thanks !