Create/drop policies

[jaulz]

What do you think about adding support for policies and row level security?

[jaulz]

I just came up with a macro in case someone else needs it:

    Blueprint::macro('policy', function (
      string $policy,
      array $options = [],
    ) {
      /** @var \Illuminate\Database\Schema\Blueprint $this */
      $prefix = $this->prefix;
      $table = $this->table;

      $command = $this->addCommand(
        'policy',
        compact('prefix', 'table', 'policy', 'options')
      );
    });

    PostgresGrammar::macro('compilePolicy', function (
      Blueprint $blueprint,
      Fluent $command
    ) {
      /** @var \Illuminate\Database\Schema\Grammars\PostgresGrammar $this */
      $prefix = $command->prefix;
      $table = $command->table;
      $policy = $command->policy;
      $options = $command->options;

      $modifiers = [];
      foreach ($options as $key => $value) {
          $modifiers[] = match (true) {
              'as' === $key => "as {$value}",
              'for' === $key => "for {$value}",
              'to' === $key => "to {$value}",
              'using' === $key => "using ({$value})",
              'check' === $key => "with check ({$value})",
              default => throw_if(true, message: "Unknown option '{$key}'."),
          };
      }
      $sqlModifiers = implode(' ', $modifiers);

      return [
        sprintf('CREATE POLICY %s ON %s%s %s', $policy, $prefix, $table, $sqlModifiers),
      ];
    });

And then similar for drop:

    Blueprint::macro('dropPolicy', function (
      string $policy,
    ) {
      /** @var \Illuminate\Database\Schema\Blueprint $this */
      $prefix = $this->prefix;
      $table = $this->table;

      $command = $this->addCommand(
        'dropPolicy',
        compact('prefix', 'table', 'policy')
      );
    });

    PostgresGrammar::macro('compileDropPolicy', function (
      Blueprint $blueprint,
      Fluent $command
    ) {
      /** @var \Illuminate\Database\Schema\Grammars\PostgresGrammar $this */
      $prefix = $command->prefix;
      $table = $command->table;
      $policy = $command->policy;

      return [
        sprintf('DROP POLICY IF EXISTS %s ON %s%s', $policy, $prefix, $table),
      ];
    });

Maybe we can incorporate it in this library as well 😊

[tpetry]

I am planning to support RLS, but I don't know yet what the API will look like. I am unsure whether I want to make it look like the SQL syntax or some higher-level API. Also working with RLS within Laravel is not that easy so I am thinking about whether some helpers need to be added to make development easier - and make it a risk-free approach.