Allow whitelisted dynamic defaults

[dmos62]

Problem

We currently don't support adding (or redefining) column defaults that are dynamic. Also, currently we're not set up to provide arbitrary dynamic defaults without compromising security of Mathesar.

Proposed solution

We can allow setting dynamic defaults for a column, without compromising security, by restricting the possible dynamic defaults to a whitelist. How a whitelist could be compiled was discussed here: we decided that a manually compiled whitelist is the most practical option.

Additional context

Dynamic defaults feature discussion thread: #2272
User's request for dynamic defaults: #2754
Discussion on how to generate the whitelist: #3078

Tasks

Incomplete list of tasks see posts in this thread for an adhoc articulation of what a full solution would involve.

• Generate function list for whitelist-based dynamic defaults #3078

[dmos62]

Currently our Postgres library (which implements column alterations, including setting of defaults), does not allow setting dynamic defaults.

Postgres-side quoting

If for example, we request that the default for a datetime column be set to now(), the Postgres library quotes it as a literal and the resulting subexpression is SET DEFAULT 'now()', where 'now()' is a string, which is cast to datetime at the time of default definition and thus turned into static default.

If the format expression in the linked code is changed from '%L' to '%s', then the resulting SQL subexpression is SET DEFAULT now() (notice lack of single quotes around now()), which does define a dynamic default.

default['is_dynamic']

However, if one wants to enter a literal value then, it has to be explicitly quoted before submitting to the Postgres library, analogously to how you'd quote literals in SQL, otherwise it is interpreted as a symbol or expression.

To workaround that, we need to specify whether the requested default value is meant to be dynamic or not. API accounts for this already by having the column default be an object with the keys value and is_dynamic. The is_dynamic is ignored in the rest of the column alteration request, currently.

Whitelist location

There are two places we can define the whitelist and thus perform checking of whether the dynamic default is acceptable:

• Django
  • leaves the Postgres library capable of defining arbitrary dynamic defaults
    • how does this impact security?
  • seems simpler to implement
• our Postgres library
  • seems more secure

I'm leaning towards choosing whichever is simpler to implement.

[dmos62]

Synced with @mathemancer over this. Here's the insights:

• where we keep/enforce the whitelist doesn't matter that much
• work needed is somewhat involved
• we have a raw_default flag in the Postgres library
  • we use it for copying columns with dynamic defaults
  • we'll probably do best to use that
  • however, it needs refactoring
    • it's not exposed to our Postgres library's API
    • it's also "global"
      • in that when set to true
        • every column that's being altered (in that batch) has its defaults interpreted without quoting/literalizing
        • need to make it per-column
• a lot of our code presumes that a default being dynamic means that we'll not touch it
  • that will need to be changed
• frontend will likely need to know
  • not only what the dynamic default whitelist is
  • but also which whitelisted functions apply to which column types
• algo for checking if a given function is applicable as dynamic default for a column
  • suppose the function is now()
    • it outputs timestamptz
    • i want to know if i can use it as default for date column
      • i look up functions that will automatically silently cast to date
        • not sure how to check if a function is an automatic silent casting function
          • needs to be investigated
        • check if there's one that casts timestamptz to date
  • or bruteforce
    • create a table with every column type
      • give it dynamic default now()
        • see to which columns it's applicable
• note, there are casts that can be performed explicitly, but not implicitly