In this phase, we add features to the FabFlixs database to allow for different user groups (each with appropriate permissions) and to tune the database.

• Official JDBC documentation
• Java 5.0 API Documentation for Statement, PreparedStatement and CallableStatement in java.sql
• Database Roles and Privileges - Sections 19-19.4 of the PostgreSQL manual
• Privileges - Section 5.6 of the PostgreSQL manual
• CREATE, ALTER, DROP, GRANT, REVOKE, ANALYZE and EXPLAIN SQL Commands - Section VI.I of the PostgreSQL manual
• System Catalogs - Chapter 44 of the postgreSQL manual
• psql documentation
• Bulk Populating a postreSQL Database - Section 14.4 of the PostgreSQL manual
• JDBC Tutorial on Connection Pooling
• And, of course, the instructor and the TA

Attributes: permissions that can be given to, or taken away from, a PostgreSQL ROLE, managed with the CREATE ROLE and ALTER ROLE commands.

Privileges: access and use permissions to the database and its objects (tables, functions, langauges, etc.) that can be given to, or taken from, a PostgreSQL ROLE, managed with the GRANT and REVOKE commands.

Required is adding user groups and to manage them with the "back-end program" (the program first developed in Phase 1); those groups and their capabilities are

• DBA - Database Administrator. Only superusers can be DBA members. DBAs are the only group to have full access to the back-end system, including the new Permissions Management feature (see below).

• DB Staff. DB staff have full access to all features of the back-end system except that 1) when they enter the Permissions Management feature, they can only view attributes and permissions—they do not even see the menu options for other features, and 2) they have either no access, or only restricted access, to the arbitrary SQL command function (see below for details).

• FabFlixs Staff. These users have access only to the back-end functions that print information; for example, they can use the function that prints movie information for a given star. They do not have access to—in fact, do not even know about—any other functions (see below).

The superuser and communication security

A superuser is a user, a role with the login privilege, that has all privileges, including the privilege of creating other roles, including users. To create FabFlixs user groups, the database must first have at least one superuser, which it does: the superuser 'postgres', created when you installed PostgreSQL.

If you wish to create another superuser, say one called root, log into the database as postgres and issue the SQL command

make sure you have this testing environment "locked down" so that only authorized computers can get acess to FabFlixs. The default installation of PostgreSQL allows only localhost access, typically what is desired at this stage. If you changed that access (e.g., in Phase 0), double check to ensure that only authorized computers will get to FabFlixs (see Enabling remote computers to communicate with the PostgreSQL server, in Phase 0, for details.

User and group Management

You are required to implement the new Permissions Management feature in the backend program. It allows a user in the DB Staff group to view users and user groups along with their attributes and privileges. It allows a DBA to not only view these settings, but to change them. The DBA can also add and remove users, move users to another group and change attributes and privileges; s/he cannot, however, add or remove groups. More specifically, the feature provides these capabilities:

1. Create new users and place them in a group: DBA only.

A DBA can create a new user and add her/him to one of the three user groups, or move an existing user to a group of which s/he is not currently a member. A new member of a group should have all and only the permissions of that group. Since these groups comprise all staff, and group permissions are cumulative (that is, since DBA has all the permisssions for DB Staff, and DB Staff has all the permissions of FabFlixs Staff), a user must be a member of a group, and of only one group. This implies that when a user is moved to a new group, that user is removed from her/his current group.)

2. Modify group attributes: DBA only.

   A member of the DBA group can alter the attributes and privileges of any of the three user groups. As a wrong setting can seriously compromise database security, this feature must have verification checks passed (e.g., "Are you sure?"-type questions answered yes) before the change is made.

3. Remove users: DBA only.

   Deletes a user from the FabFlixs system (including removing said from its group); a verfication check is required.

4. Display roles and their attributes and privileges (DBA and DB Staff only)

   The staff member can repeatedly choose to view the attributes and privileges of any user or any particular group, until a command is given to exit the feature. The display of information must, of course, be easy for a DBA or DB Staff (that is, someone with their assumed expertise) to read and understand. (Yes, it is the case that a user's attributes and privileges are completely determined by her/his group, and so one could just display a user's group and issue another command to view the group's attributes and privileges if need be—but that is cumbersome. Displaying all information about a user at once is more efficacious.)

The three user groups are permanent; therefore, there should be no way within this function to delete a group.

To enforce the security these functions implement, the arbitrary SQL command function must now be limited. Required is that only DBAs can use the feature. Desired, but not required for full credit (which means that if you do it you can earn credit that can offset points lost for other reasons) is a more nuanced restriction: DBAs can issue any command, but DB Staff and FabFlixs Staff can only issue those arbitrary SQL commands that are consistent with the permissions and access of those groups, as described above.

The exact look and feel of the Permissions Management feature is up to you, but it should be consistent with the interface used in the rest of the program.

Regardless of the interface, the Permissions Management feature should act like its own mini-system: the user enters into it, sees a menu of choices, picks and executes a choice, then goes back to the menu; this continues until some sort of exit command is issued to take the user out of Permissions Management and back to the main FabFlixs menu.

Some hints and suggestions:

• Thoroughly read the PostgreSQL documentation on roles and, in particular, on the CREATE ROLE, ALTER ROLE, DROP ROLE, GRANT and REVOKE commands.

• Have appropriate error handling. For example, if the back-end user tries to create a new user that already exists, that should be an error; All errors should be dealt with and communicated in a manner appropriate for your backend system's user interface.

• Try to display lists of things so that as many as feasible fit neatly on one screen. Of course, that will not be possible for sufficiently large lists, so have a way to scroll though the items (such as the "next 20" and "previous 20" approach used in Phase 2, if you have a Web interface, or "Press RETURN to continue"-type messages if you are using a command-line interface.).

• Thoroughly check that your Permissions Management feature and the now-changed arbirtrary SQL command feature work properly—it would be a major security breach if they did not!

Some Technical Points

To display the information required in the above-described functions, or to show us during your demonstration that changes made to roles by your program are actually appearing in the database, you will need to access certain of PostgreSQL's internal tables of metadata, called catalogs (see the references, above). In particular, you will likely find useful information in the following catalogs:

• pg_authid: roles information; pg_roles is the same, but with password blanked

• pg_auth_members: information about the membership relations between roles

• pg_class: information about tables and table-like objects, such as views; the relacl (relation access control list) attribute gives information about who has access to a particluar table

• pg_proc: information about existing stored procedures; the proacl attribute gives information about who has access to a particular procedure

• pg_namespace: a list of the namespaces in use; in particular, PUBLIC identifies those entites that are user-defined

The pg_class and pg_proc tables have information about both system and user tables, and both standard and user PostgreSQL functions, respectively. We are only interested in user-created tables and functions, i.e., your interface should allow authorized users to change user privileges only on user-created tables and functions: These all have the namespace public.

Do not directly change the information in these catalogs, as you could (quite easily) break the database. Instead, call upon GRANT, REVOKE, ALTER, CREATE and DROP as appropriate; those commands will update the catalogs.

You will need to log in as a superuser if you want unfettered access to all the roles, along with their attributes and privileges. In psql, you can use the \dg command to display roles and the \z command to display information about existing privileges. pgAdmin also can display this information.

When accessing a function, you need to provide its name and its argument types; as postgreSQL allows function overloading, the arguments are needed to uniquely define which function is being used. So, for instance, one says

GRANT EXECUTE ON FUNCTION concat(varchar, varchar) TO dbstaff ;

One way to find the arguments of a function is to issue

select proname, nspname, proargtypes 
from pg_proc, pg_namespace 
where pg_proc.pronamespace = pg_namespace.oid and
pg_namespace.nspname='public' ;

which returns a table such as

 
      proname        | nspname |               proargtypes 
----------------------+---------+------------------------------------------------- 
add_one               | public  |                                              23 
add_one               | public  |                                       1043 1043 
concat                | public  |                                       1043 1043 
plpgsql_call_handler  | public  | 
add_movie             | public  | 1043 23 1043 1043 1043 1015 1015 1015 1182 1015
(5 rows) 

Now you can look up the argtype of interest, in our case, code 1043 from concat:

		select typname from pg_type where oid=1043;

which returns

typname
--------- 
varchar
(1 row)

Here's a SELECT statement that will provide a table of just user-defined functions:

select proname, nspname
from pg_proc, pg_namespace 
where pg_proc.pronamespace = pg_namespace.oid and pg_namespace.nspname='public' ;

(No, it's not straight-forward, but so it goes...)

Similar queries can be used to identify public tables.

As movies and related information are added to the FabFlixs database, customer browsing and searching times may increase to unacceptable levels. So, here we create a large test database, reflective of how big the FabFlixs database is likely to become, and determine if indexing certain database tables is warranted.

• Generate a test database of at least 250,000 movie records and at least 25,000 star records. Titles and names can be randomly generated. (Hint: Write a short JDBC program that generates a list of titles and a list of stars, then write combinations of these repeatedly to a file such that 250,000 movie and 25,000 star records result. Store them in Comma Separated Value format; then you can use COPY to do a fast, bulk incorporation of these records into the database. Refer to the "Bulk Populating a postreSQL Database" reference above.)

• Using the FabFlixs back-end program, conduct at least 10 searches by movie title, at least 10 by star name, and at least 10 by both movie title and star name. (Searching is done in the manner described in Phase 3; in particular, fuzzy searching is allowed.) Make sure the searches lead to different parts of each table, in particular its start, middle and end. Determine the average time it takes to complete each of these three kinds of searches. (Hint: add code to your FabFlixs system so that the computer measures and reports the time. For instance, you can use System.nanoTime(), which reports the number of nanoseconds since some system-determined fixed time. Call the method before you start a set of searches, call it again right after that set completes, then subtract to get the time the set of searches takes and divide by the number of searches to obtain the average time. This code can be commented out when you are done, so when the system is put into production, the user does not see the times.)

• Now create various indexes into the database tables; your goal is to have these indices make the searches significantly faster. Which tables you index, what you index on, what kind of index you use, and how many indices you have—that's all up to you. Think carefully about what to try. Redo the same searches as before with (various combinations and types of) indices in effect and again determine the average search times. You will very likely find the ANALYZE and EXPLAIN commands provide information useful in making these decisions.

• Write a short (one page or so) summary report that explains what indices you tried and the results you obtained when using and not using these indices. Then give your reasoned opinion as to which, if any, indices should be used in the final, production system; justify your recommendations (quoting your timings and information gleaned from EXPLAIN and ANALYZE, as appropriate). Remember that indices don't always help (check out the PostgreSQL documentation for some insight into when they do and don't) and that there are trade-offs; for instance, indices take more disk space, and it takes longer to add and delete records when they are in use. Your report is to be stored in a file that is readable by Word or Firefox.

Desired, but not required for full credit, are tests with multiple users searching the database simultaneously. This can be done by modifying your back-end system to repeatedly issue a SQL command and then using multiple such back-end systems simultaneously. You can also do it by writing a multi-threaded Java program where each thread queries the database. If you are interested in doing even more sophisticated testing, contact the instructor.

Prior to your demonstration, have at least three users in each of the three user groups.

During the demonstration, first show us that the three default roles have the specified attributes and privileges and that each has at least three users. Further, show us the capabilities of your Permissions Management feature; what you do in what order is up to you, but be sure to demonstrate Permissions Management capabilities thoroughly. While showing us these features, also show us that changes you made using the program really did end up in the database. For instance, you could have an open window with pgAdmin running and, after doing some work using the Permissions Management feature, issue pgAdmin commands to show us that the changes were indeed made to the database.

Also, briefly explain the results of your tuning tests and your recommendations regarding indexing of the database.