Skip to content

Commit 08951a7

Browse files
michaelpqmichaelpq
committed
createuser: Add support for more clause types through new options
The following options are added to createuser: * --valid-until to generate a VALID UNTIL clause for the role created. * --bypassrls/--no-bypassrls for BYPASSRLS/NOBYPASSRLS. * -m/--member to make the new role a member of an existing role, with an extra ROLE clause generated. The clause generated overlaps with -g/--role, but per discussion this was the most popular choice as option name. * -a/--admin for the addition of an ADMIN clause. These option names are chosen to be completely new, so as they do not impact anybody relying on the existing option set. Tests are added for the new options and extended a bit, while on it, to cover more patterns where quotes are added to various elements of the query generated. Author: Shinya Kato Reviewed-by: Nathan Bossart, Daniel Gustafsson, Robert Haas, Kyotaro Horiguchi, David G. Johnston, Przemysław Sztoch Discussion: https://postgr.es/m/69a9851035cf0f0477bcc5d742b031a3@oss.nttdata.com
1 parent c23e3e6 commit 08951a7

File tree

3 files changed

+156
-4
lines changed

3 files changed

+156
-4
lines changed

‎doc/src/sgml/ref/createuser.sgml

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,6 +76,20 @@ PostgreSQL documentation
7676
</listitem>
7777
</varlistentry>
7878

79+
<varlistentry>
80+
<term><option>-a <replaceable class="parameter">role</replaceable></option></term>
81+
<term><option>--admin=<replaceable class="parameter">role</replaceable></option></term>
82+
<listitem>
83+
<para>
84+
Indicates role that will be immediately added as a member of the new
85+
role with admin option, giving it the right to grant membership in the
86+
new role to others. Multiple roles to add as members (with admin
87+
option) of the new role can be specified by writing multiple
88+
<option>-a</option> switches.
89+
</para>
90+
</listitem>
91+
</varlistentry>
92+
7993
<varlistentry>
8094
<term><option>-c <replaceable class="parameter">number</replaceable></option></term>
8195
<term><option>--connection-limit=<replaceable class="parameter">number</replaceable></option></term>
@@ -204,6 +218,18 @@ PostgreSQL documentation
204218
</listitem>
205219
</varlistentry>
206220

221+
<varlistentry>
222+
<term><option>-m <replaceable class="parameter">role</replaceable></option></term>
223+
<term><option>--member=<replaceable class="parameter">role</replaceable></option></term>
224+
<listitem>
225+
<para>
226+
Indicates role that will be immediately added as a member of the new
227+
role. Multiple roles to add as members of the new role can be specified
228+
by writing multiple <option>-m</option> switches.
229+
</para>
230+
</listitem>
231+
</varlistentry>
232+
207233
<varlistentry>
208234
<term><option>-P</option></term>
209235
<term><option>--pwprompt</option></term>
@@ -258,6 +284,17 @@ PostgreSQL documentation
258284
</listitem>
259285
</varlistentry>
260286

287+
<varlistentry>
288+
<term><option>-v <replaceable class="parameter">timestamp</replaceable></option></term>
289+
<term><option>--valid-until=<replaceable class="parameter">timestamp</replaceable></option></term>
290+
<listitem>
291+
<para>
292+
Set a date and time after which the role's password is no longer valid.
293+
The default is to set no password expiry date.
294+
</para>
295+
</listitem>
296+
</varlistentry>
297+
261298
<varlistentry>
262299
<term><option>-V</option></term>
263300
<term><option>--version</option></term>
@@ -268,6 +305,25 @@ PostgreSQL documentation
268305
</listitem>
269306
</varlistentry>
270307

308+
<varlistentry>
309+
<term><option>--bypassrls</option></term>
310+
<listitem>
311+
<para>
312+
The new user will bypass every row-level security (RLS) policy.
313+
</para>
314+
</listitem>
315+
</varlistentry>
316+
317+
<varlistentry>
318+
<term><option>--no-bypassrls</option></term>
319+
<listitem>
320+
<para>
321+
The new user will not bypass row-level security (RLS) policies. This is
322+
the default.
323+
</para>
324+
</listitem>
325+
</varlistentry>
326+
271327
<varlistentry>
272328
<term><option>--replication</option></term>
273329
<listitem>

‎src/bin/scripts/createuser.c

Lines changed: 70 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ int
2828
main(int argc, char *argv[])
2929
{
3030
static struct option long_options[] = {
31+
{"admin", required_argument, NULL, 'a'},
3132
{"connection-limit", required_argument, NULL, 'c'},
3233
{"createdb", no_argument, NULL, 'd'},
3334
{"no-createdb", no_argument, NULL, 'D'},
@@ -39,18 +40,22 @@ main(int argc, char *argv[])
3940
{"no-inherit", no_argument, NULL, 'I'},
4041
{"login", no_argument, NULL, 'l'},
4142
{"no-login", no_argument, NULL, 'L'},
43+
{"member", required_argument, NULL, 'm'},
4244
{"port", required_argument, NULL, 'p'},
4345
{"pwprompt", no_argument, NULL, 'P'},
4446
{"createrole", no_argument, NULL, 'r'},
4547
{"no-createrole", no_argument, NULL, 'R'},
4648
{"superuser", no_argument, NULL, 's'},
4749
{"no-superuser", no_argument, NULL, 'S'},
4850
{"username", required_argument, NULL, 'U'},
51+
{"valid-until", required_argument, NULL, 'v'},
4952
{"no-password", no_argument, NULL, 'w'},
5053
{"password", no_argument, NULL, 'W'},
5154
{"replication", no_argument, NULL, 1},
5255
{"no-replication", no_argument, NULL, 2},
5356
{"interactive", no_argument, NULL, 3},
57+
{"bypassrls", no_argument, NULL, 4},
58+
{"no-bypassrls", no_argument, NULL, 5},
5459
{NULL, 0, NULL, 0}
5560
};
5661

@@ -62,21 +67,25 @@ main(int argc, char *argv[])
6267
char *port = NULL;
6368
char *username = NULL;
6469
SimpleStringList roles = {NULL, NULL};
70+
SimpleStringList members = {NULL, NULL};
71+
SimpleStringList admins = {NULL, NULL};
6572
enum trivalue prompt_password = TRI_DEFAULT;
6673
ConnParams cparams;
6774
bool echo = false;
6875
bool interactive = false;
6976
int conn_limit = -2; /* less than minimum valid value */
7077
bool pwprompt = false;
7178
char *newpassword = NULL;
79+
char *pwexpiry = NULL;
7280

7381
/* Tri-valued variables. */
7482
enum trivalue createdb = TRI_DEFAULT,
7583
superuser = TRI_DEFAULT,
7684
createrole = TRI_DEFAULT,
7785
inherit = TRI_DEFAULT,
7886
login = TRI_DEFAULT,
79-
replication = TRI_DEFAULT;
87+
replication = TRI_DEFAULT,
88+
bypassrls = TRI_DEFAULT;
8089

8190
PQExpBufferData sql;
8291

@@ -89,11 +98,14 @@ main(int argc, char *argv[])
8998

9099
handle_help_version_opts(argc, argv, "createuser", help);
91100

92-
while ((c = getopt_long(argc, argv, "c:dDeEg:h:iIlLp:PrRsSU:wW",
101+
while ((c = getopt_long(argc, argv, "a:c:dDeEg:h:iIlLm:p:PrRsSU:v:wW",
93102
long_options, &optindex)) != -1)
94103
{
95104
switch (c)
96105
{
106+
case 'a':
107+
simple_string_list_append(&admins, optarg);
108+
break;
97109
case 'c':
98110
if (!option_parse_int(optarg, "-c/--connection-limit",
99111
-1, INT_MAX, &conn_limit))
@@ -129,6 +141,9 @@ main(int argc, char *argv[])
129141
case 'L':
130142
login = TRI_NO;
131143
break;
144+
case 'm':
145+
simple_string_list_append(&members, optarg);
146+
break;
132147
case 'p':
133148
port = pg_strdup(optarg);
134149
break;
@@ -150,6 +165,9 @@ main(int argc, char *argv[])
150165
case 'U':
151166
username = pg_strdup(optarg);
152167
break;
168+
case 'v':
169+
pwexpiry = pg_strdup(optarg);
170+
break;
153171
case 'w':
154172
prompt_password = TRI_NO;
155173
break;
@@ -165,6 +183,12 @@ main(int argc, char *argv[])
165183
case 3:
166184
interactive = true;
167185
break;
186+
case 4:
187+
bypassrls = TRI_YES;
188+
break;
189+
case 5:
190+
bypassrls = TRI_NO;
191+
break;
168192
default:
169193
/* getopt_long already emitted a complaint */
170194
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -304,8 +328,17 @@ main(int argc, char *argv[])
304328
appendPQExpBufferStr(&sql, " REPLICATION");
305329
if (replication == TRI_NO)
306330
appendPQExpBufferStr(&sql, " NOREPLICATION");
331+
if (bypassrls == TRI_YES)
332+
appendPQExpBufferStr(&sql, " BYPASSRLS");
333+
if (bypassrls == TRI_NO)
334+
appendPQExpBufferStr(&sql, " NOBYPASSRLS");
307335
if (conn_limit >= -1)
308336
appendPQExpBuffer(&sql, " CONNECTION LIMIT %d", conn_limit);
337+
if (pwexpiry != NULL)
338+
{
339+
appendPQExpBufferStr(&sql, " VALID UNTIL ");
340+
appendStringLiteralConn(&sql, pwexpiry, conn);
341+
}
309342
if (roles.head != NULL)
310343
{
311344
SimpleStringListCell *cell;
@@ -320,6 +353,35 @@ main(int argc, char *argv[])
320353
appendPQExpBufferStr(&sql, fmtId(cell->val));
321354
}
322355
}
356+
if (members.head != NULL)
357+
{
358+
SimpleStringListCell *cell;
359+
360+
appendPQExpBufferStr(&sql, " ROLE ");
361+
362+
for (cell = members.head; cell; cell = cell->next)
363+
{
364+
if (cell->next)
365+
appendPQExpBuffer(&sql, "%s,", fmtId(cell->val));
366+
else
367+
appendPQExpBufferStr(&sql, fmtId(cell->val));
368+
}
369+
}
370+
if (admins.head != NULL)
371+
{
372+
SimpleStringListCell *cell;
373+
374+
appendPQExpBufferStr(&sql, " ADMIN ");
375+
376+
for (cell = admins.head; cell; cell = cell->next)
377+
{
378+
if (cell->next)
379+
appendPQExpBuffer(&sql, "%s,", fmtId(cell->val));
380+
else
381+
appendPQExpBufferStr(&sql, fmtId(cell->val));
382+
}
383+
}
384+
323385
appendPQExpBufferChar(&sql, ';');
324386

325387
if (echo)
@@ -346,6 +408,8 @@ help(const char *progname)
346408
printf(_("Usage:\n"));
347409
printf(_(" %s [OPTION]... [ROLENAME]\n"), progname);
348410
printf(_("\nOptions:\n"));
411+
printf(_(" -a, --admin=ROLE this role will be a member of new role with admin\n"
412+
" option\n"));
349413
printf(_(" -c, --connection-limit=N connection limit for role (default: no limit)\n"));
350414
printf(_(" -d, --createdb role can create new databases\n"));
351415
printf(_(" -D, --no-createdb role cannot create databases (default)\n"));
@@ -356,14 +420,18 @@ help(const char *progname)
356420
printf(_(" -I, --no-inherit role does not inherit privileges\n"));
357421
printf(_(" -l, --login role can login (default)\n"));
358422
printf(_(" -L, --no-login role cannot login\n"));
423+
printf(_(" -m, --member=ROLE this role will be a member of new role\n"));
359424
printf(_(" -P, --pwprompt assign a password to new role\n"));
360425
printf(_(" -r, --createrole role can create new roles\n"));
361426
printf(_(" -R, --no-createrole role cannot create roles (default)\n"));
362427
printf(_(" -s, --superuser role will be superuser\n"));
363428
printf(_(" -S, --no-superuser role will not be superuser (default)\n"));
429+
printf(_(" -v, --valid-until password expiration date for role\n"));
364430
printf(_(" -V, --version output version information, then exit\n"));
365431
printf(_(" --interactive prompt for missing role name and attributes rather\n"
366432
" than using defaults\n"));
433+
printf(_(" --bypassrls role can bypass row-level security (RLS) policy\n"));
434+
printf(_(" --no-bypassrls role cannot bypass row-level security (RLS) policy\n"));
367435
printf(_(" --replication role can initiate replication\n"));
368436
printf(_(" --no-replication role cannot initiate replication\n"));
369437
printf(_(" -?, --help show this help, then exit\n"));

‎src/bin/scripts/t/040_createuser.pl

Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,13 +25,41 @@
2525
qr/statement: CREATE ROLE regress_role1 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN;/,
2626
'create a non-login role');
2727
$node->issues_sql_like(
28-
[ 'createuser', '-r', 'regress_user2' ],
29-
qr/statement: CREATE ROLE regress_user2 NOSUPERUSER NOCREATEDB CREATEROLE INHERIT LOGIN;/,
28+
[ 'createuser', '-r', 'regress user2' ],
29+
qr/statement: CREATE ROLE "regress user2" NOSUPERUSER NOCREATEDB CREATEROLE INHERIT LOGIN;/,
3030
'create a CREATEROLE user');
3131
$node->issues_sql_like(
3232
[ 'createuser', '-s', 'regress_user3' ],
3333
qr/statement: CREATE ROLE regress_user3 SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;/,
3434
'create a superuser');
35+
$node->issues_sql_like(
36+
[
37+
'createuser', '-a',
38+
'regress_user1', '-a',
39+
'regress user2', 'regress user #4'
40+
],
41+
qr/statement: CREATE ROLE "regress user #4" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ADMIN regress_user1,"regress user2";/,
42+
'add a role as a member with admin option of the newly created role');
43+
$node->issues_sql_like(
44+
[
45+
'createuser', '-m',
46+
'regress_user3', '-m',
47+
'regress user #4', 'REGRESS_USER5'
48+
],
49+
qr/statement: CREATE ROLE "REGRESS_USER5" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ROLE regress_user3,"regress user #4";/,
50+
'add a role as a member of the newly created role');
51+
$node->issues_sql_like(
52+
[ 'createuser', '-v', '2029 12 31', 'regress_user6' ],
53+
qr/statement: CREATE ROLE regress_user6 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN VALID UNTIL \'2029 12 31\';/,
54+
'create a role with a password expiration date');
55+
$node->issues_sql_like(
56+
[ 'createuser', '--bypassrls', 'regress_user7' ],
57+
qr/statement: CREATE ROLE regress_user7 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN BYPASSRLS;/,
58+
'create a BYPASSRLS role');
59+
$node->issues_sql_like(
60+
[ 'createuser', '--no-bypassrls', 'regress_user8' ],
61+
qr/statement: CREATE ROLE regress_user8 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOBYPASSRLS;/,
62+
'create a role without BYPASSRLS');
3563

3664
$node->command_fails([ 'createuser', 'regress_user1' ],
3765
'fails if role already exists');

0 commit comments

Comments
 (0)