What you're looking for is a technique called masking (wikipedia doesn't have a page for digital masking :/). You can easily achieve this using shaders, I'll guide you through adding the shader I created for you, but I highly recommend you read Godot's documentation on shaders:
First, create a ShaderMaterial:

Then, create a Shader:

Clicking the newly created Shader will open it in the bottom window (press Shift
+F12
to maximize it). Then you can paste my shader code (again, make sure to read the documentation, so you understand what's going on):
shadertype canvasitem;
uniform sampler2D paper_texture;
uniform sampler2D paper_normal;
uniform float paper_influence: hint_range(0.0, 1.0) = 0.5;
void fragment() {
vec4 original_color = texture(TEXTURE, UV);
COLOR = mix(original_color, vec4(texture(paper_texture, SCREEN_UV).rgb, original_color.a), paper_influence);
NORMALMAP = mix(NORMAL, texture(paper_normal, SCREEN_UV).xyz, paper_influence);
}
You can now adjust the settings:

A description of the settings:
- paper_texture: The overlay texture.
- paper_normal: Behaves like a sprite's normal map, you can leave it empty.
- paper_influence: How much of the overlay is visible, 0 is only the sprite's texture, 1 is only the overlay texture, anything in between is a mix of both.
Demo
I used a brick texture as it is much easier to see, also, please excuse the terrible gif quality, the noise is from ffmpeg throwing a fit.
