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
Description: Digital signature