Create new postgres db user with ownership on database and objects

sh

#!/bin/bash

read -p "Enter database super user: " PG_USER

read -p "Enter database host: " PG_HOST

read -p "Enter NEW database user: " DB_USER
read -s -p "Enter password for '$DB_USER': " DB_PASS
echo
read -p "Enter TARGET database name: " DB_NAME

echo "Creating user '$DB_USER' if not exists..."

# Create user if not exists
psql -U "$PG_USER" -h "$PG_HOST" -d postgres <<EOF
DO \$\$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${DB_USER}') THEN
      CREATE USER ${DB_USER} WITH PASSWORD '${DB_PASS}';
   END IF;
END
\$\$;
EOF

echo "Changing ownership of database '$DB_NAME'..."
psql -U "$PG_USER" -h "$PG_HOST" -d postgres -c "ALTER DATABASE \"${DB_NAME}\" OWNER TO ${DB_USER};"

echo "Granting privileges on schema & future objects..."
psql -U "$PG_USER" -h "$PG_HOST" -d "$DB_NAME" <<EOF
GRANT CONNECT, TEMPORARY ON DATABASE "$DB_NAME" TO $DB_USER;
GRANT USAGE, CREATE ON SCHEMA public TO $DB_USER;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $DB_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $DB_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO $DB_USER;
EOF

echo "Transferring ownership of existing objects using fast \gexec..."

psql -U "$PG_USER" -h "$PG_HOST" -d "$DB_NAME" <<EOF
-- Tables
SELECT 'ALTER TABLE ' || quote_ident(schemaname) || '.' || quote_ident(tablename) 
       || ' OWNER TO ${DB_USER};'
FROM pg_tables
WHERE schemaname = 'public'
\gexec

-- Sequences
SELECT 'ALTER SEQUENCE ' || quote_ident(schemaname) || '.' || quote_ident(sequencename)
       || ' OWNER TO ${DB_USER};'
FROM pg_sequences
WHERE schemaname = 'public'
\gexec

-- Functions
SELECT 'ALTER FUNCTION ' || n.nspname || '.' || p.proname || '(' 
       || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ${DB_USER};'
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
\gexec
EOF

echo "✅ Done: '$DB_USER' fully owns '$DB_NAME' and all its objects."