Create Privileged User in AWS Postgres

user.sql

# In AWS you can't make a superuser so you need to create users with all privliges sometimes.
# Connect to each db then run this script

DO $$
DECLARE
    target_user text := '<replace me>';
    target_password text := '<replace me>';
    r record;
    current_db text := current_database();
BEGIN
    -- Create the role if it doesn't already exist.
    IF EXISTS (
        SELECT 1 FROM pg_catalog.pg_roles
        WHERE rolname = target_user
    ) THEN
        RAISE NOTICE 'Role "%" already exists. Skipping.', target_user;
    ELSE
        EXECUTE format('CREATE USER %I WITH PASSWORD %L INHERIT', target_user, target_password);
    END IF;

    -- Grant CONNECT on the current database.
    EXECUTE format('GRANT CONNECT ON DATABASE %I TO %I', current_db, target_user);

    -- Loop over non‑system schemas and grant privileges on each schema and its objects.
    FOR r IN 
        SELECT schema_name 
        FROM information_schema.schemata 
        WHERE schema_name NOT IN ('pg_catalog', 'information_schema')
    LOOP
        RAISE NOTICE 'Granting privileges on schema: %', r.schema_name;
        EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', r.schema_name, target_user);
        EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO %I', r.schema_name, target_user);
        EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I TO %I', r.schema_name, target_user);
        EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %I TO %I', r.schema_name, target_user);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %I', r.schema_name, target_user);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON SEQUENCES TO %I', r.schema_name, target_user);
        EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT EXECUTE ON FUNCTIONS TO %I', r.schema_name, target_user);
    END LOOP;
END
$$;