~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

\(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, 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, NOCREATEROLE

.RS 4

These clauses determine whether a role will be permitted to create new roles (that is, execute

CREATE ROLE)\&. A role with

CREATEROLE

privilege can also alter and drop other roles\&. If not specified,

NOCREATEROLE

is the default\&.

.RE

.PP

100

CREATEUSER, NOCREATEUSER

101

.RS 4

102

These clauses are an obsolete, but still accepted, spelling of

103

SUPERUSER

104

and

105

NOSUPERUSER\&. Note that they are

106

\fInot\fR

107

equivalent to

108

CREATEROLE

109

as one might naively expect!

110

.RE

111

.PP

112

INHERIT, NOINHERIT

113

.RS 4

114

These clauses determine whether a role

115

\(lqinherits\(rq

116

the privileges of roles it is a member of\&. A role with the

117

INHERIT

118

attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of\&. Without

119

INHERIT, membership in another role only grants the ability to

120

SET ROLE

121

to that other role; the privileges of the other role are only available after having done so\&. If not specified,

122

INHERIT

123

is the default\&.

124

.RE

125

.PP

126

127

.RS 4

128

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

129

130

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,

131

NOLOGIN

132

is the default, except when

133

CREATE ROLE

134

is invoked through its alternative spelling

135

CREATE USER\&.

136

.RE

137

.PP

138

REPLICATION, NOREPLICATION

139

.RS 4

140

These clauses determine whether a role is allowed to initiate streaming replication or put the system in and out of backup mode\&. A role having the

141

REPLICATION

142

attribute is a very highly privileged role, and should only be used on roles actually used for replication\&. If not specified,

143

NOREPLICATION

144

is the default for all roles except superusers\&.

145

.RE

146

.PP

147

CONNECTION LIMIT \fIconnlimit\fR

148

.RS 4

149

If role can log in, this specifies how many concurrent connections the role can make\&. \-1 (the default) means no limit\&.

150

.RE

151

.PP

152

PASSWORD \fIpassword\fR

153

.RS 4

154

Sets the role\(aqs password\&. (A password is only of use for roles having the

155

156

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

157

PASSWORD NULL\&.

158

.RE

159

.PP

160

ENCRYPTED, UNENCRYPTED

161

.RS 4

162

These key words control whether the password is stored encrypted in the system catalogs\&. (If neither is specified, the default behavior is determined by the configuration parameter

163

password_encryption\&.) If the presented password string is already in MD5\-encrypted format, then it is stored encrypted as\-is, regardless of whether

164

ENCRYPTED

165

166

UNENCRYPTED

167

is specified (since the system cannot decrypt the specified encrypted password string)\&. This allows reloading of encrypted passwords during dump/restore\&.

168

.sp

169

Note that older clients might lack support for the MD5 authentication mechanism that is needed to work with passwords that are stored encrypted\&.

170

.RE

171

.PP

172

VALID UNTIL \(aq\fItimestamp\fR\(aq

173

.RS 4

174

The

175

VALID UNTIL

176

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\&.

177

.RE

178

.PP

179

IN ROLE \fIrole_name\fR

180

.RS 4

181

The

182

IN ROLE

183

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

184

GRANT

185

command to do that\&.)

186

.RE

187

.PP

188

IN GROUP \fIrole_name\fR

189

.RS 4

190

IN GROUP

191

is an obsolete spelling of

192

IN ROLE\&.

193

.RE

194

.PP

195

ROLE \fIrole_name\fR

196

.RS 4

197

The

198

ROLE

199

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

200

\(lqgroup\(rq\&.)

201

.RE

202

.PP

203

ADMIN \fIrole_name\fR

204

.RS 4

205

The

206

ADMIN

207

clause is like

208

ROLE, but the named roles are added to the new role

209

WITH ADMIN OPTION, giving them the right to grant membership in this role to others\&.

210

.RE

211

.PP

212

USER \fIrole_name\fR

213

.RS 4

214

The

215

USER

216

clause is an obsolete spelling of the

217

ROLE

218

clause\&.

219

.RE

220

.PP

221

SYSID \fIuid\fR

222

.RS 4

223

The

224

SYSID

225

clause is ignored, but is accepted for backwards compatibility\&.

226

.RE

227

.SH "NOTES"

228

.PP

229

Use

230

ALTER ROLE (\fBALTER_ROLE\fR(7))

231

to change the attributes of a role, and

232

DROP ROLE (\fBDROP_ROLE\fR(7))

233

to remove a role\&. All the attributes specified by

234

CREATE ROLE

235

can be modified by later

236

ALTER ROLE

237

commands\&.

238

.PP

239

The preferred way to add and remove members of roles that are being used as groups is to use

240

\fBGRANT\fR(7)

241

and

242

\fBREVOKE\fR(7)\&.

243

.PP

244

The

245

VALID UNTIL

246

clause defines an expiration time for a password only, not for the role

247

per se\&. In particular, the expiration time is not enforced when logging in using a non\-password\-based authentication method\&.

248

.PP

249

The

250

INHERIT

251

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

252

CREATE ROLE

253

and

254

ALTER ROLE\&. For example, being a member of a role with

255

CREATEDB

256

privilege does not immediately grant the ability to create databases, even if

257

INHERIT

258

is set; it would be necessary to become that role via

259

SET ROLE (\fBSET_ROLE\fR(7))

260

before creating a database\&.

261

.PP

262

The

263

INHERIT

264

attribute is the default for reasons of backwards compatibility: in prior releases of

265

PostgreSQL, users always had access to all privileges of groups they were members of\&. However,

266

NOINHERIT

267

provides a closer match to the semantics specified in the SQL standard\&.

268

.PP

269

Be careful with the

270

CREATEROLE

271

privilege\&. There is no concept of inheritance for the privileges of a

272

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

273

\(lquser\(rq

274

has the

275

CREATEROLE

276

privilege but not the

277

CREATEDB

278

privilege, nonetheless it can create a new role with the

279

CREATEDB

280

privilege\&. Therefore, regard roles that have the

281

CREATEROLE

282

privilege as almost\-superuser\-roles\&.

283

.PP

284

PostgreSQL

285

includes a program

286

\fBcreateuser\fR(1)

287

that has the same functionality as

288

CREATE ROLE

289

(in fact, it calls this command) but can be run from the command shell\&.

290

.PP

291

The

292

CONNECTION LIMIT

293

option is only enforced approximately; if two new sessions start at about the same time when just one connection

294

\(lqslot\(rq

295

remains for the role, it is possible that both will fail\&. Also, the limit is never enforced for superusers\&.

296

.PP

297

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

298

\fBcreateuser\fR(1), however, transmits the password encrypted\&. Also,

299

\fBpsql\fR(1)

300

contains a command

301

\epassword

302

that can be used to safely change the password later\&.

303

.SH "EXAMPLES"

304

.PP

305

Create a role that can log in, but don\(aqt give it a password:

306

.sp

307

.if n \{\

308

.RS 4

309

.\}

310

.nf

311

CREATE ROLE jonathan LOGIN;

312

.fi

313

.if n \{\

314

.RE

315

.\}

316

.PP

317

Create a role with a password:

318

.sp

319

.if n \{\

320

.RS 4

321

.\}

322

.nf

323

CREATE USER davide WITH PASSWORD \(aqjw8s0F4\(aq;

324

.fi

325

.if n \{\

326

.RE

327

.\}

328

.sp

329

(CREATE USER

330

is the same as

331

CREATE ROLE

332

except that it implies

333

334

.PP

335

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\&.

336

.sp

337

.if n \{\

338

.RS 4

339

.\}

340

.nf

341

CREATE ROLE miriam WITH LOGIN PASSWORD \(aqjw8s0F4\(aq VALID UNTIL \(aq2005\-01\-01\(aq;

342

.fi

343

.if n \{\

344

.RE

345

.\}

346

.PP

347

Create a role that can create databases and manage roles:

348

.sp

349

.if n \{\

350

.RS 4

351

.\}

352

.nf

353

CREATE ROLE admin WITH CREATEDB CREATEROLE;

354

.fi

355

.if n \{\

356

.RE

357

.\}

358

.SH "COMPATIBILITY"

359

.PP

360

The

361

CREATE ROLE

362

statement is in the SQL standard, but the standard only requires the syntax

363

.sp

364

.if n \{\

365

.RS 4

366

.\}

367

.nf

368

CREATE ROLE \fIname\fR [ WITH ADMIN \fIrole_name\fR ]

369

.fi

370

.if n \{\

371

.RE

372

.\}

373

.sp

374

375

Multiple initial administrators, and all the other options of

376

CREATE ROLE, are

377

PostgreSQL

378

extensions\&.

379

.PP

380

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

381

PostgreSQL

382

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\&.

383

.PP

384

The behavior specified by the SQL standard is most closely approximated by giving users the

385

NOINHERIT

386

attribute, while roles are given the

387

INHERIT

388

attribute\&.

389

.SH "SEE ALSO"

390

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)

Older »