False positive RF01 on CREATE POLICY

[gwax]

Search before asking

• [X] I searched the issues and found no similar issues.

What Happened

Postgresql CREATE POLICY with an EXISTS fails to recognize the policy targeted table as being part of the query.

Take, for example a policy creation statement of the form:

CREATE POLICY seller_can_read_buyer_address ON addresses
FOR SELECT to authenticated
USING (
  EXISTS (
    SELECT 1
    FROM orders
    WHERE
      orders.shipping_address_id = addresses.id
      AND orders.seller_id = auth.uid()
  )
)

sqlfluff reports an error akin to following error:

L:  11 | P:  36 | RF01 | Reference 'addresses.id' refers to table/view not
                       | found in the FROM clause or found in ancestor statement.
                       | [references.from]

Expected Behaviour

Expect to see no errors

Observed Behaviour

sqlfluff reports an RF01 error

How to reproduce

CREATE POLICY seller_can_read_buyer_address ON addresses
FOR SELECT to authenticated
USING (
  EXISTS (
    SELECT 1
    FROM orders
    WHERE
      orders.shipping_address_id = addresses.id
      AND orders.seller_id = auth.uid()
  )
)

Dialect

postgres

Version

==== sqlfluff ====
sqlfluff:                2.3.2 python:                 3.11.1
implementation:        cpython verbosity:                   1
dialect:              postgres templater:               jinja
rules:                                all

Configuration

[sqlfluff]
dialect = postgres
encoding = utf-8
max_line_length = 99
warnings = AM04

[sqlfluff:indentation]
indent_unit = tab
tab_space_size = 4
indented_joins = True
indented_ctes = False

[sqlfluff:rules:capitalisation.keywords]
# Keywords
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.identifiers]
# Unquoted identifiers
extended_capitalisation_policy = lower


[sqlfluff:rules:capitalisation.literals]
# Null & Boolean Literals
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.types]
# Data Types
extended_capitalisation_policy = upper

[sqlfluff:rules:convention.casting_style]
# SQL type casting
preferred_type_casting_style = shorthand

[sqlfluff:rules:convention.count_rows]
# Consistent syntax to count all rows
prefer_count_1 = True

[sqlfluff:rules:references.keywords]
# Keywords should not be used as identifiers.
unquoted_identifiers_policy = aliases
quoted_identifiers_policy = none
# Comma separated list of words to ignore for this rule
ignore_words = None
ignore_words_regex = None

[sqlfluff:rules:references.special_chars]
# Special characters in identifiers
unquoted_identifiers_policy = all
quoted_identifiers_policy = all
allow_space_in_identifier = True
additional_allowed_characters = '.-'
ignore_words = None
ignore_words_regex = None

Are you willing to work on and submit a PR to address the issue?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct