使用SQLAlchemy Engine无法启用Supabase行级安全(RLS)及创建策略

核心原因：事务未提交 #

SQLAlchemy 使用 engine.connect() 创建的连接默认处于手动事务模式，所有 DDL/DML 操作会在上下文结束时自动回滚，除非显式提交。你的代码中创建表成功是因为部分 PostgreSQL 版本中 CREATE TABLE 会自动提交，但 ALTER TABLE 和 CREATE POLICY 属于需要显式提交的操作，因此被回滚了。

具体修复步骤 #

1. 使用自动提交的事务上下文 #

将 engine.connect() 替换为 engine.begin()，它会自动管理事务：上下文结束时自动提交所有操作，出错时回滚。

修改后的代码：

def create_table(table_name):
    table_name = table_name.lower()
    # 使用begin()自动管理事务
    with engine.begin() as conn:
        # Create table
        query = text(
            f"""
            CREATE TABLE IF NOT EXISTS {table_name} (
                id UUID PRIMARY KEY,
                user_id UUID references auth.users (id) NOT NULL
            );
            """
        )
        conn.execute(query)
        st.write(f'Table "{table_name}" created in database')

        # Enable RLS
        query = text(
            f"""
            ALTER TABLE {table_name} ENABLE ROW LEVEL SECURITY;
            """
        )
        conn.execute(query)
        st.write(f'RLS for table "{table_name}" created in database')

        # Create Policy（添加IF NOT EXISTS避免重复创建报错）
        query = text(
            f"""
            CREATE POLICY IF NOT EXISTS "User can CRUD their own tables only"
            ON public.{table_name}
            FOR ALL
            TO authenticated
            USING (
                auth.uid() = user_id
            )
            WITH CHECK (
                auth.uid() = user_id
            );
            """
        )
        conn.execute(query)
        st.write(f'Policy for table "{table_name}" created in database')

2. （备选）手动提交事务 #

如果坚持使用 engine.connect()，需要在所有操作后显式调用 conn.commit()：

with engine.connect() as conn:
    # ... 执行所有SQL操作 ...
    conn.commit()  # 显式提交事务

3. 添加异常捕获排查错误 #

为了确认操作是否真的执行成功，添加异常捕获打印错误信息：

def create_table(table_name):
    table_name = table_name.lower()
    try:
        with engine.begin() as conn:
            # ... 所有SQL操作 ...
    except Exception as e:
        st.error(f"操作失败: {str(e)}")
        raise

4. 验证策略与RLS状态 #

执行完成后，在Supabase SQL编辑器中运行以下命令验证：

-- 检查RLS是否启用
SELECT relname, rowsecurity FROM pg_class WHERE relname = 'your_table_name';

-- 检查策略是否存在
SELECT polname FROM pg_policy WHERE polrelid = 'public.your_table_name'::regclass;

内容的提问来源于stack exchange，提问作者hud