Setting Up PostgreSQL on macOS: A Step-by-Step CLI Guide with Homebrew

2. How do I connect to PostgreSQL for the first time?

Homebrew automatically creates a default superuser whose name matches your macOS username, with no password set. Therefore, you can connect immediately. Run: psql -U $(whoami) -d postgres. The -U flag specifies your user, and -d postgres connects to the default database named postgres. Once successful, your terminal prompt changes to postgres=#. This indicates you are now inside the PostgreSQL interactive shell, ready to run SQL commands or utility meta-commands. No password is needed at this stage because the default superuser has a blank password. If you later set a password, you will have to include the -W flag to be prompted.

3. What useful PostgreSQL shortcuts should I know?

While inside the PostgreSQL prompt (postgres=#), several backslash commands make it easier to inspect your setup. Here are the essential ones:

• \\du — Lists all database users (roles) and their attributes.
• \\l — Shows all databases on the server, including the default postgres database.
• \\q — Exits the PostgreSQL shell and returns you to the regular macOS terminal.

These commands are quick, require no SQL syntax, and provide immediate insight into your database environment. For a full list, type \\? inside the prompt. Remember that every SQL command must end with a semicolon (;), but these backslash commands do not need one.

4. How do I create a new database user?

It is not recommended to use the default superuser for your applications. Instead, create a dedicated user with a secure password. Inside the PostgreSQL prompt, run:

CREATE USER my_project_user WITH PASSWORD 'my_secure_password';

Replace my_project_user and my_secure_password with your own values. Make sure the password is enclosed in single quotes and the command ends with a semicolon. This creates a new role that is not a superuser, which is safer for daily use. You can verify the creation by typing \\du — the new user will appear in the list.

5. How do I create a database and grant permissions to my user?

After creating your user, you need a dedicated database. Run inside the prompt:

1. CREATE DATABASE my_project_db; — Replace my_project_db with your database name.
2. GRANT ALL PRIVILEGES ON DATABASE my_project_db TO my_project_user; — This gives your user full access to the database.

However, note that on PostgreSQL 15 or higher, granting on the database level does not grant permissions on the public schema. To fix that, connect to the new database first with \\c my_project_db and then run:

GRANT ALL ON SCHEMA public TO my_project_user;

Now your user can create tables and perform all operations in that schema. After setup, type \\q to exit. You can connect your application using localhost, the database name, the new user, and its password.

6. How do I verify the PostgreSQL setup is complete?

To confirm everything works end‑to‑end, open a new terminal window and run: psql -U my_project_user -d my_project_db. When prompted, enter the password you created. If the prompt changes to my_project_db=#, your connection is successful. Inside, you can test permissions by creating a dummy table: CREATE TABLE test (id INT); and then drop it: DROP TABLE test;. No errors mean your user has full access. Finally, disconnect with \\q. Your local PostgreSQL environment is now ready for any project — whether it is running n8n, Ollama, or a custom application. If you ever need to stop the server, use brew services stop postgresql.