Page MenuHomeSoftware Heritage
Differential D4375

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

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

Details

Reviewers
olasd
Group Reviewers
Reviewers
Maniphest Tasks
T2736: Configure db1 as new staging db server
Commits
rSPSITEa098f1f60758: postgresql/server: Allow remote 'postgres' connection to postgresql
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 created this revision.Oct 28 2020, 6:57 PM
Herald added a reviewer: Reviewers. · View Herald TranscriptOct 28 2020, 6:57 PM
Harbormaster completed remote builds in B16705: Diff 15479.Oct 28 2020, 6:57 PM
ardumont added a subscriber: vsellier.Oct 28 2020, 6:57 PM
ardumont added inline comments.
data/deployments/staging/common.yaml
182 ↗(On Diff #15479)

@vsellier we may no longer need it ;)

ardumont edited the test plan for this revision. (Show Details)Oct 28 2020, 6:58 PM
ardumont edited the summary of this revision. (Show Details)Oct 28 2020, 7:02 PM
olasd added a subscriber: olasd.Oct 29 2020, 10:51 AM

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

ardumont added a comment.EditedOct 29 2020, 10:58 AM

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

ardumont added inline comments.Oct 29 2020, 11:07 AM
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 mentioned this in T2736: Configure db1 as new staging db server.Oct 29 2020, 1:31 PM
ardumont updated this revision to Diff 15499.Oct 29 2020, 3:35 PM

Drop the template1 part

Harbormaster completed remote builds in B16725: Diff 15499.Oct 29 2020, 3:35 PM
ardumont edited the summary of this revision. (Show Details)Oct 29 2020, 3:36 PM
ardumont edited the test plan for this revision. (Show Details)
ardumont updated this revision to Diff 15500.Oct 29 2020, 3:41 PM
ardumont edited the test plan for this revision. (Show Details)

Rebase

Harbormaster completed remote builds in B16726: Diff 15500.Oct 29 2020, 3:41 PM
olasd added a comment.Oct 30 2020, 11:15 AM
In D4375#108927, @ardumont wrote:

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.

ardumont planned changes to this revision.Oct 30 2020, 12:03 PM

ack, thanks.

unneeded as explained, we can use postgres instead.

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

ardumont updated this revision to Diff 15521.Oct 30 2020, 1:03 PM

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

Harbormaster completed remote builds in B16748: Diff 15521.Oct 30 2020, 1:03 PM
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)Oct 30 2020, 1:10 PM
ardumont edited the test plan for this revision. (Show Details)
ardumont added inline comments.Oct 30 2020, 1:13 PM
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).
ardumont edited the summary of this revision. (Show Details)Oct 30 2020, 2:10 PM
olasd accepted this revision.Oct 30 2020, 2:33 PM
This revision is now accepted and ready to land.Oct 30 2020, 2:33 PM
Closed by commit rSPSITEa098f1f60758: postgresql/server: Allow remote 'postgres' connection to postgresql (authored by ardumont). · Explain WhyOct 30 2020, 2:35 PM
This revision was automatically updated to reflect the committed changes.
ardumont added a commit: rSPSITEa098f1f60758: postgresql/server: Allow remote 'postgres' connection to postgresql.

Revision Contents
Changeset List

PathSize
site-modules/
profile/
manifests/
postgresql/
49 lines

Diff 15535

View Options

site-modules/profile/manifests/postgresql/server.pp

Loading...
About · Developer information · Legal