Install PostgreSQL in Mac

Setting up PostgreSQL locally on macOS is straightforward, but doing it correctly (with proper users, passwords, and security) makes a big difference, especially if you’re building backend systems or data platforms.

 

This guide walks you through:

 

·      Installation

·      Starting the database

·      Creating users and passwords

·      Securing access

·      Best practices

 

1. Install PostgreSQL on macOS

Install PostgreSQL by executing following command.

brew install postgresql

 

Start PostgreSQL

brew services start postgresql

 

Verify installation by executing below command

psql --version

   

PostgreSQL creates a default database cluster, uses your macOS username as the default role. Local connections may use trust authentication (no password). This is fine for quick testing—but not ideal even for local development.

 

2. Create User

Access PostgreSQL Shell by executing below command.

 

psql postgres

$ psql postgres
psql (14.22 (Homebrew), server 18.3 (Homebrew))
WARNING: psql major version 14, server major version 18.
         Some psql features might not work.
Type "help" for help.

postgres=#

   

Create a User

Execute below command to create user.

 

CREATE USER app_user WITH PASSWORD 'strong_password';

   

Create a Database

 

CREATE DATABASE app_db OWNER app_user;

   

Grant Privileges

 

GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;

   

This ensures your user can:

 

·      Connect

·      Create tables

·      Insert/update/delete data

 

Exit from the Sql Shell

 

\q

   

3. Connect to the Database

 

psql -U app_user -d app_db

   

4. Enable Password Authentication (Important)

By default, macOS installs may allow login without a password.

 

Open another new terminal and edit config file:

nano /opt/homebrew/var/postgresql@14/pg_hba.conf

 

Find following line

 

local   all             all                                     trust

   

Replace it with

 

local   all             all                                     md5

   

5. Restart PostgreSQL

 

brew services restart postgresql

   

6. Test password login

 

psql -U app_user -d app_db -W

   

Create sales_data table by executing following statement.

 

CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    region VARCHAR(50),
    product VARCHAR(100),
    category VARCHAR(50),
    quantity INT,
    price NUMERIC(10,2),
    revenue NUMERIC(10,2)
);

   

Insert sample data.

 

INSERT INTO sales_data (order_date, region, product, category, quantity, price, revenue) VALUES
('2024-01-01', 'India', 'Laptop', 'Electronics', 2, 60000, 120000),
('2024-01-02', 'India', 'Phone', 'Electronics', 5, 20000, 100000),
('2024-01-03', 'US', 'Tablet', 'Electronics', 3, 30000, 90000),
('2024-01-04', 'UK', 'Chair', 'Furniture', 10, 5000, 50000),
('2024-01-05', 'India', 'Desk', 'Furniture', 4, 7000, 28000),
('2024-01-06', 'US', 'Monitor', 'Electronics', 6, 15000, 90000),
('2024-01-07', 'UK', 'Keyboard', 'Accessories', 15, 2000, 30000),
('2024-01-08', 'India', 'Mouse', 'Accessories', 20, 1000, 20000);

   

Verify data

 

app_db=> SELECT * FROM sales_data;
 id | order_date | region | product  |  category   | quantity |  price   |  revenue  
----+------------+--------+----------+-------------+----------+----------+-----------
  1 | 2024-01-01 | India  | Laptop   | Electronics |        2 | 60000.00 | 120000.00
  2 | 2024-01-02 | India  | Phone    | Electronics |        5 | 20000.00 | 100000.00
  3 | 2024-01-03 | US     | Tablet   | Electronics |        3 | 30000.00 |  90000.00
  4 | 2024-01-04 | UK     | Chair    | Furniture   |       10 |  5000.00 |  50000.00
  5 | 2024-01-05 | India  | Desk     | Furniture   |        4 |  7000.00 |  28000.00
  6 | 2024-01-06 | US     | Monitor  | Electronics |        6 | 15000.00 |  90000.00
  7 | 2024-01-07 | UK     | Keyboard | Accessories |       15 |  2000.00 |  30000.00
  8 | 2024-01-08 | India  | Mouse    | Accessories |       20 |  1000.00 |  20000.00
(8 rows)

  

Previous                                                    Next                                                    Home