Database Build: Schema, Security, Backup, and ETL Implementation

Overview

This lab brings together every skill you have built during the program. You will implement a production-quality database system in PostgreSQL 15, covering schema deployment, role-based security, encrypted connections, automated backup with a tested restore, and an ETL pipeline that pulls data from two external sources. Work methodically: a mistake in security configuration or backup scheduling can have serious consequences in a real workplace.

Schema Implementation

Start by executing your approved DDL scripts in order: sequences and types first, then parent tables, then child tables that reference them. Every table must include primary key constraints, and every relationship must be enforced with a foreign key constraint. Add NOT NULL and CHECK constraints as specified in your design document. Create indexes on foreign key columns and on any column your query plan identified as a frequent filter. After DDL runs, verify the structure with `\d+ tablename` in psql.

Populate the database with at least 500 realistic rows. Use `COPY` to import the supplied CSV files, then run your Python script (with psycopg2) to generate synthetic rows that respect referential integrity — insert parent rows before child rows. After loading, run the instructor-provided SQL assertion queries. These queries use `EXCEPT`, `NOT EXISTS`, and aggregate checks to confirm that no orphan rows, duplicate primary keys, or out-of-range values exist. All assertions must return zero rows or the value zero to pass.

Role-Based Access Control and TLS

Create exactly the three required roles:

• readonly_analyst — `SELECT` on specific tables, no `INSERT`, `UPDATE`, or `DELETE`.
• data_entry_op — `INSERT` and `UPDATE` on designated tables only; row-level security (RLS) policies where the design requires a user to see only their own records.
• dba_role — full privileges plus the ability to create and drop objects.

Grant column-level privileges with `GRANT SELECT (col1, col2) ON table TO role;` when a role must see some columns but not others (for example, masking salary from the analyst). Enable row-level security with `ALTER TABLE t ENABLE ROW LEVEL SECURITY;` and define policies using `CREATE POLICY`.

For encrypted connections, generate a self-signed certificate with OpenSSL (`openssl req` and `openssl x509`), place the server key and certificate where PostgreSQL expects them, set `ssl = on` in `postgresql.conf`, and update `pg_hba.conf` to require `scram-sha-256` over TLS for all remote connections. Refer to the CIS PostgreSQL Benchmark for the specific settings it recommends for authentication and logging — follow them as closely as your sandbox allows.

Document every configuration decision in a written security policy memo. Then perform a penetration check: log in as each role and attempt an operation that role is forbidden to perform (for example, `DELETE` as readonly_analyst). PostgreSQL should return a permission denied error. Record the results in your memo.

Automated Backup and Restore Drill

Schedule a `pg_dump` backup using cron (Linux) or Task Scheduler (Windows). A typical cron line for a nightly dump at 2 a.m. looks like:

```

0 2 * pg_dump -Fc -U postgres mydb > /backups/mydb_$(date +\%Y\%m\%d).dump

```

The `-Fc` flag writes a custom-format archive that `pg_restore` can use for selective or full restore. Store backups outside the database server's primary disk when possible.

The restore drill is not optional — it is a graded checkpoint. Drop the database (or create a fresh target), then run `pg_restore -d newdb /backups/mydb_YYYYMMDD.dump`. Confirm row counts match the pre-drop state. The restore must recover all data from within the prior 24 hours with zero data loss. Document the exact commands used and the time elapsed.

ETL Pipeline

Your pipeline extracts from two sources: a CSV flat file and a SQLite database. Use Python 3 with pandas for extraction and transformation, and psycopg2 for loading into PostgreSQL.

Extract: Read the CSV with `pd.read_csv()`. Open the SQLite source with `sqlite3` or `pandas.read_sql_query()`.

Transform: Apply documented rules — cast date strings to `datetime`, deduplicate on a business key with `drop_duplicates()`, fill or reject nulls according to your policy, and standardize text case. Log every rejected row to an error log file.

Load: Use a psycopg2 connection inside a transaction block (`BEGIN` / `COMMIT`). Write a row count to a transaction log before and after the load. If any `INSERT` raises an exception, roll back the entire batch and log the error; never leave the database in a partially loaded state.

Schedule the pipeline using cron or pgAgent. Validate by comparing the before-and-after row counts and reviewing the error log for unhandled exceptions. The loaded row count must fall within 5% of the expected count.

Safety and Professional Practices

Database administration involves physical and logical risks. On physical infrastructure: follow your organization's lockout/tagout and access-control procedures before working inside a server rack. Wear appropriate PPE (antistatic wrist strap) when handling storage media. On logical infrastructure: never store credentials in plain text in scripts — use environment variables or a secrets manager. Keep private keys for TLS certificates restricted to the postgres OS user (`chmod 600`). Test every change in the sandbox before applying it to any system that holds real data.