Securing E-Commerce Chatbot Database Access with PostgreSQL Row-Level Security

In the previous blog posts, we explored how to leverage function tool calls and authenticate users with Zitadel and OAuth 2.0 for an e-commerce chatbot. In this post, we'll discuss how to secure the chatbot's database access using PostgreSQL Row-Level Security (RLS). This approach ensures that the chatbot accesses only the data belonging to the authenticated user, enhancing both security and privacy.

Why Use Row-Level Security?

Row-Level Security in PostgreSQL allows fine-grained access control at the row level. Instead of granting access to entire tables, you can define policies that restrict access based on specific conditions, such as the user's identity. This is particularly useful in multi-tenant applications like an e-commerce chatbot, where each user should see only their own data (e.g., orders, personal details).

By combining RLS with the chatbot's authentication mechanism, we can:

1. Ensure each user's data remains isolated.

2. Reduce the risk of accidental data leaks.

3. Simplify application logic by enforcing security policies at the database level.

Setting Up Row-Level Security

1. Creating Roles and Granting Permissions

Each user in the chatbot system is assigned a corresponding PostgreSQL role. This role is used to enforce access restrictions via RLS policies. Here's how to set up roles and permissions:

Before setting up roles for individual users, we create a database and a general user:

CREATE USER ecommerce_user WITH PASSWORD 'ecommerce_password';
CREATE DATABASE ecommerce WITH OWNER ecommerce_user;

A role is created for each user based on their unique user ID:

CREATE USER "123e4567-e89b-12d3-a456-426614174000";
GRANT "123e4567-e89b-12d3-a456-426614174000" TO ecommerce_user;

The necessary permissions for accessing specific tables are granted to the role. For example:

GRANT SELECT (user_id, username) ON users TO "123e4567-e89b-12d3-a456-426614174000";
GRANT SELECT (product_id, product_name, product_description, thumbnail_url, dimension, weight) ON products TO "123e4567-e89b-12d3-a456-426614174000";
GRANT SELECT (order_id, user_id, ordered_at, delivery_status, delivery_time) ON orders TO "123e4567-e89b-12d3-a456-426614174000";
GRANT SELECT (order_id, product_id, quantity, price_at_time) ON order_items TO "123e4567-e89b-12d3-a456-426614174000";

These statements ensure that the user can only access the data required for the chatbot to function.

2. Enabling RLS on Tables

To enable RLS, use the ENABLE ROW LEVEL SECURITY command on the relevant tables. For our chatbot, we'll enable RLS on the users, orders, and order_items tables:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;

3. Defining RLS Policies

RLS policies define the conditions under which rows are accessible. For instance, users should only access their own data. Here's how we implement it:

The chatbot queries the users table to retrieve details of the authenticated user. The policy ensures a user can only view their own record. Note that the current_user is the PostgreSQL user as a string while the user_id is the user ID as a UUID data type. To compare the two, we need to cast the user_id to a string (user_id::text).

CREATE POLICY users_read_policy
ON users
FOR SELECT
USING (
    current_user = user_id::text
);

To restrict access to orders belonging to the authenticated user:

CREATE POLICY orders_read_policy
ON orders
FOR SELECT
USING (
    current_user = user_id::text
);

This table stores details about items in an order. Access is granted only if the order belongs to the authenticated user:

CREATE POLICY order_items_read_policy
ON order_items
FOR SELECT
USING (
    EXISTS (
        SELECT 1
        FROM orders
        WHERE orders.order_id = order_items.order_id AND orders.user_id::text = current_user
    )
);

Integrating RLS with the Chatbot

When a user authenticates, the chatbot assigns the PostgreSQL role corresponding to the authenticated user's ID. This is achieved with the SET ROLE command and ensures that the access to the database is restricted to the user's data for every query that is executed with this cursor object. So no matter what parameters are passed to the query by the LLM, the result will only contain data that belongs to the user.

cursor.execute(sql.SQL("SET ROLE {};"), (user_id,))

Main Function

Let's now take a look at how we can integrate the function tool calls and the user authentication from the previous blog posts with PostgreSQL's row-level security.

def main():
    dotenv.load_dotenv()

    endpoint = os.environ["AZURE_OPENAI_ENDPOINT"]
    api_key = os.environ["AZURE_OPENAI_API_KEY"]
    model = os.environ["AZURE_OPENAI_MODEL"]
    postgres_user = os.environ["POSTGRES_USER"]
    postgres_password = os.environ["POSTGRES_PASSWORD"]
    postgres_db = os.environ["POSTGRES_DB"]
    client_id = os.environ["ZITADEL_CLIENT_ID"]
    zitadel_domain = os.environ["ZITADEL_DOMAIN"]

    port = 8081
    redirect_uri = f"http://localhost:{port}"

    # Perform authentication
    try:
        id_token, access_token = authenticate(client_id, zitadel_domain, redirect_uri)

        # Code omitted for brevity (see the previous blog post for the full implementation)

        # Get the email from the payload
        username = payload["email"]

    except Exception as e:
        logger.error("Authentication failed", error=str(e))
        return

    conn = psycopg2.connect(
        host="localhost",
        database=postgres_db,
        user=postgres_user,
        password=postgres_password,
    )

    # Get user id from username and set role
    cursor = conn.cursor()
    user = get_user_by_username(cursor, username)
    user_id = user.user_id

    # Limit access to the database to the user's data
    cursor = conn.cursor()
    cursor.execute(sql.SQL("SET ROLE {};").format(sql.Identifier(user_id)))

    # Initialize OpenAI client and prompt
    client = openai.AzureOpenAI(
        azure_endpoint=endpoint, api_key=api_key, api_version="2023-09-01-preview"
    )

    messages = [
        {
            "role": "system",
            "content": f"You are an assistant for an e-commerce application. Respond to user queries by performing relevant operations. You are currently answering inquiries submitted by the user with username {username} and you can only access this user's data.",
        },
    ]

    functions = [
        get_user_by_username_function_tool(),
        get_all_products_function_tool(),
        get_product_by_id_function_tool(),
        get_orders_by_user_id_function_tool(),
        get_order_items_by_order_id_function_tool(),
    ]

    # Prompt user for input
    user_input = input("Enter your message: ")
    messages.append({"role": "user", "content": user_input})

    # Get chat response and print it
    # Note that a cursor object bound to the user's PostgreSQL role is used here
    response = get_chat_response(cursor, client, model, messages, functions)
    print(response.response_text)

Conclusion

Security is a critical consideration in the deployment of agentic AI systems like autonomous e-commerce chatbots due to their ability to access and process potentially large amounts of sensitive data across various systems. These autonomous agents often operate with minimal human oversight, making them both powerful and potentially vulnerable to misuse, unauthorized access, and data breaches. As agentic AI integrates into systems like customer relationship management (CRM), enterprise resource planning (ERP), and Internet of Things (IoT) networks, ensuring the security of data and operations becomes paramount to protect user privacy, maintain trust, and comply with regulatory standards.

The consequences of weak security in agentic AI can be severe, ranging from exposure of confidential information to the manipulation of workflows and decision-making processes. Effective security measures must therefore be in place to safeguard sensitive data, prevent unauthorized access, and ensure the integrity of AI-driven processes.

One effective approach to enhancing security in agentic AI systems is leveraging database-level security features, such as PostgreSQL row-level security (RLS), in combination with robust user authentication mechanisms. RLS allows fine-grained control over data access by restricting which rows in a table can be viewed or modified by a particular user. This ensures that users and AI agents can access only the data they are authorized to see or manipulate, reducing the risk of accidental or malicious data exposure.

Disclaimer

This blog post is a simplified example and does not cover all aspects that have to be considered when deploying any LLM powered application to production. In a real-world application, you would need to consider additional security measures.