Postgres User Creation & Full Permissions for Single Database

You might sometimes want to create a new user in Postgres instead of using admin access in your database instance.

It can be for various purposes, expecially for security purposes to reduce the blast radius (Principle of least privilege)

Let's see how to create a new user in Postgres and give full permission for a selective database using SQL.

Requirements

Make sure that Postgres client is installed on your machine.

In macOS, you can easily install it using brew

brew install postgresql@15

It'll install the server and necessary CLI to interact with the database.

Connecting to Database

Connect to your database like this

psql -h localhost -p 5432 -U postgres

And for hosted db, it'll be something like this:

psql -h db-instance-name.sample.us-east-1.rds.amazonaws.com -p 5432 -U postgres

Create a database

CREATE DATABASE YOUR_DB_NAME;

Creating a new user

CREATE USER YOUR_USER_NAME WITH ENCRYPTED PASSWORD 'YOUR_PASSWORD_HERE';

Grant All Permissions for that Database

GRANT ALL PRIVILEGES ON DATABASE YOUR_DB_NAME TO YOUR_USER_NAME;
\c YOUR_DB_NAME postgres

GRANT ALL ON SCHEMA public TO YOUR_USER_NAME;

Optional: Permission for new schemas

If you want to give permission for any new schemas by default. You can run this

--- If you want to give all 
ALTER DEFAULT PRIVILEGES FOR ROLE YOUR_USER_NAME
    GRANT ALL PRIVILEGES ON TABLES TO YOUR_USER_NAME;
ALTER DEFAULT PRIVILEGES FOR ROLE YOUR_USER_NAME
    GRANT ALL PRIVILEGES ON SEQUENCES TO YOUR_USER_NAME;

With that, all the necessary permission are granted for that user 🙌

Happy creating user!