Skip to main content

user_data

The user_data template schema is used to delegate calls on behalf of a user. This is used to restrict access to user-specific data for end users.

This is not a native database schema and cannot be called using normal SQL syntax. We use double square bracket notation to call template schemas. Template functions in the user_data schema always return a set of data that can be further refined via normal SQL.

For instance, a user can execute the following query:

SELECT * FROM [[user_data.<schema_name>_<table_name>]] su WHERE su.rule_id = '<rule id>'

Internal Only​

To support a new user data table the following steps are required:

  • Create (or have in mind) an underlying table that an end user does not have access to.
  • Add a function to the user_data_access schema.
    • The function must have two parameters: organization_id and user_id.
    • The function must return a SET of data from an underlying table.
    • The function must have SECURITY DEFINER to allow end users access to the underlying data.
    • The function should be named <underlying_table_schema>_<underlying_table_name>
    • The function should project against the organization_id and/or user_id.