mynotes psql commands

How to connect

psql -h <remote_host_address> -p 5432 -U postgres

exit from connection

\q

Connect as specific user and to specific database

psql -h <remote_host_address> -p 5432 -U my_new_user -d my_new_db

create database

CREATE DATABASE my_new_db OWNER my_new_user;

Create user

CREATE USER new_owner_user WITH PASSWORD 'secure_password';

Grant user access

-- 1. Connect to the specific database first!
\c your_database_name

-- 2. Grant access to the Database "Container"
-- (Allows login and creating new schemas)
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;

-- 3. Grant access to the Schema (usually 'public')
-- (Allows creating tables inside the public folder)
GRANT ALL ON SCHEMA public TO your_username;

-- 4. Grant access to ALL EXISTING tables and sequences
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_username;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO your_username;

-- 5. Grant access to ALL FUTURE tables (Crucial Step)
-- (Ensures tables created later are also accessible)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO your_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO your_username;

Why isn't GRANT ALL ON DATABASE enough?

This is a common point of confusion in Postgres: GRANT ALL ON DATABASE: Only gives the user the key to the front door (CONNECT) and permission to build new rooms (CREATE). It does not give permission to touch the furniture (Tables) inside the rooms. GRANT ALL ON SCHEMA: Gives permission to enter the "Main Hall" (public). GRANT ALL ON TABLES: Gives permission to actually read/write data.

list all databases

\l

list of users

\du

connect to specific database

\connect databasename

List of tables in current database

\dt

List everything in database

\d