Running npx create-medusa-app@latest --with-nextjs-starter fails with error: role "postgres" does not exist

[kensteele]

Issue

Note

This is a reproduction of bug #4999

Running npx create-medusa-app@latest --with-nextjs-starter fails with error: role "postgres" does not exist

Environment

Versions:

OSX:               14.4 (Sonoma)
NodeJS:            v21.7.1
Homebrew:          4.2.15-32
create-medusa-app: 1.2.7

Steps to reproduce

1. brew install postgres
2. npx create-medusa-app@latest --with-nextjs-starter
3. Accept the defaults for project name, email, postgres username and postgres password

Results

• Fails with error: role "postgres" does not exist:

npx create-medusa-app@latest --with-nextjs-starter
? What's the name of your project? my-medusa-store
? Enter an email for your admin dashboard user admin@medusa-test.com
? Enter your Postgres username postgres
? Enter your Postgres password [hidden]
Couldn't connect to PostgreSQL because of the following error: error: role "postgres" does not exist.

Make sure you have PostgreSQL installed and the credentials you provided are correct.

You can learn how to install PostgreSQL here: https://docs.medusajs.com/development/backend/prepare-environment?os=macos#postgresql

If you keep running into this issue despite having PostgreSQL installed, please check out our troubleshooting guidelines: https://docs.medusajs.com/troubleshooting/database-error

Expected results

• npx create-medusa-app@latest --with-nextjs-starter should succeed

SOLUTION

• Create a postgres role with all privileges:

Important

psql -U $USER -d postgres -c 'CREATE ROLE "postgres"' \
-c 'ALTER ROLE "postgres" WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS'

• To verify the role was created, you should now see ${USER} and postgres when dumping the roles:

psql -U $USER -d postgres -c '\du'
                                    List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 ${USER}   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

• Run create-medusa-app again

npx create-medusa-app@latest --with-nextjs-starter

Further Analysis

Note

Substitute ${USER} below with your currently logged in OSX username

brew install postgres on OSX installs postgres@14 and does the following:

• creates a role ${USER} with no password
• grants the ${USER} role all privileges
• creates a database postgres

npx create-medusa-app@latest --with-nextjs-starter appears to assume the database name and the role name are the same, which results in either a database or role error when they don't match.

• when specifying ${USER} for the Postgres username:

Couldn't connect to PostgreSQL because of the following error: error: database "${USER}" does not exist.

• when specifying postgres (or default) for the Postgres username:

Couldn't connect to PostgreSQL because of the following error: error: role "postgres" does not exist.

• Upon inspecting the create-medusa-app npm module source in the medusa v1.20.4 tag, we see that the user is prompted to enter their database username and password, but not the database name.
  

  medusa/packages/create-medusa-app/src/utils/create-db.ts

  Lines 63 to 85 in 2524a9d

  	try {
  	client = await postgresClient({
  	user: postgresUsername,
  	password: postgresPassword,
  	})
  	} catch (e) {
  	// ask for the user's postgres credentials
  	const answers = await inquirer.prompt([
  	{
  	type: "input",
  	name: "postgresUsername",
  	message: "Enter your Postgres username",
  	default: "postgres",
  	validate: (input) => {
  	return typeof input === "string" && input.length > 0
  	},
  	},
  	{
  	type: "password",
  	name: "postgresPassword",
  	message: "Enter your Postgres password",
  	},
  	])

Potential fixes

1. In utils/create-db.ts, prompt the user to specify their database name (with a default of postgres):

      {
        type: "input",
        name: "postgresDatabaseName",
        message: "Enter your Postgres database name",
        default: "postgres",
        validate: (input) => {
          return typeof input === "string" && input.length > 0
        },
      },
    ])

    postgresUsername = answers.postgresUsername
    postgresPassword = answers.postgresPassword
    postgresDatabaseName = answers.postgresDatabaseName

2. When the initial db connection with username postgres and database name postgres fails, automatically attempt to connect using username: process.env.USER and database name: postgres

Originally posted by @kensteele in #4743 (comment)