Обсуждение: policies and extensions

I tried to define a policy within an extension but the policy does not
seem to belong to the extension.  Is this the way it is supposed to be?

This is postgres 9.5.21

Here is the relevant code from the extension:

    create table rls2 (
      username text not null,
      details  text not null
    );

    create policy only_owner on rls2
      for all
      to session_user
      using (username = user);

The table, as expected, depends on the extension but the policy does
not (determined by querying pg_depend).

Am I missing something special about policies or is this an oversight?

__
Marc

On 2/17/20 2:46 PM, Marc Munro wrote:
> I tried to define a policy within an extension but the policy does not
> seem to belong to the extension.  Is this the way it is supposed to be?
> 
> This is postgres 9.5.21
> 
> Here is the relevant code from the extension:
> 
>      create table rls2 (
>        username text not null,
>        details  text not null
>      );
> 
>      create policy only_owner on rls2
>        for all
>        to session_user
>        using (username = user);
> 
> The table, as expected, depends on the extension but the policy does
> not (determined by querying pg_depend).
> 
> Am I missing something special about policies or is this an oversight?

?:
https://www.postgresql.org/docs/9.5/sql-createpolicy.html

"The CREATE POLICY command defines a new row-level security policy for a 
table. Note that row-level security must be enabled on the table (using 
ALTER TABLE ... ENABLE ROW LEVEL SECURITY) in order for created policies 
to be applied."

> 
> __
> Marc
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Marc Munro <marc@bloodnok.com> writes:
> I tried to define a policy within an extension but the policy does not
> seem to belong to the extension.  Is this the way it is supposed to be?

Yeah, I would expect that.

https://www.postgresql.org/docs/current/extend-extensions.html

says:

    The kinds of SQL objects that can be members of an extension are shown
    in the description of ALTER EXTENSION.  ...
    Also notice that while a table can be a member of
    an extension, its subsidiary objects such as indexes are not directly
    considered members of the extension.  ...

An RLS policy is a table "subsidiary object" so it only depends indirectly
on the extension that owns the table.

            regards, tom lane

On Mon, 2020-02-17 at 22:48 -0500, Tom Lane wrote:
> Marc Munro <marc@bloodnok.com> writes:
> > 
> An RLS policy is a table "subsidiary object" so it only depends
> indirectly
> on the extension that owns the table.

Yep, I get that, and I see the dependency chain in the catalog. 

However an extension can create the table with or without the policy,
and a table created by an extension without policy can later have a
policy added, and, unless I'm missing something, the same dependency
chain exists in either case.

This means that I cannot tell whether the policy was added by the
extension or not.

I can see use cases where an extension writer might create an extension
with policies on tables, and others where a user might want to create
policies on tables from an extension provided by someone else.

Unfortunately, there is no way after the fact of determining which case
applies.

My use case is a tool that determines the state of a database for
performing diffs, etc.  It can generate ddl from database diffs to
create or alter tables, etc, and can also deal with policies and
extensions but will not be able to deal with policies created in
extensions, which is disappointing.

I can live with it though.  I'll document it as an oddity that the tool
is unable to deal with and generate commented ddl if the policy applies
to a table defined in an extension.

Thanks for the response.

__
Marc

Marc Munro <marc@bloodnok.com> writes:
> On Mon, 2020-02-17 at 22:48 -0500, Tom Lane wrote:
>> An RLS policy is a table "subsidiary object" so it only depends indirectly
>> on the extension that owns the table.

> Yep, I get that, and I see the dependency chain in the catalog. 

> However an extension can create the table with or without the policy,
> and a table created by an extension without policy can later have a
> policy added, and, unless I'm missing something, the same dependency
> chain exists in either case.

> This means that I cannot tell whether the policy was added by the
> extension or not.

I can't get very excited about that, since the same argument could be
made about triggers, indexes, or rules attached to a table, but no
one has yet complained about those cases.  It's fairly hard to see the
use-case where it matters, anyway.  If you're attaching policies to
tables owned by an extension after-the-fact, you're modifying the
definition of an extension-owned object, which is at best really poor
practice.  We say up-front that you cannot expect the extension
mechanism to track the effects of such changes.

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Marc Munro <marc@bloodnok.com> writes:
> > On Mon, 2020-02-17 at 22:48 -0500, Tom Lane wrote:
> >> An RLS policy is a table "subsidiary object" so it only depends indirectly
> >> on the extension that owns the table.
>
> > Yep, I get that, and I see the dependency chain in the catalog. 
>
> > However an extension can create the table with or without the policy,
> > and a table created by an extension without policy can later have a
> > policy added, and, unless I'm missing something, the same dependency
> > chain exists in either case.
>
> > This means that I cannot tell whether the policy was added by the
> > extension or not.
>
> I can't get very excited about that, since the same argument could be
> made about triggers, indexes, or rules attached to a table, but no
> one has yet complained about those cases.  It's fairly hard to see the
> use-case where it matters, anyway.  If you're attaching policies to
> tables owned by an extension after-the-fact, you're modifying the
> definition of an extension-owned object, which is at best really poor
> practice.  We say up-front that you cannot expect the extension
> mechanism to track the effects of such changes.

This isn't actually entirely correct because there are clear and
specific cases which we support where an extension object is modified,
in a manner of speaking, after the extension has been installed- and
that's through the GRANT system.  The entire point of pg_init_privs is
to provide a way to distinguish between what the installed extension's
original privileges were and what were added later, so that pg_dump can
correctly dump out the delta between the two.

Policies, also being part of the overall privilege system, could
certainly be looked at in a similar light as being different from
triggers and indexes.  In particular, anything to do with the privilege
system is going to have some dependency on roles and it's rather rare
for an extension to define roles- instead you'd expect the extension to
be installed in a 'safe' manner, with the user able to GRANT rights or
enable RLS and set up policies on objects from the extension after
installation to allow certain users to have access to the extension's
objects.

While it's not as nice a solution at the pg_init_privs system, I would
be inclined to state explicitly (and perhaps even enforce) that enabling
RLS or creating policies as part of an extension isn't supported, and
then modify pg_dump to always dump out those attributes of extension
objects if they exist.  When it comes to the GRANT system, we *must*
have a way for extensions to revoke access to certain types of objects
because they start out not-safe (specifically, functions), but as long
as we have that we can always tell extension authors and users that they
can arrange to have no one have access to the extension's tables by
default and they can then enable RLS and add policies after the
extension is installed.

Thanks,

Stephen

On Tue, 2020-02-18 at 15:06 -0500, Stephen Frost wrote:
> 
> Policies, also being part of the overall privilege system, could
> certainly be looked at in a similar light as being different from
> triggers and indexes...

While I think I agree with Stephen here, I don't have a vested interest
 in any particular solution and am not advocating for change.  

I am kinda surprised that policies are not explicitly tracked as part
of an extension but I can live with the status quo now that it has been
explained.  

I think it *may* be worth stating something explicitly in the
documentation but again I am not advocating.

Thanks again.

__
Marc