diff options
Diffstat (limited to 'doc/src/sgml/man7/CREATE_ROLE.7')
| -rw-r--r-- | doc/src/sgml/man7/CREATE_ROLE.7 | 429 |
1 files changed, 429 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/CREATE_ROLE.7 b/doc/src/sgml/man7/CREATE_ROLE.7 new file mode 100644 index 0000000..7677c2d --- /dev/null +++ b/doc/src/sgml/man7/CREATE_ROLE.7 @@ -0,0 +1,429 @@ +'\" t +.\" Title: CREATE ROLE +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/> +.\" Date: 2021 +.\" Manual: PostgreSQL 13.4 Documentation +.\" Source: PostgreSQL 13.4 +.\" Language: English +.\" +.TH "CREATE ROLE" "7" "2021" "PostgreSQL 13.4" "PostgreSQL 13.4 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +CREATE_ROLE \- define a new database role +.SH "SYNOPSIS" +.sp +.nf +CREATE ROLE \fIname\fR [ [ WITH ] \fIoption\fR [ \&.\&.\&. ] ] + +where \fIoption\fR can be: + + SUPERUSER | NOSUPERUSER + | CREATEDB | NOCREATEDB + | CREATEROLE | NOCREATEROLE + | INHERIT | NOINHERIT + | LOGIN | NOLOGIN + | REPLICATION | NOREPLICATION + | BYPASSRLS | NOBYPASSRLS + | CONNECTION LIMIT \fIconnlimit\fR + | [ ENCRYPTED ] PASSWORD \*(Aq\fIpassword\fR\*(Aq | PASSWORD NULL + | VALID UNTIL \*(Aq\fItimestamp\fR\*(Aq + | IN ROLE \fIrole_name\fR [, \&.\&.\&.] + | IN GROUP \fIrole_name\fR [, \&.\&.\&.] + | ROLE \fIrole_name\fR [, \&.\&.\&.] + | ADMIN \fIrole_name\fR [, \&.\&.\&.] + | USER \fIrole_name\fR [, \&.\&.\&.] + | SYSID \fIuid\fR +.fi +.SH "DESCRIPTION" +.PP +\fBCREATE ROLE\fR +adds a new role to a +PostgreSQL +database cluster\&. A role is an entity that can own database objects and have database privileges; a role can be considered a +\(lquser\(rq, a +\(lqgroup\(rq, or both depending on how it is used\&. Refer to +Chapter\ \&21 +and +Chapter\ \&20 +for information about managing users and authentication\&. You must have +CREATEROLE +privilege or be a database superuser to use this command\&. +.PP +Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster\&. +.SH "PARAMETERS" +.PP +\fIname\fR +.RS 4 +The name of the new role\&. +.RE +.PP +SUPERUSER +.br +NOSUPERUSER +.RS 4 +These clauses determine whether the new role is a +\(lqsuperuser\(rq, who can override all access restrictions within the database\&. Superuser status is dangerous and should be used only when really needed\&. You must yourself be a superuser to create a new superuser\&. If not specified, +NOSUPERUSER +is the default\&. +.RE +.PP +CREATEDB +.br +NOCREATEDB +.RS 4 +These clauses define a role\*(Aqs ability to create databases\&. If +CREATEDB +is specified, the role being defined will be allowed to create new databases\&. Specifying +NOCREATEDB +will deny a role the ability to create databases\&. If not specified, +NOCREATEDB +is the default\&. +.RE +.PP +CREATEROLE +.br +NOCREATEROLE +.RS 4 +These clauses determine whether a role will be permitted to create new roles (that is, execute +\fBCREATE ROLE\fR)\&. A role with +CREATEROLE +privilege can also alter and drop other roles\&. If not specified, +NOCREATEROLE +is the default\&. +.RE +.PP +INHERIT +.br +NOINHERIT +.RS 4 +These clauses determine whether a role +\(lqinherits\(rq +the privileges of roles it is a member of\&. A role with the +INHERIT +attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of\&. Without +INHERIT, membership in another role only grants the ability to +\fBSET ROLE\fR +to that other role; the privileges of the other role are only available after having done so\&. If not specified, +INHERIT +is the default\&. +.RE +.PP +LOGIN +.br +NOLOGIN +.RS 4 +These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection\&. A role having the +LOGIN +attribute can be thought of as a user\&. Roles without this attribute are useful for managing database privileges, but are not users in the usual sense of the word\&. If not specified, +NOLOGIN +is the default, except when +\fBCREATE ROLE\fR +is invoked through its alternative spelling +CREATE USER (\fBCREATE_USER\fR(7))\&. +.RE +.PP +REPLICATION +.br +NOREPLICATION +.RS 4 +These clauses determine whether a role is a replication role\&. A role must have this attribute (or be a superuser) in order to be able to connect to the server in replication mode (physical or logical replication) and in order to be able to create or drop replication slots\&. A role having the +REPLICATION +attribute is a very highly privileged role, and should only be used on roles actually used for replication\&. If not specified, +NOREPLICATION +is the default\&. You must be a superuser to create a new role having the +REPLICATION +attribute\&. +.RE +.PP +BYPASSRLS +.br +NOBYPASSRLS +.RS 4 +These clauses determine whether a role bypasses every row\-level security (RLS) policy\&. +NOBYPASSRLS +is the default\&. You must be a superuser to create a new role having the +BYPASSRLS +attribute\&. +.sp +Note that pg_dump will set +row_security +to +OFF +by default, to ensure all contents of a table are dumped out\&. If the user running pg_dump does not have appropriate permissions, an error will be returned\&. However, superusers and the owner of the table being dumped always bypass RLS\&. +.RE +.PP +CONNECTION LIMIT \fIconnlimit\fR +.RS 4 +If role can log in, this specifies how many concurrent connections the role can make\&. \-1 (the default) means no limit\&. Note that only normal connections are counted towards this limit\&. Neither prepared transactions nor background worker connections are counted towards this limit\&. +.RE +.PP +[ ENCRYPTED ] PASSWORD \*(Aq\fIpassword\fR\*(Aq +.br +PASSWORD NULL +.RS 4 +Sets the role\*(Aqs password\&. (A password is only of use for roles having the +LOGIN +attribute, but you can nonetheless define one for roles without it\&.) If you do not plan to use password authentication you can omit this option\&. If no password is specified, the password will be set to null and password authentication will always fail for that user\&. A null password can optionally be written explicitly as +PASSWORD NULL\&. +.if n \{\ +.sp +.\} +.RS 4 +.it 1 an-trap +.nr an-no-space-flag 1 +.nr an-break-flag 1 +.br +.ps +1 +\fBNote\fR +.ps -1 +.br +Specifying an empty string will also set the password to null, but that was not the case before +PostgreSQL +version 10\&. In earlier versions, an empty string could be used, or not, depending on the authentication method and the exact version, and libpq would refuse to use it in any case\&. To avoid the ambiguity, specifying an empty string should be avoided\&. +.sp .5v +.RE +The password is always stored encrypted in the system catalogs\&. The +ENCRYPTED +keyword has no effect, but is accepted for backwards compatibility\&. The method of encryption is determined by the configuration parameter +password_encryption\&. If the presented password string is already in MD5\-encrypted or SCRAM\-encrypted format, then it is stored as\-is regardless of +\fIpassword_encryption\fR +(since the system cannot decrypt the specified encrypted password string, to encrypt it in a different format)\&. This allows reloading of encrypted passwords during dump/restore\&. +.RE +.PP +VALID UNTIL \*(Aq\fItimestamp\fR\*(Aq +.RS 4 +The +VALID UNTIL +clause sets a date and time after which the role\*(Aqs password is no longer valid\&. If this clause is omitted the password will be valid for all time\&. +.RE +.PP +IN ROLE \fIrole_name\fR +.RS 4 +The +IN ROLE +clause lists one or more existing roles to which the new role will be immediately added as a new member\&. (Note that there is no option to add the new role as an administrator; use a separate +\fBGRANT\fR +command to do that\&.) +.RE +.PP +IN GROUP \fIrole_name\fR +.RS 4 +IN GROUP +is an obsolete spelling of +IN ROLE\&. +.RE +.PP +ROLE \fIrole_name\fR +.RS 4 +The +ROLE +clause lists one or more existing roles which are automatically added as members of the new role\&. (This in effect makes the new role a +\(lqgroup\(rq\&.) +.RE +.PP +ADMIN \fIrole_name\fR +.RS 4 +The +ADMIN +clause is like +ROLE, but the named roles are added to the new role +WITH ADMIN OPTION, giving them the right to grant membership in this role to others\&. +.RE +.PP +USER \fIrole_name\fR +.RS 4 +The +USER +clause is an obsolete spelling of the +ROLE +clause\&. +.RE +.PP +SYSID \fIuid\fR +.RS 4 +The +SYSID +clause is ignored, but is accepted for backwards compatibility\&. +.RE +.SH "NOTES" +.PP +Use +ALTER ROLE (\fBALTER_ROLE\fR(7)) +to change the attributes of a role, and +DROP ROLE (\fBDROP_ROLE\fR(7)) +to remove a role\&. All the attributes specified by +\fBCREATE ROLE\fR +can be modified by later +\fBALTER ROLE\fR +commands\&. +.PP +The preferred way to add and remove members of roles that are being used as groups is to use +\fBGRANT\fR(7) +and +\fBREVOKE\fR(7)\&. +.PP +The +VALID UNTIL +clause defines an expiration time for a password only, not for the role +per se\&. In particular, the expiration time is not enforced when logging in using a non\-password\-based authentication method\&. +.PP +The +INHERIT +attribute governs inheritance of grantable privileges (that is, access privileges for database objects and role memberships)\&. It does not apply to the special role attributes set by +\fBCREATE ROLE\fR +and +\fBALTER ROLE\fR\&. For example, being a member of a role with +CREATEDB +privilege does not immediately grant the ability to create databases, even if +INHERIT +is set; it would be necessary to become that role via +SET ROLE (\fBSET_ROLE\fR(7)) +before creating a database\&. +.PP +The +INHERIT +attribute is the default for reasons of backwards compatibility: in prior releases of +PostgreSQL, users always had access to all privileges of groups they were members of\&. However, +NOINHERIT +provides a closer match to the semantics specified in the SQL standard\&. +.PP +Be careful with the +CREATEROLE +privilege\&. There is no concept of inheritance for the privileges of a +CREATEROLE\-role\&. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges)\&. For example, if the role +\(lquser\(rq +has the +CREATEROLE +privilege but not the +CREATEDB +privilege, nonetheless it can create a new role with the +CREATEDB +privilege\&. Therefore, regard roles that have the +CREATEROLE +privilege as almost\-superuser\-roles\&. +.PP +PostgreSQL +includes a program +\fBcreateuser\fR(1) +that has the same functionality as +\fBCREATE ROLE\fR +(in fact, it calls this command) but can be run from the command shell\&. +.PP +The +CONNECTION LIMIT +option is only enforced approximately; if two new sessions start at about the same time when just one connection +\(lqslot\(rq +remains for the role, it is possible that both will fail\&. Also, the limit is never enforced for superusers\&. +.PP +Caution must be exercised when specifying an unencrypted password with this command\&. The password will be transmitted to the server in cleartext, and it might also be logged in the client\*(Aqs command history or the server log\&. The command +\fBcreateuser\fR(1), however, transmits the password encrypted\&. Also, +\fBpsql\fR(1) +contains a command +\fB\epassword\fR +that can be used to safely change the password later\&. +.SH "EXAMPLES" +.PP +Create a role that can log in, but don\*(Aqt give it a password: +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE ROLE jonathan LOGIN; +.fi +.if n \{\ +.RE +.\} +.PP +Create a role with a password: +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE USER davide WITH PASSWORD \*(Aqjw8s0F4\*(Aq; +.fi +.if n \{\ +.RE +.\} +.sp +(\fBCREATE USER\fR +is the same as +\fBCREATE ROLE\fR +except that it implies +LOGIN\&.) +.PP +Create a role with a password that is valid until the end of 2004\&. After one second has ticked in 2005, the password is no longer valid\&. +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE ROLE miriam WITH LOGIN PASSWORD \*(Aqjw8s0F4\*(Aq VALID UNTIL \*(Aq2005\-01\-01\*(Aq; +.fi +.if n \{\ +.RE +.\} +.PP +Create a role that can create databases and manage roles: +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE ROLE admin WITH CREATEDB CREATEROLE; +.fi +.if n \{\ +.RE +.\} +.SH "COMPATIBILITY" +.PP +The +\fBCREATE ROLE\fR +statement is in the SQL standard, but the standard only requires the syntax +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE ROLE \fIname\fR [ WITH ADMIN \fIrole_name\fR ] +.fi +.if n \{\ +.RE +.\} +.sp +Multiple initial administrators, and all the other options of +\fBCREATE ROLE\fR, are +PostgreSQL +extensions\&. +.PP +The SQL standard defines the concepts of users and roles, but it regards them as distinct concepts and leaves all commands defining users to be specified by each database implementation\&. In +PostgreSQL +we have chosen to unify users and roles into a single kind of entity\&. Roles therefore have many more optional attributes than they do in the standard\&. +.PP +The behavior specified by the SQL standard is most closely approximated by giving users the +NOINHERIT +attribute, while roles are given the +INHERIT +attribute\&. +.SH "SEE ALSO" +SET ROLE (\fBSET_ROLE\fR(7)), ALTER ROLE (\fBALTER_ROLE\fR(7)), DROP ROLE (\fBDROP_ROLE\fR(7)), \fBGRANT\fR(7), \fBREVOKE\fR(7), \fBcreateuser\fR(1) |