Create Postgres User, DB For self-hosted applications

If you self-host modern web applications — like Outline, Ghost, or Supabase components — you’ve probably come across the need to create a dedicated PostgreSQL database and user.

At first glance, this seems simple: create a user, create a database, and you’re done. But many developers and sysadmins run into a frustrating error that appears during migrations or runtime:

“permission denied for schema public”

This issue stems from how PostgreSQL handles permissions at two levels — the database level and the schema level. Understanding the distinction and setting up things properly can save hours of debugging.

This post will walk you through a complete, secure, and future-proof setup for creating a dedicated PostgreSQL user and database.

Understanding PostgreSQL Permission Layers

In PostgreSQL, a database is a container that holds one or more schemas, and each schema contains tables, indexes, functions, views, etc. Most applications use the default public schema, but the key thing to understand is:

• Creating a user and giving them ownership of the database does not automatically grant them full access to the public schema inside that database.

This is why migrations might fail to create tables, alter indexes, or modify constraints — the user lacks schema-level permissions even though it owns the database.

The Correct Way: Step-by-Step

Let’s say you’re hosting the Outline knowledge base, and you want a clean Postgres setup with a user and database both named outline. Here’s how to do it properly.

1. Create the PostgreSQL User

   This user will connect from your application.

   CREATE USER outline WITH PASSWORD 'supersecurepassword';
   

2. Create the Database and Assign Ownership

   CREATE DATABASE outline OWNER outline;
   

   This gives the user ownership of the database, meaning they can connect to it and perform operations — but not necessarily modify schemas inside.

3. Connect to the Database

   Switch to the newly created database:

   \c outline
   

4. Transfer Ownership of the public Schema Most applications will use the public schema. Make sure your new user owns it:

   ALTER SCHEMA public OWNER TO outline;
   

5. Grant Schema-Level Permissions

   The user needs permission to create tables, indexes, and other objects:

   GRANT USAGE, CREATE ON SCHEMA public TO outline;
   

   • USAGE: allows access to objects in the schema
   • CREATE: allows creation of new objects like tables and indexes

6. Grant Access to Existing Objects

   If there are any pre-existing tables, sequences, or functions (common in dump restores), grant permissions:

   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO outline;
   GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO outline;
   GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO outline;
   

7. Set Default Privileges for Future Objects

   This ensures that any tables or indexes created in the future will automatically grant full access to the outline user:

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO outline;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO outline;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO outline;
   

8. (Optional) Restrict the Schema to the App Only

   If you want to lock down the schema so no other users can touch it, revoke default access:

   REVOKE ALL ON SCHEMA public FROM PUBLIC;
   

   Now only the outline user and superusers (like postgres) can access or modify objects in the public schema.

Why All of This Matters

Let’s say you skip schema permissions and go straight to connecting your app. The app connects fine, but when it tries to run a migration:

Migration failed: permission denied for schema public

This happens because:

• Creating a database does not grant CREATE in the public schema.

• By default, the public schema is owned by postgres or another superuser.

• The app tries to create a table or index, and Postgres blocks it.

You might grant access to specific tables one by one, but that’s tedious, error-prone, and breaks when new objects are created — which is why default privileges are critical.