Entropy Data

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 ). For production environments, you may want to use a dedicated user with appropriate permissions.

When to Use the Default Superuser

Using the default superuser is acceptable for:

  • Local development
  • Testing and CI/CD pipelines
  • Proof of concept deployments
  • Environments where the database is dedicated to Entropy Data

Consider a dedicated user for:

  • Shared database servers with multiple applications
  • Production environments with strict security policies
  • Compliance requirements

Prerequisites

  • PostgreSQL server with superuser access to create roles and extensions
  • Database for Entropy Data (default: postgres)

Required Permissions

The Entropy Data application uses automated database migrations and requires:

  • Full DDL permissions (CREATE, ALTER, DROP tables, indexes, sequences)
  • Full DML permissions (SELECT, INSERT, UPDATE, DELETE)
  • Permission to create extensions (vector, hstore, uuid-ossp, pg_trgm)

Create a Dedicated User

Connect as superuser and run the following commands:

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

-- Connect to the database
\c :database

-- Create the dedicated user
CREATE USER :username WITH PASSWORD :'password';

-- Grant permissions on existing objects
GRANT ALL PRIVILEGES ON DATABASE :database TO :username;
GRANT ALL ON SCHEMA public TO :username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO :username;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO :username;

-- Grant permissions on future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO :username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO :username;

Grant admin privileges for creating extensions during initial installation:

Note: The admin group grant is required because database migrations create extensions with the current user. You can revoke this after initial setup.

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

After the initial setup, you can revoke these privileges:

-- Azure PostgreSQL:
REVOKE azure_pg_admin FROM :username;
-- AWS RDS:
REVOKE rds_superuser FROM :username;

Migrate from Superuser to Dedicated User

If you already have Entropy Data running with the superuser, you can switch to a dedicated user:

  1. Create the dedicated user and grant permissions (see above)
  2. Stop Entropy Data
  3. Update the environment variables to use the new user
  4. Start Entropy Data

No data migration is needed since the database remains the same.

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