Skip to content

migrations: move privileged sql to separate files

Warren Gifford requested to merge kcw/move-priv-migrations into main

Created by: kevinwojo

Some of our customers have some security restrictions around their Postgres DB deployments where an application user should have the minimal set of permissions.

I think it's reasonable to grant the service account users ALL on their DATABASE but having the ability to managed the instance may become problematic.

GRANT ALL PRIVILEGES ON DATABASE sourcegraph TO sourcegraph_user;

In our pre-packaged deployments, we run CodeIntel DB and the Sourcegraph DB (pgsql) with SUPERUSER privileges which allows our migrations to fully manage the respective DATABASEs. This is convenient but falls down when a customer requires a dedicated application user for the database instance.

I'm currently drafting some instructions on how to have a database administrator setup a dedicated service user and database as SUPERUSER and delegate access to the DATABASE to the service user.

The goal is to have some light tooling around to assist the customer on setting up the databases. This (not yet created) tooling would skip the first migration of each DB (put an entry in schema_migrations with "dirty=false").

An unprivileged service account need to skip these migrations:

DB Migration Reason
frontend 1528395833_privileged_initialization.up Creates extensions and comments on the extensions. This can only be done by a superuser.
frontend 1528395862_remove_sg_service_role.up Attempts to drop a ROLE if it exists. This can only be done by a superuser as it's an instance-wide structure.
codeintel 1000000014_privileged_initialization.up Creates extensions and comments on the extensions. This can only be done by a superuser.

Merge request reports