Skip navigation.
Home

SECURITY DEFINER or AUTHID CURRENT USER

I've just come to minor notice: PostgreSQL defaults to executing procedures with current user rights, Oracle - with definer's rights. It was rather interesting.

Let's say we have departments table in Oracle:

alp@XE>descr departments
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -----------------------
 DEPARTMENT_ID                                                  NUMBER(4)
 DEPARTMENT_NAME                                       NOT NULL VARCHAR2(30)
 MANAGER_ID                                                     NUMBER(6)
 LOCATION_ID                                                    NUMBER(4)

And we create a function to access this table's data:

create type department_row AS OBJECT  (department_id int, department_name varchar2(30), manager_id number, location_id number);

create type department_table AS TABLE OF department_row;

create or replace function showData return department_table PIPELINED IS
r department_row:= department_row(null,null,null,null);
CURSOR c1 IS SELECT * FROM  DEPARTMENTS;
BEGIN
 FOR  i in c1 LOOP
    r.department_id:=i.department_id;
    r.department_name:=i.department_name;
    r.manager_id:=i.manager_id;
    r.location_id:=i.location_id;
    PIPE ROW(r);
 END LOOP;
END;
/

Now we can just grant execute on this function to some user:

alp@XE>grant execute on showData to tu;

And this user can view data in departments table:

tu@XE>select * from TABLE(alp.showData());

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
          200 Operations                                       1700
          210 IT Support                                       1700
          220 NOC                                              1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          260 Recruiting                                       1700
          270 Payroll                                          1700

27 rows selected.

In PostgreSQL you should explicitly set definer's rights when creating function:

CREATE TYPE data_rowtype as (ip inet, traffic integer);

create function showData () returns SETOF DATA_ROWTYPE AS '
select * from data
'
LANGUAGE SQL 
SECURITY DEFINER; 

And after

grant execute on function showData() to tu;

tu can view data in table "data":

alp=> select * from showData();
       ip       | traffic  
----------------+----------
 10.1.1.6/24    | 24117248
 192.168.1.2/24 |  3145728
 10.1.1.7/24    |  4194304
 10.1.1.8/24    |  8388608
 10.1.1.2/24    | 90177536
 10.1.1.4/24    | 24117248
(6 rows)

In general, PostgreSQL's ability to use SQL in function definition is something great (1 line of  SQL instead of 10 PL/SQL - Oracle is bitten :) ). However, there are differences from Oracle's behaviour to remember...