How to setup PostgreSQL on Ubuntu server

first ensure ubuntu is updated with latest packages

sudo apt update
sudo apt upgrade -y

Once this is done, you are ready.

Install postgres

sudo apt install postgresql postgresql-contrib -y

# check installation status
sudo systemctl status postgresql

# connect to psql
sudo -u postgres psql

# sudo systemctl status postgresql
ALTER USER postgres PASSWORD 'YourSecurePassword';
# exit
\q

Create database and database user

1. Create User

CREATE USER blog_user WITH PASSWORD 'StrongPasswordHere';

2. Create Database Owned by the User

CREATE DATABASE blog_db OWNER blog_user;

At this point, the user already owns the database.

3. Connect to the New Database

\c blog_db

4. Grant Privileges on Schema

By default, objects are created in public schema.

GRANT ALL ON SCHEMA public TO blog_user;
ALTER SCHEMA public OWNER TO blog_user;

5. Grant Privileges on Existing Objects

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO blog_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO blog_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO blog_user;

6. Set Default Privileges (Future Objects)

This ensures any new objects created later are accessible.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO blog_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO blog_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON FUNCTIONS TO blog_user;

7. Optional — Allow User to Create Objects

GRANT CREATE ON SCHEMA public TO blog_user;

One-Line Execution Version

CREATE USER blog_user WITH PASSWORD 'StrongPasswordHere';
CREATE DATABASE blog_db OWNER blog_user;
\c blog_db
GRANT ALL ON SCHEMA public TO blog_user;
ALTER SCHEMA public OWNER TO blog_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO blog_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO blog_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO blog_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO blog_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO blog_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO blog_user;
GRANT CREATE ON SCHEMA public TO blog_user;