From 4ceb2d0cb619bba2ecbf5d72a10c8fa7ba321366 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 14 Jan 2000 22:11:38 +0000 Subject: * User management commands no longer user pg_exec_query_dest -> more robust * Let unprivileged users change their own passwords. * The password is now an Sconst in the parser, which better reflects its text datatype and also forces users to quote them. * If your password is NULL you won't be written to the password file, meaning you can't connect until you have a password set up (if you use password authentication). * When you drop a user that owns a database you get an error. The database is not gone. --- doc/src/sgml/Makefile | 10 ++- doc/src/sgml/ref/allfiles.sgml | 5 +- doc/src/sgml/ref/alter_group.sgml | 162 ++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/alter_user.sgml | 111 ++++++++--------------- doc/src/sgml/ref/commands.sgml | 5 +- doc/src/sgml/ref/create_group.sgml | 176 +++++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/create_user.sgml | 137 ++++++++--------------------- doc/src/sgml/ref/drop_group.sgml | 138 +++++++++++++++++++++++++++++ doc/src/sgml/ref/drop_user.sgml | 65 +++++++------- 9 files changed, 598 insertions(+), 211 deletions(-) create mode 100644 doc/src/sgml/ref/alter_group.sgml create mode 100644 doc/src/sgml/ref/create_group.sgml create mode 100644 doc/src/sgml/ref/drop_group.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/Makefile b/doc/src/sgml/Makefile index 59e8431a666..864d4621c24 100644 --- a/doc/src/sgml/Makefile +++ b/doc/src/sgml/Makefile @@ -8,7 +8,7 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/doc/src/sgml/Makefile,v 1.12 1999/12/05 20:21:59 momjian Exp $ +# $Header: /cvsroot/pgsql/doc/src/sgml/Makefile,v 1.13 2000/01/14 22:11:31 petere Exp $ # #---------------------------------------------------------------------------- @@ -85,15 +85,17 @@ APPLICATIONS= createdb.sgml createuser.sgml \ psql-ref.sgml \ vacuumdb.sgml -COMMANDS= abort.sgml alter_table.sgml alter_user.sgml \ +COMMANDS= abort.sgml alter_group.sgml alter_table.sgml alter_user.sgml \ begin.sgml \ close.sgml cluster.sgml commit.sgml copy.sgml \ - create_aggregate.sgml create_database.sgml create_function.sgml create_index.sgml \ + create_aggregate.sgml create_database.sgml create_function.sgml create_group.sgml \ + create_index.sgml \ create_language.sgml create_operator.sgml create_rule.sgml create_sequence.sgml \ create_table.sgml create_table_as.sgml create_trigger.sgml create_type.sgml \ create_user.sgml create_view.sgml \ declare.sgml delete.sgml \ - drop_aggregate.sgml drop_database.sgml drop_function.sgml drop_index.sgml \ + drop_aggregate.sgml drop_database.sgml drop_function.sgml drop_group.sgml \ + drop_index.sgml \ drop_language.sgml drop_operator.sgml drop_rule.sgml drop_sequence.sgml \ drop_table.sgml drop_trigger.sgml drop_type.sgml drop_user.sgml drop_view.sgml \ explain.sgml fetch.sgml grant.sgml \ diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index dc04300f2a2..09516b166b0 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -40,6 +40,7 @@ Complete list of usable sgml source files in this directory. + @@ -50,6 +51,7 @@ Complete list of usable sgml source files in this directory. + @@ -66,6 +68,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_group.sgml b/doc/src/sgml/ref/alter_group.sgml new file mode 100644 index 00000000000..debbe979e1a --- /dev/null +++ b/doc/src/sgml/ref/alter_group.sgml @@ -0,0 +1,162 @@ + + + + + + ALTER GROUP + + SQL - Language Statements + + + + ALTER GROUP + + + Add users to a group, remove users from a group + + + + + 2000-01-14 + + +ALTER GROUP name ADD USER username [, ... ] +ALTER GROUP name DROP USER username [, ... ] + + + + + 2000-01-14 + + + Inputs + + + + + + name + + + The name of the group to modify. + + + + + + username + + + Users which are to be added or removed from the group. The user + names must exist. + + + + + + + + + + + 2000-01-14 + + + Outputs + + + + + ALTER GROUP + + + Message returned if the alteration was successful. + + + + + + + + + + + + 2000-01-14 + + + Description + + + ALTER GROUP is used to change add users to a group or + remove them from a group. Only database superusers can use this command. + Adding a user to a group does not create the user. Similarly, removing + a user from a group does not drop the user itself. + + + Use + to create a new group and to remove a group. + + + + + + Usage + + + Add users to a group: + + +ALTER GROUP staff ADD USER karl, john + + + Remove a user from a group + + +ALTER GROUP workers DROP USER beth + + + + + + + + Compatibility + + + + + 2000-01-14 + + + SQL92 + + + There is no ALTER GROUP statement in + SQL92. The concept of roles is + similar. + + + + + + diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml index 75bcca86546..f3ce32bb16f 100644 --- a/doc/src/sgml/ref/alter_user.sgml +++ b/doc/src/sgml/ref/alter_user.sgml @@ -1,5 +1,5 @@ @@ -24,11 +24,8 @@ Postgres documentation ALTER USER username - [ WITH - [ SYSID uid ] - [ PASSWORD password ] ] + [ WITH PASSWORD 'password' ] [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] - [ IN GROUP groupname [, ...] ] [ VALID UNTIL 'abstime' ] @@ -40,24 +37,19 @@ ALTER USER username Inputs - - Refer to CREATE USER for a detailed description of each - clause. - - - username + username - The Postgres account name of the user whose details are to be altered. + The name of the user whose details are to be altered. - password + password The new password to be used for this account. @@ -66,36 +58,36 @@ ALTER USER username - uid + CREATEDB + NOCREATEDB - - The new PostgreSQL user id of the user. - Since this number is used as a key into the - pg_shadow/pg_user table - throughout the system catalogs, it is not recommended that you change - it unless the user in question does not own anything at all and/or - you really know what you are doing. Note that it is not necessary that - database and UNIX user ids match, but some people - choose to keep the numbers the same. + + These clauses define a user's ability to create databases. + If CREATEDB is specified, the user being defined will + be allowed to create his own databases. Using NOCREATEDB + will deny a user the ability to create databases. - groupname + CREATEUSER + NOCREATEUSER - The name of an access group into which this account is to be put. + These clauses determine whether a user will be permitted to + create new users himself. This option will also make the user + a superuser who can override all access restrictions. - abstime + abstime The date (and, optionally, the time) - at which this user's access is to be terminated. + at which this user's password is to expire. @@ -113,9 +105,7 @@ ALTER USER username - -ALTER USER - + ALTER USER Message returned if the alteration was successful. @@ -125,7 +115,7 @@ ALTER USER -ERROR: alterUser: user "username" does not exist +ERROR: ALTER USER: user "username" does not exist @@ -148,39 +138,15 @@ ERROR: alterUser: user "username" does not exist ALTER USER is used to change the attributes of a user's - Postgres account. - Also, it is only possible for the - Postgres - user or any user with read and modify permissions on - pg_shadow to alter user passwords. + PostgreSQL account. Only a database superuser + can change privileges and password expiration with this command. Ordinary + users can only change their own password. - - If any of the clauses of the alter user statement are - omitted, the corresponding value in the pg_shadow table - is left unchanged. + Use + to create a new user and to remove a user. - - - - 1998-09-08 - - - Notes - - - ALTER USER - is a Postgres - language extension. - - - Refer to CREATE/DROP USER - to create or remove a user account. - - - The IN GROUP clause is not yet implemented. - - @@ -190,34 +156,29 @@ ERROR: alterUser: user "username" does not exist Change a user password: - -ALTER USER davide WITH PASSWORD hu8jmn3; - + +ALTER USER davide WITH PASSWORD 'hu8jmn3'; + Change a user's valid until date - + ALTER USER manuel VALID UNTIL 'Jan 31 2030'; - + Change a user's valid until date, specifying that his authorisation should expire at midday on 4th May 1998 using the time zone which is one hour ahead of UTC - + ALTER USER chris VALID UNTIL 'May 4 12:00:00 1998 +1'; - + Give a user the ability to create other users and new databases. - + ALTER USER miriam CREATEUSER CREATEDB; - - - Place a user in two groups + - -ALTER USER miriam IN GROUP sales, payroll; - diff --git a/doc/src/sgml/ref/commands.sgml b/doc/src/sgml/ref/commands.sgml index a8f32017393..8516904dbf1 100644 --- a/doc/src/sgml/ref/commands.sgml +++ b/doc/src/sgml/ref/commands.sgml @@ -1,5 +1,5 @@ @@ -14,6 +14,7 @@ Postgres documentation &abort; + &alterGroup; &alterTable; &alterUser; &begin; @@ -24,6 +25,7 @@ Postgres documentation &createAggregate; &createDatabase; &createFunction; + &createGroup; &createIndex; &createLanguage; &createOperator; @@ -40,6 +42,7 @@ Postgres documentation &dropAggregate; &dropDatabase; &dropFunction; + &dropGroup; &dropIndex; &dropLanguage; &dropOperator; diff --git a/doc/src/sgml/ref/create_group.sgml b/doc/src/sgml/ref/create_group.sgml new file mode 100644 index 00000000000..52023723016 --- /dev/null +++ b/doc/src/sgml/ref/create_group.sgml @@ -0,0 +1,176 @@ + + + + + + CREATE GROUP + + SQL - Language Statements + + + + CREATE GROUP + + + Creates a new group + + + + + 2000-01-14 + + +CREATE GROUP name + [ WITH + [ SYSID gid ] + [ USER username [, ...] ] ] + + + + + 2000-01-14 + + + Inputs + + + + + + name + + + The name of the group. + + + + + + gid + + + The SYSID clause can be used to choose + the PostgreSQL group id of the new + group. It is not necessary to do so, however. + + + If this is not specified, the highest assigned group id plus one, + starting at 1, will be used as default. + + + + + + username + + + A list of users to include in the group. The users must already exist. + + + + + + + + + + + 2000-01-14 + + + Outputs + + + + + + CREATE GROUP + + + Message returned if the command completes successfully. + + + + + + + + + + + 2000-01-14 + + + Description + + + CREATE GROUP will create a new group in the database installation. + Refer to the adminstrator's guide for information about using groups + for authentication. + You must be a database superuser to use this command. + + + Use + to change a group's membership, and to remove a group. + + + + + Usage + + + Create an empty group: + +CREATE GROUP staff + + + + + Create a group with members: + +CREATE GROUP marketing WITH USER jonathan, david + + + + + + + Compatibility + + + + + 2000-01-14 + + + SQL92 + + + + There is no CREATE GROUP statement in SQL92. + Roles are similar in concept to groups. + + + + + + diff --git a/doc/src/sgml/ref/create_user.sgml b/doc/src/sgml/ref/create_user.sgml index 2f5d1d7feea..6994837751d 100644 --- a/doc/src/sgml/ref/create_user.sgml +++ b/doc/src/sgml/ref/create_user.sgml @@ -1,5 +1,5 @@ @@ -15,7 +15,7 @@ Postgres documentation CREATE USER - Creates account information for a new user + Creates a new database user @@ -26,7 +26,7 @@ Postgres documentation CREATE USER username [ WITH [ SYSID uid ] - [ PASSWORD password ] ] + [ PASSWORD 'password' ] ] [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ] [ IN GROUP groupname [, ...] ] [ VALID UNTIL 'abstime' ] @@ -61,13 +61,6 @@ CREATE USER username match the UNIX user ids, but some people choose to keep the numbers the same. - - If you still want the - OS user id and the usesysid to match - for any given user, - use the createuser script provided with - the Postgres distribution. - If this is not specified, the highest assigned user id plus one will be used as default. @@ -79,30 +72,11 @@ CREATE USER username password - The PASSWORD clause sets the user's password within - the "pg_shadow" table. For this reason, - "pg_shadow" is no - longer accessible to the instance of - Postgres that the - Postgres - user's password is initially set to NULL. - - - When a - user's password in the "pg_shadow" - table is NULL, user - authentication proceeds as it historically has (HBA, - PG_PASSWORD, etc). However, if a password is set for a - user, a new authentication system supplants any other - configured for the Postgres - instance, and the password - stored in the "pg_shadow" table is used - for authentication. - For more details on how this authentication system - functions see pg_crypt(3). If the WITH PASSWORD clause is - omitted, the user's password is set to the empty - string which equates to a NULL value in the authentication - system mentioned above. + Sets the user's password. If you do not plan to use password + authentication you can omit this option, otherwise the user + won't be able to connect to a password-authenticated server. + See pg_hba.conf(5) or the administrator's guide for details on + how to set up authentication mechanisms. @@ -127,8 +101,8 @@ CREATE USER username These clauses determine whether a user will be permitted to - create new - users in an instance of Postgres. + create new users himself. This option will also make the user + a superuser who can override all access restrictions. Omitting this clause will set the user's value of this attribute to be NOCREATEUSER. @@ -149,15 +123,8 @@ CREATE USER username The VALID UNTIL clause sets an absolute time after which the - user's Postgres - login is no longer valid. Please note that - if a user does not have a password defined in the - "pg_shadow" - table, the valid until date will not be checked - during user authentication. If this clause is omitted, - a NULL value is stored in "pg_shadow" - for this attribute, - and the login will be valid for all time. + user's password is no longer valid. + If this clause is omitted the login will be valid for all time. @@ -176,9 +143,7 @@ CREATE USER username - -CREATE USER - + CREATE USER Message returned if the command completes successfully. @@ -199,61 +164,38 @@ CREATE USER CREATE USER will add a new user to an instance of - PostgreSQL. + PostgreSQL. Refer to the adminstrator's + guide for information about managing users and authentication. + You must be a database superuser to use this command. - - - - 1998-09-21 - - - Notes - - - CREATE USER statement is a - Postgres language extension. - - - Use DROP USER or ALTER USER - statements to remove or modify a user account. - - - Refer to the pg_shadow table for further information. - - - Table "pg_shadow" - Attribute | Type | Extra --------------+---------+------- - usename | name | - usesysid | int4 | - usecreatedb | bool | - usetrace | bool | - usesuper | bool | - usecatupd | bool | - passwd | text | - valuntil | abstime | - - - - + + Use + to change a user's password and privileges, and to remove a user. + Use ALTER GROUP to add or remove the user from other groups. + PostgreSQL + comes with a script + which has the same functionality as this command (in fact, it calls this command) + but can be run from the command shell. + + Usage Create a user with no password: - - + CREATE USER jonathan - + Create a user with a password: - - -CREATE USER davide WITH PASSWORD "jw8s0F4" - + +CREATE USER davide WITH PASSWORD 'jw8s0F4' + @@ -261,17 +203,16 @@ CREATE USER davide WITH PASSWORD "jw8s0F4" Note that after one second has ticked in 2002, the account is not valid: - -CREATE USER miriam WITH PASSWORD "jw8s0F4" VALID UNTIL 'Jan 1 2002' - + +CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL 'Jan 1 2002' + Create an account where the user can create databases: - - -CREATE USER manuel WITH PASSWORD "jw8s0F4" CREATEDB - + +CREATE USER manuel WITH PASSWORD 'jw8s0F4' CREATEDB + diff --git a/doc/src/sgml/ref/drop_group.sgml b/doc/src/sgml/ref/drop_group.sgml new file mode 100644 index 00000000000..0d7db1ef098 --- /dev/null +++ b/doc/src/sgml/ref/drop_group.sgml @@ -0,0 +1,138 @@ + + + + + + DROP GROUP + + SQL - Language Statements + + + + DROP GROUP + + + Removes a group + + + + + 2000-01-14 + + +DROP GROUP name + + + + + 2000-01-14 + + + Inputs + + + + + + name + + + The name of an existing group. + + + + + + + + + + 2000-01-14 + + + Outputs + + + + + DROP GROUP + + + The message returned if the group is successfully deleted. + + + + + + + + + + + + 2000-01-14 + + + Description + + + DROP GROUP removes the specified group from the database. + The users in the group are not deleted. + + + Use + to add new groups, and to change a group's membership. + + + + + + Usage + + + To drop a group: + +DROP GROUP staff; + + + + + + + Compatibility + + + + + 2000-01-14 + + + SQL92 + + + There is no DROP GROUP in SQL92. + + + + + + diff --git a/doc/src/sgml/ref/drop_user.sgml b/doc/src/sgml/ref/drop_user.sgml index 27f339f82dc..b2a96e0090f 100644 --- a/doc/src/sgml/ref/drop_user.sgml +++ b/doc/src/sgml/ref/drop_user.sgml @@ -1,5 +1,5 @@ @@ -15,7 +15,7 @@ Postgres documentation DROP USER - Removes an user account information + Removes a user @@ -58,18 +58,17 @@ DROP USER name - -DROP - + DROP USER The message returned if the user is successfully deleted. + -ERROR: removeUser: user "name" does not exist. +ERROR: DROP USER: user "name" does not exist @@ -77,6 +76,18 @@ ERROR: removeUser: user "name" does + + + +DROP USER: user "name" owns database "name", cannot be removed + + + + You must drop the database first or change its ownership. + + + + @@ -90,30 +101,20 @@ ERROR: removeUser: user "name" does Description - DROP USER removes the specified - user from the database, - along with any databases owned by the user. It - does not remove tables, views, or triggers owned by the - named user in databases not owned by the user. + DROP USER removes the specified user from the database. + It does not remove tables, views, or other objects owned by the user. If the + user owns any database you get an error. - - - - 1998-09-22 - - - Notes - - - DROP USER is a Postgres - language extension. - - - Refer to CREATE USER and - ALTER USER for information on - how to create or modify user accounts. - - + + Use + to add new users, and to change a user's properties. + PostgreSQL + comes with a script + which has the same functionality as this command (in fact, it calls this command) + but can be run from the command shell. + @@ -122,9 +123,9 @@ ERROR: removeUser: user "name" does To drop a user account: - -DROP USER Jonathan; - + +DROP USER jonathan; + -- cgit v1.2.3