Easy setup PostgreSQL on MacOS
Tags: postgres, database
Published: 2021-08-24 14:30:21

Lately, I've been working on a personal project of mine, and this one particular project uses [postgresql](https://www.postgresql.org), so I'm going to go over my setup of postgresql (particularly on MacOS) in this post. :D

### TOC
  * [Installing postgresql](#part-1)
  * [Creating user and database](#part-2)
  * [Use our newly created user and database](#part-3)
  * [Final note and refs](#part-4)

<a name="part-1"></a>
## Installing postgresql 

I used [homebrew](https://brew.sh) to install postgresql on my machine.
```bash
brew install postgresql
```
Run `postgres -V` to check the version (and also if it's installed). 
To get postgresql up and running, run
```bash
brew services start postgres
# or
pg_ctl -D /usr/local/var/postgres start
```
<sub>Note: `pg_ctl` is a utility for starting, stopping, or restarting the PostgreSQL backend server that comes with postgresql</sub>

<a name="part-2"></a>
## Creating user and database

Postgresql installed with homebrew automatically creates a user with the same name as your username with no password assigned. This user has a `SUPERUSER` role. However, it's not really good practice to use this user. Hence, we're creating a new one. 

<sub>Note: there are other ways besides doing it via `psql`, but I'm not covering that here 🥺</sub>

- First of all, we're going to run `psql`. We need to specify which database we're running from. Since there's a pre-created database called `postgres`, we're going to use that. 
```bash
psql -d postgres
```
- Let's create a user (or aka role in postgresql term)
```bash
# replace <_> with your own thing
CREATE USER <user> WITH ENCRYPTED PASSWORD '<password>';
# check if your user was created
SELECT usename FROM pg_user;  # will show a table of user
# or run a psql meta command
\du
```
- Let's give our user a role (or they'll just be crippled). For more info on role (or if there are other role attributes besides `CREATEDB`), check [this great article from prisma](https://www.prisma.io/dataguide/postgresql/authentication-and-authorization/role-management#role-attributes). 
```bash
ALTER ROLE <user> CREATEDB;
```
- Next, let's create a database. Note that you can grant different types of access (besides `ALL PRIVILEGES` that was used here). For more info, check [this awesome article from TablePlus](https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html) or [check the official document](https://www.postgresql.org/docs/9.0/sql-grant.html).
```bash
# again, replace <_> with yours
CREATE DATABASE <db_name>;
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user>;
# list all databases to see if ours was created
\list
```

<a name="part-3"></a>
## Use our newly created user and database

To do this, you can...

1. Switch user and database inside `psql` session
```bash
\c <db_name> <user>
```
2. Or you can stop current `psql` session and start it using new user and database
```bash
\q  # to quit
psql -d <db_name> -U <user> 
```

<a name="part-4"></a>
## Final note and refs 
And that's it, y'all. It wasn't hard, but it can be confusing for the first time. (Yes, I'm talking from my (in)experience here 🥲)

Cover Photo by <a href="https://unsplash.com/@theshubhamdhage?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Shubham Dhage</a> on <a href="https://unsplash.com/t/experimental?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a>
[ref1](https://www.codementor.io/@engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb) | [ref2](https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/)