Permission inconsistency with views that call functions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Permission inconsistency with views that call functions

David Wheeler-2

Hi all

 

I’m seeing some inconsistency with how permissions are enforced within views. In particular, if the view accesses a table directly, then the table is accessible, however if the view uses a function to access the table then permission is denied.

 

Here’s a demonstration (from pg13.0)

 

createdb temp

 

psql temp <<\EOF

create schema hidden;

 

create table hidden.tab (col1 text);

insert into hidden.tab values ('abc');

 

create function test() returns text[] as $$

  select array_agg(col1) from hidden.tab;

$$ language sql stable;

 

create view tv1 as select test();

create view tv2 as select array_agg(col1) from hidden.tab;

 

grant select on tv1 to public;

grant select on tv2 to public;

 

 

create user test password 'test' login;

EOF

 

 

 

PGPASSWORD=test psql -U test temp <<\EOF

\echo select * from tv1;

select * from tv1;

\echo ---------------

\echo select * from tv2;

select * from tv2;

set jit_inline_above_cost to -1;

\echo ---------------

\echo select * from tv1; -- no jit inlining

select * from tv1;

EOF

 

OUTPUT

 

ERROR:  permission denied for schema hidden

LINE 2:   select array_agg(col1) from hidden.tab;

                                      ^

QUERY:

  select array_agg(col1) from hidden.tab;

 

CONTEXT:  SQL function "test" during inlining

---------------

select * from tv2;

array_agg

-----------

{abc}

(1 row)

 

SET

---------------

select * from tv1; -- no jit inlining

ERROR:  permission denied for schema hidden

LINE 2:   select array_agg(col1) from hidden.tab;

                                      ^

QUERY:

  select array_agg(col1) from hidden.tab;

 

CONTEXT:  SQL function "test" during inlining

 

 

Is this expected/desirable? Any ideas how I can work around it, short of inlining every function manually (if that’s even possible) or granting access to the “hidden” schema?

 

Thanks in advance!

 

Best regards,

 

David Wheeler, Inomial Architect

E. [hidden email]

 

313 La Trobe Street, Victoria 3000, Australia

+61 3 8820 5200

 

MELBOURNE . DENPASAR . AUCKLAND

WWW.DGITSYSTEMS.COM

 

 

signature_414257395

 

Reply | Threaded
Open this post in threaded view
|

Re: Permission inconsistency with views that call functions

Joe Conway
On 2/22/21 10:32 PM, David Wheeler wrote:
> I’m seeing some inconsistency with how permissions are enforced within views. In
> particular, if the view accesses a table directly, then the table is accessible,
> however if the view uses a function to access the table then permission is denied.

Without looking too closely at the details, I can almost guarantee that the
issue is that FUNCTIONs default to "SECURITY INVOKER" whereas VIEWs operate as
if they were "SECURITY DEFINER". See slide 33 here:

http://joeconway.com/presentations/security-pgcon2020.pdf

The solution to your issue is possibly to make the function "SECURITY DEFINER".

I have mused previously (not sure if I ever did on the lists, but in any case)
that it would be cool if VIEWs could have the option to be either DEFINER or
INVOKER so that VIEWs and FUNCTIONs could be treated the same, but no efforts
have been made in that direction as far as I am aware.

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Permission inconsistency with views that call functions

David Wheeler-2

Thanks for the reply

 

> VIEWs operate as if they were "SECURITY DEFINER".

 

My concern is that the view is not acting as a security barrier underneath which all access is evaluated using the view owner context; in some circumstances (when the view calls a function) the access is evaluated using the caller’s context.

 

Caller

   |

   |

  V

- View --------------   Security definition layer 

   |

  |---- (b)

   |    |

(a)|    v

   |  Function (not SECURITY DEFINER or explicitly SECURITY INVOKER)

   |    |

   |    |

   |    |

  V    x

   Table

 

 

I would expect that everything underneath the View would use the view owner to evaluate permissions. However it seems that in scenario (b) it enters a new security context from the caller, rather than inheriting it from the view.

 

> The solution to your issue is possibly to make the function "SECURITY DEFINER".

 

This works, but I see this as a workaround, because the function is simply a utility that makes understanding the data in the table a little easier. Why should it be security definer? If you don’t have access to the table you shouldn’t be able to use the function to access it.

 

 

Regards,

 

David

 

On 24/2/21, 2:41 am, "Joe Conway" <[hidden email]> wrote:

On 2/22/21 10:32 PM, David Wheeler wrote:

> I’m seeing some inconsistency with how permissions are enforced within views. In

> particular, if the view accesses a table directly, then the table is accessible,

> however if the view uses a function to access the table then permission is denied.

 

Without looking too closely at the details, I can almost guarantee that the

issue is that FUNCTIONs default to "SECURITY INVOKER" whereas VIEWs operate as

if they were "SECURITY DEFINER". See slide 33 here:

 

 

The solution to your issue is possibly to make the function "SECURITY DEFINER".

 

I have mused previously (not sure if I ever did on the lists, but in any case)

that it would be cool if VIEWs could have the option to be either DEFINER or

INVOKER so that VIEWs and FUNCTIONs could be treated the same, but no efforts

have been made in that direction as far as I am aware.

 

HTH,

 

Joe

 

--

Crunchy Data - http://crunchydata.com

PostgreSQL Support for Secure Enterprises

Consulting, Training, & Open Source Development