Skip to content

Instantly share code, notes, and snippets.

View JacobJohansen's full-sized avatar

Jacob Johansen JacobJohansen

45 followers · 75 following
View GitHub Profile
@JacobJohansen
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
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
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
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
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
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
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
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
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
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