When are quotes needed in .env file?

TL;DR solution: Use the quotation marks when the string contains spaces or certain special character and certain syntaxes. These include:

• space and other whitespace,

• backslash (escapes a space and newline – \ gives space even in unquoted string),

• quotation marks (but you can combine multiple quotation mark\ 'styles like'"this"),

• pound sign (#) that marks start of comment (if it is not in quotes string or $(…)),

• dollar sign (that is used to expand a variable – see below),

• parentheses (( and )) – depending on context,

• shell redirection chars (>, <, 2>, | etc.),

• asterisk (*) and question mark (?), since it is used in globs,

• square brackets (because they list characters),

• comma-separated text in {…} (because it provides multiple variants of text – {foo,bar}baz expands to foobaz barbaz),

• maybe others,

• and of course, the newline.

.env files are, according to the page linked by you, regular bash scripts. That means that:

• One string can't contain multiple words (space-separated parts), unless it is enclosed in quotes.¹

  FOO_VAR='multiple words'    # This works.
  ANOTHER_VAR="foo bar"       # This works, too.
  
  BAR_VAR=this does not work  # Executes “does” with args
                              # “not work” and variable
                              # BAR_VAR=“this”.
  

• Variable expansion is performed if the text is enclosed in double quotes or it is not enclosed in any quotes.

  my_var=42
  VARIABLES="foo ${my_var}"  # Gives “foo 42”.
  

• Shell commands can be executed to produce the strings.²

  CURRENT_DATE="$(date)"  # Executes “date” and uses its
                          # stdout as the value.
  DO_NOT_DO_THIS=$(date)  # First expands the command, and we
                          # then get multiple unquoted words.
  AVOID_THIS="`date`"  # Non-standard syntax, accepted by bash.
  

¹ In the other case, it will run the second and all later “words” as a shell command with the given variable. Refer to bash(1) manpage.

² According to the docs, it is not supported on Microsoft Windows. It does not say anything about variable expansion syntax on Windows.