1CREATE ROLE() SQL Commands CREATE ROLE()
2
6 CREATE ROLE - define a new database role
7
10 CREATE ROLE name [ [ WITH ] option [ ... ] ]
11 where option can be:
13 SUPERUSER | NOSUPERUSER
15 | CREATEDB | NOCREATEDB
16 | CREATEROLE | NOCREATEROLE
17 | CREATEUSER | NOCREATEUSER
18 | INHERIT | NOINHERIT
19 | LOGIN | NOLOGIN
20 | CONNECTION LIMIT connlimit
21 | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
22 | VALID UNTIL 'timestamp'
23 | IN ROLE rolename [, ...]
24 | IN GROUP rolename [, ...]
25 | ROLE rolename [, ...]
26 | ADMIN rolename [, ...]
27 | USER rolename [, ...]
28 | SYSID uid
29
32 CREATE ROLE adds a new role to a PostgreSQL database cluster. A role is
33 an entity that can own database objects and have database privileges; a
34 role can be considered a ``user'', a ``group'', or both depending on
35 how it is used. Refer to in the documentation and in the documentation
36 for information about managing users and authentication. You must have
37 CREATEROLE privilege or be a database superuser to use this command.
38 Note that roles are defined at the database cluster level, and so are
40 valid in all databases in the cluster.
41
43 name The name of the new role.
44 SUPERUSER
46 NOSUPERUSER
48 These clauses determine whether the new role is a ``superuser'',
49 who can override all access restrictions within the database.
50 Superuser status is dangerous and should be used only when
51 really needed. You must yourself be a superuser to create a new
52 superuser. If not specified, NOSUPERUSER is the default.
53 CREATEDB
55 NOCREATEDB
57 These clauses define a role's ability to create databases. If
58 CREATEDB is specified, the role being defined will be allowed to
59 create new databases. Specifying NOCREATEDB will deny a role the
60 ability to create databases. If not specified, NOCREATEDB is the
61 default.
62 CREATEROLE
64 NOCREATEROLE
66 These clauses determine whether a role will be permitted to cre‐
67 ate new roles (that is, execute CREATE ROLE). A role with CRE‐
68 ATEROLE privilege can also alter and drop other roles. If not
69 specified, NOCREATEROLE is the default.
70 CREATEUSER
72 NOCREATEUSER
74 These clauses are an obsolete, but still accepted, spelling of
75 SUPERUSER and NOSUPERUSER. Note that they are not equivalent to
76 CREATEROLE as one might naively expect!
77 INHERIT
79 NOINHERIT
81 These clauses determine whether a role ``inherits'' the privi‐
82 leges of roles it is a member of. A role with the INHERIT
83 attribute can automatically use whatever database privileges
84 have been granted to all roles it is directly or indirectly a
85 member of. Without INHERIT, membership in another role only
86 grants the ability to SET ROLE to that other role; the privi‐
87 leges of the other role are only available after having done so.
88 If not specified, INHERIT is the default.
89 LOGIN
91 NOLOGIN
93 These clauses determine whether a role is allowed to log in;
94 that is, whether the role can be given as the initial session
95 authorization name during client connection. A role having the
96 LOGIN attribute can be thought of as a user. Roles without this
97 attribute are useful for managing database privileges, but are
98 not users in the usual sense of the word. If not specified,
99 NOLOGIN is the default, except when CREATE ROLE is invoked
100 through its alternate spelling CREATE USER.
101 CONNECTION LIMIT connlimit
103 If role can log in, this specifies how many concurrent connec‐
104 tions the role can make. -1 (the default) means no limit.
105 PASSWORD password
107 Sets the role's password. (A password is only of use for roles
108 having the LOGIN attribute, but you can nonetheless define one
109 for roles without it.) If you do not plan to use password
110 authentication you can omit this option. If no password is spec‐
111 ified, the password will be set to null and password authentica‐
112 tion will always fail for that user. A null password can option‐
113 ally be written explicitly as PASSWORD NULL.
114 ENCRYPTED
116 UNENCRYPTED
118 These key words control whether the password is stored encrypted
119 in the system catalogs. (If neither is specified, the default
120 behavior is determined by the configuration parameter pass‐
121 word_encryption.) If the presented password string is already in
122 MD5-encrypted format, then it is stored encrypted as-is, regard‐
123 less of whether ENCRYPTED or UNENCRYPTED is specified (since the
124 system cannot decrypt the specified encrypted password string).
125 This allows reloading of encrypted passwords during
126 dump/restore.
127 Note that older clients may lack support for the MD5 authentica‐
129 tion mechanism that is needed to work with passwords that are
130 stored encrypted.
131 VALID UNTIL 'timestamp'
133 The VALID UNTIL clause sets a date and time after which the
134 role's password is no longer valid. If this clause is omitted
135 the password will be valid for all time.
136 IN ROLE rolename
138 The IN ROLE clause lists one or more existing roles to which the
139 new role will be immediately added as a new member. (Note that
140 there is no option to add the new role as an administrator; use
141 a separate GRANT command to do that.)
142 IN GROUP rolename
144 IN GROUP is an obsolete spelling of IN ROLE.
145 ROLE rolename
147 The ROLE clause lists one or more existing roles which are auto‐
148 matically added as members of the new role. (This in effect
149 makes the new role a ``group''.)
150 ADMIN rolename
152 The ADMIN clause is like ROLE, but the named roles are added to
153 the new role WITH ADMIN OPTION, giving them the right to grant
154 membership in this role to others.
155 USER rolename
157 The USER clause is an obsolete spelling of the ROLE clause.
158 SYSID uid
160 The SYSID clause is ignored, but is accepted for backwards com‐
161 patibility.
162
164 Use ALTER ROLE [alter_role(7)] to change the attributes of a role, and
165 DROP ROLE [drop_role(7)] to remove a role. All the attributes specified
166 by CREATE ROLE can be modified by later ALTER ROLE commands.
167 The preferred way to add and remove members of roles that are being
169 used as groups is to use GRANT [grant(7)] and REVOKE [revoke(7)].
170 The VALID UNTIL clause defines an expiration time for a password only,
172 not for the role per se. In particular, the expiration time is not
173 enforced when logging in using a non-password-based authentication
174 method.
175 The INHERIT attribute governs inheritance of grantable privileges (that
177 is, access privileges for database objects and role memberships). It
178 does not apply to the special role attributes set by CREATE ROLE and
179 ALTER ROLE. For example, being a member of a role with CREATEDB privi‐
180 lege does not immediately grant the ability to create databases, even
181 if INHERIT is set; it would be necessary to become that role via SET
182 ROLE [set_role(7)] before creating a database.
183 The INHERIT attribute is the default for reasons of backwards compati‐
185 bility: in prior releases of PostgreSQL, users always had access to all
186 privileges of groups they were members of. However, NOINHERIT provides
187 a closer match to the semantics specified in the SQL standard.
188 Be careful with the CREATEROLE privilege. There is no concept of inher‐
190 itance for the privileges of a CREATEROLE-role. That means that even if
191 a role does not have a certain privilege but is allowed to create other
192 roles, it can easily create another role with different privileges than
193 its own (except for creating roles with superuser privileges). For
194 example, if the role ``user'' has the CREATEROLE privilege but not the
195 CREATEDB privilege, nonetheless it can create a new role with the CRE‐
196 ATEDB privilege. Therefore, regard roles that have the CREATEROLE priv‐
197 ilege as almost-superuser-roles.
198 PostgreSQL includes a program createuser [createuser(1)] that has the
200 same functionality as CREATE ROLE (in fact, it calls this command) but
201 can be run from the command shell.
202 The CONNECTION LIMIT option is only enforced approximately; if two new
204 sessions start at about the same time when just one connection ``slot''
205 remains for the role, it is possible that both will fail. Also, the
206 limit is never enforced for superusers.
207 Caution must be exercised when specifying an unencrypted password with
209 this command. The password will be transmitted to the server in cleart‐
210 ext, and it might also be logged in the client's command history or the
211 server log. The command createuser [createuser(1)], however, transmits
212 the password encrypted. Also, psql [psql(1)] contains a command \pass‐
213 word that can be used to safely change the password later.
214
216 Create a role that can log in, but don't give it a password:
217 CREATE ROLE jonathan LOGIN;
219 Create a role with a password:
222 CREATE USER davide WITH PASSWORD 'jw8s0F4';
224 (CREATE USER is the same as CREATE ROLE except that it implies LOGIN.)
226 Create a role with a password that is valid until the end of 2004.
228 After one second has ticked in 2005, the password is no longer valid.
229 CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';
231 Create a role that can create databases and manage roles:
234 CREATE ROLE admin WITH CREATEDB CREATEROLE;
236
239 The CREATE ROLE statement is in the SQL standard, but the standard only
240 requires the syntax
241 CREATE ROLE name [ WITH ADMIN rolename ]
243 Multiple initial administrators, and all the other options of CREATE
245 ROLE, are PostgreSQL extensions.
246 The SQL standard defines the concepts of users and roles, but it
248 regards them as distinct concepts and leaves all commands defining
249 users to be specified by each database implementation. In PostgreSQL we
250 have chosen to unify users and roles into a single kind of entity.
251 Roles therefore have many more optional attributes than they do in the
252 standard.
253 The behavior specified by the SQL standard is most closely approximated
255 by giving users the NOINHERIT attribute, while roles are given the
256 INHERIT attribute.
257
259 SET ROLE [set_role(7)], ALTER ROLE [alter_role(l)], DROP ROLE
260 [drop_role(l)], GRANT [grant(l)], REVOKE [revoke(l)], createuser(1)
261SQL - Language Statements 2008-06-08 CREATE ROLE()