1 /*-------------------------------------------------------------------------
2 *
3 * user.c
4 * Commands for manipulating roles (formerly called users).
5 *
6 * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
7 * Portions Copyright (c) 1994, Regents of the University of California
8 *
9 * src/backend/commands/user.c
10 *
11 *-------------------------------------------------------------------------
12 */
13 #include "postgres.h"
14
15 #include "access/genam.h"
16 #include "access/heapam.h"
17 #include "access/htup_details.h"
18 #include "access/xact.h"
19 #include "catalog/binary_upgrade.h"
20 #include "catalog/catalog.h"
21 #include "catalog/dependency.h"
22 #include "catalog/indexing.h"
23 #include "catalog/objectaccess.h"
24 #include "catalog/pg_auth_members.h"
25 #include "catalog/pg_authid.h"
26 #include "catalog/pg_database.h"
27 #include "catalog/pg_db_role_setting.h"
28 #include "commands/comment.h"
29 #include "commands/dbcommands.h"
30 #include "commands/seclabel.h"
31 #include "commands/user.h"
32 #include "libpq/crypt.h"
33 #include "miscadmin.h"
34 #include "storage/lmgr.h"
35 #include "utils/acl.h"
36 #include "utils/builtins.h"
37 #include "utils/fmgroids.h"
38 #include "utils/syscache.h"
39 #include "utils/timestamp.h"
40 #include "utils/tqual.h"
41
42 /* Potentially set by pg_upgrade_support functions */
43 Oid binary_upgrade_next_pg_authid_oid = InvalidOid;
44
45
46 /* GUC parameter */
47 int Password_encryption = PASSWORD_TYPE_MD5;
48
49 /* Hook to check passwords in CreateRole() and AlterRole() */
50 check_password_hook_type check_password_hook = NULL;
51
52 static void AddRoleMems(const char *rolename, Oid roleid,
53 List *memberSpecs, List *memberIds,
54 Oid grantorId, bool admin_opt);
55 static void DelRoleMems(const char *rolename, Oid roleid,
56 List *memberSpecs, List *memberIds,
57 bool admin_opt);
58
59
60 /* Check if current user has createrole privileges */
61 static bool
have_createrole_privilege(void)62 have_createrole_privilege(void)
63 {
64 return has_createrole_privilege(GetUserId());
65 }
66
67
68 /*
69 * CREATE ROLE
70 */
71 Oid
CreateRole(ParseState * pstate,CreateRoleStmt * stmt)72 CreateRole(ParseState *pstate, CreateRoleStmt *stmt)
73 {
74 Relation pg_authid_rel;
75 TupleDesc pg_authid_dsc;
76 HeapTuple tuple;
77 Datum new_record[Natts_pg_authid];
78 bool new_record_nulls[Natts_pg_authid];
79 Oid roleid;
80 ListCell *item;
81 ListCell *option;
82 char *password = NULL; /* user password */
83 bool issuper = false; /* Make the user a superuser? */
84 bool inherit = true; /* Auto inherit privileges? */
85 bool createrole = false; /* Can this user create roles? */
86 bool createdb = false; /* Can the user create databases? */
87 bool canlogin = false; /* Can this user login? */
88 bool isreplication = false; /* Is this a replication role? */
89 bool bypassrls = false; /* Is this a row security enabled role? */
90 int connlimit = -1; /* maximum connections allowed */
91 List *addroleto = NIL; /* roles to make this a member of */
92 List *rolemembers = NIL; /* roles to be members of this role */
93 List *adminmembers = NIL; /* roles to be admins of this role */
94 char *validUntil = NULL; /* time the login is valid until */
95 Datum validUntil_datum; /* same, as timestamptz Datum */
96 bool validUntil_null;
97 DefElem *dpassword = NULL;
98 DefElem *dissuper = NULL;
99 DefElem *dinherit = NULL;
100 DefElem *dcreaterole = NULL;
101 DefElem *dcreatedb = NULL;
102 DefElem *dcanlogin = NULL;
103 DefElem *disreplication = NULL;
104 DefElem *dconnlimit = NULL;
105 DefElem *daddroleto = NULL;
106 DefElem *drolemembers = NULL;
107 DefElem *dadminmembers = NULL;
108 DefElem *dvalidUntil = NULL;
109 DefElem *dbypassRLS = NULL;
110
111 /* The defaults can vary depending on the original statement type */
112 switch (stmt->stmt_type)
113 {
114 case ROLESTMT_ROLE:
115 break;
116 case ROLESTMT_USER:
117 canlogin = true;
118 /* may eventually want inherit to default to false here */
119 break;
120 case ROLESTMT_GROUP:
121 break;
122 }
123
124 /* Extract options from the statement node tree */
125 foreach(option, stmt->options)
126 {
127 DefElem *defel = (DefElem *) lfirst(option);
128
129 if (strcmp(defel->defname, "password") == 0)
130 {
131 if (dpassword)
132 ereport(ERROR,
133 (errcode(ERRCODE_SYNTAX_ERROR),
134 errmsg("conflicting or redundant options"),
135 parser_errposition(pstate, defel->location)));
136 dpassword = defel;
137 }
138 else if (strcmp(defel->defname, "sysid") == 0)
139 {
140 ereport(NOTICE,
141 (errmsg("SYSID can no longer be specified")));
142 }
143 else if (strcmp(defel->defname, "superuser") == 0)
144 {
145 if (dissuper)
146 ereport(ERROR,
147 (errcode(ERRCODE_SYNTAX_ERROR),
148 errmsg("conflicting or redundant options"),
149 parser_errposition(pstate, defel->location)));
150 dissuper = defel;
151 }
152 else if (strcmp(defel->defname, "inherit") == 0)
153 {
154 if (dinherit)
155 ereport(ERROR,
156 (errcode(ERRCODE_SYNTAX_ERROR),
157 errmsg("conflicting or redundant options"),
158 parser_errposition(pstate, defel->location)));
159 dinherit = defel;
160 }
161 else if (strcmp(defel->defname, "createrole") == 0)
162 {
163 if (dcreaterole)
164 ereport(ERROR,
165 (errcode(ERRCODE_SYNTAX_ERROR),
166 errmsg("conflicting or redundant options"),
167 parser_errposition(pstate, defel->location)));
168 dcreaterole = defel;
169 }
170 else if (strcmp(defel->defname, "createdb") == 0)
171 {
172 if (dcreatedb)
173 ereport(ERROR,
174 (errcode(ERRCODE_SYNTAX_ERROR),
175 errmsg("conflicting or redundant options"),
176 parser_errposition(pstate, defel->location)));
177 dcreatedb = defel;
178 }
179 else if (strcmp(defel->defname, "canlogin") == 0)
180 {
181 if (dcanlogin)
182 ereport(ERROR,
183 (errcode(ERRCODE_SYNTAX_ERROR),
184 errmsg("conflicting or redundant options"),
185 parser_errposition(pstate, defel->location)));
186 dcanlogin = defel;
187 }
188 else if (strcmp(defel->defname, "isreplication") == 0)
189 {
190 if (disreplication)
191 ereport(ERROR,
192 (errcode(ERRCODE_SYNTAX_ERROR),
193 errmsg("conflicting or redundant options"),
194 parser_errposition(pstate, defel->location)));
195 disreplication = defel;
196 }
197 else if (strcmp(defel->defname, "connectionlimit") == 0)
198 {
199 if (dconnlimit)
200 ereport(ERROR,
201 (errcode(ERRCODE_SYNTAX_ERROR),
202 errmsg("conflicting or redundant options"),
203 parser_errposition(pstate, defel->location)));
204 dconnlimit = defel;
205 }
206 else if (strcmp(defel->defname, "addroleto") == 0)
207 {
208 if (daddroleto)
209 ereport(ERROR,
210 (errcode(ERRCODE_SYNTAX_ERROR),
211 errmsg("conflicting or redundant options"),
212 parser_errposition(pstate, defel->location)));
213 daddroleto = defel;
214 }
215 else if (strcmp(defel->defname, "rolemembers") == 0)
216 {
217 if (drolemembers)
218 ereport(ERROR,
219 (errcode(ERRCODE_SYNTAX_ERROR),
220 errmsg("conflicting or redundant options"),
221 parser_errposition(pstate, defel->location)));
222 drolemembers = defel;
223 }
224 else if (strcmp(defel->defname, "adminmembers") == 0)
225 {
226 if (dadminmembers)
227 ereport(ERROR,
228 (errcode(ERRCODE_SYNTAX_ERROR),
229 errmsg("conflicting or redundant options"),
230 parser_errposition(pstate, defel->location)));
231 dadminmembers = defel;
232 }
233 else if (strcmp(defel->defname, "validUntil") == 0)
234 {
235 if (dvalidUntil)
236 ereport(ERROR,
237 (errcode(ERRCODE_SYNTAX_ERROR),
238 errmsg("conflicting or redundant options"),
239 parser_errposition(pstate, defel->location)));
240 dvalidUntil = defel;
241 }
242 else if (strcmp(defel->defname, "bypassrls") == 0)
243 {
244 if (dbypassRLS)
245 ereport(ERROR,
246 (errcode(ERRCODE_SYNTAX_ERROR),
247 errmsg("conflicting or redundant options"),
248 parser_errposition(pstate, defel->location)));
249 dbypassRLS = defel;
250 }
251 else
252 elog(ERROR, "option \"%s\" not recognized",
253 defel->defname);
254 }
255
256 if (dpassword && dpassword->arg)
257 password = strVal(dpassword->arg);
258 if (dissuper)
259 issuper = intVal(dissuper->arg) != 0;
260 if (dinherit)
261 inherit = intVal(dinherit->arg) != 0;
262 if (dcreaterole)
263 createrole = intVal(dcreaterole->arg) != 0;
264 if (dcreatedb)
265 createdb = intVal(dcreatedb->arg) != 0;
266 if (dcanlogin)
267 canlogin = intVal(dcanlogin->arg) != 0;
268 if (disreplication)
269 isreplication = intVal(disreplication->arg) != 0;
270 if (dconnlimit)
271 {
272 connlimit = intVal(dconnlimit->arg);
273 if (connlimit < -1)
274 ereport(ERROR,
275 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
276 errmsg("invalid connection limit: %d", connlimit)));
277 }
278 if (daddroleto)
279 addroleto = (List *) daddroleto->arg;
280 if (drolemembers)
281 rolemembers = (List *) drolemembers->arg;
282 if (dadminmembers)
283 adminmembers = (List *) dadminmembers->arg;
284 if (dvalidUntil)
285 validUntil = strVal(dvalidUntil->arg);
286 if (dbypassRLS)
287 bypassrls = intVal(dbypassRLS->arg) != 0;
288
289 /* Check some permissions first */
290 if (issuper)
291 {
292 if (!superuser())
293 ereport(ERROR,
294 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
295 errmsg("must be superuser to create superusers")));
296 }
297 else if (isreplication)
298 {
299 if (!superuser())
300 ereport(ERROR,
301 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
302 errmsg("must be superuser to create replication users")));
303 }
304 else if (bypassrls)
305 {
306 if (!superuser())
307 ereport(ERROR,
308 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
309 errmsg("must be superuser to change bypassrls attribute")));
310 }
311 else
312 {
313 if (!have_createrole_privilege())
314 ereport(ERROR,
315 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
316 errmsg("permission denied to create role")));
317 }
318
319 /*
320 * Check that the user is not trying to create a role in the reserved
321 * "pg_" namespace.
322 */
323 if (IsReservedName(stmt->role))
324 ereport(ERROR,
325 (errcode(ERRCODE_RESERVED_NAME),
326 errmsg("role name \"%s\" is reserved",
327 stmt->role),
328 errdetail("Role names starting with \"pg_\" are reserved.")));
329
330 /*
331 * Check the pg_authid relation to be certain the role doesn't already
332 * exist.
333 */
334 pg_authid_rel = heap_open(AuthIdRelationId, RowExclusiveLock);
335 pg_authid_dsc = RelationGetDescr(pg_authid_rel);
336
337 if (OidIsValid(get_role_oid(stmt->role, true)))
338 ereport(ERROR,
339 (errcode(ERRCODE_DUPLICATE_OBJECT),
340 errmsg("role \"%s\" already exists",
341 stmt->role)));
342
343 /* Convert validuntil to internal form */
344 if (validUntil)
345 {
346 validUntil_datum = DirectFunctionCall3(timestamptz_in,
347 CStringGetDatum(validUntil),
348 ObjectIdGetDatum(InvalidOid),
349 Int32GetDatum(-1));
350 validUntil_null = false;
351 }
352 else
353 {
354 validUntil_datum = (Datum) 0;
355 validUntil_null = true;
356 }
357
358 /*
359 * Call the password checking hook if there is one defined
360 */
361 if (check_password_hook && password)
362 (*check_password_hook) (stmt->role,
363 password,
364 get_password_type(password),
365 validUntil_datum,
366 validUntil_null);
367
368 /*
369 * Build a tuple to insert
370 */
371 MemSet(new_record, 0, sizeof(new_record));
372 MemSet(new_record_nulls, false, sizeof(new_record_nulls));
373
374 new_record[Anum_pg_authid_rolname - 1] =
375 DirectFunctionCall1(namein, CStringGetDatum(stmt->role));
376
377 new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(issuper);
378 new_record[Anum_pg_authid_rolinherit - 1] = BoolGetDatum(inherit);
379 new_record[Anum_pg_authid_rolcreaterole - 1] = BoolGetDatum(createrole);
380 new_record[Anum_pg_authid_rolcreatedb - 1] = BoolGetDatum(createdb);
381 new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
382 new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication);
383 new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
384
385 if (password)
386 {
387 char *shadow_pass;
388 char *logdetail;
389
390 /*
391 * Don't allow an empty password. Libpq treats an empty password the
392 * same as no password at all, and won't even try to authenticate. But
393 * other clients might, so allowing it would be confusing. By clearing
394 * the password when an empty string is specified, the account is
395 * consistently locked for all clients.
396 *
397 * Note that this only covers passwords stored in the database itself.
398 * There are also checks in the authentication code, to forbid an
399 * empty password from being used with authentication methods that
400 * fetch the password from an external system, like LDAP or PAM.
401 */
402 if (password[0] == '\0' ||
403 plain_crypt_verify(stmt->role, password, "", &logdetail) == STATUS_OK)
404 {
405 ereport(NOTICE,
406 (errmsg("empty string is not a valid password, clearing password")));
407 new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
408 }
409 else
410 {
411 /* Encrypt the password to the requested format. */
412 shadow_pass = encrypt_password(Password_encryption, stmt->role,
413 password);
414 new_record[Anum_pg_authid_rolpassword - 1] =
415 CStringGetTextDatum(shadow_pass);
416 }
417 }
418 else
419 new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
420
421 new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
422 new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
423
424 new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls);
425
426 tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
427
428 /*
429 * pg_largeobject_metadata contains pg_authid.oid's, so we use the
430 * binary-upgrade override.
431 */
432 if (IsBinaryUpgrade)
433 {
434 if (!OidIsValid(binary_upgrade_next_pg_authid_oid))
435 ereport(ERROR,
436 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
437 errmsg("pg_authid OID value not set when in binary upgrade mode")));
438
439 HeapTupleSetOid(tuple, binary_upgrade_next_pg_authid_oid);
440 binary_upgrade_next_pg_authid_oid = InvalidOid;
441 }
442
443 /*
444 * Insert new record in the pg_authid table
445 */
446 roleid = CatalogTupleInsert(pg_authid_rel, tuple);
447
448 /*
449 * Advance command counter so we can see new record; else tests in
450 * AddRoleMems may fail.
451 */
452 if (addroleto || adminmembers || rolemembers)
453 CommandCounterIncrement();
454
455 /*
456 * Add the new role to the specified existing roles.
457 */
458 if (addroleto)
459 {
460 RoleSpec *thisrole = makeNode(RoleSpec);
461 List *thisrole_list = list_make1(thisrole);
462 List *thisrole_oidlist = list_make1_oid(roleid);
463
464 thisrole->roletype = ROLESPEC_CSTRING;
465 thisrole->rolename = stmt->role;
466 thisrole->location = -1;
467
468 foreach(item, addroleto)
469 {
470 RoleSpec *oldrole = lfirst(item);
471 HeapTuple oldroletup = get_rolespec_tuple(oldrole);
472 Form_pg_authid oldroleform = (Form_pg_authid) GETSTRUCT(oldroletup);
473 Oid oldroleid = HeapTupleGetOid(oldroletup);
474 char *oldrolename = NameStr(oldroleform->rolname);
475
476 AddRoleMems(oldrolename, oldroleid,
477 thisrole_list,
478 thisrole_oidlist,
479 GetUserId(), false);
480
481 ReleaseSysCache(oldroletup);
482 }
483 }
484
485 /*
486 * Add the specified members to this new role. adminmembers get the admin
487 * option, rolemembers don't.
488 */
489 AddRoleMems(stmt->role, roleid,
490 adminmembers, roleSpecsToIds(adminmembers),
491 GetUserId(), true);
492 AddRoleMems(stmt->role, roleid,
493 rolemembers, roleSpecsToIds(rolemembers),
494 GetUserId(), false);
495
496 /* Post creation hook for new role */
497 InvokeObjectPostCreateHook(AuthIdRelationId, roleid, 0);
498
499 /*
500 * Close pg_authid, but keep lock till commit.
501 */
502 heap_close(pg_authid_rel, NoLock);
503
504 return roleid;
505 }
506
507
508 /*
509 * ALTER ROLE
510 *
511 * Note: the rolemembers option accepted here is intended to support the
512 * backwards-compatible ALTER GROUP syntax. Although it will work to say
513 * "ALTER ROLE role ROLE rolenames", we don't document it.
514 */
515 Oid
AlterRole(AlterRoleStmt * stmt)516 AlterRole(AlterRoleStmt *stmt)
517 {
518 Datum new_record[Natts_pg_authid];
519 bool new_record_nulls[Natts_pg_authid];
520 bool new_record_repl[Natts_pg_authid];
521 Relation pg_authid_rel;
522 TupleDesc pg_authid_dsc;
523 HeapTuple tuple,
524 new_tuple;
525 Form_pg_authid authform;
526 ListCell *option;
527 char *rolename = NULL;
528 char *password = NULL; /* user password */
529 int issuper = -1; /* Make the user a superuser? */
530 int inherit = -1; /* Auto inherit privileges? */
531 int createrole = -1; /* Can this user create roles? */
532 int createdb = -1; /* Can the user create databases? */
533 int canlogin = -1; /* Can this user login? */
534 int isreplication = -1; /* Is this a replication role? */
535 int connlimit = -1; /* maximum connections allowed */
536 List *rolemembers = NIL; /* roles to be added/removed */
537 char *validUntil = NULL; /* time the login is valid until */
538 Datum validUntil_datum; /* same, as timestamptz Datum */
539 bool validUntil_null;
540 int bypassrls = -1;
541 DefElem *dpassword = NULL;
542 DefElem *dissuper = NULL;
543 DefElem *dinherit = NULL;
544 DefElem *dcreaterole = NULL;
545 DefElem *dcreatedb = NULL;
546 DefElem *dcanlogin = NULL;
547 DefElem *disreplication = NULL;
548 DefElem *dconnlimit = NULL;
549 DefElem *drolemembers = NULL;
550 DefElem *dvalidUntil = NULL;
551 DefElem *dbypassRLS = NULL;
552 Oid roleid;
553
554 check_rolespec_name(stmt->role,
555 "Cannot alter reserved roles.");
556
557 /* Extract options from the statement node tree */
558 foreach(option, stmt->options)
559 {
560 DefElem *defel = (DefElem *) lfirst(option);
561
562 if (strcmp(defel->defname, "password") == 0)
563 {
564 if (dpassword)
565 ereport(ERROR,
566 (errcode(ERRCODE_SYNTAX_ERROR),
567 errmsg("conflicting or redundant options")));
568 dpassword = defel;
569 }
570 else if (strcmp(defel->defname, "superuser") == 0)
571 {
572 if (dissuper)
573 ereport(ERROR,
574 (errcode(ERRCODE_SYNTAX_ERROR),
575 errmsg("conflicting or redundant options")));
576 dissuper = defel;
577 }
578 else if (strcmp(defel->defname, "inherit") == 0)
579 {
580 if (dinherit)
581 ereport(ERROR,
582 (errcode(ERRCODE_SYNTAX_ERROR),
583 errmsg("conflicting or redundant options")));
584 dinherit = defel;
585 }
586 else if (strcmp(defel->defname, "createrole") == 0)
587 {
588 if (dcreaterole)
589 ereport(ERROR,
590 (errcode(ERRCODE_SYNTAX_ERROR),
591 errmsg("conflicting or redundant options")));
592 dcreaterole = defel;
593 }
594 else if (strcmp(defel->defname, "createdb") == 0)
595 {
596 if (dcreatedb)
597 ereport(ERROR,
598 (errcode(ERRCODE_SYNTAX_ERROR),
599 errmsg("conflicting or redundant options")));
600 dcreatedb = defel;
601 }
602 else if (strcmp(defel->defname, "canlogin") == 0)
603 {
604 if (dcanlogin)
605 ereport(ERROR,
606 (errcode(ERRCODE_SYNTAX_ERROR),
607 errmsg("conflicting or redundant options")));
608 dcanlogin = defel;
609 }
610 else if (strcmp(defel->defname, "isreplication") == 0)
611 {
612 if (disreplication)
613 ereport(ERROR,
614 (errcode(ERRCODE_SYNTAX_ERROR),
615 errmsg("conflicting or redundant options")));
616 disreplication = defel;
617 }
618 else if (strcmp(defel->defname, "connectionlimit") == 0)
619 {
620 if (dconnlimit)
621 ereport(ERROR,
622 (errcode(ERRCODE_SYNTAX_ERROR),
623 errmsg("conflicting or redundant options")));
624 dconnlimit = defel;
625 }
626 else if (strcmp(defel->defname, "rolemembers") == 0 &&
627 stmt->action != 0)
628 {
629 if (drolemembers)
630 ereport(ERROR,
631 (errcode(ERRCODE_SYNTAX_ERROR),
632 errmsg("conflicting or redundant options")));
633 drolemembers = defel;
634 }
635 else if (strcmp(defel->defname, "validUntil") == 0)
636 {
637 if (dvalidUntil)
638 ereport(ERROR,
639 (errcode(ERRCODE_SYNTAX_ERROR),
640 errmsg("conflicting or redundant options")));
641 dvalidUntil = defel;
642 }
643 else if (strcmp(defel->defname, "bypassrls") == 0)
644 {
645 if (dbypassRLS)
646 ereport(ERROR,
647 (errcode(ERRCODE_SYNTAX_ERROR),
648 errmsg("conflicting or redundant options")));
649 dbypassRLS = defel;
650 }
651 else
652 elog(ERROR, "option \"%s\" not recognized",
653 defel->defname);
654 }
655
656 if (dpassword && dpassword->arg)
657 password = strVal(dpassword->arg);
658 if (dissuper)
659 issuper = intVal(dissuper->arg);
660 if (dinherit)
661 inherit = intVal(dinherit->arg);
662 if (dcreaterole)
663 createrole = intVal(dcreaterole->arg);
664 if (dcreatedb)
665 createdb = intVal(dcreatedb->arg);
666 if (dcanlogin)
667 canlogin = intVal(dcanlogin->arg);
668 if (disreplication)
669 isreplication = intVal(disreplication->arg);
670 if (dconnlimit)
671 {
672 connlimit = intVal(dconnlimit->arg);
673 if (connlimit < -1)
674 ereport(ERROR,
675 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
676 errmsg("invalid connection limit: %d", connlimit)));
677 }
678 if (drolemembers)
679 rolemembers = (List *) drolemembers->arg;
680 if (dvalidUntil)
681 validUntil = strVal(dvalidUntil->arg);
682 if (dbypassRLS)
683 bypassrls = intVal(dbypassRLS->arg);
684
685 /*
686 * Scan the pg_authid relation to be certain the user exists.
687 */
688 pg_authid_rel = heap_open(AuthIdRelationId, RowExclusiveLock);
689 pg_authid_dsc = RelationGetDescr(pg_authid_rel);
690
691 tuple = get_rolespec_tuple(stmt->role);
692 authform = (Form_pg_authid) GETSTRUCT(tuple);
693 rolename = pstrdup(NameStr(authform->rolname));
694 roleid = HeapTupleGetOid(tuple);
695
696 /*
697 * To mess with a superuser or replication role in any way you gotta be
698 * superuser. We also insist on superuser to change the BYPASSRLS
699 * property. Otherwise, if you don't have createrole, you're only allowed
700 * to change your own password.
701 */
702 if (authform->rolsuper || issuper >= 0)
703 {
704 if (!superuser())
705 ereport(ERROR,
706 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
707 errmsg("must be superuser to alter superusers")));
708 }
709 else if (authform->rolreplication || isreplication >= 0)
710 {
711 if (!superuser())
712 ereport(ERROR,
713 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
714 errmsg("must be superuser to alter replication users")));
715 }
716 else if (bypassrls >= 0)
717 {
718 if (!superuser())
719 ereport(ERROR,
720 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
721 errmsg("must be superuser to change bypassrls attribute")));
722 }
723 else if (!have_createrole_privilege())
724 {
725 /* We already checked issuper, isreplication, and bypassrls */
726 if (!(inherit < 0 &&
727 createrole < 0 &&
728 createdb < 0 &&
729 canlogin < 0 &&
730 !dconnlimit &&
731 !rolemembers &&
732 !validUntil &&
733 dpassword &&
734 roleid == GetUserId()))
735 ereport(ERROR,
736 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
737 errmsg("permission denied")));
738 }
739
740 /* Convert validuntil to internal form */
741 if (validUntil)
742 {
743 validUntil_datum = DirectFunctionCall3(timestamptz_in,
744 CStringGetDatum(validUntil),
745 ObjectIdGetDatum(InvalidOid),
746 Int32GetDatum(-1));
747 validUntil_null = false;
748 }
749 else
750 {
751 /* fetch existing setting in case hook needs it */
752 validUntil_datum = SysCacheGetAttr(AUTHNAME, tuple,
753 Anum_pg_authid_rolvaliduntil,
754 &validUntil_null);
755 }
756
757 /*
758 * Call the password checking hook if there is one defined
759 */
760 if (check_password_hook && password)
761 (*check_password_hook) (rolename,
762 password,
763 get_password_type(password),
764 validUntil_datum,
765 validUntil_null);
766
767 /*
768 * Build an updated tuple, perusing the information just obtained
769 */
770 MemSet(new_record, 0, sizeof(new_record));
771 MemSet(new_record_nulls, false, sizeof(new_record_nulls));
772 MemSet(new_record_repl, false, sizeof(new_record_repl));
773
774 /*
775 * issuper/createrole/etc
776 */
777 if (issuper >= 0)
778 {
779 new_record[Anum_pg_authid_rolsuper - 1] = BoolGetDatum(issuper > 0);
780 new_record_repl[Anum_pg_authid_rolsuper - 1] = true;
781 }
782
783 if (inherit >= 0)
784 {
785 new_record[Anum_pg_authid_rolinherit - 1] = BoolGetDatum(inherit > 0);
786 new_record_repl[Anum_pg_authid_rolinherit - 1] = true;
787 }
788
789 if (createrole >= 0)
790 {
791 new_record[Anum_pg_authid_rolcreaterole - 1] = BoolGetDatum(createrole > 0);
792 new_record_repl[Anum_pg_authid_rolcreaterole - 1] = true;
793 }
794
795 if (createdb >= 0)
796 {
797 new_record[Anum_pg_authid_rolcreatedb - 1] = BoolGetDatum(createdb > 0);
798 new_record_repl[Anum_pg_authid_rolcreatedb - 1] = true;
799 }
800
801 if (canlogin >= 0)
802 {
803 new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin > 0);
804 new_record_repl[Anum_pg_authid_rolcanlogin - 1] = true;
805 }
806
807 if (isreplication >= 0)
808 {
809 new_record[Anum_pg_authid_rolreplication - 1] = BoolGetDatum(isreplication > 0);
810 new_record_repl[Anum_pg_authid_rolreplication - 1] = true;
811 }
812
813 if (dconnlimit)
814 {
815 new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
816 new_record_repl[Anum_pg_authid_rolconnlimit - 1] = true;
817 }
818
819 /* password */
820 if (password)
821 {
822 char *shadow_pass;
823 char *logdetail;
824
825 /* Like in CREATE USER, don't allow an empty password. */
826 if (password[0] == '\0' ||
827 plain_crypt_verify(rolename, password, "", &logdetail) == STATUS_OK)
828 {
829 ereport(NOTICE,
830 (errmsg("empty string is not a valid password, clearing password")));
831 new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
832 }
833 else
834 {
835 /* Encrypt the password to the requested format. */
836 shadow_pass = encrypt_password(Password_encryption, rolename,
837 password);
838 new_record[Anum_pg_authid_rolpassword - 1] =
839 CStringGetTextDatum(shadow_pass);
840 }
841 new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
842 }
843
844 /* unset password */
845 if (dpassword && dpassword->arg == NULL)
846 {
847 new_record_repl[Anum_pg_authid_rolpassword - 1] = true;
848 new_record_nulls[Anum_pg_authid_rolpassword - 1] = true;
849 }
850
851 /* valid until */
852 new_record[Anum_pg_authid_rolvaliduntil - 1] = validUntil_datum;
853 new_record_nulls[Anum_pg_authid_rolvaliduntil - 1] = validUntil_null;
854 new_record_repl[Anum_pg_authid_rolvaliduntil - 1] = true;
855
856 if (bypassrls >= 0)
857 {
858 new_record[Anum_pg_authid_rolbypassrls - 1] = BoolGetDatum(bypassrls > 0);
859 new_record_repl[Anum_pg_authid_rolbypassrls - 1] = true;
860 }
861
862 new_tuple = heap_modify_tuple(tuple, pg_authid_dsc, new_record,
863 new_record_nulls, new_record_repl);
864 CatalogTupleUpdate(pg_authid_rel, &tuple->t_self, new_tuple);
865
866 InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
867
868 ReleaseSysCache(tuple);
869 heap_freetuple(new_tuple);
870
871 /*
872 * Advance command counter so we can see new record; else tests in
873 * AddRoleMems may fail.
874 */
875 if (rolemembers)
876 CommandCounterIncrement();
877
878 if (stmt->action == +1) /* add members to role */
879 AddRoleMems(rolename, roleid,
880 rolemembers, roleSpecsToIds(rolemembers),
881 GetUserId(), false);
882 else if (stmt->action == -1) /* drop members from role */
883 DelRoleMems(rolename, roleid,
884 rolemembers, roleSpecsToIds(rolemembers),
885 false);
886
887 /*
888 * Close pg_authid, but keep lock till commit.
889 */
890 heap_close(pg_authid_rel, NoLock);
891
892 return roleid;
893 }
894
895
896 /*
897 * ALTER ROLE ... SET
898 */
899 Oid
AlterRoleSet(AlterRoleSetStmt * stmt)900 AlterRoleSet(AlterRoleSetStmt *stmt)
901 {
902 HeapTuple roletuple;
903 Oid databaseid = InvalidOid;
904 Oid roleid = InvalidOid;
905
906 if (stmt->role)
907 {
908 check_rolespec_name(stmt->role,
909 "Cannot alter reserved roles.");
910
911 roletuple = get_rolespec_tuple(stmt->role);
912 roleid = HeapTupleGetOid(roletuple);
913
914 /*
915 * Obtain a lock on the role and make sure it didn't go away in the
916 * meantime.
917 */
918 shdepLockAndCheckObject(AuthIdRelationId, HeapTupleGetOid(roletuple));
919
920 /*
921 * To mess with a superuser you gotta be superuser; else you need
922 * createrole, or just want to change your own settings
923 */
924 if (((Form_pg_authid) GETSTRUCT(roletuple))->rolsuper)
925 {
926 if (!superuser())
927 ereport(ERROR,
928 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
929 errmsg("must be superuser to alter superusers")));
930 }
931 else
932 {
933 if (!have_createrole_privilege() &&
934 HeapTupleGetOid(roletuple) != GetUserId())
935 ereport(ERROR,
936 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
937 errmsg("permission denied")));
938 }
939
940 ReleaseSysCache(roletuple);
941 }
942
943 /* look up and lock the database, if specified */
944 if (stmt->database != NULL)
945 {
946 databaseid = get_database_oid(stmt->database, false);
947 shdepLockAndCheckObject(DatabaseRelationId, databaseid);
948
949 if (!stmt->role)
950 {
951 /*
952 * If no role is specified, then this is effectively the same as
953 * ALTER DATABASE ... SET, so use the same permission check.
954 */
955 if (!pg_database_ownercheck(databaseid, GetUserId()))
956 aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
957 stmt->database);
958 }
959 }
960
961 if (!stmt->role && !stmt->database)
962 {
963 /* Must be superuser to alter settings globally. */
964 if (!superuser())
965 ereport(ERROR,
966 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
967 errmsg("must be superuser to alter settings globally")));
968 }
969
970 AlterSetting(databaseid, roleid, stmt->setstmt);
971
972 return roleid;
973 }
974
975
976 /*
977 * DROP ROLE
978 */
979 void
DropRole(DropRoleStmt * stmt)980 DropRole(DropRoleStmt *stmt)
981 {
982 Relation pg_authid_rel,
983 pg_auth_members_rel;
984 ListCell *item;
985
986 if (!have_createrole_privilege())
987 ereport(ERROR,
988 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
989 errmsg("permission denied to drop role")));
990
991 /*
992 * Scan the pg_authid relation to find the Oid of the role(s) to be
993 * deleted.
994 */
995 pg_authid_rel = heap_open(AuthIdRelationId, RowExclusiveLock);
996 pg_auth_members_rel = heap_open(AuthMemRelationId, RowExclusiveLock);
997
998 foreach(item, stmt->roles)
999 {
1000 RoleSpec *rolspec = lfirst(item);
1001 char *role;
1002 HeapTuple tuple,
1003 tmp_tuple;
1004 ScanKeyData scankey;
1005 char *detail;
1006 char *detail_log;
1007 SysScanDesc sscan;
1008 Oid roleid;
1009
1010 if (rolspec->roletype != ROLESPEC_CSTRING)
1011 ereport(ERROR,
1012 (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1013 errmsg("cannot use special role specifier in DROP ROLE")));
1014 role = rolspec->rolename;
1015
1016 tuple = SearchSysCache1(AUTHNAME, PointerGetDatum(role));
1017 if (!HeapTupleIsValid(tuple))
1018 {
1019 if (!stmt->missing_ok)
1020 {
1021 ereport(ERROR,
1022 (errcode(ERRCODE_UNDEFINED_OBJECT),
1023 errmsg("role \"%s\" does not exist", role)));
1024 }
1025 else
1026 {
1027 ereport(NOTICE,
1028 (errmsg("role \"%s\" does not exist, skipping",
1029 role)));
1030 }
1031
1032 continue;
1033 }
1034
1035 roleid = HeapTupleGetOid(tuple);
1036
1037 if (roleid == GetUserId())
1038 ereport(ERROR,
1039 (errcode(ERRCODE_OBJECT_IN_USE),
1040 errmsg("current user cannot be dropped")));
1041 if (roleid == GetOuterUserId())
1042 ereport(ERROR,
1043 (errcode(ERRCODE_OBJECT_IN_USE),
1044 errmsg("current user cannot be dropped")));
1045 if (roleid == GetSessionUserId())
1046 ereport(ERROR,
1047 (errcode(ERRCODE_OBJECT_IN_USE),
1048 errmsg("session user cannot be dropped")));
1049
1050 /*
1051 * For safety's sake, we allow createrole holders to drop ordinary
1052 * roles but not superuser roles. This is mainly to avoid the
1053 * scenario where you accidentally drop the last superuser.
1054 */
1055 if (((Form_pg_authid) GETSTRUCT(tuple))->rolsuper &&
1056 !superuser())
1057 ereport(ERROR,
1058 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1059 errmsg("must be superuser to drop superusers")));
1060
1061 /* DROP hook for the role being removed */
1062 InvokeObjectDropHook(AuthIdRelationId, roleid, 0);
1063
1064 /*
1065 * Lock the role, so nobody can add dependencies to her while we drop
1066 * her. We keep the lock until the end of transaction.
1067 */
1068 LockSharedObject(AuthIdRelationId, roleid, 0, AccessExclusiveLock);
1069
1070 /* Check for pg_shdepend entries depending on this role */
1071 if (checkSharedDependencies(AuthIdRelationId, roleid,
1072 &detail, &detail_log))
1073 ereport(ERROR,
1074 (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST),
1075 errmsg("role \"%s\" cannot be dropped because some objects depend on it",
1076 role),
1077 errdetail_internal("%s", detail),
1078 errdetail_log("%s", detail_log)));
1079
1080 /*
1081 * Remove the role from the pg_authid table
1082 */
1083 CatalogTupleDelete(pg_authid_rel, &tuple->t_self);
1084
1085 ReleaseSysCache(tuple);
1086
1087 /*
1088 * Remove role from the pg_auth_members table. We have to remove all
1089 * tuples that show it as either a role or a member.
1090 *
1091 * XXX what about grantor entries? Maybe we should do one heap scan.
1092 */
1093 ScanKeyInit(&scankey,
1094 Anum_pg_auth_members_roleid,
1095 BTEqualStrategyNumber, F_OIDEQ,
1096 ObjectIdGetDatum(roleid));
1097
1098 sscan = systable_beginscan(pg_auth_members_rel, AuthMemRoleMemIndexId,
1099 true, NULL, 1, &scankey);
1100
1101 while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
1102 {
1103 CatalogTupleDelete(pg_auth_members_rel, &tmp_tuple->t_self);
1104 }
1105
1106 systable_endscan(sscan);
1107
1108 ScanKeyInit(&scankey,
1109 Anum_pg_auth_members_member,
1110 BTEqualStrategyNumber, F_OIDEQ,
1111 ObjectIdGetDatum(roleid));
1112
1113 sscan = systable_beginscan(pg_auth_members_rel, AuthMemMemRoleIndexId,
1114 true, NULL, 1, &scankey);
1115
1116 while (HeapTupleIsValid(tmp_tuple = systable_getnext(sscan)))
1117 {
1118 CatalogTupleDelete(pg_auth_members_rel, &tmp_tuple->t_self);
1119 }
1120
1121 systable_endscan(sscan);
1122
1123 /*
1124 * Remove any comments or security labels on this role.
1125 */
1126 DeleteSharedComments(roleid, AuthIdRelationId);
1127 DeleteSharedSecurityLabel(roleid, AuthIdRelationId);
1128
1129 /*
1130 * Remove settings for this role.
1131 */
1132 DropSetting(InvalidOid, roleid);
1133
1134 /*
1135 * Advance command counter so that later iterations of this loop will
1136 * see the changes already made. This is essential if, for example,
1137 * we are trying to drop both a role and one of its direct members ---
1138 * we'll get an error if we try to delete the linking pg_auth_members
1139 * tuple twice. (We do not need a CCI between the two delete loops
1140 * above, because it's not allowed for a role to directly contain
1141 * itself.)
1142 */
1143 CommandCounterIncrement();
1144 }
1145
1146 /*
1147 * Now we can clean up; but keep locks until commit.
1148 */
1149 heap_close(pg_auth_members_rel, NoLock);
1150 heap_close(pg_authid_rel, NoLock);
1151 }
1152
1153 /*
1154 * Rename role
1155 */
1156 ObjectAddress
RenameRole(const char * oldname,const char * newname)1157 RenameRole(const char *oldname, const char *newname)
1158 {
1159 HeapTuple oldtuple,
1160 newtuple;
1161 TupleDesc dsc;
1162 Relation rel;
1163 Datum datum;
1164 bool isnull;
1165 Datum repl_val[Natts_pg_authid];
1166 bool repl_null[Natts_pg_authid];
1167 bool repl_repl[Natts_pg_authid];
1168 int i;
1169 Oid roleid;
1170 ObjectAddress address;
1171 Form_pg_authid authform;
1172
1173 rel = heap_open(AuthIdRelationId, RowExclusiveLock);
1174 dsc = RelationGetDescr(rel);
1175
1176 oldtuple = SearchSysCache1(AUTHNAME, CStringGetDatum(oldname));
1177 if (!HeapTupleIsValid(oldtuple))
1178 ereport(ERROR,
1179 (errcode(ERRCODE_UNDEFINED_OBJECT),
1180 errmsg("role \"%s\" does not exist", oldname)));
1181
1182 /*
1183 * XXX Client applications probably store the session user somewhere, so
1184 * renaming it could cause confusion. On the other hand, there may not be
1185 * an actual problem besides a little confusion, so think about this and
1186 * decide. Same for SET ROLE ... we don't restrict renaming the current
1187 * effective userid, though.
1188 */
1189
1190 roleid = HeapTupleGetOid(oldtuple);
1191 authform = (Form_pg_authid) GETSTRUCT(oldtuple);
1192
1193 if (roleid == GetSessionUserId())
1194 ereport(ERROR,
1195 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1196 errmsg("session user cannot be renamed")));
1197 if (roleid == GetOuterUserId())
1198 ereport(ERROR,
1199 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1200 errmsg("current user cannot be renamed")));
1201
1202 /*
1203 * Check that the user is not trying to rename a system role and not
1204 * trying to rename a role into the reserved "pg_" namespace.
1205 */
1206 if (IsReservedName(NameStr(authform->rolname)))
1207 ereport(ERROR,
1208 (errcode(ERRCODE_RESERVED_NAME),
1209 errmsg("role name \"%s\" is reserved",
1210 NameStr(authform->rolname)),
1211 errdetail("Role names starting with \"pg_\" are reserved.")));
1212
1213 if (IsReservedName(newname))
1214 ereport(ERROR,
1215 (errcode(ERRCODE_RESERVED_NAME),
1216 errmsg("role name \"%s\" is reserved",
1217 newname),
1218 errdetail("Role names starting with \"pg_\" are reserved.")));
1219
1220 /* make sure the new name doesn't exist */
1221 if (SearchSysCacheExists1(AUTHNAME, CStringGetDatum(newname)))
1222 ereport(ERROR,
1223 (errcode(ERRCODE_DUPLICATE_OBJECT),
1224 errmsg("role \"%s\" already exists", newname)));
1225
1226 /*
1227 * createrole is enough privilege unless you want to mess with a superuser
1228 */
1229 if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper)
1230 {
1231 if (!superuser())
1232 ereport(ERROR,
1233 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1234 errmsg("must be superuser to rename superusers")));
1235 }
1236 else
1237 {
1238 if (!have_createrole_privilege())
1239 ereport(ERROR,
1240 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1241 errmsg("permission denied to rename role")));
1242 }
1243
1244 /* OK, construct the modified tuple */
1245 for (i = 0; i < Natts_pg_authid; i++)
1246 repl_repl[i] = false;
1247
1248 repl_repl[Anum_pg_authid_rolname - 1] = true;
1249 repl_val[Anum_pg_authid_rolname - 1] = DirectFunctionCall1(namein,
1250 CStringGetDatum(newname));
1251 repl_null[Anum_pg_authid_rolname - 1] = false;
1252
1253 datum = heap_getattr(oldtuple, Anum_pg_authid_rolpassword, dsc, &isnull);
1254
1255 if (!isnull && get_password_type(TextDatumGetCString(datum)) == PASSWORD_TYPE_MD5)
1256 {
1257 /* MD5 uses the username as salt, so just clear it on a rename */
1258 repl_repl[Anum_pg_authid_rolpassword - 1] = true;
1259 repl_null[Anum_pg_authid_rolpassword - 1] = true;
1260
1261 ereport(NOTICE,
1262 (errmsg("MD5 password cleared because of role rename")));
1263 }
1264
1265 newtuple = heap_modify_tuple(oldtuple, dsc, repl_val, repl_null, repl_repl);
1266 CatalogTupleUpdate(rel, &oldtuple->t_self, newtuple);
1267
1268 InvokeObjectPostAlterHook(AuthIdRelationId, roleid, 0);
1269
1270 ObjectAddressSet(address, AuthIdRelationId, roleid);
1271
1272 ReleaseSysCache(oldtuple);
1273
1274 /*
1275 * Close pg_authid, but keep lock till commit.
1276 */
1277 heap_close(rel, NoLock);
1278
1279 return address;
1280 }
1281
1282 /*
1283 * GrantRoleStmt
1284 *
1285 * Grant/Revoke roles to/from roles
1286 */
1287 void
GrantRole(GrantRoleStmt * stmt)1288 GrantRole(GrantRoleStmt *stmt)
1289 {
1290 Relation pg_authid_rel;
1291 Oid grantor;
1292 List *grantee_ids;
1293 ListCell *item;
1294
1295 if (stmt->grantor)
1296 grantor = get_rolespec_oid(stmt->grantor, false);
1297 else
1298 grantor = GetUserId();
1299
1300 grantee_ids = roleSpecsToIds(stmt->grantee_roles);
1301
1302 /* AccessShareLock is enough since we aren't modifying pg_authid */
1303 pg_authid_rel = heap_open(AuthIdRelationId, AccessShareLock);
1304
1305 /*
1306 * Step through all of the granted roles and add/remove entries for the
1307 * grantees, or, if admin_opt is set, then just add/remove the admin
1308 * option.
1309 *
1310 * Note: Permissions checking is done by AddRoleMems/DelRoleMems
1311 */
1312 foreach(item, stmt->granted_roles)
1313 {
1314 AccessPriv *priv = (AccessPriv *) lfirst(item);
1315 char *rolename = priv->priv_name;
1316 Oid roleid;
1317
1318 /* Must reject priv(columns) and ALL PRIVILEGES(columns) */
1319 if (rolename == NULL || priv->cols != NIL)
1320 ereport(ERROR,
1321 (errcode(ERRCODE_INVALID_GRANT_OPERATION),
1322 errmsg("column names cannot be included in GRANT/REVOKE ROLE")));
1323
1324 roleid = get_role_oid(rolename, false);
1325 if (stmt->is_grant)
1326 AddRoleMems(rolename, roleid,
1327 stmt->grantee_roles, grantee_ids,
1328 grantor, stmt->admin_opt);
1329 else
1330 DelRoleMems(rolename, roleid,
1331 stmt->grantee_roles, grantee_ids,
1332 stmt->admin_opt);
1333 }
1334
1335 /*
1336 * Close pg_authid, but keep lock till commit.
1337 */
1338 heap_close(pg_authid_rel, NoLock);
1339 }
1340
1341 /*
1342 * DropOwnedObjects
1343 *
1344 * Drop the objects owned by a given list of roles.
1345 */
1346 void
DropOwnedObjects(DropOwnedStmt * stmt)1347 DropOwnedObjects(DropOwnedStmt *stmt)
1348 {
1349 List *role_ids = roleSpecsToIds(stmt->roles);
1350 ListCell *cell;
1351
1352 /* Check privileges */
1353 foreach(cell, role_ids)
1354 {
1355 Oid roleid = lfirst_oid(cell);
1356
1357 if (!has_privs_of_role(GetUserId(), roleid))
1358 ereport(ERROR,
1359 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1360 errmsg("permission denied to drop objects")));
1361 }
1362
1363 /* Ok, do it */
1364 shdepDropOwned(role_ids, stmt->behavior);
1365 }
1366
1367 /*
1368 * ReassignOwnedObjects
1369 *
1370 * Give the objects owned by a given list of roles away to another user.
1371 */
1372 void
ReassignOwnedObjects(ReassignOwnedStmt * stmt)1373 ReassignOwnedObjects(ReassignOwnedStmt *stmt)
1374 {
1375 List *role_ids = roleSpecsToIds(stmt->roles);
1376 ListCell *cell;
1377 Oid newrole;
1378
1379 /* Check privileges */
1380 foreach(cell, role_ids)
1381 {
1382 Oid roleid = lfirst_oid(cell);
1383
1384 if (!has_privs_of_role(GetUserId(), roleid))
1385 ereport(ERROR,
1386 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1387 errmsg("permission denied to reassign objects")));
1388 }
1389
1390 /* Must have privileges on the receiving side too */
1391 newrole = get_rolespec_oid(stmt->newrole, false);
1392
1393 if (!has_privs_of_role(GetUserId(), newrole))
1394 ereport(ERROR,
1395 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1396 errmsg("permission denied to reassign objects")));
1397
1398 /* Ok, do it */
1399 shdepReassignOwned(role_ids, newrole);
1400 }
1401
1402 /*
1403 * roleSpecsToIds
1404 *
1405 * Given a list of RoleSpecs, generate a list of role OIDs in the same order.
1406 *
1407 * ROLESPEC_PUBLIC is not allowed.
1408 */
1409 List *
roleSpecsToIds(List * memberNames)1410 roleSpecsToIds(List *memberNames)
1411 {
1412 List *result = NIL;
1413 ListCell *l;
1414
1415 foreach(l, memberNames)
1416 {
1417 RoleSpec *rolespec = lfirst_node(RoleSpec, l);
1418 Oid roleid;
1419
1420 roleid = get_rolespec_oid(rolespec, false);
1421 result = lappend_oid(result, roleid);
1422 }
1423 return result;
1424 }
1425
1426 /*
1427 * AddRoleMems -- Add given members to the specified role
1428 *
1429 * rolename: name of role to add to (used only for error messages)
1430 * roleid: OID of role to add to
1431 * memberSpecs: list of RoleSpec of roles to add (used only for error messages)
1432 * memberIds: OIDs of roles to add
1433 * grantorId: who is granting the membership
1434 * admin_opt: granting admin option?
1435 *
1436 * Note: caller is responsible for calling auth_file_update_needed().
1437 */
1438 static void
AddRoleMems(const char * rolename,Oid roleid,List * memberSpecs,List * memberIds,Oid grantorId,bool admin_opt)1439 AddRoleMems(const char *rolename, Oid roleid,
1440 List *memberSpecs, List *memberIds,
1441 Oid grantorId, bool admin_opt)
1442 {
1443 Relation pg_authmem_rel;
1444 TupleDesc pg_authmem_dsc;
1445 ListCell *specitem;
1446 ListCell *iditem;
1447
1448 Assert(list_length(memberSpecs) == list_length(memberIds));
1449
1450 /* Skip permission check if nothing to do */
1451 if (!memberIds)
1452 return;
1453
1454 /*
1455 * Check permissions: must have createrole or admin option on the role to
1456 * be changed. To mess with a superuser role, you gotta be superuser.
1457 */
1458 if (superuser_arg(roleid))
1459 {
1460 if (!superuser())
1461 ereport(ERROR,
1462 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1463 errmsg("must be superuser to alter superusers")));
1464 }
1465 else
1466 {
1467 if (!have_createrole_privilege() &&
1468 !is_admin_of_role(grantorId, roleid))
1469 ereport(ERROR,
1470 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1471 errmsg("must have admin option on role \"%s\"",
1472 rolename)));
1473 }
1474
1475 /*
1476 * The role membership grantor of record has little significance at
1477 * present. Nonetheless, inasmuch as users might look to it for a crude
1478 * audit trail, let only superusers impute the grant to a third party.
1479 *
1480 * Before lifting this restriction, give the member == role case of
1481 * is_admin_of_role() a fresh look. Ensure that the current role cannot
1482 * use an explicit grantor specification to take advantage of the session
1483 * user's self-admin right.
1484 */
1485 if (grantorId != GetUserId() && !superuser())
1486 ereport(ERROR,
1487 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1488 errmsg("must be superuser to set grantor")));
1489
1490 pg_authmem_rel = heap_open(AuthMemRelationId, RowExclusiveLock);
1491 pg_authmem_dsc = RelationGetDescr(pg_authmem_rel);
1492
1493 forboth(specitem, memberSpecs, iditem, memberIds)
1494 {
1495 RoleSpec *memberRole = lfirst_node(RoleSpec, specitem);
1496 Oid memberid = lfirst_oid(iditem);
1497 HeapTuple authmem_tuple;
1498 HeapTuple tuple;
1499 Datum new_record[Natts_pg_auth_members];
1500 bool new_record_nulls[Natts_pg_auth_members];
1501 bool new_record_repl[Natts_pg_auth_members];
1502
1503 /*
1504 * Refuse creation of membership loops, including the trivial case
1505 * where a role is made a member of itself. We do this by checking to
1506 * see if the target role is already a member of the proposed member
1507 * role. We have to ignore possible superuserness, however, else we
1508 * could never grant membership in a superuser-privileged role.
1509 */
1510 if (is_member_of_role_nosuper(roleid, memberid))
1511 ereport(ERROR,
1512 (errcode(ERRCODE_INVALID_GRANT_OPERATION),
1513 (errmsg("role \"%s\" is a member of role \"%s\"",
1514 rolename, get_rolespec_name(memberRole)))));
1515
1516 /*
1517 * Check if entry for this role/member already exists; if so, give
1518 * warning unless we are adding admin option.
1519 */
1520 authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM,
1521 ObjectIdGetDatum(roleid),
1522 ObjectIdGetDatum(memberid));
1523 if (HeapTupleIsValid(authmem_tuple) &&
1524 (!admin_opt ||
1525 ((Form_pg_auth_members) GETSTRUCT(authmem_tuple))->admin_option))
1526 {
1527 ereport(NOTICE,
1528 (errmsg("role \"%s\" is already a member of role \"%s\"",
1529 get_rolespec_name(memberRole), rolename)));
1530 ReleaseSysCache(authmem_tuple);
1531 continue;
1532 }
1533
1534 /* Build a tuple to insert or update */
1535 MemSet(new_record, 0, sizeof(new_record));
1536 MemSet(new_record_nulls, false, sizeof(new_record_nulls));
1537 MemSet(new_record_repl, false, sizeof(new_record_repl));
1538
1539 new_record[Anum_pg_auth_members_roleid - 1] = ObjectIdGetDatum(roleid);
1540 new_record[Anum_pg_auth_members_member - 1] = ObjectIdGetDatum(memberid);
1541 new_record[Anum_pg_auth_members_grantor - 1] = ObjectIdGetDatum(grantorId);
1542 new_record[Anum_pg_auth_members_admin_option - 1] = BoolGetDatum(admin_opt);
1543
1544 if (HeapTupleIsValid(authmem_tuple))
1545 {
1546 new_record_repl[Anum_pg_auth_members_grantor - 1] = true;
1547 new_record_repl[Anum_pg_auth_members_admin_option - 1] = true;
1548 tuple = heap_modify_tuple(authmem_tuple, pg_authmem_dsc,
1549 new_record,
1550 new_record_nulls, new_record_repl);
1551 CatalogTupleUpdate(pg_authmem_rel, &tuple->t_self, tuple);
1552 ReleaseSysCache(authmem_tuple);
1553 }
1554 else
1555 {
1556 tuple = heap_form_tuple(pg_authmem_dsc,
1557 new_record, new_record_nulls);
1558 CatalogTupleInsert(pg_authmem_rel, tuple);
1559 }
1560
1561 /* CCI after each change, in case there are duplicates in list */
1562 CommandCounterIncrement();
1563 }
1564
1565 /*
1566 * Close pg_authmem, but keep lock till commit.
1567 */
1568 heap_close(pg_authmem_rel, NoLock);
1569 }
1570
1571 /*
1572 * DelRoleMems -- Remove given members from the specified role
1573 *
1574 * rolename: name of role to del from (used only for error messages)
1575 * roleid: OID of role to del from
1576 * memberSpecs: list of RoleSpec of roles to del (used only for error messages)
1577 * memberIds: OIDs of roles to del
1578 * admin_opt: remove admin option only?
1579 *
1580 * Note: caller is responsible for calling auth_file_update_needed().
1581 */
1582 static void
DelRoleMems(const char * rolename,Oid roleid,List * memberSpecs,List * memberIds,bool admin_opt)1583 DelRoleMems(const char *rolename, Oid roleid,
1584 List *memberSpecs, List *memberIds,
1585 bool admin_opt)
1586 {
1587 Relation pg_authmem_rel;
1588 TupleDesc pg_authmem_dsc;
1589 ListCell *specitem;
1590 ListCell *iditem;
1591
1592 Assert(list_length(memberSpecs) == list_length(memberIds));
1593
1594 /* Skip permission check if nothing to do */
1595 if (!memberIds)
1596 return;
1597
1598 /*
1599 * Check permissions: must have createrole or admin option on the role to
1600 * be changed. To mess with a superuser role, you gotta be superuser.
1601 */
1602 if (superuser_arg(roleid))
1603 {
1604 if (!superuser())
1605 ereport(ERROR,
1606 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1607 errmsg("must be superuser to alter superusers")));
1608 }
1609 else
1610 {
1611 if (!have_createrole_privilege() &&
1612 !is_admin_of_role(GetUserId(), roleid))
1613 ereport(ERROR,
1614 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1615 errmsg("must have admin option on role \"%s\"",
1616 rolename)));
1617 }
1618
1619 pg_authmem_rel = heap_open(AuthMemRelationId, RowExclusiveLock);
1620 pg_authmem_dsc = RelationGetDescr(pg_authmem_rel);
1621
1622 forboth(specitem, memberSpecs, iditem, memberIds)
1623 {
1624 RoleSpec *memberRole = lfirst(specitem);
1625 Oid memberid = lfirst_oid(iditem);
1626 HeapTuple authmem_tuple;
1627
1628 /*
1629 * Find entry for this role/member
1630 */
1631 authmem_tuple = SearchSysCache2(AUTHMEMROLEMEM,
1632 ObjectIdGetDatum(roleid),
1633 ObjectIdGetDatum(memberid));
1634 if (!HeapTupleIsValid(authmem_tuple))
1635 {
1636 ereport(WARNING,
1637 (errmsg("role \"%s\" is not a member of role \"%s\"",
1638 get_rolespec_name(memberRole), rolename)));
1639 continue;
1640 }
1641
1642 if (!admin_opt)
1643 {
1644 /* Remove the entry altogether */
1645 CatalogTupleDelete(pg_authmem_rel, &authmem_tuple->t_self);
1646 }
1647 else
1648 {
1649 /* Just turn off the admin option */
1650 HeapTuple tuple;
1651 Datum new_record[Natts_pg_auth_members];
1652 bool new_record_nulls[Natts_pg_auth_members];
1653 bool new_record_repl[Natts_pg_auth_members];
1654
1655 /* Build a tuple to update with */
1656 MemSet(new_record, 0, sizeof(new_record));
1657 MemSet(new_record_nulls, false, sizeof(new_record_nulls));
1658 MemSet(new_record_repl, false, sizeof(new_record_repl));
1659
1660 new_record[Anum_pg_auth_members_admin_option - 1] = BoolGetDatum(false);
1661 new_record_repl[Anum_pg_auth_members_admin_option - 1] = true;
1662
1663 tuple = heap_modify_tuple(authmem_tuple, pg_authmem_dsc,
1664 new_record,
1665 new_record_nulls, new_record_repl);
1666 CatalogTupleUpdate(pg_authmem_rel, &tuple->t_self, tuple);
1667 }
1668
1669 ReleaseSysCache(authmem_tuple);
1670
1671 /* CCI after each change, in case there are duplicates in list */
1672 CommandCounterIncrement();
1673 }
1674
1675 /*
1676 * Close pg_authmem, but keep lock till commit.
1677 */
1678 heap_close(pg_authmem_rel, NoLock);
1679 }
1680