PostgreSQL function 会遗传security 属性吗?
背景
PostgreSQL function 会遗传security 属性吗?答案是不遗传。
在参数中的函数,只管自己的security属性,不管外面函数的security属性。也就是各管各的。
当函数体中包含其他函数时,两个函数的security属性都会被判断,并且是一个与的关系。或者可以理解为整个调用栈的所有函数中只要有definer的权限的,都以definer来处理。
分两种情况来验证一下:
1. 参数遗传,不遗传。
我们要用到一个invoker属性的abs函数,作为外部函数。
postgres=# select proname,prosecdef from pg_proc where proname='abs';
proname | prosecdef
---------+-----------
abs | f
abs | f
abs | f
abs | f
abs | f
abs | f
(6 rows)
创建一个普通用户的函数,定义为definer属性。
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create or replace function ff1() returns int as $$
declare
begin
drop table t3;
return 1;
end;
$$ language plpgsql security definer;
CREATE FUNCTION
在超级用户下,调用abs(ff1()),报权限不足,说明函数ff1()作为abs的参数,没有遗传abs的invoker属性。
各自判断。
这点是安全的。
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select abs(ff1());
ERROR: must be owner of relation t3
CONTEXT: SQL statement "drop table t3"
PL/pgSQL function ff1() line 4 at SQL statement
反过来,
postgres=# alter function abs(int) owner to digoal;
ALTER FUNCTION
postgres=# alter function abs(int) security definer;
ALTER FUNCTION
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create or replace function ff1() returns int as $$
declare
begin
drop table t3;
return 1;
end;
$$ language plpgsql security invoker;
CREATE FUNCTION
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select abs((ff1()::int));
abs
-----
1
(1 row)
在参数中的函数,只管自己的security属性,不管外面函数的security属性。
2. 函数体遗传,不遗传。
我们在普通用户下定义两个函数,一个为invoker属性,一个为definer属性。
在invoker属性的函数中调用definer属性的函数,看看内部函数会不会遗传外部函数的security属性?
结果说明,两个函数的security属性好像都判断了,并且是一个与的关系。或者可以理解为整个调用栈的所有函数中只要有definer的权限的,都以definer来处理。
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create or replace function ff2() returns int as $$
declare
begin
drop table t3;
end;
$$ language plpgsql security invoker;
CREATE FUNCTION
postgres=> create or replace function ff1() returns int as $$
declare
begin
perform ff2();
end;
$$ language plpgsql security definer;
CREATE FUNCTION
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select ff1();
ERROR: must be owner of relation t3
CONTEXT: SQL statement "drop table t3"
PL/pgSQL function ff2() line 4 at SQL statement
SQL statement "SELECT ff2()"
PL/pgSQL function ff1() line 4 at PERFORM
反过来依然:
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> alter function ff2() security definer;
ALTER FUNCTION
postgres=> alter function ff1() security invoker;
ALTER FUNCTION
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select ff1();
ERROR: must be owner of relation t3
CONTEXT: SQL statement "drop table t3"
PL/pgSQL function ff2() line 4 at SQL statement
SQL statement "SELECT ff2()"
PL/pgSQL function ff1() line 4 at PERFORM
这点上PostgreSQL是安全的,符合预期。