Greetings,
* Amul Sul (sulamul@gmail.com) wrote:
> I have a ROW LEVEL SECURITY policy on the table part of an extension, and
> while
> dumping the database where that extension is installed, dumps the policy of
> that table too even though not dumpling that table .
>
> Here is quick tests, where I have added following SQL to adminpack--1.0.sql
> extension file:
>
> CREATE TABLE public.foo (i int CHECK(i > 0));
> ALTER TABLE public.foo ENABLE ROW LEVEL SECURITY;
> CREATE POLICY foo_policy ON public.foo USING (true);
>
> After installation and creation of this extension, the dump output will have
> policy without that table:
>
> --
> -- Name: foo; Type: ROW SECURITY; Schema: public; Owner: amul
> --
>
> ALTER TABLE public.foo ENABLE ROW LEVEL SECURITY;
>
> --
> -- Name: foo foo_policy; Type: POLICY; Schema: public; Owner: amul
> --
>
> CREATE POLICY foo_policy ON public.foo USING (true);
>
>
> I am not sure if that is expected behaviour. The code comment in
> checkExtensionMembership() seems to be doing intentionally:
>
> * In 9.6 and above, mark the member object to have any non-initial ACL,
> * policies, and security labels dumped.
>
> The question is why were we doing this? Shouldn't skip this policy if it is
> part of the create-extension script?
>
> Also, If you try to drop this policy, get dropped without any warning/error
> unlike tables or other objects which are not allowed to drop at all.
At least at the time, it wasn't really envisioned that policies would be
part of an extension's creation script. That was probably short-sighted
and it'd be better if we handled that cleanly, but to do so we'd need
something akin to pg_init_privs where we track what policies are created
as part of the creation script vs. what are created afterwards and then
dump out the post-installation policy changes (note that we'd need to
track if any installation-time policies were dropped or changed too...)
as part of the pg_dump.
It'd be helpful if you could maybe provide some use-cases around this?
Permission changes such as those handled by pg_init_privs are a bit more
understandable since an extension script might want to revoke access
from PUBLIC for functions or to GRANT access to PUBLIC for other things,
or to leverage predefined roles, but how does that work for policies?
Most extensions aren't likely to be creating their own roles or
depending on non-predefined roles to already exist in the system as
otherwise they'd end up failing to install if those roles didn't
exist...
Thanks,
Stephen