advanced features - cs.rpi.edu
Post on 01-May-2022
6 Views
Preview:
TRANSCRIPT
Advanced Features
CSCI 4380
Databases
• A postgresql database cluster is organized into databases.
• No data can be shared across databases.
• Information in a database can be clustered into logical units called schema.
Schemas• Create a schema with:
• CREATE SCHEMA myschema;
• Access/create tables in the schema with:
• schema.table
• To delete a schema and all the objects in it:
• DROP SCHEMA myschema;
• To create a schema owned by someone else:
• CREATE SCHEMA schemaname AUTHORIZATION username;
Search path• Whenever a table name is used, the database
tries to find the correct instance
• The search path is usually
• first: $user: a schema with the same name as the current user
• second: public: any information that is open to public, i.e. all users.
• The search path can be changed by:
• set search_path to ....
Security• Postgresql allows the creation of roles
• A role is like a user, but more general
• A role with a login privilege is considered a user
• A role can be given the right to create databases and/or create other roles.
• A role with superuser privileges can bypass all security checks
Role creation• Inherit allows the role to inherit all the privileges given to
that role.
• CREATE ROLE joe LOGIN INHERIT;
• CREATE ROLE admin NOINHERIT;
• CREATE ROLE wheel NOINHERIT;
• GRANT admin TO joe;
• GRANT wheel TO admin;
• Admin has the privileges of wheel, but joe does not have wheel privileges (it is not inherited).
• As a role connects to the database, it has all the rights given to that role.
Objects• All database objects (database, tables, indices,
procedures, triggers, etc.) have an owner, the role that created them.
• Owner has all the access rights on the objects they create.
• Other roles can be given explicit privileges on these objects:
• SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.
Privileges• SELECT, INSERT, DELETE, UPDATE are the privileges to
query (select) and change the data of some other role.
• Can be specific: SELECT(name)
• REFERENCES is the right to refer to a relation in an integrity constraint
• USAGE is the right to use a schema element in relations, assertions, etc.
• TRIGGER is the right to define triggers.
• UNDER is the right create subtypes
Grant option
• Users/roles can pass a privilege to another user/role is they have the grant option.
• GRANT select ON users TO spock
• WITH GRANT OPTION
• Only a role that has a grant option can grant the grant option to the others.
Grant diagrams• Nodes represent a user and a privilege
• Two different privileges of the same person should be put in two different nodes
• If one privilege for a user is the more general version of another, they should both be included.
• Example: select, select(name)
• Each grant generates a path in the grant diagram
Grant diagrams
• Nodes are marked by:
• ** for owners
• * for users who have grant option
• nothing for all other users
Grant diagrams
• When a new privilege X is given from role A to role B
• If there are no nodes for (A,X) and (B,X), then create them.
• Add all the necessary links.
Grant diagramspicard
select on movie
**janewayselect on
movie*
siskoselect on
movie
siskoselect(name)
on movie
Revoking privileges• Revoke <privileges> on <database element>
from <role list>
• will remove the listed privileges.
• Cascade: will remove any privileges that are granted only because of the removed privileges.
• Restrict: will fail if the revoked privileges were passed on to other roles previously.
Revoking privileges• Delete any edges corresponding to the deleted
privileges.
• If there are any nodes not reachable from a double starred role, then they should be removed together with all the edges coming out of them.
• Continue this process until all the nodes are reacheable from a doubly starred node.
Grant diagramspicard
select on movie
**janewayselect on
movie*
siskoselect on
movie
siskoselect(name)
on movie
revoke select on movies from janeway cascade
Grant diagramspicard
select on movie
**janewayselect on
movie*
siskoselect on
movie
siskoselect(name)
on movie
revoke select on movies from janeway cascade
Grant diagramspicard
select on movie
**
siskoselect on
movie
siskoselect(name)
on movie
revoke select on movies from janeway cascade
Grant diagramspicard
select on movie
**
siskoselect(name)
on movie
revoke select on movies from janeway cascade
Grant diagramspicard
select on movie
**janewayselect on
movie*
siskoselect on
movie
siskoselect(name)
on movie
revoke grant option on movies from janeway cascade
Grant diagramspicard
select on movie
**janewayselect on
movie*
siskoselect on
movie
siskoselect(name)
on movie
revoke grant option on movies from janeway cascade
janewayselect on
movie
Grant diagramspicard
select on movie
**
siskoselect(name)
on movie
revoke grant option on movies from janeway cascade
janewayselect on
movie
System tables
• Information about the database are also stored in database tables
• they can be queried like any other
• Examples:
• pg_constraint: all constraints on tables
• pg_user: all users that their access rights (can they create databases? are they superusers?)
• pg_views: the name of the views, owner and text
Inheritance
• Recall in E-R diagrams, we talked about ISA relationships.
• A isa B, meaning A inherits all the attributes of B (and adds some more)
• Postgresql allows you to define hierarchies:
InheritanceCREATE TABLE cities ( name text, population float, altitude int -- in feet);
CREATE TABLE capitals ( state char(2)) INHERITS (cities);
InheritanceSELECT name, altitude FROM cities WHERE altitude > 500;Includes all cities, i.e. capitals as well.
SELECT name, altitude FROM ONLY cities WHERE altitude > 500;Includes only cities, not capitals.
InheritanceTo find out which table a row comes from:
SELECT p.relname, c.name, c.altitudeFROM cities c, pg_class pWHERE c.altitude > 500 AND c.tableoid = p.oid;
Output:relname | name | altitude----------+-----------+---------- cities | Las Vegas | 2174 cities | Mariposa | 1953 capitals| Madison | 845
CASE statement in selectSELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test;
a | case---+------- 1 | one 2 | two 3 | other
Window functionsSELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667(10 rows)
top related