Multi-Tenant AI Platform Permissions with PostgreSQL GrantRole

Question

Pulumi · Accepted Answer

When designing a multi-tenant AI platform that interfaces with a PostgreSQL database, you may need to manage database permissions in a way that isolates each tenant's data. In Pulumi, you can use the `GrantRole` resource to assign specific permissions to user roles within your PostgreSQL database, ensuring that each tenant can only access their own data.

Let's set up a Pulumi program that demonstrates how to use the `postgresql.GrantRole` resource to manage permissions for a multi-tenant AI platform. We will create two roles: one for application services that need read-only access to tenant data, and another for administrative services that require full access. The `GrantRole` resource allows us to delegate a subset of these permissions to other roles.

Below is a Pulumi program written in Python that uses the `postgresql.Role` resource to create these roles and the `postgresql.GrantRole` to assign privileges:

```python
import pulumi
import pulumi_postgresql as postgresql

# Assume we have a configured PostgreSQL provider

# Create a read-only role for application services
app_service_role = postgresql.Role("app-service-role",
    name="app_service_role",
    login=True,  # Allows the role to log in to the PostgreSQL database
    # The password should ideally be retrieved from a secret store or Pulumi's configuration system for security
    password="super-secure-password",
    privileges={
        "SUPERUSER": False,
        "CREATEDB": False,
        "CREATEROLE": False,
        "INHERIT": True,
        "LOGIN": True,
        "REPLICATION": False,
        "BYPASSRLS": False,
        "CONNECTION LIMIT": -1,
    }
)

# Create an administrative role with full access
admin_service_role = postgresql.Role("admin-service-role",
    name="admin_service_role",
    login=True,  # Allows the role to log in to the PostgreSQL database
    # The password should ideally be retrieved from a secret store or Pulumi's configuration system for security
    password="even-more-secure-password",
    privileges={
        "SUPERUSER": True,
        "CREATEDB": True,
        "CREATEROLE": True,
        "INHERIT": True,
        "LOGIN": True,
        "REPLICATION": True,
        "BYPASSRLS": True,
        "CONNECTION LIMIT": -1,
    }
)

# Grant the app_service_role to the admin_service_role with admin option
# This allows admin_service_role to grant/revoke the role to/from other roles/users
grant_role_to_admin = postgresql.GrantRole("grant-role-to-admin",
    role=admin_service_role.name,
    grant_role=app_service_role.name,
    with_admin_option=True
)

# Exports: Output the created role names for reference or use in other Pulumi stacks
pulumi.export("app_service_role_name", app_service_role.name)
pulumi.export("admin_service_role_name", admin_service_role.name)
```

In this program, we first create an `app_service_role` with limited privileges geared towards applications that don't need to alter the database structure, but just perform read operations. The `login` parameter is set to `True` to allow this role to connect to the database.

Next, we create an `admin_service_role` with administrative privileges, including creating databases and roles, which is suitable for more privileged operations like setting up the database schema or performing migrations.

Finally, we use the `GrantRole` resource to grant the `app_service_role` to the `admin_service_role` with the `with_admin_option` parameter set to `True`. This gives the admin role the ability to grant the app role to other users or roles, which is useful for managing permissions dynamically.

To use this infrastructure code, save it to a file, say `main.py`, and then simply run `pulumi up` in the same directory after configuring Pulumi for Python and setting up the PostgreSQL provider. The program will provision the specified resources in your PostgreSQL database. Remember to manage your passwords securely and avoid hardcoding them as shown in this example.