JacobJohansen’s gists

JacobJohansen / postgres_queries_and_commands.sql

Created March 29, 2024 19:05 — forked from rgreenjr/postgres_queries_and_commands.sql

Useful PostgreSQL Queries and Commands

	-- show running queries (pre 9.2)
	SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
	FROM pg_stat_activity
	WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

	-- show running queries (9.2)
	SELECT pid, age(clock_timestamp(), query_start), usename, query
	FROM pg_stat_activity
	WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'

JacobJohansen / cq_sg_rules.sql

Created July 11, 2022 18:20

CloudQuery AWS Security Group Rules output

	SELECT sg.group_name, sg.id as sg_id, permission_type, from_port, ip_protocol, to_port, cidr, null as rule_group_id, null as rule_group_name, tags from aws_ec2_security_groups as sg
	LEFT JOIN aws_ec2_security_group_ip_permissions as sg_ip on sg.cq_id = sg_ip.security_group_cq_id
	LEFT JOIN aws_ec2_security_group_ip_permission_ip_ranges as sg_ip_range on sg_ip.cq_id = sg_ip_range.security_group_ip_permission_cq_id

	UNION

	SELECT sg.group_name, sg.id as sg_id, permission_type, from_port, ip_protocol, to_port, null as cidr, rule_group_id, rule_group_name, tags from aws_ec2_security_groups as sg
	LEFT JOIN aws_ec2_security_group_ip_permissions as sg_ip on sg.cq_id = sg_ip.security_group_cq_id
	LEFT JOIN (
	SELECT security_group_ip_permission_cq_id, group_id as rule_group_id, aws_ec2_security_groups.group_name as rule_group_name from aws_ec2_security_groups

JacobJohansen / shell-relative-to-script.sh

Created March 24, 2022 13:08

Provides a solid way for scripts to run from no matter where they're executed from, even when simlinked for all nix systems with perl. most systems don't have readlink

	#!/bin/sh
	relative_dir=`perl -e 'use Cwd "realpath";$pwd = realpath(shift); $pwd =~ s/\/[^\/]*$//; print $pwd' $0`
	cd $relative_dir

JacobJohansen / s3-to-alb-with-lambda.py

Created November 18, 2021 20:11 — forked from Burekasim/s3-to-alb-with-lambda.py

access s3 objects with ALB using lambda

	import os
	import json
	import boto3
	from botocore.exceptions import ClientError


	def lambda_handler(event, context):
	# use 'AWS_REGION' environment variable from lambda built-in variables
	aws_region = os.environ['AWS_REGION']
	bucket_name = os.environ['BUCKET_NAME']

JacobJohansen / convert-pgp-to-ssh.sh

Created November 18, 2021 18:14 — forked from mdellavo/convert-pgp-to-ssh.sh

Convert PGP Public Key to OpenSSH

	# import the public key
	gpg --import ../alice.asc
	gpg --export $KEYID \| openpgp2ssh $KEYID

JacobJohansen / curltime.sh

Created November 10, 2021 14:15

CURL Time helps get stats on the call

	#!/bin/sh

	curl -w @- -o /dev/null -s "$@" <<'EOF'
	time_namelookup: %{time_namelookup}\n
	time_connect: %{time_connect}\n
	time_appconnect: %{time_appconnect}\n
	time_pretransfer: %{time_pretransfer}\n
	time_redirect: %{time_redirect}\n
	time_starttransfer: %{time_starttransfer}\n
	----------\n

JacobJohansen / relative-directory.sh

Created November 9, 2021 18:57

Script Path Relative to File

	#!/bin/sh
	relative_dir=`perl -e 'use Cwd "realpath";$pwd = realpath(shift); $pwd =~ s/\/[^\/]*$//; print $pwd' $0`

JacobJohansen / gist:cbfb65cf9a651303eec7f92293049550

Created November 8, 2021 18:27

shell to get db password

	#!/bin/sh
	relative_dir=`perl -e 'use Cwd "realpath";$pwd = realpath(shift); $pwd =~ s/\/[^\/]*$//; print $pwd' $0`

	GREEN='\033[0;32m'
	NC='\033[0m' # No Color
	RDSHOST=""
	RDSUSER="reader"
	PROFILE_POST_FIX="ro"

	case $2 in

JacobJohansen / redshift-grants-template.sql

Created October 19, 2021 00:42

Generic Redshift Grants

	-- Read Users in Redshift
	GRANT Temporary ON DATABASE "database_name" TO GROUP "read_users";

	GRANT Usage ON SCHEMA "schema_name" TO GROUP "read_users";

	GRANT References, Select ON ALL TABLES IN SCHEMA "schema_name" TO GROUP "read_users";

	-- ReadWrite Users in Redshift

	GRANT Temporary ON DATABASE "database_name" TO GROUP "read_write_users";

JacobJohansen / .gitlab-ci.yml

Created June 30, 2021 23:00

Generic Gitlab Pipeline to For Docker Image

	---
	variables:
	DEFAULT_DOCKER_FILE: "Dockerfile"
	DOCKER_IMAGE_NAME: "{{ imageName }}"

	stages:
	- build

	build-n-push-latest:
	stage: build

Jacob Johansen JacobJohansen