Cheatsheets

aws-bastion-host.md

Steps to Set Up Bastion Host on AWS

This Bastion Host is set up so that one could connect to the Postgres RDS that is not exposed to the public internet.

Spin up an EC2 Instance

• Ubuntu (or any linux that has SSH)
• t2.micro is enough
• select VPC subnet (should be same as the RDS is)
• select storage (default is fine, more if one intents to use it to download anything)

Create Security Groups

public-to-bastion

• set inbound rules to only allow SSH from selected IP range

bastion-to-rds

• set inbound rule to allow SSH from public-to-bastion security group
• set inbound rule to allow Postgresql conn on Port 5432 from public-to-bastion security group

Add security groups to Bastion and RDS

• add public-to-bastion to bastion ec2
• add bastion-to-rds to RDS instance

Elastic IP for Bastion

• get a static public IP for bastion

aws-rds-postgis-fresh-start.sql

CREATE ROLE filip WITH PASSWORD 'xyxyxyxyxyxyx' login;
CREATE ROLE yoongshin WITH PASSWORD 'jkjkjkjkjkjk' login;

GRANT rds_superuser TO filip;
GRANT rds_superuser TO yoongshin;

-- easier to troubleshoot if needed
GRANT ALL PRIVILEGES ON DATABASE postgres TO filip;
GRANT ALL PRIVILEGES ON DATABASE postgres TO yoongshin;


-- power users
GRANT ALL PRIVILEGES ON DATABASE openstreetmap TO filip;
GRANT ALL PRIVILEGES ON SCHEMA public TO filip;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO filip;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO filip;

-- power users
GRANT ALL PRIVILEGES ON DATABASE openstreetmap TO yoongshin;
GRANT ALL PRIVILEGES ON SCHEMA public TO yoongshin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO yoongshin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO yoongshin;

-- regular users
GRANT CONNECT ON DATABASE {db_name} TO {user};
GRANT USAGE ON SCHEMA public TO {user};
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO {user};
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO {user};


-- create extensions
CREATE EXTENSION postgis;
CREATE EXTENSION hstore;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;

-- change owner of schemas
ALTER SCHEMA tiger OWNER TO rds_superuser;
ALTER SCHEMA tiger_data OWNER TO rds_superuser;
ALTER SCHEMA topology OWNER TO rds_superuser;


ALTER TABLE public.spatial_ref_sys OWNER TO rds_superuser;
GRANT SELECT, INSERT ON TABLE public.spatial_ref_sys TO public;

aws-rds-postgresql-0.sql

-- access logs
SELECT
	datname,
	usename,
	ssl,
	client_addr
FROM
	pg_stat_ssl
INNER JOIN pg_stat_activity ON
	pg_stat_ssl.pid = pg_stat_activity.pid
WHERE
	ssl IS TRUE
	AND usename <> 'rdsadmin';

-- get all usernames that has had activities before
SELECT
	DISTINCT usename
FROM
	pg_stat_ssl
INNER JOIN pg_stat_activity ON
	pg_stat_ssl.pid = pg_stat_activity.pid;

-- get all usernames and privileges
SELECT
	*
FROM
	pg_catalog.pg_user;
	
-- get all roles
SELECT
	*
FROM
	pg_roles;

-- get roles and privilege attributes
SELECT
	u.usename AS "Role name",
	CASE
		WHEN u.usesuper
		AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
		WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
		WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
		ELSE CAST('' AS pg_catalog.text)
	END AS "Attributes"
FROM
	pg_catalog.pg_user u
ORDER BY
	1;
	

-- show role heirarchy 
SELECT
	r.rolname,
	ARRAY(
		SELECT
			b.rolname
		FROM
			pg_catalog.pg_auth_members m
		JOIN pg_catalog.pg_roles b ON
			(
				m.roleid = b.oid
			)
		WHERE
			m.member = r.oid
	) AS memberof
FROM
	pg_catalog.pg_roles r
--WHERE
--	r.rolname NOT IN (
--		'pg_signal_backend', 'rds_iam', 'rds_replication', 'rds_superuser', 'rdsadmin', 'rdsrepladmin'
--	)
ORDER BY
	1;
	
	
-- create database openstreetmap
CREATE DATABASE openstreetmap;

-- create role/user
CREATE ROLE yoongshin WITH PASSWORD 'xxxxxx' login;
-- change user password (if needed)
ALTER ROLE yoongshin WITH PASSWORD 'yyyyyyyyy';
-- grant role(s) to user
GRANT rds_superuser TO yoongshin;
GRANT postgres TO yoongshin;

-- grant privileges of a database to user
GRANT ALL PRIVILEGES ON
DATABASE postgres TO yoongshin;
GRANT ALL PRIVILEGES ON
DATABASE openstreetmap TO yoongshin;
GRANT ALL PRIVILEGES ON
DATABASE openstreetmap TO postgres;


-- grant privileges of within schema(s) to user
GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA pg_catalog TO postgres;

-- grant privileges of within schema(s) to user
GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA public TO yoongshin;
GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA pg_catalog TO yoongshin;


-- grant selective privileges
GRANT CONNECT ON DATABASE openstreetmap TO {user};
GRANT USAGE ON SCHEMA public TO {user};
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO {user};
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO {user};

-- check created database
SELECT
	datname
FROM
	pg_database;

-- login as yoongshin and switch to openstreetmap database before continuing

aws-rds-postgresql-1.sql

-- login as yoongshin and switch to openstreetmap database before continuing

-- create extensions

CREATE EXTENSION postgis;
CREATE EXTENSION hstore;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION postgis_topology;

-- change owner (optional)
ALTER SCHEMA tiger OWNER TO yoongshin;
ALTER SCHEMA tiger_data OWNER TO yoongshin;
ALTER SCHEMA topology OWNER TO yoongshin;


-- test POSTGIS installation and version
SELECT
	PostGIS_full_version();

-- list other installed pg_extension
SELECT *
FROM
pg_extension;

-- create functions
CREATE FUNCTION EXEC(TEXT) RETURNS TEXT LANGUAGE plpgsql VOLATILE AS $f$ BEGIN EXECUTE $1;

RETURN $1;
END;

$f$;

-- call EXEC function to test 
SELECT
	EXEC(
		'ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO yoongshin;'
	)
FROM
	(
		SELECT
			nspname, relname
		FROM
			pg_class c
		JOIN pg_namespace n ON
			(
				c.relnamespace = n.oid
			)
		WHERE
			nspname IN (
				'tiger', 'topology'
			)
			AND relkind IN (
				'r', 'S', 'v'
			)
		ORDER BY
			relkind = 'S'
	) s;


-- set schema(s) search path
SET
search_path = public,
tiger;

-- test PostGIS function
SELECT
	na.address,
	na.streetname,
	na.streettypeabbrev,
	na.zip
FROM
	normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;

-- set schema(s) search path
SET
search_path = public,
topology;

-- test topology function
SELECT
	topology.createtopology(
		'my_new_topo', 26986, 0.5
	);

homebrew-hack.md

Sometimes, we need older packages/libraries that are not available on Homebrew.

Understanbly it is hard enough to maintain so many packages, let alone having multiple versions of them available. Homebrew team also work very hard to make sure the versioned packages are still supported by their respective creators/maintainers.

However, what if the version that we need isn't on Homebrew. Hope is not lost, here is a quick way to hack the desired version locally. Here I am using Hugo static site generator as an example.

First get the url of the release package. When in doubt which file or how the link looks like, refer to the main formulae under https://github.com/Homebrew/homebrew-core/tree/master/Formula. In our case this file here.

With the release package url, we create a template of the version formulae using the following command. brew create https://github.com/gohugoio/hugo/archive/v0.70.0.tar.gz --set-name [email protected]

Note that --set-version 0.70.0 if version is following semver format, and the filename [email protected] should dictate in this case it is v.0.70.0.

The earlier command created file [email protected] will be created on /usr/local/Homebrew/Library/Taps/homebrew/homebrew-core/Formula/ folder. Open it in the editor and fill in description and homepage. The rest of the content should be as close to the main formulae as possible.

Once that is done, run brew audit --new-formula [email protected] to verify it is correct. Fix error if there is any.

Then run brew audit --strict --online [email protected] for strict audit online. If that's OK, that means no big issue for submission.

Now, it is ready to be installed locally, run brew install --build-from-source [email protected]. This should pull the 0.70.0 version directly from GoHugo and install it locally.

Last, add the path to .zshrc or .bashrc by echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.zshrc.

If one has gotten this far, one might feel charitable and want to contribute the formulaes back to Homebrew so others could benefit from it too. If so one desires, follow the respective guides to commit and push to a forked repo, and [Open a Pull Request].

However, be aware that ALL THESE CONDITIONS HAS TO BE FULLFILLED or the PR won't be approved. These rules are there for good reasons I belive, as Homebrew has great responsibility in providing as stress-free as an experience for its users.

osm2pgsql.md

osm2pgsql planet to PostGIS on AWS RDS

pre-requisites and notes:

• since no direct ssh allowed to AWS RDS, I need an EC2 instance to download the planet.osm.pbf
• the EC2 instance needs to be on the same VPC as the RDS
• the EC2 instance needs to be allowed to connect to the PostGIS RDS via psql
• the Bastion host was a natural choice for this
• the Bastion host was upgraded to m5.8xlarge
• the volume has to be expanded to at least 500GB as well (note: volume can only be adjusted once every 24 hours)
• remember to use screen sessions for long processes

download planet.osm.pbf

Download OSM latest planet from osm or AWS S3: wget https://planet.osm.org/pbf/planet-latest.osm.pbf

Or copy from aws s3 (need to figure out the latest filename): aws s3 cp s3://osm-pds/2020/planet-200629.osm.pbf .

Test connection to RDS to make sure the database is reachable

First test the connection to RDS via psql, key in password when prompted psql -h postgis.cwfu5zygh6d4.ap-southeast-1.rds.amazonaws.com -p 5432 -U postgres -W or psql -h postgis.cwfu5zygh6d4.ap-southeast-1.rds.amazonaws.com -p 5432 -d {database} -U {username} -W

load OSM planet tiles to PostGIS Server

Then, load osm to PostGIS using the verified credentials time osm2pgsql --create --slim --cache 50000 --keep-coastlines --flat-nodes tmp-osm --hstore --extra-attributes --host postgis.cwfu5zygh6d4.ap-southeast-1.rds.amazonaws.com --port 5432 --username postgres --password --database openstreetmap planet-latest.osm.pbf

random.md

I've gotten 3 different certs for connecting to AWS RDS

• rds-combined-ca-bundle.pem
• rds-ca-2019-root.pem
• rds-ca-2019-ap-southeast-1.pem

Usage is as such:

• psql "host=postgis.cwfu5zygh6d4.ap-southeast-1.rds.amazonaws.com port=5432 sslmode=verify-full sslrootcert={path-to-cert} user={username} password={password}"
• psql "host=aurora-postgis-cluster.cluster-cwfu5zygh6d4.ap-southeast-1.rds.amazonaws.com port=5432 sslmode=verify-full sslrootcert={path-to-cert} user={username} password={password}"

Somehow only the rds-combined-ca-bundle.pem and rds-ca-2019-root.pem worked for me.

screen-cheatsheet.md

Basic Screen Command

Install if Screen not exists

sudo apt-get install screen

Simple Screen Session

Get a simple screen session started

screen

start some long process like download a huge file

wget http://download.geofabrik.de/europe/estonia-latest.osm.pbf

Detach from screen

Crtl+A, then type d

List all the screen sessions

screen -ls

To reattach to screen by session number

screen -r {session_number}

To exit when a process is done

exit

To exit by killing session

Ctrl+A, then type K

Screen Session with Screen Names

create a screen session with session name

screen -S {session_name}

Detach from this screen session the same way

Crtl+A, then type d

List all the screen sessions, see session listed in {session_number}.{session_name} format

screen -ls

reattach to a screen session with session name

screen -r {session_name}

Screen Session with Multiple Windows

start first screen session with name of first program

screen -S {first_program}

start first program

{command to start first program}

create another screen window for second program

Ctrl+A, then C

start second program

{command to start second program}

to hop between two windows

Crtl+A, then {index_of_window} (index starts from 0)

Split Screen View on window

Split the terminal window horizontally

Ctrl+A, then Shift+S (focus is on upper region by default)

Split the terminal window horizontally

Ctrl+A, then |

Switch between split regions

Ctrl+A, then Tab

Attach an existing session to the bottom region

Ctrl+A, and then {index_of_window}

Sometimes instead of attaching an existing session to the bottom region, one can also create a new screen session

Ctrl+A, then C

Hop between regions

Ctrl+A

Close all regions except the current one

Ctrl+A, then Q

Close this region

Ctrl+A, then X

To Detach from all session

Ctrl+A, then D

Note that window split is temporary. When we detached from screen session, it will be gone.

Sharing a Screen Session via SSH

Create a named screen session (at the shared host after ssh in)

screen -d -m -S {session_name} -d: detached -m: enforced creation -S: with session name

activate screen sharing

screen -X ssh-geek -X: multiple screen mode

another SSH session who also SSH into same shared host with same credential can join the shared screen session

screen -X ssh-geek

Steps to Set up JupyterHub with JupyterLab

1. `sudo apt update && sudo apt upgrade`
2. add the following lines to the end of `.bashrc` 
```python3
alias python='python3'
alias pip='pip3'

export PATH=/home/{username}/.local/bin:$PATH
```
3. Follow [this guide](https://jupyterhub.readthedocs.io/en/latest/) to install JupyterHub. 
	
	- set up SSH, HTTP, HTTPS access on security group
	- `curl -L https://tljh.jupyter.org/bootstrap.py | sudo -E python3 - --admin {admin-username}`
   Once installation is done and successful, JupyterHub is accessible at http://{bastion-host-ip}/hub
4. To install globally accessible pip packages, use 
	`sudo -E pip install {package}` or `sudo -E conda install -c conda-forge {package}`
5. To install `psycopg2`, follow the following steps:
	- install pre-requisites: `sudo apt install -y cmake curl git libbz2-dev libedit-dev libffi-dev libpq-dev libreadline-dev libsqlite3-dev libssl-dev libxml2-dev python3-dev python3-openssl
	python3-pip python3.8-dev software-properties-common tk-dev unzip wget xz-utils
	zlib1g-dev build-essential`
	- `sudo -E pip install psycopg2`
6. Install R kernel. Follow the following steps:
	- Install R following instruction from [CRAN.r](https://cran.r-project.org/) 
	- In R console: Type `install.packages('IRkernel')` and then `IRkernel::installspec(user = FALSE)` Key in the following into R console
7. Install [jupyterlab-git](https://github.com/jupyterlab/jupyterlab-git): `sudo jupyter labextension install @jupyterlab/git` 
8. Install [nbdime-jupyterlab](https://github.com/jupyter/nbdime): `sudo jupyter labextension install nbdime-jupyterlab` 

ubuntu-bash.md

Ubuntu Cheatsheet

SSH as root/power user

SSH via private cert ssh -i "ssh_tunnel.pem" ubuntu@{host}

ssh -i "ssh_tunnel.pem" root@{host}

User Management

Add User

add user

Type sudo adduser {username}. Follow prompt to set password, name etc.

assign sudo power

If user needs sudo power, type sudo usermod -aG sudo {username}.

test sudo power

Morph into the new user, su - {username}. Try sudo apt update, to see if sudo works for this user.

Setup SSH

su - {username}

set up authorized_keys for future SSH without going through ubuntu

create .ssh directory by mkdir -p /home/{username}/.ssh create/edit authorized_keys by nano /home/{username}/.ssh/authorized_keys and paste in user's public key.

make sure permissions are set correctly

sudo chmod 700 /home/{username}/.ssh sudo chmod 644 /home/{username}/.ssh/authorized_keys sudo chown -R {username}:{username} /home/{username}/

Test SSH set up

Logout and SSH using user's private key ssh -i "~/{username}/.ssh/private_key" {username}@{host}