================================================================
CSE 344 -- Spring 2011
Lecture 21:   Transactions and Security

================================================================

How postgres handles conflicts:

More details: http://www.postgresql.org/docs/8.2/static/transaction-iso.html

  -- On READ: fetch the version of the record at time t

  -- On WRITE: fetch the version of the record at time t
       If this is the current version, then write it
       If it was updated by some T' then WAIT for T' to finish
               if T' rolls back, then update the record
               if T' commits, then ABORT T

drop table r;
create table r(a int, b int);
insert into r values (1,10);

T1:
   begin transaction isolation level serializable;
   update r set b=11 where a=1;

T2
   begin transaction isolation level serializable;
   update r set b=12 where a=1;
   -- WAIT

T1:
   commit;   -- T2 is aborted
   -- rollback;   -- allows T2 to proceed


Advantages of MVCC:
   -- read-only transactions are always allowed to proceed without delay
   -- non-conflicting transactions are also allowed to proceed without delay


*** In class: are deadlocks possible in posgres ?  We will try out
    together in class.

================================================================

Posgres is NOT serializable:

drop table r;
create table r(a int, b int);
insert into r values (1,10);
insert into r values (2,20);

T1:
   begin transaction isolation level serializable;
   update r 
   set b = (select y.b from r y where y.a=2)
   where a=1;


T2
   begin transaction isolation level serializable;
   update r 
   set b = (select y.b from r y where y.a=1)
   where a=2;

T1:
   select * from r;
   commit;

T2:
   select * from r;
   commit;

--- *** In class: what is the user intent ?  what actually happens ?

   select * from r;

================================================================

Postgres *DOES* handle phantoms ! (I stated incorrectly last lecture)

drop table r;
create table r(a int, b int);
insert into r values (1,10);

T1:
   begin transaction isolation level serializable;
   select * from r;

T2:
   insert into r values (2,20);
   -- *** in class: there is no 'begin transaction'; what happens ?

T1:
   select * from r;
   commit;

----------------
Compare with Read committed:

drop table r;
create table r(a int, b int);
insert into r values (1,10);

T1:
   begin transaction isolation level read committed;
   select * from r;

T2:
   insert into r values (2,20);

T1:
   select * from r;
   commit;

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

However, postgres does NOT handle phantoms serializable, because it
fails to detect read/write conflicts

drop table r;
create table r(a int, b int);
insert into r values (0,0);

T1:
   begin transaction isolation level serializable;
   select * from r;

T2:
   begin transaction isolation level serializable;
   select * from r;

T1:
   insert into r values (1,10);
   select * from r;
   commit;

T2:
   insert into r values (2,20);
   select * from r;
   commit;


*** discuss in class whether this schedule is serializable

================================================================

PRACTICAL SOLUTIONS TO SERIALIZABILITY

Consider this:

Customer(cid, name)
PendingOrder(oid, cid, date, value)

A customer may place an order only if the total value of the pending
orders is <= 100.  How can we do this in postgres ?

================================================================

Security in SQL

Privileges:  SELECT, INSERT, DELETE, UPDATE, REFERENCES ...
Apply to a table or a view
Granting privileges:

GRANT SELECT On Person TO Joe
GRANT SELECT(name, balance) On Person TO Jim
GRANT SELECT, INSERT On Orders TO Fred
GRANT DELETE On Person TO Sue WITH GRANT OPTION;

Revoking prvileges:

REVOKE INSERT ON Orders FROM Fred
REVOKE DELETE on Person FROM Sue CASCADE

Views and Security

How can we restrict read access to only a subset of the rows ?
E.g. A doctors is allowed to see the recors of all patients they
treat, but not more.

Patient(pid, name, diseases)
Treats(pid, did)
Doctor(did, name, speciality)

1. Grant access to Dr. Seus:

CREATE VIEW Drseus
   select x.pid, x.name, x.disease
   from Patient x, Treats y, Doctor z
   where x.pid = y.pid and y.did = z.did
     and z.name = 'Dr. Seus'

GRANT SELECT ON Drseus TO seus

*** In class: what is the problem with thsi approach ?

2. Grant  *each doctor* access to their patients

*** not supported in SQL !  How do we cope with that ?