Configure PostgreSQL with a Dedicated User

Set up a dedicated PostgreSQL user for the Entropy Data application database.

By default, most PostgreSQL installations create an initial superuser (often called postgres). Running Entropy Data with it is fine for local development, testing, CI/CD, and databases that are dedicated to Entropy Data anyway. Use a dedicated user on shared database servers, in production environments with strict security policies, and where compliance requires it.

Create the User

Entropy Data runs its database migrations (Flyway) automatically at startup, with the configured application user. That user creates the schema and therefore owns it, which is all it needs to create, alter, and drop its own tables and indexes later on.

Connect as superuser and run:

-- Configure variables (adjust as needed)
\set database 'postgres'
\set username 'entropy_data'
\set password 'your-secure-password'

\c :database

CREATE USER :username WITH PASSWORD :'password';
GRANT ALL PRIVILEGES ON DATABASE :database TO :username;
GRANT ALL ON SCHEMA public TO :username;

Install the Extensions

The first migration run installs three extensions:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Recommended: run the three statements above as a superuser before starting Entropy Data for the first time. The migration then finds them in place, IF NOT EXISTS turns it into a no-op, and the application user never needs an admin role.

Alternative: grant the admin role for the initial installation and revoke it afterwards.

-- Azure PostgreSQL:
GRANT azure_pg_admin TO :username;
-- AWS RDS:
GRANT rds_superuser TO :username;

On Azure Database for PostgreSQL Flexible Server, vector also has to be allow-listed through the azure.extensions server parameter.

Configure Entropy Data

Update your environment variables to use the new user:

environment:
  - SPRING_DATASOURCE_URL=jdbc:postgresql://your-host:5432/postgres
  - SPRING_DATASOURCE_USERNAME=entropy_data
  - SPRING_DATASOURCE_PASSWORD=your-secure-password

Upgrades

Migrations that ship with later Entropy Data versions run with the same application user and need no admin role. The only exception would be a release that introduces a new PostgreSQL extension. We announce that in the release notes, and you install it the same way as during the initial setup.

Switch an Existing Installation

If Entropy Data already runs with the superuser, switching to a dedicated user needs no data migration, but the existing tables are still owned by the superuser, and ownership is what lets later migrations run ALTER TABLE.

1. Create the dedicated user (see above)

2. Stop Entropy Data

3. Transfer ownership of the existing tables. Connect as a superuser and run:

   DO $$
   DECLARE
     obj record;
   BEGIN
     FOR obj IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
       EXECUTE format('ALTER TABLE public.%I OWNER TO %I', obj.tablename, 'entropy_data');
     END LOOP;
   END
   $$;
   

   CopyCopied!

   Indexes and sequences follow their table, so tables are all you need to transfer.

4. Update the environment variables and start Entropy Data

Without step 3, everything keeps working until the next version upgrade, which then fails with ERROR: must be owner of table ... while applying its migrations.

Optional: Separate Migration User

To keep the runtime user from being able to change the schema at all, let Flyway migrate with its own user. Create a second user that owns the schema:

\set migration_username 'entropy_data_migration'

CREATE USER :migration_username WITH PASSWORD 'your-secure-migration-password';
GRANT ALL PRIVILEGES ON DATABASE :database TO :migration_username;
GRANT ALL ON SCHEMA public TO :migration_username;

-- the runtime user only needs to look into the schema, not to create in it
REVOKE ALL ON SCHEMA public FROM :username;
GRANT USAGE ON SCHEMA public TO :username;

-- default privileges apply per creating role, so grant them for the migration user
ALTER DEFAULT PRIVILEGES FOR ROLE :migration_username IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO :username;
ALTER DEFAULT PRIVILEGES FOR ROLE :migration_username IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO :username;

Set this up before the first start, so that every table is created under these default privileges. Then point Flyway at the migration user:

environment:
  - SPRING_DATASOURCE_URL=jdbc:postgresql://your-host:5432/postgres
  - SPRING_DATASOURCE_USERNAME=entropy_data
  - SPRING_DATASOURCE_PASSWORD=your-secure-password
  - SPRING_FLYWAY_URL=jdbc:postgresql://your-host:5432/postgres
  - SPRING_FLYWAY_USER=entropy_data_migration
  - SPRING_FLYWAY_PASSWORD=your-secure-migration-password

The runtime user can then run SELECT, INSERT, UPDATE, and DELETE, while CREATE TABLE, ALTER TABLE, and DROP TABLE are rejected.