Thom,
* Thom Brown (t...@linux.com) wrote:
> I find it a bit of a limitation that I can't specify both INSERT and
> UPDATE for a policy. I'd want to be able to specify something like
> this:
>
> CREATE POLICY no_greys_allowed
> ON colours
> FOR INSERT, UPDATE
> WITH CHECK (name NOT IN ('grey','gray'));
>
> I would expect this to be rather common to prevent certain values
> making their way into a table. Instead I'd have to create 2 policies
> as it stands.
That's not actually the case...
CREATE POLICY no_greys_allowed
ON colours
FOR ALL
USING (true) -- assuming this is what you intended
WITH CHECK (name NOT IN ('grey','gray'));
Right? That said, I'm not against the idea of supporting mulitple
commands with one policy (similar to how ALL is done). It wouldn't be
difficult or much of a change- make the 'cmd' a bitfield instead. If
others feel the same then I'll look at doing that.
> In order to debug issues with accessing table data, perhaps it would
> be useful to output the name of the policy that was violated. If a
> table had 20 policies on, it could become time-consuming to debug.
Good point. That'll involve a bit more as I'll need to look at the
existing with check options structure, but I believe it's just adding
the field to the structure, populating it when adding the WCO entries,
and then checking for it in the ereport() call. The policy name is
already stashed in the relcache entry, so it's already pretty easily
available.
> I keep getting tripped up by overlapping policies. On the one hand, I
> created a policy to ensure rows being added or selected have a
> "visible" column set to true. On the other hand, I have a policy that
> ensures that the name of a colour doesn't appear in a list. Policy 1
> is violated until policy 2 is added:
>
> (using the table I created in a previous post on this thread...)
>
> # create policy must_be_visible ON colours for all to joe using
> (visible = true) with check (visible = true);
> CREATE POLICY
>
> \c - joe
>
> > insert into colours (name, visible) values ('pink',false);
> ERROR: new row violates WITH CHECK OPTION for "colours"
> DETAIL: Failing row contains (28, pink, f).
>
> \c - thom
>
> # create policy no_greys_allowed on colours for insert with check
> (name not in ('grey','gray'));
> CREATE POLICY
>
> \c - joe
>
> # insert into colours (name, visible) values ('pink',false);
> INSERT 0 1
>
> I expected this to still trigger an error due to the first policy. Am
> I to infer from this that the policy model is permissive rather than
> restrictive?
That's correct and I believe pretty clear in the documentation- policies
are OR'd together, just the same as how roles are handled. As a
logged-in user, you have the rights of all of the roles you are a member
of (subject to inheiritance rules, of course), and similairly, you are
able to view and add all rows which match any policy which applies to
you (either through role membership or through different policies).
> I've also attached a few corrections for the docs.
Thanks! I'll plan to include these with a few other typos and the fix
for the bug that Andres pointed out, once I finish testing (and doing
another CLOBBER_CACHE_ALWAYS run..).
Thanks again,
Stephen
signature.asc
Description: Digital signature
