Setting Up PostgreSQL¶

Installation¶

First download PostgreSQL on your operating system.

Secure Database Configuration¶

To ensure your database configuration is secure, do not allow access from all internet sources. Avoid settings such as 0.0.0.0/0 in pg_hba.conf and listen_addresses='*' in postgresql.conf.

For remote connections, use an SSH tunnel.

Example:

ssh -L 5432:localhost:5432 user@remote_host

Creating Database and User¶

Create a new database and user by executing the following commands:

sudo -u postgres psql

CREATE USER <name> WITH PASSWORD 'securepassword';
CREATE DATABASE <name> OWNER <name>;

Secure Schema Usage¶

To enhance security, constrain ordinary users to user-private schemas. Ensure that no schemas have public CREATE privileges. For each user who needs to create non-temporary objects, create a schema with the same name as the user.

Example:

CREATE SCHEMA <name> AUTHORIZATION <name>;

This setup ensures that users access their own schemas by default.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Documentation:

PostgreSQL Documentation on Schemas Patterns