Create mechanism to create/update or delete new users in database

[pcrespov]

Following from discussion with @mrnicegyu11 and @YuryHrytsuk in ITISFoundation/osparc-simcore#6426

---

Alembic for managing PostgreSQL users and other database configuration tasks (such as privileges, roles, etc.) is not the most common or recommended approach. Alembic is primarily intended for schema migrations—managing the structure of the database (tables, columns, indexes, etc.) rather than configuration-related tasks like user management.

Let’s dive into the pros and cons of using Alembic for managing users and explore whether this approach is common and best suited for the task.

Pros of Using Alembic for User Management

1. Consistency Across Environments:

   • By using Alembic, you ensure that the creation of users (e.g., a read-only user) is handled consistently across all environments (development, staging, production). It becomes part of the same version-controlled migration flow as the schema changes, ensuring that no manual steps are required to configure users in each environment.

2. Single Point of Management:

   • Alembic provides a single mechanism for both schema migrations and user management, meaning that everything related to the database is managed through the same tool. This can reduce the number of tools and processes needed.

3. Automation:

   • Automating user creation as part of the migration process reduces the likelihood of human error (e.g., forgetting to manually create a user in a staging or production environment). This can be especially useful in CI/CD pipelines where everything is expected to be automated.

Cons of Using Alembic for User Management

1. Alembic’s Purpose:

   • Alembic is intended to manage the schema of a database (migrations involving tables, indexes, etc.), not the configuration of the database itself (e.g., users, permissions, settings). Mixing the two can blur the responsibilities of the migration tool and make it harder to maintain and reason about.
   • For instance, schema changes affect the structure of the database for all users, while user management is usually a different concern handled by the DBA or application configuration.

2. Not Common Practice:

   • It’s not common to use Alembic (or similar tools) to manage database users, as users/roles and their permissions are typically managed at the PostgreSQL level outside the application migration lifecycle. Typically, user management is done manually by a DBA or via infrastructure-as-code tools like Terraform, Ansible, or Docker (through docker-compose init scripts).

3. Security Concerns:

   • By including user creation in Alembic migrations, you might introduce security concerns by exposing user credentials (e.g., passwords) in scripts that are version-controlled. This can lead to sensitive information being stored in places where it might not belong.
   • Environment variable usage can mitigate this risk, but many consider user management to be outside the scope of what application migrations should handle.

4. Complexity in Rollbacks:

   • Managing users via Alembic makes it harder to handle rollbacks cleanly. While it is straightforward to reverse schema changes, deciding what to do with users during a rollback can be more complicated. For example, rolling back a migration might require removing users or revoking permissions, which might not be the desired behavior.

5. Separation of Concerns:

   • User management and schema management are different concerns. Schema migrations are application-focused, whereas user management is infrastructure-focused. Conflating these two responsibilities can lead to confusion over who is responsible for managing different aspects of the database (e.g., developers vs. DBAs).

Alternative Approaches (More Common and Recommended)

1. Infrastructure as Code (IaC):

   • Tools like Terraform, Ansible, or Chef are better suited for managing the infrastructure of your application, including PostgreSQL user creation and permissions. These tools provide a clear separation between infrastructure configuration (e.g., users, roles, database settings) and application-level concerns (e.g., schema changes).
   • For example, Terraform can be used to manage the creation of users, roles, and databases while Alembic manages the schema.

   Example with Terraform:

   resource "postgresql_role" "readonly_user" {
     name     = var.POSTGRES_READONLY_USER
     password = var.POSTGRES_READONLY_PASSWORD
     login    = true
   }
   
   resource "postgresql_grant" "grant_select" {
     role     = postgresql_role.readonly_user.name
     database = var.POSTGRES_DB
     schema   = "public"
     object_type = "table"
     privileges  = ["SELECT"]
   }

2. Docker or docker-compose Init Scripts:

   • If you are using Docker to manage PostgreSQL instances, you can include SQL scripts in the docker-entrypoint-initdb.d directory to create users and assign permissions as part of the container startup process.
   • This is a good option for local development environments, and it keeps the user creation logic out of your application migration files.

3. Manual DBA or Admin Management:

   • For production systems, user management is often done manually by a DBA or system administrator. DBAs typically create and manage roles, permissions, and other database-level configurations. This provides tighter control over access and security.

When to Consider Using Alembic for User Management?

While Alembic is not typically used for user management, there may be some niche cases where it's appropriate:

• Testing and Development Environments: If you need to automate the creation of users across multiple test or development environments, and you want everything to be self-contained in one process, it might make sense to use Alembic.
• Single Responsibility Team: In small teams where the responsibility for both schema and user management falls on the developers, automating everything via Alembic could simplify workflows (but it still comes with the risks outlined above).

Conclusion: Is This Approach Recommended?

• Not Common: Using Alembic for user management is uncommon and not generally recommended. Alembic is best suited for schema migrations, while PostgreSQL user management is usually handled at the infrastructure level using tools like Terraform or manually by DBAs.

• Best Practice: The best practice is to separate schema migrations (handled by Alembic) from infrastructure management (handled by tools like Terraform, Docker, or manual DBAs). This separation ensures cleaner, more maintainable code and reduces the risk of introducing security issues or complexity.

By keeping Alembic focused on schema migrations and using other tools to manage database users and configuration, you'll have a more maintainable, scalable, and secure setup.