conflicts_grants.sh

#!/bin/bash
PSQL="psql-17 --no-psqlrc"  # Disable .psqlrc to avoid timing output
DB="test_pg_concurrent"
NUM_ROLES=5

echo "PostgreSQL Concurrent GRANT Test (${NUM_ROLES} roles)"
echo "========================================================"

cleanup() {
    $PSQL -d postgres -q -c "DROP DATABASE IF EXISTS $DB;" 2>/dev/null || true
    for i in $(seq 1 $NUM_ROLES); do
        $PSQL -d postgres -q -c "DROP ROLE IF EXISTS r${i};" 2>/dev/null || true
    done
}
cleanup
trap cleanup EXIT

echo "Setup: Creating database, ${NUM_ROLES} tables, ${NUM_ROLES} schemas, ${NUM_ROLES} roles..."
$PSQL -d postgres -q -c "CREATE DATABASE $DB;"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "CREATE TABLE t${i} (c${i} int);"
    $PSQL -d $DB -q -c "CREATE SCHEMA s${i};"
    $PSQL -d $DB -q -c "CREATE ROLE r${i};"
done

test_grant() {
    local tmpfile=$(mktemp)
    if ! $PSQL -d $DB -q -c "$1" 2>"$tmpfile"; then
        grep -q "tuple concurrently updated" "$tmpfile" && echo "CONFLICT $1"
    else
        echo "SUCCESS $1"
    fi
    rm "$tmpfile"
}

echo ""
echo "Test 1: Concurrent GRANTs on SAME table (t1)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on table t1..."
for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT ON t1 TO r$i;") &
done
wait


echo ""
echo "Test 2: Concurrent GRANTs on DIFFERENT tables (t1-t${NUM_ROLES})"
echo "  Running ${NUM_ROLES} GRANTs in parallel, each on different table..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT ON t$i TO r$i;") &
done
wait

echo ""
echo "Test 3: Concurrent GRANTs on SAME column (c1 on t1)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on column c1 of table t1..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT (c1) ON t1 TO r$i;") &
done
wait


echo ""
echo "Test 4: Concurrent GRANTs on DIFFERENT columns"
echo "  Running ${NUM_ROLES} GRANTs in parallel, each on different column..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT (c$i) ON t$i TO r$i;") &
done
wait


echo ""
echo "Test 5: Concurrent GRANTs on SAME schema (public)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on schema public..."
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT USAGE ON SCHEMA public TO r$i;") &
done
wait

With advisory lock

#!/bin/bash
PSQL="psql-17 --no-psqlrc"  # Disable .psqlrc to avoid timing output
DB="test_pg_concurrent"
NUM_ROLES=5

echo "PostgreSQL Concurrent GRANT Test with Advisory Locks (${NUM_ROLES} roles)"
echo "=========================================================================="

cleanup() {
    $PSQL -d postgres -q -c "DROP DATABASE IF EXISTS $DB;" 2>/dev/null || true
    for i in $(seq 1 $NUM_ROLES); do
        $PSQL -d postgres -q -c "DROP ROLE IF EXISTS r${i};" 2>/dev/null || true
    done
}
cleanup
trap cleanup EXIT

echo "Setup: Creating database, ${NUM_ROLES} tables, ${NUM_ROLES} schemas, ${NUM_ROLES} roles..."
$PSQL -d postgres -q -c "CREATE DATABASE $DB;"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "CREATE TABLE t${i} (c${i} int);"
    $PSQL -d $DB -q -c "CREATE SCHEMA s${i};"
    $PSQL -d $DB -q -c "CREATE ROLE r${i};"
done

# Generate lock ID from a string (simplified hash using CRC32)
generate_lock_id() {
    local lock_string="$1"
    echo -n "$lock_string" | cksum | awk '{print $1}'
}

# Generate grant lock ID based on object type and name like in https://github.com/cyrilgdn/terraform-provider-postgresql/commit/ab2f4fb47c31d04fc67e3c65d98de38248195761
generate_grant_lock_id() {
    local database="$1"
    local object_type="$2"
    local schema="$3"
    local object_name="$4"

    local lock_string

    case "$object_type" in
        database)
            lock_string="grant:db:${database}"
            ;;
        schema)
            lock_string="grant:schema:${database}.${schema}"
            ;;
        table|sequence|column)
            if [ -z "$object_name" ]; then
                lock_string="grant:schema:${database}.${schema}"
            else
                lock_string="grant:${object_type}:${database}.${schema}.${object_name}"
            fi
            ;;
        *)
            lock_string="grant:db:${database}"
            ;;
    esac

    generate_lock_id "$lock_string"
}

test_grant() {
    local grant_sql="$1"
    local database="$2"
    local object_type="$3"
    local schema="$4"
    local object_name="$5"

    local lock_id=$(generate_grant_lock_id "$database" "$object_type" "$schema" "$object_name")

    local tmpfile=$(mktemp)

    if ! $PSQL -d $DB -q 2>"$tmpfile" >/dev/null <<-SQL
		BEGIN;
		SELECT pg_advisory_xact_lock($lock_id);
		$grant_sql
		COMMIT;
	SQL
    then
        grep -q "tuple concurrently updated" "$tmpfile" && echo "CONFLICT $grant_sql (lock_id=$lock_id)"
    else
        echo "SUCCESS $grant_sql (lock_id=$lock_id)"
    fi
    rm "$tmpfile"
}

echo ""
echo "Test 1: Concurrent GRANTs on SAME table (t1)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on table t1..."
echo "  All should use the same lock ID -> serialized execution"
for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT ON t1 TO r$i;" "$DB" "table" "public" "t1") &
done
wait


echo ""
echo "Test 2: Concurrent GRANTs on DIFFERENT tables (t1-t${NUM_ROLES})"
echo "  Running ${NUM_ROLES} GRANTs in parallel, each on different table..."
echo "  Each should use a different lock ID -> true parallel execution"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT ON t$i TO r$i;" "$DB" "table" "public" "t$i") &
done
wait

echo ""
echo "Test 3: Concurrent GRANTs on SAME column (c1 on t1)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on column c1 of table t1..."
echo "  Columns use table-level lock -> should use same lock ID as table"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT (c1) ON t1 TO r$i;" "$DB" "column" "public" "t1") &
done
wait


echo ""
echo "Test 4: Concurrent GRANTs on DIFFERENT columns on DIFFERENT tables"
echo "  Running ${NUM_ROLES} GRANTs in parallel, each on different column..."
echo "  Each should use different lock ID -> true parallel execution"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT SELECT (c$i) ON t$i TO r$i;" "$DB" "column" "public" "t$i") &
done
wait


echo ""
echo "Test 5: Concurrent GRANTs on SAME schema (public)"
echo "  Running ${NUM_ROLES} GRANTs in parallel on schema public..."
echo "  All should use the same lock ID -> serialized execution"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON SCHEMA public FROM r$i;" 2>&1 || true
done

for i in $(seq 1 $NUM_ROLES); do
    (test_grant "GRANT USAGE ON SCHEMA public TO r$i;" "$DB" "schema" "public" "") &
done
wait

PostgreSQL Concurrent GRANT Test with Advisory Locks (5 roles)
==========================================================================
Setup: Creating database, 5 tables, 5 schemas, 5 roles...

Test 1: Concurrent GRANTs on SAME table (t1)
  Running 5 GRANTs in parallel on table t1...
  All should use the same lock ID -> serialized execution
SUCCESS GRANT SELECT ON t1 TO r3; (lock_id=279114261)
SUCCESS GRANT SELECT ON t1 TO r1; (lock_id=279114261)
SUCCESS GRANT SELECT ON t1 TO r2; (lock_id=279114261)
SUCCESS GRANT SELECT ON t1 TO r4; (lock_id=279114261)
SUCCESS GRANT SELECT ON t1 TO r5; (lock_id=279114261)

Test 2: Concurrent GRANTs on DIFFERENT tables (t1-t5)
  Running 5 GRANTs in parallel, each on different table...
  Each should use a different lock ID -> true parallel execution
SUCCESS GRANT SELECT ON t4 TO r4; (lock_id=2275349088)
SUCCESS GRANT SELECT ON t1 TO r1; (lock_id=279114261)
SUCCESS GRANT SELECT ON t3 TO r3; (lock_id=2958060570)
SUCCESS GRANT SELECT ON t5 TO r5; (lock_id=1434899388)
SUCCESS GRANT SELECT ON t2 TO r2; (lock_id=1648995782)

Test 3: Concurrent GRANTs on SAME column (c1 on t1)
  Running 5 GRANTs in parallel on column c1 of table t1...
  Columns use table-level lock -> should use same lock ID as table
SUCCESS GRANT SELECT (c1) ON t1 TO r5; (lock_id=1085196788)
SUCCESS GRANT SELECT (c1) ON t1 TO r3; (lock_id=1085196788)
SUCCESS GRANT SELECT (c1) ON t1 TO r2; (lock_id=1085196788)
SUCCESS GRANT SELECT (c1) ON t1 TO r4; (lock_id=1085196788)
SUCCESS GRANT SELECT (c1) ON t1 TO r1; (lock_id=1085196788)

Test 4: Concurrent GRANTs on DIFFERENT columns on DIFFERENT tables
  Running 5 GRANTs in parallel, each on different column...
  Each should use different lock ID -> true parallel execution
SUCCESS GRANT SELECT (c3) ON t3 TO r3; (lock_id=3764148219)
SUCCESS GRANT SELECT (c1) ON t1 TO r1; (lock_id=1085196788)
SUCCESS GRANT SELECT (c5) ON t5 TO r5; (lock_id=92989533)
SUCCESS GRANT SELECT (c2) ON t2 TO r2; (lock_id=843421223)
SUCCESS GRANT SELECT (c4) ON t4 TO r4; (lock_id=3616745857)

Test 5: Concurrent GRANTs on SAME schema (public)
  Running 5 GRANTs in parallel on schema public...
  All should use the same lock ID -> serialized execution
SUCCESS GRANT USAGE ON SCHEMA public TO r1; (lock_id=1055784270)
SUCCESS GRANT USAGE ON SCHEMA public TO r4; (lock_id=1055784270)
SUCCESS GRANT USAGE ON SCHEMA public TO r2; (lock_id=1055784270)
SUCCESS GRANT USAGE ON SCHEMA public TO r5; (lock_id=1055784270)
SUCCESS GRANT USAGE ON SCHEMA public TO r3; (lock_id=1055784270)

with some sort of better parallelism

#!/bin/bash
PSQL="psql-17 --no-psqlrc"
DB="test_pg_concurrent"
NUM_ROLES=10

cleanup() {
    $PSQL -d postgres -q -c "DROP DATABASE IF EXISTS $DB;" 2>/dev/null || true
    for i in $(seq 1 $NUM_ROLES); do
        $PSQL -d postgres -q -c "DROP ROLE IF EXISTS r${i};" 2>/dev/null || true
    done
}
cleanup
trap cleanup EXIT

echo "Setup..."
$PSQL -d postgres -q -c "CREATE DATABASE $DB;"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "CREATE TABLE t${i} (c${i} int); CREATE ROLE r${i};" 2>/dev/null
done

generate_lock_id() {
    echo -n "$1" | cksum | awk '{print $1}'
}

test_grant_with_lock() {
    local sql="$1"
    local lock_key="$2"
    local barrier="$3"

    local lock_id=$(generate_lock_id "$lock_key")
    local tmpfile=$(mktemp)
    local timefile=$(mktemp)

    # Wait for barrier
    while [ -f "$barrier" ]; do sleep 0.001; done

    if $PSQL -d $DB -q -t 2>"$tmpfile" >"$timefile" <<-SQL
		BEGIN;
		SELECT CURRENT_TIMESTAMP(3);
		SELECT pg_advisory_xact_lock($lock_id);
		$sql
		COMMIT;
	SQL
    then
        local timestamp=$(head -1 "$timefile" | xargs)
        echo "✓ [$timestamp] $sql"
    else
        grep -q "tuple concurrently updated" "$tmpfile" && echo "✗ CONFLICT: $sql"
    fi
    rm "$tmpfile" "$timefile"
}

test_grant_no_lock() {
    local sql="$1"
    local barrier="$2"
    local tmpfile=$(mktemp)

    while [ -f "$barrier" ]; do sleep 0.001; done

    # Get timestamp first, then execute
    local timestamp=$($PSQL -d $DB -q -t -c "SELECT CURRENT_TIMESTAMP(3);" | xargs)

    if $PSQL -d $DB -q 2>"$tmpfile" >/dev/null -c "$sql"
    then
        echo "✓ [$timestamp] $sql"
    else
        if grep -q "tuple concurrently updated" "$tmpfile"; then
            echo "✗ [$timestamp] CONFLICT: $sql"
        fi
    fi
    rm "$tmpfile"
}

run_test() {
    local barrier=$(mktemp)
    for cmd in "$@"; do
        eval "$cmd &"
    done
    sleep 0.1
    rm "$barrier"
    wait
}

echo ""
echo "Test 1: Same table WITH locks"
echo "------------------------------------------------------------"
cmds=()
for i in $(seq 1 $NUM_ROLES); do
    cmds+=("test_grant_with_lock 'GRANT SELECT ON t1 TO r$i;' 'grant:table:$DB.public.t1' '$barrier'")
done
run_test "${cmds[@]}"

echo ""
echo "Test 2: Same column WITH locks"
echo "------------------------------------------------------------"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>/dev/null || true
done
cmds=()
for i in $(seq 1 $NUM_ROLES); do
    cmds+=("test_grant_with_lock 'GRANT SELECT (c1) ON t1 TO r$i;' 'grant:column:$DB.public.t1' '$barrier'")
done
run_test "${cmds[@]}"

echo ""
echo "Test 3: Same table WITHOUT locks (expect conflicts!)"
echo "------------------------------------------------------------"
for i in $(seq 1 $NUM_ROLES); do
    $PSQL -d $DB -q -c "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM r$i;" 2>/dev/null || true
done
cmds=()
for i in $(seq 1 $NUM_ROLES); do
    cmds+=("test_grant_no_lock 'GRANT SELECT ON t1 TO r$i;' '$barrier'")
done
run_test "${cmds[@]}"

echo ""
echo "Done!"

./test_concurrent_simple.sh
Setup...

Test 1: Same table WITH locks
------------------------------------------------------------
✓ [2025-11-22 16:12:18.674+01] GRANT SELECT ON t1 TO r2;
✓ [2025-11-22 16:12:18.69+01] GRANT SELECT ON t1 TO r4;
✓ [2025-11-22 16:12:18.698+01] GRANT SELECT ON t1 TO r10;
✓ [2025-11-22 16:12:18.717+01] GRANT SELECT ON t1 TO r3;
✓ [2025-11-22 16:12:18.723+01] GRANT SELECT ON t1 TO r1;
✓ [2025-11-22 16:12:18.744+01] GRANT SELECT ON t1 TO r7;
✓ [2025-11-22 16:12:18.752+01] GRANT SELECT ON t1 TO r5;
✓ [2025-11-22 16:12:18.761+01] GRANT SELECT ON t1 TO r8;
✓ [2025-11-22 16:12:18.778+01] GRANT SELECT ON t1 TO r6;
✓ [2025-11-22 16:12:18.787+01] GRANT SELECT ON t1 TO r9;

Test 2: Same column WITH locks
------------------------------------------------------------
✓ [2025-11-22 16:12:19.115+01] GRANT SELECT (c1) ON t1 TO r2;
✓ [2025-11-22 16:12:19.126+01] GRANT SELECT (c1) ON t1 TO r1;
✓ [2025-11-22 16:12:19.135+01] GRANT SELECT (c1) ON t1 TO r3;
✓ [2025-11-22 16:12:19.147+01] GRANT SELECT (c1) ON t1 TO r4;
✓ [2025-11-22 16:12:19.194+01] GRANT SELECT (c1) ON t1 TO r9;
✓ [2025-11-22 16:12:19.234+01] GRANT SELECT (c1) ON t1 TO r10;
✓ [2025-11-22 16:12:19.229+01] GRANT SELECT (c1) ON t1 TO r7;
✓ [2025-11-22 16:12:19.217+01] GRANT SELECT (c1) ON t1 TO r6;
✓ [2025-11-22 16:12:19.197+01] GRANT SELECT (c1) ON t1 TO r5;
✓ [2025-11-22 16:12:19.252+01] GRANT SELECT (c1) ON t1 TO r8;

Test 3: Same table WITHOUT locks (expect conflicts!)
------------------------------------------------------------
✓ [2025-11-22 16:12:19.567+01] GRANT SELECT ON t1 TO r1;
✓ [2025-11-22 16:12:19.592+01] GRANT SELECT ON t1 TO r5;
✗ [2025-11-22 16:12:19.615+01] CONFLICT: GRANT SELECT ON t1 TO r7;
✗ [2025-11-22 16:12:19.589+01] CONFLICT: GRANT SELECT ON t1 TO r3;
✗ [2025-11-22 16:12:19.609+01] CONFLICT: GRANT SELECT ON t1 TO r6;
✓ [2025-11-22 16:12:19.635+01] GRANT SELECT ON t1 TO r8;
✗ [2025-11-22 16:12:19.588+01] CONFLICT: GRANT SELECT ON t1 TO r4;
✓ [2025-11-22 16:12:19.592+01] GRANT SELECT ON t1 TO r2;
✓ [2025-11-22 16:12:19.716+01] GRANT SELECT ON t1 TO r9;
✗ [2025-11-22 16:12:19.678+01] CONFLICT: GRANT SELECT ON t1 TO r10;

Done!