Allow external connections to PostgreSQL
ClosedPublic
Actions

Authored by dereckson on Mar 29 2023, 18:08.

Details

Reviewers

dereckson

Maniphest Tasks

T1750: Import FANTOIR database

Commits

rOPS857190beb8ad: Allow external connections to PostgreSQL

Summary

As this change is the first to modify the PostgreSQL configuration
files, it provisions both postgresql.conf and pg_hba.conf.

PostgreSQL is currently configured to only accept local connections.
As the goal is to use it for applications on other hosts,
this change adds external connections possibilities to pg_hba.conf.

External connections are allowed to selected CIDR, depending
of the user and database selected. For fantoir and airflow users,
connections are expected from Dwellers and docker-002.

The 'peer' authentication mechanism is selected on the server,
so it's possible to use postgres account to get a root shell
on the database for maintenance purpose. No reference to 'trust'.

Ref T1750

References:

Test Plan

Connectivity: sockstat + nc -zv db-A-001 5432
Actual connection: airflow nasqueron upgrade

Diff Detail

Repository

rOPS Nasqueron Operations

Lint

Lint Not Applicable

Unit

Tests Not Applicable

Event Timeline

dereckson requested review of this revision.Mar 29 2023, 18:08

dereckson created this revision.

Harbormaster completed remote builds in B4639: Diff 7489.Mar 29 2023, 18:08

s/nbsp/space

Harbormaster completed remote builds in B4640: Diff 7490.Mar 29 2023, 18:10

dereckson planned changes to this revision.Mar 29 2023, 19:36

dereckson added inline comments.

roles/dbserver-pgsql/server/files/pg_hba.conf
23	peer is only for local connections (UNIX sockets), not for hosts connections (TCP/IP).
roles/dbserver-pgsql/server/files/postgresql.conf
22	Arbitrary strings, by opposition to enums, must be quoted by ''.

Fix configuration files. Split software and service, so we've the software/config/service order for first run.

Harbormaster completed remote builds in B4641: Diff 7491.Mar 29 2023, 19:43

Connectivity works like a charm for Airflow:

Dwellers

$ nc -zv 172.27.27.8 5432
Ncat: Version 7.91 ( https://nmap.org/ncat )                                                                                                                                                                                  Ncat: Connected to 172.27.27.8:5432.                                                                                                                                                                                          Ncat: 0 bytes sent, 0 bytes received in 0.03 seconds.

$ airflow nasqueron upgrade
/home/airflow/.local/lib/python3.7/site-packages/airflow/models/base.py:49 MovedIn20Warning: [31mDeprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. [32mTo prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to "sqlalchemy<2.0". [36mSet environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message.[0m (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
DB: postgresql+psycopg2://airflow:***@172.27.27.8/airflow
Performing upgrade with database postgresql+psycopg2://airflow:***@172.27.27.8/airflow
[2023-03-29 19:39:50,622] {migration.py:205} INFO - Context impl PostgresqlImpl.
[2023-03-29 19:39:50,622] {migration.py:212} INFO - Will assume transactional DDL.
[2023-03-29 19:39:50,625] {migration.py:205} INFO - Context impl PostgresqlImpl.
[2023-03-29 19:39:50,626] {migration.py:212} INFO - Will assume transactional DDL.
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running stamp_revision  -> 290244fb8b83
Upgrades done
2.5.2

db-A-001

$ sudo -u postgres psql airflow
psql (15.1)
Type "help" for help.

airflow=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

airflow=# \dt
                     List of relations
 Schema |              Name              | Type  |  Owner
--------+--------------------------------+-------+---------
 public | ab_permission                  | table | airflow
 public | ab_permission_view             | table | airflow
 public | ab_permission_view_role        | table | airflow
 public | ab_register_user               | table | airflow
 public | ab_role                        | table | airflow
 public | ab_user                        | table | airflow
 public | ab_user_role                   | table | airflow
 public | ab_view_menu                   | table | airflow
 public | alembic_version                | table | airflow
 public | callback_request               | table | airflow
 public | connection                     | table | airflow
 public | dag                            | table | airflow
 public | dag_code                       | table | airflow
 public | dag_owner_attributes           | table | airflow
 public | dag_pickle                     | table | airflow
 public | dag_run                        | table | airflow
 public | dag_run_note                   | table | airflow
 public | dag_schedule_dataset_reference | table | airflow
 public | dag_tag                        | table | airflow
 public | dag_warning                    | table | airflow
 public | dagrun_dataset_event           | table | airflow
 public | dataset                        | table | airflow
 public | dataset_dag_run_queue          | table | airflow
 public | dataset_event                  | table | airflow
 public | import_error                   | table | airflow
 public | job                            | table | airflow
 public | log                            | table | airflow
 public | log_template                   | table | airflow
 public | rendered_task_instance_fields  | table | airflow
 public | serialized_dag                 | table | airflow
 public | session                        | table | airflow
 public | sla_miss                       | table | airflow
 public | slot_pool                      | table | airflow
 public | task_fail                      | table | airflow
 public | task_instance                  | table | airflow
 public | task_instance_note             | table | airflow
 public | task_map                       | table | airflow
 public | task_outlet_dataset_reference  | table | airflow
 public | task_reschedule                | table | airflow
 public | trigger                        | table | airflow
 public | variable                       | table | airflow
 public | xcom                           | table | airflow
(42 rows)