Skip to content

Instantly share code, notes, and snippets.

CREATE OR REPLACE FUNCTION check_fk_child() RETURNS trigger AS $$
DECLARE
fk_local TEXT := TG_ARGV[0];
parent_table TEXT := TG_ARGV[1];
fk_val INT;
is_valid BOOLEAN;
query TEXT;
BEGIN
-- fk_val = getattr(NEW, fk_local)
EXECUTE format('SELECT $1.%I', fk_local) USING NEW INTO fk_val;
@darkxanter
darkxanter / table_size.sql
Created March 22, 2024 17:05
postgresql: size of tables
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
@darkxanter
darkxanter / ValueId.kt
Last active March 29, 2023 07:37
Kotlin Exposed Tables with value class ID
import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.ColumnType
import org.jetbrains.exposed.sql.IColumnType
import org.jetbrains.exposed.sql.IntegerColumnType
import org.jetbrains.exposed.sql.LongColumnType
import org.jetbrains.exposed.sql.UUIDColumnType
import java.sql.ResultSet
import kotlin.reflect.KClass
public interface ValueId<T : Comparable<T>> : Comparable<ValueId<T>> {
@darkxanter
darkxanter / 54-manage-units.pkla
Created August 28, 2022 12:08
Allow user to manage units on Ubuntu
cat /etc/polkit-1/localauthority/50-local.d/54-manage-units.pkla
[systemd]
Identity=unix-user:deploy
Action=org.freedesktop.systemd1.manage-units
ResultAny=yes
ResultInactive=yes
ResultActive=yes
@darkxanter
darkxanter / stack-proxy.yml
Created February 8, 2022 10:34
traefik deploy
# this traefik reverse proxy has a bunch of features:
# - reverse proxy all 80/443 ingress traffic on a swarm
# - dynamic config via each app's swarm service labels
# - HA multi-container design for traefik
# - runs traefik on host NIC directly, to improve performance
# and capture client IP's
# - uses consul to store static config for startup
# - uses haproxy to allow offloading traefik to worker nodes
# - store consul data in a volume on cloud storage with rexray
@darkxanter
darkxanter / pg_backup.config
Last active September 6, 2021 09:00
PostgreSQL systemd backup service
##############################
## POSTGRESQL BACKUP CONFIG ##
##############################
# Optional system user to run backups as. If the user the script is running as doesn't match this
# the script terminates. Leave blank to skip check.
BACKUP_USER=postgres
# Optional hostname to adhere to pg_hba policies. Will default to "localhost" if none specified.
HOSTNAME=
@darkxanter
darkxanter / docker-cleanup.service
Last active September 6, 2021 08:49
docker cleanup
[Unit]
Description=docker cleanup service
Requires=docker.service
After=docker.service
[Service]
Type=oneshot
ExecStart=/usr/bin/docker system prune --volumes -f
@darkxanter
darkxanter / tcp_flags.txt
Created July 2, 2021 11:19 — forked from tuxfight3r/tcp_flags.txt
tcpdump - reading tcp flags
##TCP FLAGS##
Unskilled Attackers Pester Real Security Folks
==============================================
TCPDUMP FLAGS
Unskilled = URG = (Not Displayed in Flag Field, Displayed elsewhere)
Attackers = ACK = (Not Displayed in Flag Field, Displayed elsewhere)
Pester = PSH = [P] (Push Data)
Real = RST = [R] (Reset Connection)
Security = SYN = [S] (Start Connection)
@darkxanter
darkxanter / RateLimit.kt
Created May 18, 2021 06:29
ktor rate limit
fun Application.module(testing: Boolean = false) {
val config = RateLimiterConfig.custom()
.limitRefreshPeriod(Duration.ofMillis(1))
.limitForPeriod(10)
.timeoutDuration(Duration.ofMillis(25))
.build()
val rateLimiterRegistry = RateLimiterRegistry.of(config)
val testRateLimiter = rateLimiterRegistry
.rateLimiter("test")
routing {
@darkxanter
darkxanter / _firewalld
Created February 28, 2021 13:06
zsh firewalld
#compdef firewall-cmd firewall-offline-cmd
local curcontext="$curcontext" name nm="$compstate[nmatches]"
local -a state line expl direct args auxargs opargs suf
typeset -A opt_args
direct=(
'--get-all-chains[get all chains]'
'--get-chains[get all chains added to the table]:family:(ipv4 ipv6 eb):table:->tables'
'--add-chain[add a new chain to the table]:family:(ipv4 ipv6 eb):table:->tables:new chain'