Andrew Hudson - Fedora™ Unleashed, 2008 edition

To delete a database user, you use the dropusercommand, along with the user's name, and the user's access is removed from the default database, like this:

$ dropuser msmith

DROP USER

You can also log in to your database by using psqland then use the DROP USERcommands. Here's an example:

$ psql demodb

Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

demodb=# DROP USER msmith ;

DROP USER

demodb=# \q

$

As in MySQL, granting and revoking privileges in PostgreSQL is done with the GRANTand REVOKEstatements. The syntax is the same as in MySQL except that PostgreSQL doesn't use the IDENTIFIED BYportion of the statement because with PostgreSQL, passwords are assigned when you create the user with the CREATE USERstatement, as discussed previously. Here is the syntax of the GRANTstatement:

GRANT what_to_grant ON where_to_grant TO user_name;

The following command, for example, grants all privileges to the user foobaron the data base sampledata:

GRANT ALL ON sampledata TO foobar;

To revoke privileges, you use the REVOKEstatement. Here is an example: REVOKE ALL ON sampledata FROM foobar;

This command removes all privileges from the user foobaron the database sampledata.

Advanced administration and user configuration are complex topics. This section cannot begin to cover all the aspects of PostgreSQL administration or of privileges and users. For more information on administering PostgreSQL, see the PostgreSQL documentation or consult a book on PostgreSQL, such as Korry Douglas's PostgreSQL (Sams Publishing).

Both MySQL and PostgreSQL use a client/server system for accessing databases. In the simplest terms, the database server handles the requests that come into the database and the database client handles getting the requests to the server as well as getting the output from the server to the user.

Users never interact directly with the database server even if it happens to be located on the same machine they are using. All requests to the database server are handled by a database client, which might or might not be running on the same machine as the data base server.

Both MySQL and PostgreSQL have command-line clients. A command-line client is a very primitive way of interfacing with a database and generally isn't used by end users. As a DBA, however, you use the command-line client to test new queries interactively without having to write front-end programs for that purpose. In later sections of this chapter, you will learn a bit about the MySQL graphical client and the web-based database administration interfaces available for both MySQL and PostgreSQL.

The following sections examine two common methods of accessing a remote database, a method of local access to a database server, and the concept of web access to a database.

You should consider access and permission issues when setting up a database. Should users be able to create and destroy databases? Or should they only be able to use existing databases? Will users be able to add records to the database and modify existing records? Or should users be limited to read-only access to the database? And what about the rest of the world? Will the general public need to have any kind of access to your database through the Internet? As DBA, you must determine the answers to these questions.

Two types of remote database access scenarios are briefly discussed in this section. In the first scenario, the user directly logs in to the database server through SSH (to take advantage of the security benefits of encrypted sessions) and then starts a program on the server to access the database. In this case, shown in Figure 18.4, the database client is running on the database server itself.

FIGURE 18.4 The user logs in to the database server located on host simbafrom the workstation (host cheetah). The database client is running on simba.

In the other scenario, shown in Figure 18.5, the user logs in to a remote host through SSH and starts a program on it to access the database, but the database is actually running on a different system. Three systems are now involved: the user's workstation, the remote host running the database client, and the remote host running the database server.

FIGURE 18.5 The user logs in to the remote host leopardfrom the workstation ( host cheetah) and starts a database client on leopard. The client on leopardthen connects to the database server running on host simba. The database client is running on leopard.

The important thing to note in Figure 18.5 is the middleman system leopard. Although the client is no longer running on the database server itself, it isn't running on the user's local workstation, either.

A user can log in to the database server by using a graphical client (which could be running on Windows, Macintosh OS, or a Unix workstation). The graphical client then connects to the database server. In this case, the client is running on the user's workstation. Figure 18.6 shows an example.

FIGURE 18.6 The user starts a GUI database program on his workstation (hostname cheetah). This program, which is the database client, then connects to the database server running on the host lion.

In this section, we look at two basic examples of web access to the database server. In the first example, a user accesses the database through a form located on the World Wide Web. At first glance, it might appear that the client is running on the user's workstation. Of course, in reality it is not; the client is actually running on the web server. The web browser on the user's workstation simply provides a way for the user to enter the data that he wants to send to the database and a way for the results sent from the database to be displayed to the user. The software that actually handles sending the request to the database is running on the web server in the form of a CGI script; a Java servlet; or embedded scripting such as the PHP or Sun Microsystems, Inc.'s JavaServer Pages (JSP).

Often, the terms client and front end are used interchangeably when speaking of database structures. However, Figure 18.7 shows an example of a form of access in which the client and the front end aren't the same thing at all. In this example, the front end is the form displayed in the user's web browser. In such cases, the client is referred to as middleware.