DO $$ 
DECLARE
    username text := 'new_user';          -- Variable for the username
    user_password text := 'new_password'; -- Variable for the user's password
    db_name text := 'new_database';       -- Variable for the database name
BEGIN
    -- Step 1: Create the database
    EXECUTE format('CREATE DATABASE %I;', db_name);

    -- Step 2: Create the user with the password
    EXECUTE format('CREATE USER %I WITH PASSWORD %L;', username, user_password);

    -- Step 3: Connect to the created database
    -- In this case, we can't connect from within the same query, but you can use this after running the block:
    -- \c new_database;

    -- Step 4: Grant the new user all privileges on the created database
    EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I;', db_name, username);

    -- Step 5: Grant the new user full privileges on the public schema of the created database
    EXECUTE format('GRANT ALL PRIVILEGES ON SCHEMA public TO %I;', username);
    EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %I;', username);
    EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %I;', username);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO %I;', username);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO %I;', username);

    -- Optionally, raise a notice to confirm completion
    RAISE NOTICE 'Database % created, user % granted privileges.', db_name, username;
END $$;
