PostgreSQL Permissions for Public Schema After v15

Problem Statement

Since PostgreSQL 15, users often encounter permission denied for schema public errors when attempting to create tables as non-superusers. This occurs because:

• PostgreSQL 15+ revokes default CREATE permission on the public schema for non-owners
• The change enhances security but breaks legacy workflows
• Privileges must be explicitly granted per-database
• Each database has its own isolated public schema

Security Change in v15

"PostgreSQL 15 revokes the CREATE permission from all users except a database owner from the public (or default) schema."

Core Solutions

1. Make Target User a Database Owner (Recommended)

The simplest solution is assigning ownership of your database to the application user:

-- When creating new databases:
CREATE DATABASE yourdb OWNER youruser;

-- For existing databases:
ALTER DATABASE yourdb OWNER TO youruser;

Owners automatically receive full permissions on the public schema.

2. Explicitly Grant Permissions

If you can't change ownership, manually grant privileges in the target database:

Command Line

# Connect to target database first
psql -d yourdbname -U postgres

SQL Commands

-- Grant privileges within target database
GRANT USAGE, CREATE ON SCHEMA public TO youruser;
-- Grant table privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO youruser;

WARNING

Privileges are database-specific. Executing grants without connecting to the target database does nothing!

Common Pitfalls & Fixes

Mistake: Granting Privileges in Wrong Database

# ERROR: Grants applied to wrong database
$ psql -c "GRANT CREATE ON SCHEMA public TO youruser;"

# FIX: Specify target database
$ psql -d yourdb -c "GRANT CREATE ON SCHEMA public TO youruser;"

Mistake: Assigning ALL Instead of Ownership

-- Doesn't solve schema permissions
GRANT ALL ON DATABASE yourdb TO youruser; 

-- Correct approach:
ALTER DATABASE yourdb OWNER TO youruser;

Verifying Permissions

Check effective privileges with:

SELECT 
  has_schema_privilege('youruser', 'public', 'USAGE') AS has_usage,
  has_schema_privilege('youruser', 'public', 'CREATE') AS has_create;

Migration Workflow

Best Practices

1. Always create databases with owners
   CREATE DATABASE appdb OWNER appuser;

2. Avoid using the postgres database for applications
   Create dedicated databases instead

3. Connect to target database before granting schema privileges

4. Limit superuser usage - Assign specific ownership instead

Why this changed?

This security enhancement prevents:

• Unauthorized object creation in public schema
• Privilege escalation risks
• Schema pollution by unprivileged users

Conclusion

PostgreSQL 15's permission changes require either:

• Assigning database ownership to application users
• Explicitly granting USAGE and CREATE privileges in the target database

Always verify permissions in the specific database where operations occur, as public schemas are database-specific. Proper permission management following these patterns ensures secure and functional PostgreSQL deployments in v15+.