754 lines
28 KiB
Bash
Executable File
754 lines
28 KiB
Bash
Executable File
#!/bin/bash
|
|
#==============================================================#
|
|
# File : pg-drop-role
|
|
# Desc : Drop a postgres role safely with all dependencies
|
|
# Ctime : 2026-01-01
|
|
# Mtime : 2026-01-01
|
|
# Path : /pg/bin/pg-drop-role
|
|
# Deps : psql
|
|
# License : Apache-2.0 @ https://pigsty.io/docs/about/license/
|
|
# Copyright : 2018-2026 Ruohang Feng / Vonng (rh@vonng.com)
|
|
#==============================================================#
|
|
# Usage: pg-drop-role <role_name> [successor_role] [options]
|
|
# Example: pg-drop-role olduser postgres
|
|
# pg-drop-role olduser --dry-run
|
|
# pg-drop-role olduser --check
|
|
# pg-drop-role olduser postgres -h /tmp -p 5432
|
|
#==============================================================#
|
|
PROG_NAME="$(basename $0)"
|
|
PROG_DIR="$(cd $(dirname $0) && pwd)"
|
|
|
|
#--------------------------------------------------------------#
|
|
# Global Variables
|
|
#--------------------------------------------------------------#
|
|
TARGET_ROLE=""
|
|
SUCCESSOR_ROLE="postgres"
|
|
PGHOST="${PGHOST:-/var/run/postgresql}"
|
|
PGPORT="${PGPORT:-5432}"
|
|
PGDATABASE="${PGDATABASE:-postgres}"
|
|
PGUSER="${PGUSER:-postgres}"
|
|
PSQL="${PSQL:-psql}"
|
|
DRY_RUN=false
|
|
VERBOSE=false
|
|
FORCE=false
|
|
CHECK_ONLY=false
|
|
AUDIT_LOG=""
|
|
PROTECTED_ROLES="postgres replicator dbuser_dba dbuser_monitor"
|
|
TERMINATION_TIMEOUT=10
|
|
|
|
#--------------------------------------------------------------#
|
|
# Log Util
|
|
#--------------------------------------------------------------#
|
|
if [[ -t 1 ]]; then
|
|
__CN='\033[0m';__CK='\033[0;30m';__CR='\033[0;31m';__CG='\033[0;32m';
|
|
__CY='\033[0;33m';__CB='\033[0;34m';__CM='\033[0;35m';__CC='\033[0;36m';__CW='\033[0;37m';
|
|
else
|
|
__CN='';__CK='';__CR='';__CG='';__CY='';__CB='';__CM='';__CC='';__CW='';
|
|
fi
|
|
function log_info() { printf "[${__CG} OK ${__CN}] ${__CG}$*${__CN}\n"; }
|
|
function log_warn() { printf "[${__CY}WARN${__CN}] ${__CY}$*${__CN}\n"; }
|
|
function log_error() { printf "[${__CR}FAIL${__CN}] ${__CR}$*${__CN}\n"; }
|
|
function log_debug() { if [[ "${VERBOSE}" == "true" ]]; then printf "[${__CB}DEBUG${__CN}] ${__CB}$*${__CN}\n"; fi }
|
|
function log_hint() { printf "[${__CB}HINT${__CN}] ${__CB}$*${__CN}\n"; }
|
|
function log_title() { printf "[${__CG}$1${__CN}] ${__CG}$2${__CN}\n"; }
|
|
function log_line() { printf "\n${__CM}===== $* =====${__CN}\n"; }
|
|
|
|
|
|
#--------------------------------------------------------------#
|
|
# Usage
|
|
#--------------------------------------------------------------#
|
|
function usage() {
|
|
cat <<-EOF
|
|
NAME
|
|
${PROG_NAME} - Safely drop a PostgreSQL role with all dependencies
|
|
|
|
SYNOPSIS
|
|
${PROG_NAME} <role_name> [successor_role] [OPTIONS]
|
|
|
|
DESCRIPTION
|
|
This script safely removes a PostgreSQL role by:
|
|
1. Creating audit snapshot of all dependencies
|
|
2. Checking if role exists and is not protected
|
|
3. Disabling login (ALTER ROLE ... NOLOGIN)
|
|
4. Terminating all active connections
|
|
5. Transferring database/tablespace ownership
|
|
6. For each database: REASSIGN OWNED + DROP OWNED
|
|
7. Revoking group memberships
|
|
8. Final DROP ROLE
|
|
|
|
SAFETY GUARANTEE: If REASSIGN OWNED fails, DROP OWNED will NOT
|
|
be executed for that database, preventing accidental data loss.
|
|
|
|
OPTIONS
|
|
-s, --successor <role> Successor role for ownership transfer (default: postgres)
|
|
-h, --host <host> Database server host (default: /var/run/postgresql)
|
|
-p, --port <port> Database server port (default: 5432)
|
|
-d, --dbname <db> Initial database to connect (default: postgres)
|
|
-U, --username <user> Database superuser (default: postgres)
|
|
--psql <path> Path to psql binary (default: psql)
|
|
--dry-run Show what would be done without executing
|
|
--check Only check dependencies, don't modify anything
|
|
--force Force drop even if role has active sessions
|
|
--timeout <seconds> Connection termination timeout (default: 10)
|
|
-v, --verbose Enable verbose output
|
|
--help Show this help message
|
|
|
|
EXAMPLES
|
|
# Check what would be affected (safe, read-only)
|
|
${PROG_NAME} olduser --check
|
|
|
|
# Dry run to see exact SQL statements
|
|
${PROG_NAME} olduser --dry-run -v
|
|
|
|
# Drop role 'olduser', transfer to 'postgres'
|
|
${PROG_NAME} olduser
|
|
|
|
# Drop role with custom successor
|
|
${PROG_NAME} olduser newowner
|
|
|
|
# Force drop with active sessions
|
|
${PROG_NAME} olduser --force
|
|
|
|
PROTECTED ROLES
|
|
The following roles cannot be dropped: ${PROTECTED_ROLES}
|
|
|
|
EXIT CODES
|
|
0 - Success
|
|
1 - Error (connection, permission, or operation failure)
|
|
2 - Role does not exist (not an error, just nothing to do)
|
|
|
|
EOF
|
|
exit 0
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Argument Parsing
|
|
#--------------------------------------------------------------#
|
|
function parse_args() {
|
|
while [[ $# -gt 0 ]]; do
|
|
case "$1" in
|
|
-s|--successor)
|
|
SUCCESSOR_ROLE="$2"
|
|
shift 2
|
|
;;
|
|
-h|--host)
|
|
PGHOST="$2"
|
|
shift 2
|
|
;;
|
|
-p|--port)
|
|
PGPORT="$2"
|
|
shift 2
|
|
;;
|
|
-d|--dbname)
|
|
PGDATABASE="$2"
|
|
shift 2
|
|
;;
|
|
-U|--username)
|
|
PGUSER="$2"
|
|
shift 2
|
|
;;
|
|
--psql)
|
|
PSQL="$2"
|
|
shift 2
|
|
;;
|
|
--dry-run)
|
|
DRY_RUN=true
|
|
shift
|
|
;;
|
|
--check)
|
|
CHECK_ONLY=true
|
|
shift
|
|
;;
|
|
--force)
|
|
FORCE=true
|
|
shift
|
|
;;
|
|
--timeout)
|
|
TERMINATION_TIMEOUT="$2"
|
|
shift 2
|
|
;;
|
|
-v|--verbose)
|
|
VERBOSE=true
|
|
shift
|
|
;;
|
|
--help)
|
|
usage
|
|
;;
|
|
-*)
|
|
log_error "Unknown option: $1"
|
|
usage
|
|
;;
|
|
*)
|
|
if [[ -z "${TARGET_ROLE}" ]]; then
|
|
TARGET_ROLE="$1"
|
|
elif [[ "${SUCCESSOR_ROLE}" == "postgres" ]]; then
|
|
SUCCESSOR_ROLE="$1"
|
|
fi
|
|
shift
|
|
;;
|
|
esac
|
|
done
|
|
|
|
# Validate required arguments
|
|
if [[ -z "${TARGET_ROLE}" ]]; then
|
|
log_error "Target role name is required"
|
|
usage
|
|
fi
|
|
|
|
# Initialize audit log path
|
|
AUDIT_LOG="/tmp/pg_drop_role_${TARGET_ROLE}_$(date +%Y%m%d_%H%M%S).log"
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Database Functions
|
|
#--------------------------------------------------------------#
|
|
|
|
# Escape single quotes in role name for safe SQL
|
|
function escape_sql() {
|
|
echo "${1//\'/\'\'}"
|
|
}
|
|
|
|
function psql_query() {
|
|
local sql="$1"
|
|
local db="${2:-${PGDATABASE}}"
|
|
${PSQL} -h "${PGHOST}" -p "${PGPORT}" -U "${PGUSER}" -d "${db}" -AXtqw -c "${sql}" 2>/dev/null
|
|
}
|
|
|
|
# Execute SQL with proper error handling
|
|
# Returns: 0 on success, non-zero on failure
|
|
# Outputs: error message on failure
|
|
function psql_exec() {
|
|
local sql="$1"
|
|
local db="${2:-${PGDATABASE}}"
|
|
|
|
if [[ "${DRY_RUN}" == "true" ]]; then
|
|
log_hint "[DRY-RUN] Would execute on '${db}':"
|
|
echo " ${sql}"
|
|
return 0
|
|
fi
|
|
|
|
log_debug "Executing on '${db}': ${sql}"
|
|
|
|
local output
|
|
local rc
|
|
output=$(${PSQL} -h "${PGHOST}" -p "${PGPORT}" -U "${PGUSER}" -d "${db}" -AXtqw -c "${sql}" 2>&1)
|
|
rc=$?
|
|
|
|
if [[ ${rc} -ne 0 ]]; then
|
|
echo "${output}"
|
|
fi
|
|
|
|
return ${rc}
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Audit Snapshot
|
|
#--------------------------------------------------------------#
|
|
function create_audit_snapshot() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
|
|
log_info "Creating audit snapshot: ${AUDIT_LOG}"
|
|
|
|
{
|
|
echo "========================================================"
|
|
echo "PostgreSQL Role Deletion Audit Log"
|
|
echo "========================================================"
|
|
echo "Timestamp : $(date -Iseconds)"
|
|
echo "Target Role : ${TARGET_ROLE}"
|
|
echo "Successor Role: ${SUCCESSOR_ROLE}"
|
|
echo "Server : ${PGHOST}:${PGPORT}"
|
|
echo "Dry Run : ${DRY_RUN}"
|
|
echo "Force : ${FORCE}"
|
|
echo ""
|
|
echo "=== Role Attributes ==="
|
|
psql_query "SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolconnlimit, rolvaliduntil FROM pg_roles WHERE rolname = '${escaped_role}'"
|
|
echo ""
|
|
echo "=== Owned Databases ==="
|
|
psql_query "SELECT datname FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')"
|
|
echo ""
|
|
echo "=== Owned Tablespaces ==="
|
|
psql_query "SELECT spcname FROM pg_tablespace WHERE spcowner = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')"
|
|
echo ""
|
|
echo "=== Group Memberships (member of) ==="
|
|
psql_query "SELECT r.rolname FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.roleid WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')"
|
|
echo ""
|
|
echo "=== Group Memberships (has members) ==="
|
|
psql_query "SELECT r.rolname FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.member WHERE m.roleid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')"
|
|
echo ""
|
|
echo "=== All Dependencies (pg_shdepend) ==="
|
|
psql_query "
|
|
SELECT COALESCE(d.datname, 'GLOBAL') AS database,
|
|
CASE s.deptype WHEN 'o' THEN 'OWNER' WHEN 'a' THEN 'ACL' WHEN 'r' THEN 'POLICY' ELSE s.deptype::text END AS dep_type,
|
|
s.classid::regclass AS catalog,
|
|
s.objid
|
|
FROM pg_shdepend s
|
|
LEFT JOIN pg_database d ON s.dbid = d.oid
|
|
WHERE s.refobjid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')
|
|
ORDER BY d.datname, s.deptype
|
|
"
|
|
echo ""
|
|
echo "=== Active Connections ==="
|
|
psql_query "SELECT pid, datname, application_name, client_addr, state, query_start FROM pg_stat_activity WHERE usename = '${escaped_role}'"
|
|
echo ""
|
|
echo "========================================================"
|
|
} > "${AUDIT_LOG}" 2>&1
|
|
|
|
log_info "Audit snapshot saved"
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Check Mode (Read-Only Analysis)
|
|
#--------------------------------------------------------------#
|
|
function run_check_mode() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
|
|
log_line "Dependency Analysis for '${TARGET_ROLE}'"
|
|
|
|
# Active connections
|
|
local active=$(psql_query "SELECT COUNT(*) FROM pg_stat_activity WHERE usename = '${escaped_role}'")
|
|
echo "Active connections : ${active}"
|
|
|
|
# Owned databases
|
|
local owned_dbs=$(psql_query "SELECT string_agg(datname, ', ') FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')")
|
|
echo "Owned databases : ${owned_dbs:-none}"
|
|
|
|
# Owned tablespaces
|
|
local owned_ts=$(psql_query "SELECT string_agg(spcname, ', ') FROM pg_tablespace WHERE spcowner = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')")
|
|
echo "Owned tablespaces : ${owned_ts:-none}"
|
|
|
|
# Member of groups
|
|
local member_of=$(psql_query "SELECT string_agg(r.rolname, ', ') FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.roleid WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')")
|
|
echo "Member of groups : ${member_of:-none}"
|
|
|
|
# Has members
|
|
local has_members=$(psql_query "SELECT string_agg(r.rolname, ', ') FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.member WHERE m.roleid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')")
|
|
echo "Has members : ${has_members:-none}"
|
|
|
|
echo ""
|
|
echo "Dependencies by Database:"
|
|
echo "-------------------------"
|
|
psql_query "
|
|
SELECT COALESCE(d.datname, 'GLOBAL') AS database,
|
|
COUNT(*) AS total,
|
|
SUM(CASE WHEN s.deptype = 'o' THEN 1 ELSE 0 END) AS owners,
|
|
SUM(CASE WHEN s.deptype = 'a' THEN 1 ELSE 0 END) AS acls,
|
|
SUM(CASE WHEN s.deptype = 'r' THEN 1 ELSE 0 END) AS policies
|
|
FROM pg_shdepend s
|
|
LEFT JOIN pg_database d ON s.dbid = d.oid
|
|
WHERE s.refobjid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')
|
|
GROUP BY d.datname
|
|
ORDER BY d.datname
|
|
" | column -t -s '|'
|
|
|
|
echo ""
|
|
local total_deps=$(psql_query "SELECT COUNT(*) FROM pg_shdepend WHERE refobjid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')")
|
|
echo "Total dependencies : ${total_deps}"
|
|
|
|
echo ""
|
|
log_info "Check complete. Use without --check to perform actual deletion."
|
|
log_hint "Use --dry-run to see exact SQL statements that would be executed."
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Pre-flight Checks
|
|
#--------------------------------------------------------------#
|
|
function check_connectivity() {
|
|
log_line "Pre-flight Checks"
|
|
|
|
# Test connection
|
|
if ! psql_query "SELECT 1" >/dev/null 2>&1; then
|
|
log_error "Cannot connect to PostgreSQL at ${PGHOST}:${PGPORT}"
|
|
exit 1
|
|
fi
|
|
log_info "Connected to PostgreSQL at ${PGHOST}:${PGPORT}"
|
|
|
|
# Check if running as superuser
|
|
local is_super=$(psql_query "SELECT current_setting('is_superuser')")
|
|
if [[ "${is_super}" != "on" ]]; then
|
|
log_error "Must run as superuser (current user: ${PGUSER})"
|
|
exit 1
|
|
fi
|
|
log_info "Running as superuser: ${PGUSER}"
|
|
}
|
|
|
|
function check_role_exists() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
local exists=$(psql_query "SELECT 1 FROM pg_roles WHERE rolname = '${escaped_role}'")
|
|
if [[ "${exists}" != "1" ]]; then
|
|
log_warn "Role '${TARGET_ROLE}' does not exist, nothing to do"
|
|
exit 2
|
|
fi
|
|
log_info "Target role '${TARGET_ROLE}' exists"
|
|
}
|
|
|
|
function check_protected_role() {
|
|
for protected in ${PROTECTED_ROLES}; do
|
|
if [[ "${TARGET_ROLE}" == "${protected}" ]]; then
|
|
log_error "Role '${TARGET_ROLE}' is protected and cannot be dropped"
|
|
exit 1
|
|
fi
|
|
done
|
|
log_info "Role '${TARGET_ROLE}' is not protected"
|
|
}
|
|
|
|
function check_successor_exists() {
|
|
local escaped_successor=$(escape_sql "${SUCCESSOR_ROLE}")
|
|
local exists=$(psql_query "SELECT 1 FROM pg_roles WHERE rolname = '${escaped_successor}'")
|
|
if [[ "${exists}" != "1" ]]; then
|
|
log_error "Successor role '${SUCCESSOR_ROLE}' does not exist"
|
|
exit 1
|
|
fi
|
|
log_info "Successor role '${SUCCESSOR_ROLE}' exists"
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Step 1: Disable Login & Terminate Connections
|
|
#--------------------------------------------------------------#
|
|
function disable_and_terminate() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
|
|
log_line "Step 1: Disable Login & Terminate Connections"
|
|
|
|
# Check active connections
|
|
local active_count=$(psql_query "SELECT COUNT(*) FROM pg_stat_activity WHERE usename = '${escaped_role}'")
|
|
log_info "Active connections for '${TARGET_ROLE}': ${active_count}"
|
|
|
|
# Disable login first to prevent new connections
|
|
log_info "Disabling login for '${TARGET_ROLE}'..."
|
|
local result
|
|
result=$(psql_exec "ALTER ROLE \"${TARGET_ROLE}\" NOLOGIN" 2>&1)
|
|
if [[ $? -ne 0 && "${DRY_RUN}" != "true" ]]; then
|
|
log_error "Failed to disable login: ${result}"
|
|
exit 1
|
|
fi
|
|
|
|
if [[ "${active_count}" -gt 0 ]]; then
|
|
if [[ "${FORCE}" != "true" && "${DRY_RUN}" != "true" ]]; then
|
|
log_warn "Role has ${active_count} active connection(s). Use --force to terminate."
|
|
log_hint "Or manually run: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = '${TARGET_ROLE}';"
|
|
exit 1
|
|
fi
|
|
|
|
log_warn "Terminating ${active_count} active connection(s)..."
|
|
psql_exec "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = '${escaped_role}' AND pid <> pg_backend_pid()"
|
|
|
|
# Wait for termination with timeout
|
|
if [[ "${DRY_RUN}" != "true" ]]; then
|
|
local attempt=0
|
|
while [[ ${attempt} -lt ${TERMINATION_TIMEOUT} ]]; do
|
|
sleep 1
|
|
active_count=$(psql_query "SELECT COUNT(*) FROM pg_stat_activity WHERE usename = '${escaped_role}'")
|
|
if [[ "${active_count}" -eq 0 ]]; then
|
|
log_info "All connections terminated"
|
|
return 0
|
|
fi
|
|
attempt=$((attempt + 1))
|
|
log_debug "Waiting for connections to terminate... (${active_count} remaining, attempt ${attempt}/${TERMINATION_TIMEOUT})"
|
|
done
|
|
|
|
log_error "Failed to terminate all connections after ${TERMINATION_TIMEOUT} seconds (${active_count} remaining)"
|
|
exit 1
|
|
fi
|
|
else
|
|
log_info "No active connections to terminate"
|
|
fi
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Step 2: Transfer Shared Objects (Databases & Tablespaces)
|
|
#--------------------------------------------------------------#
|
|
function transfer_shared_objects() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
local transfer_failed=false
|
|
|
|
log_line "Step 2: Transfer Shared Objects"
|
|
|
|
# Transfer databases ownership
|
|
local owned_dbs=$(psql_query "SELECT datname FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')")
|
|
if [[ -n "${owned_dbs}" ]]; then
|
|
log_warn "Role '${TARGET_ROLE}' owns the following database(s):"
|
|
for db in ${owned_dbs}; do
|
|
log_hint " - ${db}"
|
|
local result
|
|
result=$(psql_exec "ALTER DATABASE \"${db}\" OWNER TO \"${SUCCESSOR_ROLE}\"" 2>&1)
|
|
if [[ $? -ne 0 && "${DRY_RUN}" != "true" ]]; then
|
|
log_error "Failed to transfer database '${db}': ${result}"
|
|
transfer_failed=true
|
|
else
|
|
log_debug "Database '${db}' ownership transferred"
|
|
fi
|
|
done
|
|
else
|
|
log_info "No databases owned by '${TARGET_ROLE}'"
|
|
fi
|
|
|
|
# Transfer tablespaces ownership
|
|
local owned_ts=$(psql_query "SELECT spcname FROM pg_tablespace WHERE spcowner = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')")
|
|
if [[ -n "${owned_ts}" ]]; then
|
|
log_warn "Role '${TARGET_ROLE}' owns the following tablespace(s):"
|
|
for ts in ${owned_ts}; do
|
|
log_hint " - ${ts}"
|
|
local result
|
|
result=$(psql_exec "ALTER TABLESPACE \"${ts}\" OWNER TO \"${SUCCESSOR_ROLE}\"" 2>&1)
|
|
if [[ $? -ne 0 && "${DRY_RUN}" != "true" ]]; then
|
|
log_error "Failed to transfer tablespace '${ts}': ${result}"
|
|
transfer_failed=true
|
|
else
|
|
log_debug "Tablespace '${ts}' ownership transferred"
|
|
fi
|
|
done
|
|
else
|
|
log_info "No tablespaces owned by '${TARGET_ROLE}'"
|
|
fi
|
|
|
|
if [[ "${transfer_failed}" == "true" ]]; then
|
|
log_error "Some shared objects failed to transfer. Aborting to prevent data loss."
|
|
log_hint "Please fix the issues above and retry."
|
|
exit 1
|
|
fi
|
|
|
|
log_info "Shared objects transferred successfully"
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Step 3: Process Each Database (REASSIGN + DROP OWNED)
|
|
#--------------------------------------------------------------#
|
|
function process_all_databases() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
|
|
log_line "Step 3: Process All Databases"
|
|
|
|
# Get list of all databases (excluding templates, but including template1 for cleanup)
|
|
local databases=$(psql_query "SELECT datname FROM pg_database WHERE datallowconn = true ORDER BY datname")
|
|
|
|
if [[ -z "${databases}" ]]; then
|
|
log_error "No accessible databases found"
|
|
exit 1
|
|
fi
|
|
|
|
local db_count=$(echo "${databases}" | wc -l | tr -d ' ')
|
|
log_info "Processing ${db_count} database(s)..."
|
|
|
|
local current=0
|
|
local failed_dbs=""
|
|
|
|
for db in ${databases}; do
|
|
current=$((current + 1))
|
|
log_info "[${current}/${db_count}] Processing database: ${db}"
|
|
|
|
#----------------------------------------------------------#
|
|
# CRITICAL SAFETY: REASSIGN must succeed before DROP OWNED
|
|
#----------------------------------------------------------#
|
|
|
|
# Step 3a: REASSIGN OWNED
|
|
local reassign_result
|
|
reassign_result=$(psql_exec "REASSIGN OWNED BY \"${TARGET_ROLE}\" TO \"${SUCCESSOR_ROLE}\"" "${db}" 2>&1)
|
|
local reassign_rc=$?
|
|
|
|
if [[ "${DRY_RUN}" != "true" && ${reassign_rc} -ne 0 ]]; then
|
|
# Check if it's just "no objects" (not a real error)
|
|
if [[ "${reassign_result}" != *"ERROR"* ]]; then
|
|
log_debug "REASSIGN OWNED completed (no objects or warnings)"
|
|
else
|
|
log_error "REASSIGN OWNED failed in '${db}': ${reassign_result}"
|
|
log_warn "SKIPPING DROP OWNED for '${db}' to prevent data loss!"
|
|
failed_dbs="${failed_dbs} ${db}"
|
|
continue # CRITICAL: Skip DROP OWNED if REASSIGN failed
|
|
fi
|
|
fi
|
|
|
|
# Step 3b: DROP OWNED (only if REASSIGN succeeded)
|
|
local drop_result
|
|
drop_result=$(psql_exec "DROP OWNED BY \"${TARGET_ROLE}\"" "${db}" 2>&1)
|
|
local drop_rc=$?
|
|
|
|
if [[ "${DRY_RUN}" != "true" && ${drop_rc} -ne 0 ]]; then
|
|
if [[ "${drop_result}" != *"ERROR"* ]]; then
|
|
log_debug "DROP OWNED completed (no objects or warnings)"
|
|
else
|
|
log_error "DROP OWNED failed in '${db}': ${drop_result}"
|
|
failed_dbs="${failed_dbs} ${db}"
|
|
fi
|
|
fi
|
|
|
|
log_debug "Database '${db}' processed"
|
|
done
|
|
|
|
if [[ -n "${failed_dbs}" ]]; then
|
|
log_error "Failed to fully process some databases:${failed_dbs}"
|
|
log_hint "The role may not be droppable. Please manually check these databases."
|
|
log_hint "Audit log saved to: ${AUDIT_LOG}"
|
|
# Don't exit here - let drop_role() fail naturally with proper error message
|
|
fi
|
|
|
|
log_info "Database processing completed"
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Step 4: Revoke Group Memberships
|
|
#--------------------------------------------------------------#
|
|
function revoke_memberships() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
|
|
log_line "Step 4: Revoke Group Memberships"
|
|
|
|
# Roles that this role is a member of
|
|
local member_of=$(psql_query "
|
|
SELECT r.rolname
|
|
FROM pg_auth_members m
|
|
JOIN pg_roles r ON r.oid = m.roleid
|
|
WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')
|
|
")
|
|
|
|
if [[ -n "${member_of}" ]]; then
|
|
log_info "Revoking memberships from '${TARGET_ROLE}':"
|
|
for role in ${member_of}; do
|
|
log_hint " - REVOKE \"${role}\" FROM \"${TARGET_ROLE}\""
|
|
psql_exec "REVOKE \"${role}\" FROM \"${TARGET_ROLE}\""
|
|
done
|
|
else
|
|
log_info "No group memberships to revoke"
|
|
fi
|
|
|
|
# Roles that are members of this role
|
|
local has_members=$(psql_query "
|
|
SELECT r.rolname
|
|
FROM pg_auth_members m
|
|
JOIN pg_roles r ON r.oid = m.member
|
|
WHERE m.roleid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')
|
|
")
|
|
|
|
if [[ -n "${has_members}" ]]; then
|
|
log_info "Revoking '${TARGET_ROLE}' from its members:"
|
|
for role in ${has_members}; do
|
|
log_hint " - REVOKE \"${TARGET_ROLE}\" FROM \"${role}\""
|
|
psql_exec "REVOKE \"${TARGET_ROLE}\" FROM \"${role}\""
|
|
done
|
|
else
|
|
log_info "No members to revoke from"
|
|
fi
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Step 5: Final Drop Role
|
|
#--------------------------------------------------------------#
|
|
function drop_role() {
|
|
local escaped_role=$(escape_sql "${TARGET_ROLE}")
|
|
|
|
log_line "Step 5: Final Drop Role"
|
|
|
|
# Final verification: check for remaining dependencies
|
|
local remaining_deps=$(psql_query "
|
|
SELECT COUNT(*) FROM pg_shdepend
|
|
WHERE refobjid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')
|
|
")
|
|
|
|
if [[ "${remaining_deps}" -gt 0 && "${DRY_RUN}" != "true" ]]; then
|
|
log_warn "Role '${TARGET_ROLE}' still has ${remaining_deps} remaining dependencies"
|
|
|
|
if [[ "${VERBOSE}" == "true" ]]; then
|
|
log_hint "Remaining dependencies:"
|
|
psql_query "
|
|
SELECT COALESCE(d.datname, 'GLOBAL') AS database,
|
|
CASE s.deptype
|
|
WHEN 'o' THEN 'OWNER'
|
|
WHEN 'a' THEN 'ACL'
|
|
WHEN 'r' THEN 'POLICY'
|
|
ELSE s.deptype::text
|
|
END AS type,
|
|
s.classid::regclass AS catalog,
|
|
s.objid
|
|
FROM pg_shdepend s
|
|
LEFT JOIN pg_database d ON s.dbid = d.oid
|
|
WHERE s.refobjid = (SELECT oid FROM pg_roles WHERE rolname = '${escaped_role}')
|
|
" | sed 's/^/ /'
|
|
else
|
|
log_hint "Run with -v to see details"
|
|
fi
|
|
fi
|
|
|
|
log_info "Dropping role '${TARGET_ROLE}'..."
|
|
local result
|
|
result=$(psql_exec "DROP ROLE IF EXISTS \"${TARGET_ROLE}\"" 2>&1)
|
|
local rc=$?
|
|
|
|
# Verify deletion
|
|
if [[ "${DRY_RUN}" != "true" ]]; then
|
|
if [[ ${rc} -ne 0 ]]; then
|
|
log_error "DROP ROLE failed: ${result}"
|
|
log_hint "Check for remaining dependencies with:"
|
|
log_hint " SELECT * FROM pg_shdepend WHERE refobjid = (SELECT oid FROM pg_roles WHERE rolname = '${TARGET_ROLE}');"
|
|
log_hint "Audit log saved to: ${AUDIT_LOG}"
|
|
exit 1
|
|
fi
|
|
|
|
local still_exists=$(psql_query "SELECT 1 FROM pg_roles WHERE rolname = '${escaped_role}'")
|
|
if [[ "${still_exists}" == "1" ]]; then
|
|
log_error "Role '${TARGET_ROLE}' still exists after DROP ROLE"
|
|
log_hint "This should not happen. Please check PostgreSQL logs."
|
|
exit 1
|
|
fi
|
|
log_info "Role '${TARGET_ROLE}' has been successfully dropped"
|
|
else
|
|
log_info "[DRY-RUN] Role '${TARGET_ROLE}' would be dropped"
|
|
fi
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Summary Report
|
|
#--------------------------------------------------------------#
|
|
function print_summary() {
|
|
log_line "Summary"
|
|
|
|
if [[ "${DRY_RUN}" == "true" ]]; then
|
|
log_warn "DRY-RUN mode - no changes were made"
|
|
log_hint "Remove --dry-run to execute actual deletion"
|
|
else
|
|
log_info "Role '${TARGET_ROLE}' has been completely removed"
|
|
log_info "All objects transferred to '${SUCCESSOR_ROLE}'"
|
|
log_info "Audit log saved to: ${AUDIT_LOG}"
|
|
fi
|
|
|
|
echo ""
|
|
}
|
|
|
|
#--------------------------------------------------------------#
|
|
# Main Entry
|
|
#--------------------------------------------------------------#
|
|
function main() {
|
|
parse_args "$@"
|
|
|
|
echo ""
|
|
log_line "PostgreSQL Role Deletion Tool"
|
|
echo " Target Role : ${TARGET_ROLE}"
|
|
echo " Successor Role : ${SUCCESSOR_ROLE}"
|
|
echo " Server : ${PGHOST}:${PGPORT}"
|
|
echo " Dry Run : ${DRY_RUN}"
|
|
echo " Check Only : ${CHECK_ONLY}"
|
|
echo " Force : ${FORCE}"
|
|
|
|
check_connectivity
|
|
check_role_exists
|
|
check_protected_role
|
|
check_successor_exists
|
|
|
|
# Check mode - read only analysis
|
|
if [[ "${CHECK_ONLY}" == "true" ]]; then
|
|
run_check_mode
|
|
exit 0
|
|
fi
|
|
|
|
# Create audit snapshot before any modifications
|
|
create_audit_snapshot
|
|
|
|
disable_and_terminate
|
|
transfer_shared_objects
|
|
process_all_databases
|
|
revoke_memberships
|
|
drop_role
|
|
|
|
print_summary
|
|
}
|
|
|
|
main "$@"
|