Lecture 17: Access Control and Procedural SQL

Lecture 17: Access Control and Procedural SQL#

Announcements#

  • Homework #5 is out, great for studying for the exam.

  • Lecture Exercises 16, 17, 18 are due on monday at 2pm.

  • We will drop the lowest 4 lecture exercises, use them to learn and practice.

  • Exam #2 next week thursday. It will be solely on SQL. It will incorporate everything we did in class, including access control but not procedural SQL.

    • I will post the database schema for the exam the day before the exam.


Access Control in SQL#

PostgreSQL checks privileges in layers.

  • To get at a table, a role has to pass the check at each level: database -> schema -> table -> (and optionally column).

  • A grant at one level does not carry over to another level.

  1. Database privileges: login/connect

  2. Schema privileges: access to the namespace

A schema has only two privileges:

  • USAGE Look up objects inside the schema. Without it you can’t reach anything in the schema at all, even tables you have full rights on.

  • CREATE Create new objects in the schema.

  1. Table privileges: access to the data

  • Privilege Allows

  • SELECT Read rows (also needed for UPDATE/DELETE with a WHERE clause that reads columns)

  • INSERT Add rows

  • UPDATE Change rows

  • DELETE Remove rows

  • TRUNCATE Empty the table

  • REFERENCES Create foreign keys that point to it

  • TRIGGER Create triggers on it

  • MAINTAIN (PG 17+) VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, etc.

Who has privileges?

  • Users and roles

  • A role is a database identity that can own objects, receive privileges, and grant permissions to other roles.

  • A user is simply a role that has the LOGIN attribute, allowing it to log in to PostgreSQL.


-- Create two schemas
CREATE SCHEMA sales;
CREATE SCHEMA hr;

-- Create tables in the sales schema
CREATE TABLE sales.customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(100)
);

CREATE TABLE sales.orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES sales.customers(customer_id),
    amount NUMERIC(10, 2)
);

-- Create tables in the hr schema
CREATE TABLE hr.employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    salary NUMERIC(10, 2)
);

CREATE TABLE hr.departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);


CREATE ROLE sales_readonly INHERIT;
CREATE ROLE hr_readonly NOINHERIT;

CREATE USER john WITH PASSWORD 'john' CREATEDB;
CREATE USER mary WITH PASSWORD 'mary' NOINHERIT;

GRANT USAGE ON SCHEMA sales TO sales_readonly;

GRANT SELECT
ON ALL TABLES IN SCHEMA sales
TO sales_readonly;

GRANT USAGE ON SCHEMA hr TO hr_readonly;

GRANT SELECT
ON ALL TABLES IN SCHEMA hr
TO hr_readonly;

GRANT sales_readonly TO john;

GRANT hr_readonly TO mary;
  • Users have all (owner) rights to any object they create

  • They can grant these rights to others

  • Check access control for a role:

SELECT
    rolname,
    rolcanlogin,
    rolsuper,
    rolcreatedb,
    rolcreaterole,
    rolinherit,
    rolbypassrls -- bypasses row-level security
FROM pg_roles
WHERE rolname = 'john';

  • You can assign a role to many users and then assign access rights to a role.

create role dbstudent ;

grant role dbstudent to john ;
grant role dbstudent to mary ;


grant select on bakers to dbstudent ;
grant select on bakers to john ;

grant insert on episodes to dbstudent ;
grant insert, update, delete on bakercnt to dbstudent ;

grant select on showstoppers to dbstudent with grant option ;


revoke select on bakers from dbstudent ;
revoke grant option on showstoppers from dbstudent ;

Extended Group by#

select day, time, avg(price)
from events
group by rollup(day, time);


group by day, time
group by day
group by <nothing>


select day, time, avg(price)
from events
group by cube(day, time);


day time
-   time
day -
-   - 

Procedural SQL#

  • Client side programming example with Python:


import psycopg2 as dbapi2

db = dbapi2.connect (database="baking", user="sibeladali", password="sibeladali")

cur = db.cursor()

cur.execute ("select baker,fullname, age from bakers;")

rows = cur.fetchall()
i =0
for row in rows:
    print ("Baker: {}\t Fullname: {}\t Age:{}".format(row[0],row[1],row[2]))
    i += 1

try:
    cur.execute("drop table if exists bakercnt;") 
    cur.execute("create table bakercnt (id int) ;") 
except  dbapi2.Error as e:
    print(f"Database error: {e}")
    raise
             

try:
    cur.execute("insert into bakercnt values(%s);",(i,))
except  dbapi2.Error as e:
    print(f"Database error: {e}")
    raise
    
cur.close()
db.commit()
db.close()

PL/PGSQL#

CREATE OR REPLACE FUNCTION format_name(f1 varchar, a1 int) RETURNS varchar AS $$
DECLARE
   outvar VARCHAR ;
BEGIN
   outvar = f1 || ' (' || a1::varchar || ')';
   RETURN outvar ;
END ;
$$ LANGUAGE plpgsql ;

select format_name(fullname, age) as formattedname, hometown from bakers ;