How do we know if the correlation is significant? Suppose that we have continuous data $(X_1,Y_1),\dots,(X_n,Y_n)$. Suppose that $r_{x,y}$ is the Karl-Pearson correlation coefficient between $X_i$'s and $Y_i$'s. For what range of values of $r_{x,y}$, can we really decide that there may indeed be a linear relationship between $X_i$'s and $Y_i$' and proceed to predict $Y$ by using a linear regression?
I'm sure the topic concerning this question should be a well-studied one. I did a little search here; couldn't find relevant posts. Any answers to the above question or pointers to such a study is greatly appreciated.
 A: Often the term "significance" is used in the meaning "$\rho$ is statistically significantly different from zero". This is, however, not what most users of $\rho$ are interested in, because the null hypothesis that $\rho$ is exactly zero is almost certainly false. Hence even the tiniest deviation from zero becomes "significant" for a sample size that is large enough.
It is generally of more interest whether a correlation is strong. What is considered a "strong" correlation depends on the field, but here is a rule of thumb taken from an introductory textbook (here is an online reference for the same rule):
\begin{eqnarray*}
|\rho|\leq 0.3: & & \mbox{weak correlation}\\
0.3 < |\rho|\leq 0.7: & & \mbox{moderate correlation}\\
|\rho|> 0.7: & & \mbox{strong correlation}\\
\end{eqnarray*}
I would thus suggest, not to do a hypothesis test against $\rho=0$, but to report a confidence interval for $\rho$. You can find the formulas, e.g., here, and most statistical packages provide functions that compute it for you, for example cor.test in R. Then you can see how far this interval overlaps with the "weak" range.
A: There is a difference between a well-evidenced effect and a strong effect. For example, there is good evidence that eating bacon causes cancer, but the risk is low; and there is weak evidence that smoking marijuana leaf causes cancer, but the risk is probably high. (The reason for the gap is that the bacon eaters are subject to more medical surveillance than ganja smokers.)
So a useful statistical test of whether the correlation is well evidenced is not based on the correlation coefficient, but on the sample size.
Another feature of the situation that matters is how much of the variation is explained by the correlation: this is the R-squared statistic, coefficient of determination.
A: You could use the following test to check whether there is significant correlation between $X$ and $Y$. Assume that you have the observations $(x_i,y_i), i =1,\dots,n$.
The null and alternative hypothesis are given by:
$$
H_0: \, \rho = 0 \quad vs. \quad H_1: \rho \neq 0
$$
The test statistic is given by:
$$
T = \sqrt{n-2}\frac{\hat{\rho}}{\sqrt{1-\hat{\rho}^2}}\overset{H_0}{\sim} t_{n-2}
$$
Where $\hat{\rho}$ is the sample estimate for the correlation coefficient, i.e.
$$
\hat{\rho}=\frac{\frac{1}{n}\sum_{i=1}^n((x_i-\bar{x})(y_i-\bar{y}))}{\sqrt{\frac{1}{n}\sum_{i=1}^n(x_i-\bar{x})^2} \cdot \sqrt{\frac{1}{n}\sum_{i=1}^n(y_i-\bar{y})^2} }
$$
Thus, the null is rejected if $\vert T\vert >t_{n-2;1-\frac{\alpha}{2}}$.
A: All you need is to compute the degree of freedom of the system, which is the number of participants minus $2$, and then refer to the table of critical values for $r$, which can be found here.
However, this gives you nothing but the mathematical significance, you still need to have a look at the scatterplot to see if a linear relationship really is a good guess or not.
A: 
For what range of values of rx,y, can we [...] proceed to predict Y by using a linear regression?

If the relationship is indeed linear, any value of correlation can work; linear regression behaves as it should across the entire range of correlations, including 0. You don't even need to examine the correlation beforehand (it seems to serve no purpose not already covered by the usual regression calculations).
However, that's a big if. You can get any correlation (except exactly 1 or -1) and not have linearity; a large (magnitude of) correlation doesn't necessarily imply the relationship is actually linear (nor does a small one imply that it isn't); correlation is not of itself a useful way to decide on the suitability of a linear regression model.
In the case of multiple regression, examining bivariate correlations is even more problematic, since the marginal bivariate correlations may be quite different from what you get in a multiple regression model. (See the Wikipedia articles on Simpson's paradox and omitted variable bias, for example.)
However, if you're interested in whether the regression is doing something useful in terms of prediction, we'd need to pin down precisely what is intended by "useful". In some cases that might be attributable to correlation values.
On the other hand, if you're instead asking "how do we perform a hypothesis test of a Pearson correlation?" you should probably edit the question to make that explicit. Under suitable assumptions you get a "standard" test readily available in packages - or fairly easily carried out by hand. [However, you're not limited to those specific assumptions, other tests of a Pearson correlation - including nonparametric tests - are possible.]
A: Like hypothesis testing in general, all you can do is propose a null hypothesis and calculate the probability of seeing the data given that null hypothesis. There is no point at which the data "definitely" comes from correlated sources, only some line in the sand where you decide that the data is "unlikely enough" to reject the null.
If you want to know how to calculate the p-value, you need to know the correlation coefficient and degrees of freedom (which is number of data points minus two). The formula generally given is $p = \frac{r \sqrt{n-2}}{1-r^2}$. There are many online calculators that give you $p$ given $n$ and $r$.
However, this formula is for the null hypothesis that the data is coming from normal IID. Just because this null is rejected, does not mean that there isn't some other hypothesis that doesn't involve correlation between $X$ and $Y$; if there is correlation within $X$ and $Y$, rather than between them, that increases the probability of seeing large sample correlation.
