postgres cheat / psql cheat

postgres.md

create user: CREATE ROLE username WITH LOGIN PASSWORD 'password';

show users: \du

create database: CREATE DATABASE databasename;

show databases: \l

use a specific db: \c dbnamehere;

give user permissions (this is too broad!!): GRANT ALL PRIVILEGES ON DATABASE super_awesome_database TO johndoe;

give user (less) permissions:

GRANT CONNECT ON DATABASE database_name TO username;

GRANT USAGE ON SCHEMA public TO username;

(just 1 table)
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE sometablenamehere TO username;

(all tables)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO username;

(for INSERT, if auto-incremented ID, user will need access to SEQ):
GRANT USAGE, SELECT ON SEQUENCE sometablename_id_seq TO username;

give user ownership of db: ALTER DATABASE db_name OWNER TO new_owner_username;

change table owner: ALTER TABLE <tablename> OWNER TO <username>

show tables: \dt

describe table: \d <table name>

insert (use single quote for strings):

insert into mm_thoughts_contexts (t_id, mm_id) VALUES
(41, 289),
(5, 289),
(35, 289);

read only user:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;

join example:

SELECT
	users_table.first_name, 
	users_table.last_name,
	people_sessions.session_status 

FROM
users_table

LEFT JOIN
some_appointments on users_table.id = some_appointments.people_id 

INNER JOIN
people_sessions on some_appointments.id = people_sessions.some_appointments_id

WHERE
people_sessions.session_status = 'WINNING';

Knex migrations example:

export NODE_ENV=name_of_local_dev_env ; npx knex migrate:latest --knexfile=./path/to/knexfile.ts

limited dev user. only permission to connect and do CRUD

GRANT CONNECT
ON database next13dbdev TO limiteddevuser;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO limiteddevuser;

GRANT USAGE ON SEQUENCE tablenamehere TO limiteddevuser;

-- as superuser, create new db with app only user

CREATE ROLE appusername WITH LOGIN PASSWORD 'appuserpasswd';

CREATE DATABASE appuserdbname; -- owner of db is still superuser

-- connect to correct database first!
/c database_name_here

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE appusertable TO appusername;

-- to get seq name: SELECT pg_get_serial_sequence('appusertable', 'id'); 
GRANT USAGE, SELECT ON SEQUENCE appusertable_id_seq TO appusername;