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. |