Skip to main content

Python Psycog2: How to Resolve "psycopg2.errors.InsufficientPrivilege: Permission Denied for Schema Public" in Python

When connecting to PostgreSQL from Python using psycopg2, you may encounter the error psycopg2.errors.InsufficientPrivilege: permission denied for schema public. This error means the database user your application is connecting as doesn't have the necessary permissions to perform the requested operation.

In this guide, we'll explain why this error occurs (especially after PostgreSQL 15's security changes), walk through common scenarios that trigger it, and show you exactly how to fix it with proper permission management.

What Does This Error Mean?​

psycopg2.errors.InsufficientPrivilege is raised when a SQL command fails because the connected user lacks the required privileges. The most common variant is permission denied for schema public and it means the user can't create objects in or access the public schema.

The full error typically looks like:

psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR...
Important: PostgreSQL 15 Changed Default Permissions

In PostgreSQL 15, the default permissions for the public schema were tightened for security reasons. Previously, all users could create objects in public by default. Starting with PostgreSQL 15, only the database owner has CREATE privileges on the public schema. This is the most common reason developers suddenly encounter this error after upgrading.

Common Scenarios That Trigger This Error​

1. Creating Tables Without CREATE Privilege​

❌ Error: User lacks CREATE permission on the schema:

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="mydb",
        user="app_user",
        password="password",
        host="localhost"
    )
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100),
            price DECIMAL(10, 2)
        );
    """)
    conn.commit()
except psycopg2.errors.InsufficientPrivilege as e:
    print(f"Permission error: {e}")
finally:
    cursor.close()
    conn.close()

Output:

Permission error: permission denied for schema public

2. Inserting, Updating, or Deleting Data Without Table Privileges​

Even if the table exists, the user may not have permission to modify its data:

cursor.execute("INSERT INTO products (name, price) VALUES ('Widget', 9.99);")

Output:

psycopg2.errors.InsufficientPrivilege: permission denied for table products

3. Reading Data Without SELECT Privilege​

cursor.execute("SELECT * FROM products;")

Output:

psycopg2.errors.InsufficientPrivilege: permission denied for table products

How to Fix It​

All permission changes must be made by a superuser or the database owner (typically the postgres user). Connect as the privileged user to run the GRANT commands.

Solution 1: Grant Schema-Level Permissions​

The first step is to grant USAGE (ability to access objects) and CREATE (ability to create new objects) on the schema:

-- Connect as postgres or database owner
-- psql -U postgres -d mydb

-- Allow the user to access and create objects in the public schema
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

Solution 2: Grant Table-Level Permissions​

Schema access alone isn't enough. You also need permissions on the individual tables:

-- Grant all CRUD operations on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Grant usage on sequences (needed for SERIAL/auto-increment columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
caution

GRANT ... ON ALL TABLES only affects tables that already exist. Tables created in the future won't automatically inherit these permissions. Use ALTER DEFAULT PRIVILEGES to handle future objects (see Solution 3).

Solution 3: Set Default Privileges for Future Objects​

To ensure the user automatically gets permissions on tables created in the future, set default privileges:

-- Run as the user who will be creating the tables (e.g., postgres)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

This is critical in production environments where migrations or other processes create new tables over time.

Solution 4: Use Role-Based Access Control​

For better security and maintainability, create roles with specific permission sets and assign users to those roles:

-- Create roles with specific privileges
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydb TO readwrite;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO readwrite;

-- Assign roles to users
GRANT readwrite TO app_user;
GRANT readonly TO reporting_user;

Now any user assigned to readwrite inherits all the necessary permissions, and you manage permissions in one place.

Solution 5: Create a Dedicated Schema​

Instead of modifying permissions on public, create a separate schema for your application. This is a cleaner approach, especially for multi-tenant applications or when multiple applications share a database:

-- Create a dedicated schema
CREATE SCHEMA app_schema;

-- Grant full access to the application user
GRANT USAGE, CREATE ON SCHEMA app_schema TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app_schema TO app_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA app_schema TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema
    GRANT ALL PRIVILEGES ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema
    GRANT ALL PRIVILEGES ON SEQUENCES TO app_user;

Then update your Python code to use the schema:

import psycopg2

conn = psycopg2.connect(
    dbname="mydb",
    user="app_user",
    password="password",
    host="localhost",
    options="-c search_path=app_schema"  # Set the default schema
)

cursor = conn.cursor()
cursor.execute("""
    CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        price DECIMAL(10, 2)
    );
""")
conn.commit()
print("Table created successfully in app_schema.")
cursor.close()
conn.close()

Complete Working Example​

Here's a full example showing how to set up permissions and verify they work:

Step 1: Set up permissions (run as postgres superuser):

-- Create the database and user
CREATE DATABASE myapp_db;
CREATE USER app_user WITH PASSWORD 'secure_password';

-- Connect to the database
\c myapp_db

-- Grant necessary permissions
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

Step 2: Test from Python:

import psycopg2

def test_permissions():
    conn = psycopg2.connect(
        dbname="myapp_db",
        user="app_user",
        password="secure_password",
        host="localhost",
        port="5432"
    )
    conn.autocommit = True
    cursor = conn.cursor()

    try:
        # Test CREATE
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS test_products (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                price DECIMAL(10, 2) DEFAULT 0.00
            );
        """)
        print("✅ CREATE TABLE: success")

        # Test INSERT
        cursor.execute(
            "INSERT INTO test_products (name, price) VALUES (%s, %s) RETURNING id;",
            ("Widget", 19.99)
        )
        product_id = cursor.fetchone()[0]
        print(f"✅ INSERT: success (id={product_id})")

        # Test SELECT
        cursor.execute("SELECT * FROM test_products;")
        rows = cursor.fetchall()
        print(f"✅ SELECT: success ({len(rows)} rows)")

        # Test UPDATE
        cursor.execute(
            "UPDATE test_products SET price = %s WHERE id = %s;",
            (24.99, product_id)
        )
        print("✅ UPDATE: success")

        # Test DELETE
        cursor.execute("DELETE FROM test_products WHERE id = %s;", (product_id,))
        print("✅ DELETE: success")

        # Cleanup
        cursor.execute("DROP TABLE test_products;")
        print("✅ DROP TABLE: success")

    except psycopg2.errors.InsufficientPrivilege as e:
        print(f"❌ Permission denied: {e}")
    finally:
        cursor.close()
        conn.close()


test_permissions()

Output:

✅ CREATE TABLE: success
✅ INSERT: success (id=1)
✅ SELECT: success (1 rows)
✅ UPDATE: success
✅ DELETE: success
✅ DROP TABLE: success

Checking Current Permissions​

If you're unsure what permissions a user currently has, you can inspect them:

-- Check schema privileges
SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';

-- Check table-level privileges for a specific user
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_user';

-- Check if a user has a specific privilege
SELECT has_schema_privilege('app_user', 'public', 'CREATE');
SELECT has_table_privilege('app_user', 'products', 'SELECT');

Quick Reference: Common GRANT Commands​

Operation NeededSQL Command
Access objects in a schemaGRANT USAGE ON SCHEMA public TO user;
Create tables in a schemaGRANT CREATE ON SCHEMA public TO user;
Read data from all tablesGRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
Full CRUD on all tablesGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user;
Use auto-increment sequencesGRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO user;
Auto-grant on future tablesALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ... ON TABLES TO user;
Full access (use cautiously)GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user;

Conclusion​

The psycopg2.errors.InsufficientPrivilege: permission denied for schema public error occurs when your database user lacks the necessary permissions to perform an operation. This became more common after PostgreSQL 15 tightened default permissions on the public schema.

The fix involves granting appropriate privileges using GRANT statements, at both the schema level (USAGE, CREATE) and the table level (SELECT, INSERT, UPDATE, DELETE).

For production environments, use ALTER DEFAULT PRIVILEGES to handle future tables automatically, and consider using role-based access control for cleaner permission management across multiple users.

Table of Contents