/*
    CSE 444, summer 2010
    Examples for section 6 - triggers and security
    July 28, 2010
 */

/*
    Triggers
    --
    Make sure to run
      CREATE LANGUAGE plpgsql;
    first if you haven't already.
 */

-- Example table
DROP TABLE emp;
CREATE TABLE emp ( 
    empname varchar(100), 
    salary integer, 
    last_date timestamp, 
    last_user varchar(100)
);

-- Define the triggered action
DROP FUNCTION emp_stamp();
CREATE FUNCTION emp_stamp() RETURNS trigger AS
$$ 
    BEGIN 
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION 'Salary must be non-negative'; 
        END IF; 

        NEW.last_date := current_timestamp; 
        NEW.last_user := current_user; 
        RETURN NEW; 
    END; 
$$ LANGUAGE plpgsql;

-- Create the trigger
CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
EXECUTE PROCEDURE emp_stamp();

-- Now this insertion should fail
INSERT INTO emp (empname, salary)
VALUES ('Alice', -1000);

-- After this insertion, last_date and last_user
-- are set rather than being NULL
INSERT INTO emp (empname, salary)
VALUES ('Bob', 10000);

-- Let's check:
SELECT * FROM emp;

/*
    Security
    --
    Make sure you have CREATEROLE privilege to run these examples.
    If not, log in as superuser and run
      ALTER ROLE your_username WITH CREATEROLE;
 */
DROP USER fred;
DROP USER john;
DROP ROLE repo_men;

-- Example table
DROP TABLE Customers;
CREATE TABLE Customers (
    name VARCHAR(10),
    address VARCHAR(30),
    balance NUMERIC,
    PRIMARY KEY (name)
);
INSERT INTO Customers (name, address, balance)
VALUES
    ('Mary', 'Houston', 450.99),
    ('Sue', 'Seattle', -240),
    ('Joan', 'Seattle', 333.25),
    ('Ann', 'Houston', -520);

-- Column-level access control using views
--

-- Set up Fred's account - you need CREATEROLE privilege for this!
CREATE USER fred WITH PASSWORD 'fredpass';

-- Prevent Fred from reading the base table
REVOKE ALL PRIVILEGES ON Customers FROM fred;

-- Create the view that contains what Fred can access
CREATE VIEW PublicCustomers AS
    SELECT Name, Address
    FROM Customers;

-- Allow Fred to read the view
GRANT SELECT ON PublicCustomers TO fred;

-- (1) Now these SELECTs should fail when run as Fred
SELECT * FROM Customers;
SELECT balance FROM Customers;
SELECT name, balance FROM Customers;

-- (2) So will these
SELECT name FROM Customers;
SELECT name, address FROM Customers;

-- (3) But these SELECTs should work as Fred, just as with us
SELECT * FROM PublicCustomers;
SELECT name, address FROM PublicCustomers;
SELECT address
FROM PublicCustomers
WHERE name = 'Mary';

-- Column-level access control without views
--

-- Allow Fred to read only the name and address columns
GRANT SELECT (name, address) ON Customers TO fred;

-- Now group (1) will still fail, and group (3) will still succeed,
-- but now group (2) succeeds too

-- Row-level access control
--

-- Create the repo men's group, and make Fred and John repo men
CREATE ROLE repo_men;
GRANT repo_men TO fred;
CREATE USER john WITH PASSWORD 'johnpass' 
    IN ROLE repo_men;

-- Prevent repo men from reading the base table
REVOKE ALL PRIVILEGES ON Customers FROM repo_men;

-- Create the view that repo men can access
CREATE VIEW BadCreditCustomers AS
    SELECT *
    FROM Customers
    WHERE balance < 0;
GRANT SELECT ON BadCreditCustomers TO repo_men;
-- Must use view, because GRANT doesn't support WHERE clause

-- (4) Now these still fail as John (or as Fred for that matter)
SELECT * FROM Customers;

-- (5) But these succeed as either John or Fred
SELECT * FROM BadCreditCustomers;
SELECT name, balance
FROM BadCreditCustomers
WHERE address = 'Seattle';
