Page MenuHomeSoftware Heritage

postgresql/server: Allow remote postgres access to db (via pgbouncer as well)
ClosedPublic

Authored by ardumont on Oct 28 2020, 6:57 PM.

Details

Summary

This allows 'postgres' user connection to postgresql db (direct access or
via pgbouncer).
Ultimately, allowing to run swh db init-admin cli for staging/production nodes.

Related to T2736

Test Plan
  • vagrant
$ vagrant provision staging-db1
... <- ok
$ psql -U postgres -h 10.168.130.11 -p 5432 swh-scheduler  # <- or any other db, 5433 works as well
Password for user postgres:
psql (12.2, server 12.4 (Debian 12.4-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
>
  • bin/octocatalog

[2]

bin/octocatalog-diff --octocatalog-diff-args --no-truncate-details --to staging db1.internal.staging.swh.network
Found host db1.internal.staging.swh.network
WARN     -> Environment "open-template1" contained non-word characters, correcting name to open_template1
WARN     -> Environment "wip-pg-hba-rules-in-yaml" contained non-word characters, correcting name to wip_pg_hba_rules_in_yaml
Cloning into '/tmp/swh-ocd.EnwPLnXh/environments/production/data/private'...
done.
Cloning into '/tmp/swh-ocd.EnwPLnXh/environments/staging/data/private'...
done.
*** Running octocatalog-diff on host db1.internal.staging.swh.network
I, [2020-10-30T13:12:02.760548 #23147]  INFO -- : Catalogs compiled for db1.internal.staging.swh.network
I, [2020-10-30T13:12:04.304854 #23147]  INFO -- : Diffs computed for db1.internal.staging.swh.network
diff origin/production/db1.internal.staging.swh.network current/db1.internal.staging.swh.network
*******************************************
- Concat::Fragment[pg_hba_rule_deny access to postgresql user]
*******************************************
- Concat_fragment[pg_hba_rule_deny access to postgresql user]
*******************************************
- Postgresql::Server::Pg_hba_rule[deny access to postgresql user]
*******************************************
*** End octocatalog-diff on db1.internal.staging.swh.network

Diff Detail

Repository
rSPSITE puppet-swh-site
Lint
Automatic diff as part of commit; lint not applicable.
Unit
Automatic diff as part of commit; unit tests not applicable.

Event Timeline

ardumont added inline comments.
data/deployments/staging/common.yaml
182 ↗(On Diff #15479)

@vsellier we may no longer need it ;)

What's the point of a new swh-admin user (with hardcoded username) when we already set the password of the postgres user?

What's the point of a new swh-admin user (with hardcoded username) when we already set the password of the postgres user?

We wanted to have a user with just the right amount of permission to allow admin commands (instead of using the mother of all super admins ;)

Also the hard-coded name can be changed if that's a problem.

Do you imply it'd be fine to just use "postgres"?

data/deployments/staging/common.yaml
182 ↗(On Diff #15479)

in regards to D4374

(template1 is needed by the swh db create, if we switch to swh db init-admin which does not need it)

ardumont edited the test plan for this revision. (Show Details)
ardumont edited the test plan for this revision. (Show Details)

Rebase

What's the point of a new swh-admin user (with hardcoded username) when we already set the password of the postgres user?

We wanted to have a user with just the right amount of permission to allow admin commands (instead of using the mother of all super admins ;)

The user you're creating is superuser. It's exactly the same as the postgres user.

Also the hard-coded name can be changed if that's a problem.

Do you imply it'd be fine to just use "postgres"?

Yes.

ack, thanks.

unneeded as explained, we can use postgres instead.

On to find a way to allow pgbouncer connection with postgres user.

Allow postgres access to postgres/pgbouncer instead of creating a new swh-admin role

ardumont retitled this revision from postgresql/server: Add swh_admin user to allow db initialization to postgresql/server: Allow remote postgres access to db (via pgbouncer as well).Oct 30 2020, 1:04 PM
ardumont edited the summary of this revision. (Show Details)
ardumont edited the test plan for this revision. (Show Details)
site-modules/profile/manifests/postgresql/server.pp
75
  1. There is no way to drop the simple blocking rules in the puppetlabs module. So the stand has been to redeclare all rules dropping the blocking one. It works as the octocatalog-diff shows.
  1. Tried to declare it as yaml to no avail so far. That should not be a blocker for now though. We can always try that back at another time (we already spent too much time on this).
This revision is now accepted and ready to land.Oct 30 2020, 2:33 PM