How to Create a PostgreSQL User and Grant Database Permissions

Kapag nagse-setup ka ng server or nagde-deploy ng application gamit ang PostgreSQL, same rule applies:
huwag na huwag mong gamitin ang superuser (postgres) sa application mo.

Very common beginner mistake ’yan.

Ang tamang approach:

Gumawa ng dedicated PostgreSQL user (role)
Gumawa ng specific database
Ibigay lang ang permissions na kailangan ng app

Mas secure, mas madaling i-maintain, at production-ready.

Usually, naka-disable ang direct password login kay postgres, so ang common way ay:

sudo -u postgres psql

Kapag successful, papasok ka sa PostgreSQL shell.

Create a New PostgreSQL User (Role)h2

Sa PostgreSQL, users are roles.

CREATE USER app_user WITH PASSWORD 'strong_password';

Example:

CREATE USER laravel_user WITH PASSWORD 'secret123';

Explanationh3

laravel_user → database user / role
WITH PASSWORD → password para sa authentication
Walang admin privileges by default (good)

Create a Databaseh2

CREATE DATABASE laravel_db;

Optional: gawing owner agad ang user:

CREATE DATABASE laravel_db OWNER laravel_user;

This is recommended para mas clean ang permission setup.

Grant Database Accessh2

Kung hindi mo ginawang owner yung user, kailangan mong mag-grant:

GRANT ALL PRIVILEGES ON DATABASE laravel_db TO laravel_user;

This allows the user to connect and manage objects inside the database.

Grant Schema Permissions (Important Step)h2

By default, naka-lock down ang public schema sa PostgreSQL.

\c laravel_db

Then run:

GRANT ALL ON SCHEMA public TO laravel_user;

Without this, makaka-connect ang user pero hindi makaka-create ng tables.

Grant Table Permissions (For Existing Tables)h2

Kung may existing tables na:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO laravel_user;

And for future tables:

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

This avoids permission issues later.

Verify the User Permissionsh2

Exit muna:

\q

Then login as the new user:

psql -U laravel_user -d laravel_db

Kapag successful ang login, tama ang setup.

(Optional) Grant Limited Permissions Onlyh2

Kung gusto mo ng mas strict setup:

GRANT CONNECT ON DATABASE laravel_db TO laravel_user;
GRANT USAGE ON SCHEMA public TO laravel_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO laravel_user;

This is usually enough for web applications.

Common Mistakes to Avoidh2

❌ Using postgres superuser sa application
❌ Forgetting schema permissions
❌ Granting unnecessary superuser rights
❌ Assuming database access = table access

Final Thoughtsh2

PostgreSQL is stricter than MySQL when it comes to permissions — and that’s a good thing.

Rule of thumb:

One app = one database = one role with limited permissions

Kapag naintindihan mo ang roles, schemas, at privileges, mas magiging smooth ang PostgreSQL deployments mo.

Login to PostgreSQLh2