Giving AI Agents Read-Only PostgreSQL Access Without Touching Production

AI agents can be useful for answering questions about application data, generating reports, investigating records, and analyzing operational information.

But giving an agent direct access to a production PostgreSQL database creates an important security problem.

An AI agent may generate SQL dynamically. Even when the agent is instructed to perform read-only operations, instructions alone should not be treated as a security boundary.

A safer design is to enforce read-only access at the database and infrastructure layers.

The basic idea is simple:

AI Agent
   |
   v
Read-Only Database Access
   |
   v
PostgreSQL
   |
   X
Production Writes Blocked

The agent can query approved data, but the database itself prevents it from modifying production records.

This article explains how to implement this pattern in PostgreSQL and C#, along with safer alternatives such as replicas and restricted database roles.

Why Read-Only Access Matters

Consider an agent that generates SQL based on a user's request.

A normal request might result in:

SELECT id, name, status
FROM orders
WHERE customer_id = 1001;

That is a read operation.

But an incorrectly generated query could potentially be:

DELETE FROM orders
WHERE customer_id = 1001;

Or:

UPDATE orders
SET status = 'Cancelled';

An application-level instruction such as:

Only generate SELECT queries.

is useful, but it is not sufficient protection.

The database should enforce the rule independently.

The Basic Architecture

A safer architecture looks like this:

                   +----------------+
                   |    AI Agent    |
                   +-------+--------+
                           |
                           v
                  +-------------------+
                  | Read-Only Role    |
                  +---------+---------+
                            |
                            v
                  +-------------------+
                  | PostgreSQL        |
                  | Production Data   |
                  +-------------------+
                            |
                            X
                     INSERT / UPDATE /
                     DELETE blocked

The important security boundary is the PostgreSQL role.

The agent should authenticate using credentials that have only the permissions it requires.

Creating a Read-Only PostgreSQL Role

Start by creating a dedicated role.

CREATE ROLE agent_reader
LOGIN
PASSWORD 'replace-with-a-secure-secret';

The password should not be stored directly in source code or committed to a repository.

Next, allow the role to connect to the required database:

GRANT CONNECT ON DATABASE production_db
TO agent_reader;

Then provide access to the required schema:

GRANT USAGE ON SCHEMA public
TO agent_reader;

Finally, grant read access:

GRANT SELECT ON ALL TABLES IN SCHEMA public
TO agent_reader;

The role can now read the tables for which it has permissions.

It does not automatically receive permission to modify them.

Restricting Future Tables

There is an important detail here.

If new tables are created later, a previous GRANT SELECT ON ALL TABLES statement does not automatically grant permissions on every future table.

PostgreSQL supports default privileges for this scenario:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO agent_reader;

This should be applied by the appropriate object owner.

The exact privilege strategy should match how your database objects are created and managed.

Revoking Unnecessary Permissions

A good security model starts with least privilege.

You can explicitly revoke permissions that should not be available:

REVOKE INSERT, UPDATE, DELETE, TRUNCATE
ON ALL TABLES IN SCHEMA public
FROM agent_reader;

You can also remove broader privileges if they were previously granted:

REVOKE CREATE
ON SCHEMA public
FROM agent_reader;

The objective is not merely to tell the agent not to write.

The database account should lack the ability to write.

Giving Access to Only Specific Tables

An agent does not necessarily need access to the entire production database.

Suppose the agent only needs:

customers
orders
products

You can grant access specifically:

GRANT SELECT
ON TABLE customers, orders, products
TO agent_reader;

This is generally safer than granting access to every table.

The architecture becomes:

Agent
 |
 +--> customers
 |
 +--> orders
 |
 +--> products
 |
 X--> payments
 X--> credentials
 X--> internal_security

This reduces the amount of production data exposed to the agent.

Column-Level Access

Sometimes even an entire table is too much.

For example, a customer table might contain:

id
name
email
phone
address
password_hash

The agent may only need:

id
name
email

PostgreSQL supports column-level privileges.

For example:

GRANT SELECT (id, name, email)
ON customers
TO agent_reader;

This allows the role to read only the specified columns.

Column-level permissions can be useful when a table contains information that the agent does not need.

Using a View for Safer Agent Access

Another useful pattern is exposing a controlled view.

Suppose the production table contains internal fields:

CREATE VIEW agent_customer_summary AS
SELECT
    id,
    name,
    status
FROM customers;

Then grant access to the view:

GRANT SELECT
ON agent_customer_summary
TO agent_reader;

The agent does not need direct access to the underlying table.

This creates a controlled database interface:

Production Tables
       |
       v
Controlled View
       |
       v
Read-Only Agent Role
       |
       v
AI Agent

Views can also simplify the data presented to the agent.

Using a Read Replica

A read-only role prevents writes, but the agent may still execute expensive queries against the production database.

That creates another problem.

Consider:

SELECT
    customer_id,
    COUNT(*)
FROM orders
GROUP BY customer_id;

The query is read-only, but it could still consume significant database resources depending on the dataset and execution plan.

For workloads where isolation is important, a read replica can be useful.

The architecture becomes:

                 Production Database
                         |
                         | Replication
                         v
                  Read Replica
                         |
                         v
                    AI Agent

The agent can query the replica without directly competing with the primary database for the same read workload.

The exact replication and consistency characteristics depend on the PostgreSQL deployment.

Read-Only Does Not Mean Risk-Free

This distinction is important.

A read-only agent cannot directly modify database records, but it can still create risks.

For example:

Sensitive Data Exposure

A query could return confidential information.

Expensive Queries

A poorly designed query can consume database resources.

Excessive Data Retrieval

The agent could request thousands or millions of rows.

Data Inference

Multiple harmless queries may reveal information that should not be exposed together.

Therefore, read-only access is one security layer, not the entire security model.

Connecting From C#

Once the database role is configured, the C# application can connect using its restricted credentials.

For example:

using Npgsql;

await using var connection =
    new NpgsqlConnection(connectionString);

await connection.OpenAsync(cancellationToken);

await using var command =
    new NpgsqlCommand(
        """
        SELECT id, name, status
        FROM customers
        WHERE id = @id
        """,
        connection);

command.Parameters.AddWithValue("id", customerId);

await using var reader =
    await command.ExecuteReaderAsync(cancellationToken);

while (await reader.ReadAsync(cancellationToken))
{
    Console.WriteLine(
        $"{reader["id"]}: {reader["name"]}");
}

The important security property is not the C# code.

It is the permission assigned to the database account used by this connection.

Never Build SQL by Concatenating Agent Input

Avoid code such as:

var sql =
    $"SELECT * FROM customers WHERE name = '{name}'";

Agent-generated values should never be blindly inserted into SQL strings.

Use parameters:

await using var command =
    new NpgsqlCommand(
        """
        SELECT id, name
        FROM customers
        WHERE name = @name
        """,
        connection);

command.Parameters.AddWithValue("name", name);

Parameterized queries help protect against SQL injection and make the boundary between data and SQL explicit.

What About Agent-Generated SQL?

This is a more difficult problem.

Suppose the user asks:

Show me the five largest orders this month.

The agent might generate:

SELECT id, total_amount
FROM orders
WHERE created_at >= date_trunc('month', CURRENT_DATE)
ORDER BY total_amount DESC
LIMIT 5;

Even with a read-only account, unrestricted SQL generation can produce expensive queries.

A safer architecture can introduce a SQL validation layer:

User
 |
 v
Agent
 |
 v
Generated Query
 |
 v
SQL Validation
 |
 +---- Rejected
 |
 +---- Approved
        |
        v
Read-Only PostgreSQL

The validator can enforce application-specific rules.

For example:

Only permitted tables
Only permitted operations
Required row limits
No dangerous database functions
Query timeout
Maximum result size

The exact validation strategy depends on the application.

Limiting Query Results

Agents usually do not need unlimited result sets.

A simple application-level rule can require a limit:

SELECT id, name, status
FROM orders
ORDER BY created_at DESC
LIMIT 100;

The application can also enforce a maximum number of rows returned.

This reduces accidental large data transfers.

For analytical workloads, more specialized controls may be appropriate.

Query Timeouts

A read-only role does not prevent an expensive query from running for a long time.

PostgreSQL supports statement timeouts.

For example:

SET statement_timeout = '5000';

This sets a five-second statement timeout for the current session.

In an application, the appropriate timeout should be selected based on the workload rather than using an arbitrary value.

A timeout should be treated as a protection mechanism, not a substitute for query optimization.

Application-Level Authorization

The database role should not be the only authorization layer.

The application should first determine whether the user is allowed to ask the agent about the requested information.

For example:

User
 |
 v
Application Authorization
 |
 +---- Denied
 |
 +---- Allowed
        |
        v
       Agent
        |
        v
Read-Only Database

This matters when different users should have access to different records.

For example, an employee may be allowed to view only their department's data.

The database architecture should enforce that boundary where appropriate rather than relying solely on the AI model to filter results.

Common Mistakes

Assuming the Prompt Provides Security

Instructions such as "Never modify data" are not a database permission system.

Using an Admin Account

Never give an agent a database administrator account simply because it makes integration easier.

Giving Access to Every Table

Only expose the data the agent actually needs.

Forgetting Future Tables

Review default privileges when new database objects are created.

Ignoring Expensive Queries

A read-only query can still consume substantial CPU, memory, or I/O.

Exposing Sensitive Columns

Use views or column-level permissions when the agent does not need complete records.

Putting Database Credentials in Code

Use secure configuration and secret-management mechanisms.

Best Practices for Production

Use a Dedicated Database Role

Create a role specifically for agent access.

Follow Least Privilege

Grant only:

CONNECT
USAGE
SELECT

where required.

Do not grant write or administrative privileges unless there is a separate, justified use case.

Prefer Views for Controlled Data

Views can expose exactly the fields and records that the agent needs.

Consider a Read Replica

For heavy analytical agent workloads, separate reads from the production primary where the architecture supports it.

Validate Agent-Generated Queries

Treat model-generated SQL as untrusted input.

Add Query Limits

Use appropriate:

Row limits
Statement timeouts
Query restrictions
Application-level quotas

Monitor Agent Queries

Track which queries the agent executes and investigate unexpected patterns.

Protect Sensitive Data

Apply database permissions, application authorization, and appropriate data-filtering mechanisms together.

Advantages and Disadvantages

Advantages

Prevents direct database writes by the agent
Uses PostgreSQL's permission system as a security boundary
Supports least-privilege access
Can restrict access to selected tables or columns
Views can expose controlled data
Works naturally with C# and PostgreSQL applications
Read replicas can provide additional workload isolation

Disadvantages

Read-only access does not prevent expensive queries
Sensitive information can still be exposed
Query validation may add application complexity
Replica-based architectures introduce additional infrastructure
Permissions require ongoing maintenance
Database security alone cannot replace application authorization

Troubleshooting Agent Database Access

If the agent cannot query PostgreSQL, check:

Verify that the database role exists.
Confirm the role can connect to the database.
Check schema permissions.
Check table or view permissions.
Verify column-level permissions if they are being used.
Check whether the requested object is accessible to the role.
Confirm the application is using the intended credentials.
Review database authentication and authorization errors.
Check whether the query is exceeding configured timeouts.
Verify whether the agent is connecting to the intended production or replica environment.

If the agent can access data it should not see, review the role's effective privileges, views, application authorization, and database connection configuration.

Summary of the Article

Giving an AI agent read-only access to PostgreSQL is safer than giving it a write-enabled production account, but read-only access should be implemented as a real database permission boundary rather than a prompt instruction.

A dedicated PostgreSQL role can be restricted to SELECT permissions on specific tables, views, or columns. For more controlled access, database views can expose only the information the agent needs. Heavy read workloads can also be directed toward a suitable read replica where the architecture supports it.

C# applications should use parameterized queries, secure credential management, appropriate query limits, and application-level authorization. Agent-generated SQL should be treated as untrusted input and controlled with validation and resource limits.

The goal is to let an agent answer useful questions from production data while minimizing its ability to modify, expose, or overload the underlying database.

AI agents can be useful for answering questions about application data, generating reports, investigating records, and analyzing operational information.

But giving an agent direct access to a production PostgreSQL database creates an important security problem.

An AI agent may generate SQL dynamically. Even when the agent is instructed to perform read-only operations, instructions alone should not be treated as a security boundary.

A safer design is to enforce read-only access at the database and infrastructure layers.

The basic idea is simple:

AI Agent
   |
   v
Read-Only Database Access
   |
   v
PostgreSQL
   |
   X
Production Writes Blocked

The agent can query approved data, but the database itself prevents it from modifying production records.

This article explains how to implement this pattern in PostgreSQL and C#, along with safer alternatives such as replicas and restricted database roles.

Why Read-Only Access Matters

Consider an agent that generates SQL based on a user's request.

A normal request might result in:

SELECT id, name, status
FROM orders
WHERE customer_id = 1001;

That is a read operation.

But an incorrectly generated query could potentially be:

DELETE FROM orders
WHERE customer_id = 1001;

Or:

UPDATE orders
SET status = 'Cancelled';

An application-level instruction such as:

Only generate SELECT queries.

is useful, but it is not sufficient protection.

The database should enforce the rule independently.

The Basic Architecture

A safer architecture looks like this:

                   +----------------+
                   |    AI Agent    |
                   +-------+--------+
                           |
                           v
                  +-------------------+
                  | Read-Only Role    |
                  +---------+---------+
                            |
                            v
                  +-------------------+
                  | PostgreSQL        |
                  | Production Data   |
                  +-------------------+
                            |
                            X
                     INSERT / UPDATE /
                     DELETE blocked

The important security boundary is the PostgreSQL role.

The agent should authenticate using credentials that have only the permissions it requires.

Creating a Read-Only PostgreSQL Role

Start by creating a dedicated role.

CREATE ROLE agent_reader
LOGIN
PASSWORD 'replace-with-a-secure-secret';

The password should not be stored directly in source code or committed to a repository.

Next, allow the role to connect to the required database:

GRANT CONNECT ON DATABASE production_db
TO agent_reader;

Then provide access to the required schema:

GRANT USAGE ON SCHEMA public
TO agent_reader;

Finally, grant read access:

GRANT SELECT ON ALL TABLES IN SCHEMA public
TO agent_reader;

The role can now read the tables for which it has permissions.

It does not automatically receive permission to modify them.

Restricting Future Tables

There is an important detail here.

If new tables are created later, a previous GRANT SELECT ON ALL TABLES statement does not automatically grant permissions on every future table.

PostgreSQL supports default privileges for this scenario:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO agent_reader;

This should be applied by the appropriate object owner.

The exact privilege strategy should match how your database objects are created and managed.

Revoking Unnecessary Permissions

A good security model starts with least privilege.

You can explicitly revoke permissions that should not be available:

REVOKE INSERT, UPDATE, DELETE, TRUNCATE
ON ALL TABLES IN SCHEMA public
FROM agent_reader;

You can also remove broader privileges if they were previously granted:

REVOKE CREATE
ON SCHEMA public
FROM agent_reader;

The objective is not merely to tell the agent not to write.

The database account should lack the ability to write.

Giving Access to Only Specific Tables

An agent does not necessarily need access to the entire production database.

Suppose the agent only needs:

customers
orders
products

You can grant access specifically:

GRANT SELECT
ON TABLE customers, orders, products
TO agent_reader;

This is generally safer than granting access to every table.

The architecture becomes:

Agent
 |
 +--> customers
 |
 +--> orders
 |
 +--> products
 |
 X--> payments
 X--> credentials
 X--> internal_security

This reduces the amount of production data exposed to the agent.

Column-Level Access

Sometimes even an entire table is too much.

For example, a customer table might contain:

id
name
email
phone
address
password_hash

The agent may only need:

id
name
email

PostgreSQL supports column-level privileges.

For example:

GRANT SELECT (id, name, email)
ON customers
TO agent_reader;

This allows the role to read only the specified columns.

Column-level permissions can be useful when a table contains information that the agent does not need.

Using a View for Safer Agent Access

Another useful pattern is exposing a controlled view.

Suppose the production table contains internal fields:

CREATE VIEW agent_customer_summary AS
SELECT
    id,
    name,
    status
FROM customers;

Then grant access to the view:

GRANT SELECT
ON agent_customer_summary
TO agent_reader;

The agent does not need direct access to the underlying table.

This creates a controlled database interface:

Production Tables
       |
       v
Controlled View
       |
       v
Read-Only Agent Role
       |
       v
AI Agent

Views can also simplify the data presented to the agent.

Using a Read Replica

A read-only role prevents writes, but the agent may still execute expensive queries against the production database.

That creates another problem.

Consider:

SELECT
    customer_id,
    COUNT(*)
FROM orders
GROUP BY customer_id;

The query is read-only, but it could still consume significant database resources depending on the dataset and execution plan.

For workloads where isolation is important, a read replica can be useful.

The architecture becomes:

                 Production Database
                         |
                         | Replication
                         v
                  Read Replica
                         |
                         v
                    AI Agent

The agent can query the replica without directly competing with the primary database for the same read workload.

The exact replication and consistency characteristics depend on the PostgreSQL deployment.

Read-Only Does Not Mean Risk-Free

This distinction is important.

A read-only agent cannot directly modify database records, but it can still create risks.

For example:

Sensitive Data Exposure

A query could return confidential information.

Expensive Queries

A poorly designed query can consume database resources.

Excessive Data Retrieval

The agent could request thousands or millions of rows.

Data Inference

Multiple harmless queries may reveal information that should not be exposed together.

Therefore, read-only access is one security layer, not the entire security model.

Connecting From C#

Once the database role is configured, the C# application can connect using its restricted credentials.

For example:

using Npgsql;

await using var connection =
    new NpgsqlConnection(connectionString);

await connection.OpenAsync(cancellationToken);

await using var command =
    new NpgsqlCommand(
        """
        SELECT id, name, status
        FROM customers
        WHERE id = @id
        """,
        connection);

command.Parameters.AddWithValue("id", customerId);

await using var reader =
    await command.ExecuteReaderAsync(cancellationToken);

while (await reader.ReadAsync(cancellationToken))
{
    Console.WriteLine(
        $"{reader["id"]}: {reader["name"]}");
}

The important security property is not the C# code.

It is the permission assigned to the database account used by this connection.

Never Build SQL by Concatenating Agent Input

Avoid code such as:

var sql =
    $"SELECT * FROM customers WHERE name = '{name}'";

Agent-generated values should never be blindly inserted into SQL strings.

Use parameters:

await using var command =
    new NpgsqlCommand(
        """
        SELECT id, name
        FROM customers
        WHERE name = @name
        """,
        connection);

command.Parameters.AddWithValue("name", name);

Parameterized queries help protect against SQL injection and make the boundary between data and SQL explicit.

What About Agent-Generated SQL?

This is a more difficult problem.

Suppose the user asks:

Show me the five largest orders this month.

The agent might generate:

SELECT id, total_amount
FROM orders
WHERE created_at >= date_trunc('month', CURRENT_DATE)
ORDER BY total_amount DESC
LIMIT 5;

Even with a read-only account, unrestricted SQL generation can produce expensive queries.

A safer architecture can introduce a SQL validation layer:

User
 |
 v
Agent
 |
 v
Generated Query
 |
 v
SQL Validation
 |
 +---- Rejected
 |
 +---- Approved
        |
        v
Read-Only PostgreSQL

The validator can enforce application-specific rules.

For example:

Only permitted tables
Only permitted operations
Required row limits
No dangerous database functions
Query timeout
Maximum result size

The exact validation strategy depends on the application.

Limiting Query Results

Agents usually do not need unlimited result sets.

A simple application-level rule can require a limit:

SELECT id, name, status
FROM orders
ORDER BY created_at DESC
LIMIT 100;

The application can also enforce a maximum number of rows returned.

This reduces accidental large data transfers.

For analytical workloads, more specialized controls may be appropriate.

Query Timeouts

A read-only role does not prevent an expensive query from running for a long time.

PostgreSQL supports statement timeouts.

For example:

SET statement_timeout = '5000';

This sets a five-second statement timeout for the current session.

In an application, the appropriate timeout should be selected based on the workload rather than using an arbitrary value.

A timeout should be treated as a protection mechanism, not a substitute for query optimization.

Application-Level Authorization

The database role should not be the only authorization layer.

The application should first determine whether the user is allowed to ask the agent about the requested information.

For example:

User
 |
 v
Application Authorization
 |
 +---- Denied
 |
 +---- Allowed
        |
        v
       Agent
        |
        v
Read-Only Database

This matters when different users should have access to different records.

For example, an employee may be allowed to view only their department's data.

The database architecture should enforce that boundary where appropriate rather than relying solely on the AI model to filter results.

Common Mistakes

Assuming the Prompt Provides Security

Instructions such as "Never modify data" are not a database permission system.

Using an Admin Account

Never give an agent a database administrator account simply because it makes integration easier.

Giving Access to Every Table

Only expose the data the agent actually needs.

Forgetting Future Tables

Review default privileges when new database objects are created.

Ignoring Expensive Queries

A read-only query can still consume substantial CPU, memory, or I/O.

Exposing Sensitive Columns

Use views or column-level permissions when the agent does not need complete records.

Putting Database Credentials in Code

Use secure configuration and secret-management mechanisms.

Best Practices for Production

Use a Dedicated Database Role

Create a role specifically for agent access.

Follow Least Privilege

Grant only:

CONNECT
USAGE
SELECT

where required.

Do not grant write or administrative privileges unless there is a separate, justified use case.

Prefer Views for Controlled Data

Views can expose exactly the fields and records that the agent needs.

Consider a Read Replica

For heavy analytical agent workloads, separate reads from the production primary where the architecture supports it.

Validate Agent-Generated Queries

Treat model-generated SQL as untrusted input.

Add Query Limits

Use appropriate:

Row limits
Statement timeouts
Query restrictions
Application-level quotas

Monitor Agent Queries

Track which queries the agent executes and investigate unexpected patterns.

Protect Sensitive Data

Apply database permissions, application authorization, and appropriate data-filtering mechanisms together.

Advantages and Disadvantages

Advantages

Prevents direct database writes by the agent
Uses PostgreSQL's permission system as a security boundary
Supports least-privilege access
Can restrict access to selected tables or columns
Views can expose controlled data
Works naturally with C# and PostgreSQL applications
Read replicas can provide additional workload isolation

Disadvantages

Read-only access does not prevent expensive queries
Sensitive information can still be exposed
Query validation may add application complexity
Replica-based architectures introduce additional infrastructure
Permissions require ongoing maintenance
Database security alone cannot replace application authorization

Troubleshooting Agent Database Access

If the agent cannot query PostgreSQL, check:

Verify that the database role exists.
Confirm the role can connect to the database.
Check schema permissions.
Check table or view permissions.
Verify column-level permissions if they are being used.
Check whether the requested object is accessible to the role.
Confirm the application is using the intended credentials.
Review database authentication and authorization errors.
Check whether the query is exceeding configured timeouts.
Verify whether the agent is connecting to the intended production or replica environment.

If the agent can access data it should not see, review the role's effective privileges, views, application authorization, and database connection configuration.

Summary of the Article

The goal is to let an agent answer useful questions from production data while minimizing its ability to modify, expose, or overload the underlying database.