Interactive PostgreSQL Setup Guide

Introduction

This guide will walk you through the process of setting up a new user and database in PostgreSQL. We will cover creating the user, creating the database, granting all privileges on that database to the new user, and finally, how to execute a SQL script to populate the database with tables.

Prerequisites

These commands are typically run using a PostgreSQL interactive terminal like `psql`, or through a database administration tool. You must be connected as a superuser (like the default `postgres` user) to execute these commands.

Step 1: Create a New User (Role)

First, we'll create a new user. In PostgreSQL, users are a type of "role". We'll create a role that can log in and has a password. Replace `new_user` with your desired username and `strong_password` with a secure password.

CREATE USER new_user WITH PASSWORD 'strong_password';

Step 2: Create a New Database

Next, let's create the new database. It's good practice to set the owner of the database to the user you just created. Remember to replace `new_database` and `new_user` with your chosen names.

CREATE DATABASE new_database OWNER new_user;

Step 3: Grant Privileges

Now, we need to grant the `new_user` all permissions for the `new_database`. This allows the user to create, read, update, and delete tables and data within that database.

GRANT ALL PRIVILEGES ON DATABASE new_database TO new_user;

While the above command grants general privileges, to allow the user to create tables, you also need to grant privileges on the `public` schema. As the same superuser you are currently logged in as, switch your connection to the new database:

\c new_database

Then, grant the privileges on the schema:

GRANT ALL ON SCHEMA public TO new_user;

Step 4: Import a SQL Script

To ensure all tables and objects in your script are owned by the correct user, you must run the import as the `new_user` you created. This follows the security best practice known as the "Principle of Least Privilege."

Method 1: From the Command Line

The `-U new_user` flag ensures this command is run by the correct user. You will be prompted for the user's password.

psql -h localhost -d new_database -U new_user -f /path/to/your/script.sql

Method 2: From Inside psql

If you are currently connected to `psql` as a superuser, you must first switch your connection to be the `new_user` connected to the `new_database`. You can do this in one command:

\c new_database new_user

After you enter the password and the prompt changes, you can now run the import command. The script will be executed by `new_user`.

\i /path/to/your/script.sql

Common Management Commands

Here are a few useful `psql` meta-commands to verify your setup and view the state of your PostgreSQL server.

Show All Users (Roles)

To see a list of all user roles defined in your PostgreSQL instance, use the `\du` command.

\du

Show All Databases

To see a list of all databases on the server, along with their owners and encoding, use the `\l` command.

\l

Debugging Common Issues

Scenario: "How do I know my command worked?"

When you run a command like `CREATE USER` or `GRANT`, you won't get back a table of data like you would with a `SELECT` query. Instead, PostgreSQL gives you a simple, one-word confirmation message.

Look for a confirmation like CREATE ROLE, CREATE DATABASE, or GRANT. If you see this message and no ERROR:, the command was successful. You can then use verification commands like `\du` or `\l` to see the results.

Scenario: "Commands fail on macOS with Homebrew (Version Mismatch)"

If you installed a "keg-only" version of PostgreSQL with Homebrew (like `postgresql@17`) and commands fail, you likely have a version mismatch. This happens when your system finds an old `psql` client before the new one.

Step 1: Diagnose with `which`

Run this command to see the exact file path of the `psql` command your terminal is using.

which psql

If the path does not contain `postgresql@17`, you have confirmed the problem.

Step 2: Choose Your Solution

You have two good options to fix this: updating your `PATH` (the standard, more robust solution) or creating an `alias` (a simpler, more direct solution).

Solution A: Fix Your PATH (Recommended)

This method tells your shell to always look in the correct directory for `psql` and other related tools. Run the command that matches your Mac's architecture to add the correct directory to the start of your shell's search path.

For Apple Silicon Macs (M1/M2/M3, etc.):

echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc

For Intel Macs:

echo 'export PATH="/usr/local/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc

Important Note: This `PATH` solution only affects your interactive terminal sessions. It will not be read by `brew services`, which runs background processes in a different environment.

Solution B: Create an Alias

An alias is a simple shortcut. This command tells your shell "whenever I type `psql`, run this specific file instead." This is a very direct fix.

For Apple Silicon Macs (M1/M2/M3, etc.):

echo "alias psql='/opt/homebrew/opt/postgresql@17/bin/psql'" >> ~/.zshrc

For Intel Macs:

echo "alias psql='/usr/local/opt/postgresql@17/bin/psql'" >> ~/.zshrc

Step 3: Apply and Verify

After choosing a solution, you **must** apply the changes. Close and reopen your terminal, or run the command below:

source ~/.zshrc

Now, check the version again. It should report the correct, new version (e.g., 17.x), and your commands will work.

psql --version

Summary of Commands

Here is a quick summary of all the commands covered in this guide for your reference.

-- Core Setup Commands
CREATE USER new_user WITH PASSWORD 'strong_password';
CREATE DATABASE new_database OWNER new_user;
GRANT ALL PRIVILEGES ON DATABASE new_database TO new_user;
\c new_database
GRANT ALL ON SCHEMA public TO new_user;

-- Import Script (from OS terminal)
psql -h localhost -d new_database -U new_user -f /path/to/your/script.sql

-- Import Script (from inside psql)
\c new_database new_user
\i /path/to/your/script.sql

-- Verification & Debugging Commands
\du               -- List all users/roles
\l                -- List all databases
psql --version    -- Check client version
which psql        -- Find which psql is being used