From c889c9c90151229baad4af205f42f6b81d7b02f4 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 25 Sep 2002 21:16:10 +0000 Subject: [PATCH] Enhance the description of user and database management. Reduce the number of forward references in the admin guide. --- doc/src/sgml/admin.sgml | 6 +- doc/src/sgml/filelist.sgml | 3 +- doc/src/sgml/manage-ag.sgml | 198 +++++++++++++++++++++--------- doc/src/sgml/manage.sgml | 228 ----------------------------------- doc/src/sgml/runtime.sgml | 12 +- doc/src/sgml/user-manag.sgml | 97 ++++++++++----- doc/src/sgml/user.sgml | 3 +- 7 files changed, 221 insertions(+), 326 deletions(-) delete mode 100644 doc/src/sgml/manage.sgml diff --git a/doc/src/sgml/admin.sgml b/doc/src/sgml/admin.sgml index 24a770d3685..75227bf1823 100644 --- a/doc/src/sgml/admin.sgml +++ b/doc/src/sgml/admin.sgml @@ -1,5 +1,5 @@ @@ -25,10 +25,10 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/admin.sgml,v 1.37 2002/06/13 05:15:22 &installation; &installw; &runtime; + &user-manag; + &manage-ag; &client-auth; &charset; - &manage-ag; - &user-manag; &maintenance; &backup; &monitoring; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index b520e466adb..cbdd3bf5f18 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -1,4 +1,4 @@ - + @@ -29,7 +29,6 @@ - diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 15ece75a398..e37864f79ee 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -1,5 +1,5 @@ @@ -8,36 +8,65 @@ $Header: /cvsroot/pgsql/doc/src/sgml/manage-ag.sgml,v 2.20 2001/11/28 20:49:10 p database - A database is a named collection of SQL objects (database - objects). Generally, every database object (tables, functions, - etc.) belongs to one and only one database. (But there are a few system - catalogs, for example pg_database, that belong to a whole - installation and are accessible from each database within the - installation.) - An application that connects - to the database server specifies in its connection request the - name of the database it wants to connect to. It is not possible to - access more than one database per connection. (But an application - is not restricted in the number of connections it opens to the same - or other databases.) + Every instance of a running PostgreSQL server manages one or more + databases. Databases are therefore the topmost hierarchical level + for organizing SQL objects (database objects). This + chapter describes the properties of databases, and how to create, + manage, and destroy them. - + + Overview + - SQL calls databases catalogs, but there is no - difference in practice. + A database is a named collection of SQL objects (database + objects). Generally, every database object (tables, + functions, etc.) belongs to one and only one database. (But there + are a few system catalogs, for example pg_database, + that belong to a whole installation and are accessible from each + database within the installation.) More accurately, a database is + a collection of schemas and the schemas contain the tables, + functions, etc. So the full hierarchy is: + server-database-schema-table (or something else instead of a + table). - - - In order to create or drop databases, the PostgreSQL - postmaster must be up and running (see ). - + + An application that connects to the database server specifies in + its connection request the name of the database it wants to connect + to. It is not possible to access more than one database per + connection. (But an application is not restricted in the number of + connections it opens to the same or other databases.) It is + possible, however, to access more than one schema from the same + connection. Schemas are a purely logical structure and who can + access what is managed by the privilege system. Databases are + physically separated and access control is managed at the + connection level. If one PostgreSQL server instance is to house + projects or users that should be separate and for the most part + unaware of each other, it is therefore recommendable to put them + into separate databases. If the projects or users are interrelated + and should be able to use each other's resources they should be put + in the same databases but possibly into separate schemas. More + information about managing schemas is in &cite-user;. + + + + + SQL calls databases catalogs, but there is no + difference in practice. + + + Creating a Database + + In order to create a databases, the PostgreSQL + server must be up and running (see ). + + Databases are created with the query language command CREATE DATABASE: @@ -56,20 +85,17 @@ CREATE DATABASE name linkend="user-attributes"> for how to grant permission. - - Bootstrapping: - - Since you need to be connected to the database server in order to - execute the CREATE DATABASE command, the - question remains how the first database at any given - site can be created. The first database is always created by the - initdb command when the data storage area is - initialized. (See .) By convention - this database is called template1. So - to create the first real database you can connect to - template1. - - + + Since you need to be connected to the database server in order to + execute the CREATE DATABASE command, the + question remains how the first database at any given + site can be created. The first database is always created by the + initdb command when the data storage area is + initialized. (See .) By convention + this database is called template1. So to create the + first real database you can connect to + template1. + The name template1 is no accident: When a new @@ -77,14 +103,14 @@ CREATE DATABASE name This means that any changes you make in template1 are propagated to all subsequently created databases. This implies that you should not use the template database for real work, but when - used judiciously this feature can be convenient. More details appear - below. + used judiciously this feature can be convenient. More details + appear in . As an extra convenience, there is also a program that you can execute from the shell to create new databases, - createdb. + createdb. createdb dbname @@ -99,8 +125,32 @@ createdb dbname you want. - - Template Databases + + + contains information about + how to restrict who can connect to a given database. + + + + + Sometimes you want to create a database for someone else. That + user should become the owner of the new database, so he can + configure and manage it himself. To achieve that, use one of the + following commands: + +CREATE DATABASE dbname OWNER username; + + from the SQL environment, or + +createdb -O username dbname + + You must be a superuser to be allowed to create a database for + someone else. + + + + + Template Databases CREATE DATABASE actually works by copying an existing @@ -111,7 +161,7 @@ createdb dbname will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural - language plpgsql in template1, it will + language PL/pgSQL in template1, it will automatically be available in user databases without any extra action being taken when those databases are made. @@ -132,12 +182,24 @@ createdb dbname additions that may now be present in template1. + + To create a database by copying template0, use + +CREATE DATABASE dbname TEMPLATE template0; + + from the SQL environment, or + +createdb -T template0 dbname + + from the shell. + + It is possible to create additional template databases, and indeed one might copy any database in an installation by specifying its name as the template for CREATE DATABASE. It is important to understand, however, that this is not (yet) intended as - a general-purpose COPY DATABASE facility. In particular, it is + a general-purpose COPY DATABASE facility. In particular, it is essential that the source database be idle (no data-altering transactions in progress) for the duration of the copying operation. CREATE DATABASE @@ -151,7 +213,7 @@ createdb dbname Two useful flags exist in pg_database for each - database: datistemplate and + database: the columns datistemplate and datallowconn. datistemplate may be set to indicate that a database is intended as a template for CREATE DATABASE. If this flag is set, the database may be @@ -187,18 +249,47 @@ createdb dbname template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE and the default - database-to-connect-to for various scripts such as createdb. + database-to-connect-to for various programs such as createdb. For example, one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. + + + + Database Configuration + + + Recall from that the PostgreSQL + server provides a large number of run-time configuration variables. + You can set database-specific default values for many of these + settings. + - + + For example, if for some reason you want to disable the + GEQO optimizer for a given database, you'd + ordinarily have to either disable it for all databases or make sure + that every connecting client is careful to issue SET geqo + TO off;. To make this setting the default you can + execute the command + +ALTER DATABASE mydb SET geqo TO off; + + This will save the setting (but not set it immediately) and in + subsequent connections it will appear as though SET geqo + TO off; had been called right before the session started. + Note that users can still alter this setting during the session; it + will only be the default. To undo any such setting, use + ALTER DATABASE dbname RESET + varname;. + + - - Alternative Locations + + Alternative Locations It is possible to create a database in a location other than the @@ -227,18 +318,14 @@ createdb dbname initialize the data area, and finally restart the server. (See and .) To set an environment variable, type - PGDATA2=/home/postgres/data export PGDATA2 - in Bourne shells, or - setenv PGDATA2 /home/postgres/data - in csh or tcsh. You have to make sure that this environment variable is always defined in the server environment, otherwise you won't be able to access that database. Therefore you probably @@ -253,12 +340,11 @@ setenv PGDATA2 /home/postgres/data already exists and is writable by the user account that runs the server (see ). Then from the command line, type - initlocation PGDATA2 - - Then you can restart the server. + (not initlocation + $PGDATA2). Then you can restart the server. @@ -290,8 +376,6 @@ gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all - - diff --git a/doc/src/sgml/manage.sgml b/doc/src/sgml/manage.sgml deleted file mode 100644 index 1850b7481ea..00000000000 --- a/doc/src/sgml/manage.sgml +++ /dev/null @@ -1,228 +0,0 @@ - - - - Managing a Database - - - This section is currently a thinly disguised copy of the - Tutorial. Needs to be augmented. - - thomas 1998-01-12 - - - - Although the site administrator is responsible for overall management - of the PostgreSQL installation, some databases within the - installation may be managed by another person, - designated the database administrator. - This assignment of responsibilities occurs when a database is created. - A user may be assigned explicit privileges to create databases and/or to create new users. - A user assigned both privileges can perform most administrative tasks - within PostgreSQL, but will - not by default have the same operating system privileges as the site administrator. - - - - The Administrator's Guide covers these topics in - more detail. - - - - Database Creation - - - Databases are created by the CREATE DATABASE - command issued from within - PostgreSQL. createdb - is a shell script provided to give the same functionality from the - Unix command line. - - - - The PostgreSQL backend must be running for either method - to succeed, and the user issuing the command must be the PostgreSQL - superuser or have been assigned database creation privileges by the - superuser. - - - - To create a new database named mydb from the command line, type - -% createdb mydb - - - and to do the same from within psql type - -=> CREATE DATABASE mydb; - - - - - If you do not have the privileges required to create a database, you will see - the following: - -ERROR: CREATE DATABASE: Permission denied. - - - - - You automatically become the - database administrator of the database you just created. - Database names must have an alphabetic first - character and are limited to 63 characters in length. - PostgreSQL allows you to create any number of - databases at a given site. - - - - The Administrator's Guide discusses database creation - in more detail, including advanced options of the CREATE - DATABASE command. - - - - - -Accessing a Database - - - Once you have constructed a database, you can access it - by: - - - - - Running the PostgreSQL interactive - terminal program, called psql, which allows you - to interactively enter, edit, and execute - SQL commands. - - - - - - Using an existing graphical frontend tool like - PgAccess or - ApplixWare (via - ODBC) to create and manipulate a database. - These possibilities are not covered in this tutorial. - - - - - - Writing a custom application, using one of the several - available language bindings. These possibilities are discussed - further in The PostgreSQL Programmer's - Guide. - - - - -You probably want to start up psql, -to try out the examples in this manual. - It can be activated for the mydb - database by typing the command: - -% psql mydb - - - You will be greeted with the following message: - -Welcome to psql &version;, 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 - -mydb=> - - - - -This prompt indicates that psql is listening - to you and that you can type SQL queries into a - work space maintained by the terminal monitor. -The psql program itself responds to special -commands that begin - with the backslash character, \. For example, you - can get help on the syntax of various - PostgreSQL SQL commands by typing: - -mydb=> \h - - - Once you have finished entering your queries into the - work space, you can pass the contents of the work space - to the PostgreSQL server by typing: - -mydb=> \g - - - This tells the server to process the query. If you - terminate your query with a semicolon, the \g is not - necessary. -psql will automatically process semicolon terminated queries. - To read queries from a file, say myFile, instead of - entering them interactively, type: - -mydb=> \i myFile - - - To get out of psql and return to Unix, type - -mydb=> \q - - - and psql will quit and return you to your command - shell. (For more escape codes, type \? at the psql - prompt.) - White space (i.e., spaces, tabs and newlines) may be - used freely in SQL queries. Single-line comments are denoted by - --. Everything after the dashes up to the end of the - line is ignored. Multiple-line comments, and comments within a line, - are denoted by /* ... */. - - - - - -Destroying a Database - - - If you are the owner of the database - mydb, you can destroy it using the SQL command - -=> DROP DATABASE mydb; - - or the Unix shell script - -% dropdb mydb - - This action physically removes all of the Unix files - associated with the database and cannot be undone, so - this should only be done with a great deal of forethought. - - - - - - diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index 7265896eaba..753d084b355 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -57,7 +57,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.138 2002/09/21 18:32:53 pe database cluster will contain a database named template1. As the name suggests, this will be used as a template for subsequently created databases; it should not be - used for actual work. + used for actual work. (See for information + about creating databases.) @@ -120,18 +121,17 @@ postgres$ initdb -D /usr/local/pgsql/data However, while the directory contents are secure, the default - pg_hba.conf authentication method of - trust allows any local user to connect to the + client authentication setup allows any local user to connect to the database and even become the database superuser. If you don't trust other local users, we recommend you use initdb's or option to assign a password to the database superuser. After initdb, - modify pg_hba.conf to use md5 or + modify the pg_hba.conf file to use md5 or password instead of trust authentication before you start the server for the first time. (Other, approaches include using ident authentication or file system permissions to restrict connections. See for more information. + linkend="client-authentication"> for more information.) diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 70a4aba94bf..aad8d93100e 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -1,14 +1,23 @@ - Database Users and Permissions + Database Users and Privileges - Managing database users and their privileges is in concept similar - to managing the users of a Unix operating system, but the details - are not identical. + Every database cluster contains a set of database users. Those + users are separate from the users managed by the operating system on + which the server runs. Users own database objects (for example, + tables) and can assign privileges on those objects to other users to + control who has access to which object. + + + + This chapter describes how to create and manage users and introduces + the privilege system. More information about the various types of + database objects and the effects of privileges can be found in + &cite-user;. @@ -27,19 +36,27 @@ CREATE USER name name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. To remove an existing user, use the analogous - DROP USER command. + DROP USER command: + +DROP USER name + - For convenience, the shell scripts createuser - and dropuser are provided as wrappers around these SQL - commands. + For convenience, the programs createuser + and dropuser are provided as wrappers + around these SQL commands that can be called from the shell command + line: + +createuser name +dropuser name + In order to bootstrap the database system, a freshly initialized system always contains one predefined user. This user will have the - fixed id 1, and by default (unless altered when running + fixed ID 1, and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this user will be named @@ -48,21 +65,34 @@ CREATE USER name - The user name to use for a particular database connection is - indicated by the client that is initiating the connection request - in an application-specific fashion. For example, the - psql program uses the - command line option to indicate the user to connect as. The set of - database users a given client connection may connect as is - determined by the client authentication setup, as explained in + Exactly one user identity is active for a connection to the + database server. The user name to use for a particular database + connection is indicated by the client that is initiating the + connection request in an application-specific fashion. For example, + the psql program uses the + command line option to indicate the user to + connect as. Many applications assume the name of the current + operating system user by default (including + createuser and psql). Therefore it + is convenient to maintain a naming correspondence between the two + user sets. + + + + The set of database users a given client connection may connect as + is determined by the client authentication setup, as explained in . (Thus, a client is not necessarily limited to connect as the user with the same name as its operating system user, in the same way a person is not - constrained in its login name by her real name.) + constrained in its login name by her real name.) Since the user + identity determines the set of privileges available to a connected + client, it is important to carefully configure this when setting up + a multiuser environment. + - - User attributes + + User Attributes A database user may have a number of attributes that define its @@ -115,7 +145,23 @@ CREATE USER name See the reference pages for CREATE USER and ALTER USER for details. - + + + A user can also set personal defaults for many of the run-time + configuration settings described in . For example, if for some reason you + want to disable index scans (hint: not a good idea) anytime you + connect, you can use + +ALTER USER myname SET enable_indexscan TO off; + + This will save the setting (but not set it immediately) and in + subsequent connections it will appear as though SET geqo + TO off; had been called right before the session started. + You can still alter this setting during the session; it will only + be the default. To undo any such setting, use ALTER USER + username RESET varname;. + @@ -123,7 +169,7 @@ CREATE USER name As in Unix, groups are a way of logically grouping users to ease - management of permissions: permissions can be granted to, or revoked + management of privileges: privileges can be granted to, or revoked from, a group as a whole. To create a group, use CREATE GROUP name @@ -203,11 +249,7 @@ REVOKE ALL ON accounts FROM PUBLIC; server that other users may execute without knowing it. Hence, both mechanisms permit users to Trojan horse others with relative impunity. The only real protection is tight - control over who can define functions (e.g., write to relations - with SQL fields) and triggers. Audit trails and alerters on the - system catalogs pg_class, - pg_shadow and pg_group are also - possible. + control over who can define functions. @@ -219,7 +261,6 @@ REVOKE ALL ON accounts FROM PUBLIC; system access controls. This is an inherent problem with user-defined C functions. - diff --git a/doc/src/sgml/user.sgml b/doc/src/sgml/user.sgml index 22127be7556..f7ed4448add 100644 --- a/doc/src/sgml/user.sgml +++ b/doc/src/sgml/user.sgml @@ -1,5 +1,5 @@ @@ -24,7 +24,6 @@ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/user.sgml,v 1.31 2002/08/05 19:43:31 &typeconv; &indices; &mvcc; - &manage; &perform; -- 2.39.5