02postgresql的函数安全定义解说

PostgreSQL的函数安全定2015-07-1716:31:43|作者：|分类：PgSQL2015年度PG大象会报名地址：PostgreSQL中国社区： Postgres PostgreSQL用户PostgreSQL函数可以设置被调用时的角色，以及参数。CREATE[CREATE[OR CE]name([[argmode][argname]argtype[{DEFAULT|=default_expr][,...]])[RETURNSrettype|RETURNSTABLE(column_namecolumn_type[,...]){{LANGUAGE|TRANSFORM{FORTYPEtype_name}[,...||IMMUTABLE|STABLE|VOLATILE|[NOT]|CALLEDONNULLINPUT|RETURNSNULLONNULLINPUT||[EXTERNAL]SECURITYINVOKER|[EXTERNAL]SECURITY|COST|ROWS|SETconfiguration_parameter{TOvalue|=value|FROMCURRENT|AS|AS'obj_file',}[WITH(attribute[,...])我们可以一下，角色需要用到session_user和current_user，这两者 SETSESSIONAUTHORIZATION设置的角色。current_user是指setrole设置的角色，或者继承自sessionuser，或者是函数createroledigoallogin;postgres=>createroledigoallogin;postgres=>\cpostgrespostgresYouarenowconnectedtodatabase"postgres"asuserpostgres=#selectsession_usersession_user|+|(1postgres=#setroledigoal;postgres=>selectsession_user,current_user;session_user|current_user+|(1postgres=#createorre cepostgres=#createorre cefunctionf1()returnsvoidas$$xtext;showsearch_pathintoraisenotice'search_path:%|session_role:%|current_role:%',x,session_user,current_user;$$languageplpgsqlsecuritydefinersetsearch_pathto这里的securitydefiner表示调用函数时，使用函数ownerpostgres=#postgres=#grantusageonschemapostgresto使用digoal用户连接到postgres数据库，并调用postgres.f1()postgres=#postgres=#\cpostgresYouarenowconnectedtodatabase"postgres"asuser"digoal".postgres=>selectpostgres.f1(); search_path:public|session_role:digoal|current_role:(1(1NOTICE可以看到我们对函数的设置起作用了。search_pathpostgres=>selectsession_user|+|postgres=>selectsession_user|+|(1postgres=>showsearch_path;(1securitydefiner时，需特别注意，因为可能造成权限升级，例如本文使用超级用户创建的securitydefiner函数。postgres=>\cpostgresYouarenowconnectedtopostgres=>\cpostgresYouarenowconnectedtodatabase"postgres"asuser"postgres".postgres=#alterfunctionf1()securityinvoker;ALTERpostgres=#\cpostgresYouarenowconnectedtodatabase"postgres"asuser"digoal".postgres=>selectpostgres.f1(); search_path:public|session_role:digoal|current_role:digoal(1postgres=#createtablepostgres.pwds(usernamename,pwdCREATEpostgres=#insertintopwdsvalues postgres=#createtablepostgres.pwds(usernamename,pwdCREATEpostgres=#insertintopwdsvalues INSERT0postgres=#CREATEFUNCTIONcheck_password(unameTEXT,passTEXT)RETURNSBOOLEANAS$$DECLAREpassedBOOLEAN; (pwd=$2)INTO username=RETURN LANGUAGESECURITYinvoker; --假设pwds这个表只有超级用户可以 这个函数时，如果设置为securityinvoker会有问题。\c\cpostgrespostgres=>showpostgres,"$user",(1postgres=>select permissiondeniedforrelation SQLstatement (pwd username=PL/pgSQLPL/pgSQLfunctioncheck_password(text,text)line4atSQL但是如果设置为securitydefinerpostgres=#postgres=#alterfunctioncheck_password(text,text)securityALTERpostgres=#\cpostgresYouarenowconnectedtodatabase"postgres"asuserpostgres=>selectt(1postgres=>selectf(1schema.table的方式，所以我先级来通过认证，甚至可以使用临时表的SCHEMA，都不需要修改search_path(因为临时表schema优先级被排在最前)，偷偷就搞定了。postgres=>postgres=>createtemptablepwds(usernametext,pwdtext);CREATETABLEpostgres=>insertintopwdsvaluesINSERT0postgres=>selectpostgres.check_password('digoal','err');t(1为了提高securitydefiner建议在里面使用的函数或表等一切对象，都使用schemapostgres=#CREATEorpostgres=#CREATEor ceFUNCTIONcheck_password(unameTEXT,passRETURNSBOOLEANAS$$DECLAREpassedBOOLEAN; (pwd=$2)INTO username=RETURN LANGUAGEplpgsqlSECURITYdefiner;CREATEpostgres=#postgres=#\cpostgresYouarenowconnectedtodatabase"postgres"asuser"digoal".postgres=>createtemptablepwds(usernametext,pwdtext);CREATETABLEpostgres=>insertintopwdsvaluesINSERT0postgres=>selectpostgres.check_password('digoal','err');f(1或者在调用函数时使用设置的search_pathschema都postgres=#CREATEorre cepostgres=#CREATEorre ceFUNCTIONcheck_password(unameTEXT,passTEXT)RETURNSBOOLEANAS$$DECLAREpassed (pwd (pwd=$2)INTO username=RETURN LANGUAGESECURITYdefinersetsearch_pathto CREATEpostgres=#postgres=#\cpostgrespostgres=>createtemptablepwds(usernametext,pwdtext);CREATETABLEpostgres=>insertintopwdsvaluesINSERT0postgres=>setsearch_path=pg_temp,postgres,"$user",public;postgres=>select*frompwds;username|pwd+ |err(1row)因为函数中设置了search_pathto"$user",public,pg_temp;postgres=>selectpostgres.check_password('digoal','err');f(1不过这里还是推荐在函数中使用schema[参考*User*UserID*Wehavetotrackseveraldifferentvaluesassociatedwiththeof"userAuthenticatedUserIdisdeterminedatconnectionstartandneverSessionUserIdisinitiallythesameasAuthenticatedUserId,butcanchangedbySETSESSIONAUTHORIZATION(ifThisistheIDreportedbytheSESSION_USERSQLOuterUserIdisthecurrentuserIDineffectatthe"outerlevel"anytransactionor ThisisinitiallythesameasbutcanbechangedbySETROLEtoanyrolethatSessionUserIdismember (XXXrenametosomethinglikeCurrentUserIdisthecurrenteffectiveuserID;thisistheonetoforallnormalpermissions-checking AtouterlevelthisbethesameasOuterUse