The error occurs because your user lacks the CREATE privilege on the database. In PostgreSQL, creating schemas requires:

1. CREATE privilege on the database
2. Sufficient privileges in the target database

Here's how to properly configure schema creation permissions:

-- As superuser (postgres), grant necessary privileges
GRANT CREATE ON DATABASE test TO eonil;

-- Optionally grant all privileges if needed
GRANT ALL PRIVILEGES ON DATABASE test TO eonil;

After granting privileges, verify with:

-- Check database privileges
SELECT datname, datacl FROM pg_database WHERE datname = 'test';

-- Connect as eonil and test schema creation
\c test eonil
CREATE SCHEMA new_schema AUTHORIZATION eonil;

For production environments, consider this more secure pattern:

-- Create a dedicated role for schema ownership
CREATE ROLE schema_owner;
GRANT schema_owner TO eonil;

-- Grant limited privileges
GRANT CREATE ON DATABASE test TO schema_owner;

-- Now create schema with specific owner
CREATE SCHEMA app_schema AUTHORIZATION schema_owner;

• Forgetting to reconnect after privilege changes
• Not checking existing privileges before troubleshooting
• Over-granting privileges (avoid using ALL PRIVILEGES in production)

---

When trying to create a schema as a normal user in PostgreSQL, you might encounter the error:

ERROR: permission denied for database test

This occurs because new database users don't automatically get schema creation privileges, even when they can connect to the database.

In PostgreSQL, schema creation requires two things:

• CREATE privilege on the database
• Sufficient privileges in the target database

The default behavior is that only database owners (usually the superuser or the user who created the database) can create schemas.

There are two approaches to solve this:

Option 1: Grant CREATE Privilege on the Database

Connect as a superuser or database owner and run:

GRANT CREATE ON DATABASE test TO eonil;

This gives the user the fundamental right to create schemas in the specified database.

Option 2: Make the User a Database Owner

For more extensive privileges (use cautiously):

ALTER DATABASE test OWNER TO eonil;

After granting privileges, verify with:

\l test

You should see 'eonil' in the "Access privileges" column with 'C' (Create) permission. Then test schema creation:

psql -U eonil test
test=> CREATE SCHEMA new_schema AUTHORIZATION eonil;
CREATE SCHEMA

For more granular control, you can:

-- Create schema as superuser
CREATE SCHEMA restricted_schema;

-- Grant limited privileges
GRANT USAGE ON SCHEMA restricted_schema TO eonil;
GRANT CREATE ON SCHEMA restricted_schema TO eonil;

When granting schema creation privileges:

• Consider using specific schemas rather than allowing arbitrary schema creation
• Regularly review privileges with \dp and \dn+
• Document all privilege grants for audit purposes

For deployment scripts, you might use:

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_database 
        WHERE datname = 'test'
    ) THEN
        CREATE DATABASE test;
    END IF;
    
    IF NOT EXISTS (
        SELECT 1 FROM pg_roles 
        WHERE rolname = 'eonil'
    ) THEN
        CREATE ROLE eonil LOGIN PASSWORD 'password';
    END IF;
    
    GRANT CREATE ON DATABASE test TO eonil;
END $$;