INSTALL.PostgreSQL.md
Rama main · réplica de lectura del repositorio original.
# Journeyman with PostgreSQL
PostgreSQL is the recommended database for operational Journeyman installations. This
document covers both the tested single-host/local-database pattern and the additional
requirements for a remote PostgreSQL service.
The recommended new-install path is `deploy/ansible/install-journeyman.yml`, which
performs the local PostgreSQL setup automatically. The commands below document the
same steps for manual installs and troubleshooting.
## 1. Install and initialise PostgreSQL
On the tested RHEL-family host:
```bash
yum install postgresql-server
mkdir -p /var/lib/pgsql/data
/usr/bin/postgresql-setup --initdb
systemctl enable --now postgresql
```
The systemd service name is normally `postgresql`.
## 2. Use SCRAM password authentication
Set this in the active `postgresql.conf` before creating/resetting the Journeyman
role password:
```text
password_encryption = 'scram-sha-256'
```
Then reload/restart PostgreSQL and verify:
```bash
sudo -u postgres psql -tAc 'SHOW password_encryption;'
```
Expected result:
```text
scram-sha-256
```
Changing `password_encryption` does not convert an existing MD5 verifier. Reset the
Journeyman role password after changing the setting so PostgreSQL writes a SCRAM
verifier.
## 3. Configure local host-based authentication
For PostgreSQL on the Journeyman host, keep TCP access narrow. A typical
`pg_hba.conf` contains peer authentication for local administrative socket access and
specific SCRAM rules for Journeyman:
```text
local all all peer
host journeyman journeyman 127.0.0.1/32 scram-sha-256
host journeyman journeyman ::1/128 scram-sha-256
```
`pg_hba.conf` is first-match-wins. Put the specific Journeyman rules before any
broader TCP rule that could match the same connection. There is no need to permit the
PostgreSQL administrator over TCP merely to administer a local instance; use the Unix
socket/peer path with `sudo -u postgres psql`.
Reload PostgreSQL after changing HBA rules:
```bash
systemctl reload postgresql
```
## 4. Create the database role and database
Choose a strong, unique password. As the local PostgreSQL administrator:
```bash
sudo -u postgres psql
```
Then:
```sql
CREATE ROLE journeyman
LOGIN
PASSWORD 'REPLACE-WITH-A-STRONG-UNIQUE-PASSWORD';
CREATE DATABASE journeyman
OWNER journeyman
ENCODING 'UTF8'
TEMPLATE template0;
REVOKE ALL ON DATABASE journeyman FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE journeyman TO journeyman;
\connect journeyman
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO journeyman;
```
Journeyman does not require PostgreSQL superuser, `CREATEDB`, `CREATEROLE`, or
replication privileges.
If the role existed before SCRAM was enabled, reset its password now:
```sql
ALTER ROLE journeyman WITH PASSWORD 'REPLACE-WITH-A-STRONG-UNIQUE-PASSWORD';
```
## 5. Install the Python PostgreSQL requirements
Run pip as the Journeyman service account:
```bash
sudo -u journeyman -H /bin/bash
cd /opt/journeyman
/opt/journeyman/venv/bin/python -m pip install -r requirements-postgresql.lock
exit
```
This installs the exact base application and Psycopg dependency versions validated
for this Journeyman release. The human-maintained `requirements-postgresql.txt`
records direct dependencies; production installation deliberately uses the lock file
so pip does not re-resolve transitive dependencies at install time.
Verify the resulting environment:
```bash
/opt/journeyman/venv/bin/python scripts/check_dependency_lock.py --postgresql
```
## 6. Configure the database URI
Set `database.uri` in `/etc/journeyman/journeyman.yml`.
For a local server:
```yaml
database:
uri: postgresql+psycopg://journeyman:DB_PASSWORD@127.0.0.1:5432/journeyman
```
If the password contains URI-reserved characters such as `@`, `:`, `/`, `?`, `#`, or
`%`, percent-encode it before placing it in the URI.
For a remote PostgreSQL server, use a finite connect timeout and verified TLS when
available, for example:
```yaml
database:
uri: "postgresql+psycopg://journeyman:DB_PASSWORD@postgres.example.com:5432/journeyman?sslmode=verify-full&sslrootcert=/etc/journeyman/postgresql-ca.pem&connect_timeout=10"
```
Restrict network/firewall access to the required Journeyman application hosts. A
remote CA file should be readable by the service account but not writable by it.
## 7. Select the configuration before command-line tests
The installed `/opt/journeyman/.bashrc` activates the application virtual environment;
Journeyman command-line tools load `/etc/journeyman/journeyman.yml` themselves for interactive shells started as the
`journeyman` account with `HOME=/opt/journeyman`. For root shells, installation
bootstrap commands, or environments where that profile has not yet been installed,
load and export the environment explicitly before running Flask/SQLAlchemy tests:
```bash
cd /opt/journeyman
export JOURNEYMAN_CONFIG=/etc/journeyman/journeyman.yml
```
Without this step, a command may silently use the development/default SQLite URI
instead of the configured PostgreSQL database.
## 8. Test the connection
Using the loaded production environment:
```bash
sudo -u journeyman -H /bin/bash -c '
cd /opt/journeyman
export JOURNEYMAN_CONFIG=/etc/journeyman/journeyman.yml
/opt/journeyman/venv/bin/python - <<"PY"
from sqlalchemy import create_engine, text
from app.config import Config
engine = create_engine(Config.SQLALCHEMY_DATABASE_URI)
with engine.connect() as connection:
print(connection.execute(text("SELECT version()")) .scalar())
PY
'
```
A successful command prints the PostgreSQL server version.
Some libpq builds attempt GSS encryption before password authentication. On a host
where Kerberos/GSS is intentionally not used for this connection, `PGGSSENCMODE=disable`
can be set for a diagnostic `psql` test to remove the unrelated GSS negotiation
message.
## 9. Create or upgrade the Journeyman schema
Run committed migrations as the Journeyman account **before starting the scheduler,
runner, or environment builder**:
```bash
sudo -u journeyman -H /bin/bash -c '
cd /opt/journeyman
export JOURNEYMAN_CONFIG=/etc/journeyman/journeyman.yml
/opt/journeyman/venv/bin/flask --app run.py db upgrade
'
```
On a new empty database this creates the complete schema. On upgrades it applies only
new migrations. Do not use `db.create_all()` as a substitute for the committed
migration history.
Migration code must use native SQL boolean values (`TRUE`/`FALSE`, or SQLAlchemy
`sa.true()`/`sa.false()`) rather than SQLite-style integer `1`/`0` defaults. The test
suite contains a portability check for this class of regression.
## 10. Verify schema ownership
As a PostgreSQL administrator:
```sql
\connect journeyman
\dt
\dn+ public
```
The Journeyman database role should own/control its application objects without using
a privileged PostgreSQL role.
## Backup and recovery
Use the organisation's normal PostgreSQL backup/recovery tooling and test restoration
periodically. A database backup is not sufficient by itself: separately back up
`/etc/journeyman/credential.key`, because encrypted Journeyman credentials cannot be
recovered without it.
PostgreSQL HA, replication, external pooling, and managed database services are
platform responsibilities outside Journeyman itself.