can't enable row level security using sqlalchemy engine

Question

I have my sqlalchemy engine setup as such:

DATABASE_URL = (
    f"postgresql://postgres:{PG_PASS}@db.blabla.supabase.co:5432/postgres"
)
engine = create_engine(DATABASE_URL)

And I use the following to try and create a table in supabase

def create_table(table_name):
    table_name = table_name.lower()
    # Create new table
    with engine.connect() 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
        query = text(
            f"""
            CREATE POLICY "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')

This created the table, but neither enabled RLS nor created the policy.

In supabase, running this in the sql editor works: ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

I am not sure how to troubleshoot

Answer 1

Based on the comments from @snakecharmerb and @Oluwafemi Sule I got this to work. Running create_engine(..., echo=True) allows me to see logs of what's being executed.

And separating the functions ending with commit and close:

def create_table(table_name):
    with engine.connect() 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)
        conn.commit()
        conn.close()