Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit08951a7

Browse files
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 anextra ROLE clause generated. The clause generated overlaps with-g/--role, but per discussion this was the most popular choice as optionname.* -a/--admin for the addition of an ADMIN clause.These option names are chosen to be completely new, so as they do notimpact anybody relying on the existing option set. Tests are added forthe new options and extended a bit, while on it, to cover more patternswhere quotes are added to various elements of the query generated.Author: Shinya KatoReviewed-by: Nathan Bossart, Daniel Gustafsson, Robert Haas, KyotaroHoriguchi, David G. Johnston, Przemysław SztochDiscussion:https://postgr.es/m/69a9851035cf0f0477bcc5d742b031a3@oss.nttdata.com
1 parentc23e3e6 commit08951a7

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(intargc,char*argv[])
2929
{
3030
staticstructoptionlong_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
SimpleStringListroles= {NULL,NULL};
70+
SimpleStringListmembers= {NULL,NULL};
71+
SimpleStringListadmins= {NULL,NULL};
6572
enumtrivalueprompt_password=TRI_DEFAULT;
6673
ConnParamscparams;
6774
boolecho= false;
6875
boolinteractive= false;
6976
intconn_limit=-2;/* less than minimum valid value */
7077
boolpwprompt= false;
7178
char*newpassword=NULL;
79+
char*pwexpiry=NULL;
7280

7381
/* Tri-valued variables. */
7482
enumtrivaluecreatedb=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
PQExpBufferDatasql;
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
case3:
166184
interactive= true;
167185
break;
186+
case4:
187+
bypassrls=TRI_YES;
188+
break;
189+
case5:
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 ROLEregress_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)
[8]ページ先頭
©2009-2025 Movatter.jp