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.
Database privileges: login/connect
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.
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 ;