psql Client

WARNING: This is a personal cheatsheet. Mostly stuff I want to lookup later. You might find more comprehensive ones somewhere else online.

Connect to localhost with user and password

psql -U $USER -h $HOST

Basic Usage

• \h = list all SQL commands
• \l = show a tabular version of given databases
• \c $DBNAME = connect to a specific database
• \dt = display tables when connected with a database
• \d $TABLE = display strucuter of a specific table
• \du = List all users and their respective roles

Scenarios

Creating a New Dedicated User

CREATE USER theuser WITH PASSWORD ’thepassword’; CREATE ROLE theuser WITH LOGIN; CREATE ROLE thesuperuser LOGIN SUPERUSER;

Creating a New Schema and privileging an Existing User

GRANT ALL ON DATABASE db_name TO username; GRANT ALL ON SCHEMA public TO username;

Delete All Databases

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

SELECT grantee, table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE table_catalog = ‘risu_prod’; – Replace ‘your_database_name’ with your actual database name