On Thu, May 19, 2022 at 10:35:23AM +0900, Shinya Kato wrote: > I created a new patch to test the new options!
Thanks for the new patch! I attached a new version with a few small changes. What do you think? -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
>From f1c00de3e3daf0150fdaa6ed5fe4215ebf928b6a Mon Sep 17 00:00:00 2001 From: Nathan Bossart <bossa...@amazon.com> Date: Fri, 20 May 2022 21:28:06 +0000 Subject: [PATCH v6 1/1] Add missing options to createuser. This adds the following options to the createuser program: --admin (ADMIN) --member (IN ROLE) --valid-until (VALID UNTIL) --bypassrls (BYPASSRLS) --no-bypassrls (NOBYPASSRLS) --- doc/src/sgml/ref/createuser.sgml | 56 ++++++++++++++++++++++ src/bin/scripts/createuser.c | 72 ++++++++++++++++++++++++++++- src/bin/scripts/t/040_createuser.pl | 20 ++++++++ 3 files changed, 146 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml index 17579e50af..27efebbfe2 100644 --- a/doc/src/sgml/ref/createuser.sgml +++ b/doc/src/sgml/ref/createuser.sgml @@ -76,6 +76,20 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>-a <replaceable class="parameter">role</replaceable></option></term> + <term><option>--admin=<replaceable class="parameter">role</replaceable></option></term> + <listitem> + <para> + Indicates role that will be immediately added as a member of the new + role with admin option, giving it the right to grant membership in the + new role to others. Multiple roles to add as members (with admin + option) of the new role can be specified by writing multiple + <option>-a</option> switches. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-c <replaceable class="parameter">number</replaceable></option></term> <term><option>--connection-limit=<replaceable class="parameter">number</replaceable></option></term> @@ -204,6 +218,18 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>-m <replaceable class="parameter">role</replaceable></option></term> + <term><option>--member=<replaceable class="parameter">role</replaceable></option></term> + <listitem> + <para> + Indicates role that will be immediately added as a member of the new + role. Multiple roles to add as members of the new role can be specified + by writing multiple <option>-m</option> switches. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-P</option></term> <term><option>--pwprompt</option></term> @@ -258,6 +284,17 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>-v <replaceable class="parameter">timestamp</replaceable></option></term> + <term><option>--valid-until=<replaceable class="parameter">timestamp</replaceable></option></term> + <listitem> + <para> + Set a date and time after which the role's password is no longer valid. + The default is to set no password expiry date. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-V</option></term> <term><option>--version</option></term> @@ -290,6 +327,25 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>--bypassrls</option></term> + <listitem> + <para> + The new user will bypass every row-level security (RLS) policy. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--no-bypassrls</option></term> + <listitem> + <para> + The new user will not bypass row-level security (RLS) policies. This is + the default. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-?</option></term> <term><option>--help</option></term> diff --git a/src/bin/scripts/createuser.c b/src/bin/scripts/createuser.c index bfba0d09d1..7e3e61a9c8 100644 --- a/src/bin/scripts/createuser.c +++ b/src/bin/scripts/createuser.c @@ -51,6 +51,11 @@ main(int argc, char *argv[]) {"connection-limit", required_argument, NULL, 'c'}, {"pwprompt", no_argument, NULL, 'P'}, {"encrypted", no_argument, NULL, 'E'}, + {"bypassrls", no_argument, NULL, 4}, + {"no-bypassrls", no_argument, NULL, 5}, + {"valid-until", required_argument, NULL, 'v'}, + {"member", required_argument, NULL, 'm'}, + {"admin", required_argument, NULL, 'a'}, {NULL, 0, NULL, 0} }; @@ -62,6 +67,8 @@ main(int argc, char *argv[]) char *port = NULL; char *username = NULL; SimpleStringList roles = {NULL, NULL}; + SimpleStringList members = {NULL, NULL}; + SimpleStringList admins = {NULL, NULL}; enum trivalue prompt_password = TRI_DEFAULT; ConnParams cparams; bool echo = false; @@ -69,6 +76,7 @@ main(int argc, char *argv[]) int conn_limit = -2; /* less than minimum valid value */ bool pwprompt = false; char *newpassword = NULL; + char *pwexpiry = NULL; /* Tri-valued variables. */ enum trivalue createdb = TRI_DEFAULT, @@ -76,7 +84,8 @@ main(int argc, char *argv[]) createrole = TRI_DEFAULT, inherit = TRI_DEFAULT, login = TRI_DEFAULT, - replication = TRI_DEFAULT; + replication = TRI_DEFAULT, + bypassrls = TRI_DEFAULT; PQExpBufferData sql; @@ -89,7 +98,7 @@ main(int argc, char *argv[]) handle_help_version_opts(argc, argv, "createuser", help); - while ((c = getopt_long(argc, argv, "h:p:U:g:wWedDsSrRiIlLc:PE", + while ((c = getopt_long(argc, argv, "h:p:U:g:wWedDsSrRiIlLc:PEv:m:a:", long_options, &optindex)) != -1) { switch (c) @@ -165,6 +174,21 @@ main(int argc, char *argv[]) case 3: interactive = true; break; + case 4: + bypassrls = TRI_YES; + break; + case 5: + bypassrls = TRI_NO; + break; + case 'v': + pwexpiry = pg_strdup(optarg); + break; + case 'm': + simple_string_list_append(&members, optarg); + break; + case 'a': + simple_string_list_append(&admins, optarg); + break; default: /* getopt_long already emitted a complaint */ pg_log_error_hint("Try \"%s --help\" for more information.", progname); @@ -304,8 +328,17 @@ main(int argc, char *argv[]) appendPQExpBufferStr(&sql, " REPLICATION"); if (replication == TRI_NO) appendPQExpBufferStr(&sql, " NOREPLICATION"); + if (bypassrls == TRI_YES) + appendPQExpBufferStr(&sql, " BYPASSRLS"); + if (bypassrls == TRI_NO) + appendPQExpBufferStr(&sql, " NOBYPASSRLS"); if (conn_limit >= -1) appendPQExpBuffer(&sql, " CONNECTION LIMIT %d", conn_limit); + if (pwexpiry != NULL) + { + appendPQExpBufferStr(&sql, " VALID UNTIL "); + appendStringLiteralConn(&sql, pwexpiry, conn); + } if (roles.head != NULL) { SimpleStringListCell *cell; @@ -320,6 +353,35 @@ main(int argc, char *argv[]) appendPQExpBufferStr(&sql, fmtId(cell->val)); } } + if (members.head != NULL) + { + SimpleStringListCell *cell; + + appendPQExpBufferStr(&sql, " ROLE "); + + for (cell = members.head; cell; cell = cell->next) + { + if (cell->next) + appendPQExpBuffer(&sql, "%s,", fmtId(cell->val)); + else + appendPQExpBufferStr(&sql, fmtId(cell->val)); + } + } + if (admins.head != NULL) + { + SimpleStringListCell *cell; + + appendPQExpBufferStr(&sql, " ADMIN "); + + for (cell = admins.head; cell; cell = cell->next) + { + if (cell->next) + appendPQExpBuffer(&sql, "%s,", fmtId(cell->val)); + else + appendPQExpBufferStr(&sql, fmtId(cell->val)); + } + } + appendPQExpBufferChar(&sql, ';'); if (echo) @@ -346,6 +408,8 @@ help(const char *progname) printf(_("Usage:\n")); printf(_(" %s [OPTION]... [ROLENAME]\n"), progname); printf(_("\nOptions:\n")); + printf(_(" -a, --admin=ROLE this role will be a member of new role with admin\n" + " option\n")); printf(_(" -c, --connection-limit=N connection limit for role (default: no limit)\n")); printf(_(" -d, --createdb role can create new databases\n")); printf(_(" -D, --no-createdb role cannot create databases (default)\n")); @@ -356,16 +420,20 @@ help(const char *progname) printf(_(" -I, --no-inherit role does not inherit privileges\n")); printf(_(" -l, --login role can login (default)\n")); printf(_(" -L, --no-login role cannot login\n")); + printf(_(" -m, --member=ROLE this role will be a member of new role\n")); printf(_(" -P, --pwprompt assign a password to new role\n")); printf(_(" -r, --createrole role can create new roles\n")); printf(_(" -R, --no-createrole role cannot create roles (default)\n")); printf(_(" -s, --superuser role will be superuser\n")); printf(_(" -S, --no-superuser role will not be superuser (default)\n")); + printf(_(" -v, --valid-until password expiration date for role\n")); printf(_(" -V, --version output version information, then exit\n")); printf(_(" --interactive prompt for missing role name and attributes rather\n" " than using defaults\n")); printf(_(" --replication role can initiate replication\n")); printf(_(" --no-replication role cannot initiate replication\n")); + printf(_(" --bypassrls role can bypass row-level security (RLS) policy\n")); + printf(_(" --no-bypassrls role cannot bypass row-level security (RLS) policy\n")); printf(_(" -?, --help show this help, then exit\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); diff --git a/src/bin/scripts/t/040_createuser.pl b/src/bin/scripts/t/040_createuser.pl index 2a34be81cf..9e17c8e54b 100644 --- a/src/bin/scripts/t/040_createuser.pl +++ b/src/bin/scripts/t/040_createuser.pl @@ -32,6 +32,26 @@ $node->issues_sql_like( [ 'createuser', '-s', 'regress_user3' ], qr/statement: CREATE ROLE regress_user3 SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;/, 'create a superuser'); +$node->issues_sql_like( + [ 'createuser', 'regress_role2', '-a', 'regress_user1' ], + qr/statement: CREATE ROLE regress_role2 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ADMIN regress_user1;/, + 'add a role as a member with admin option of the newly created role'); +$node->issues_sql_like( + [ 'createuser', 'regress_role3', '-m', 'regress_user1' ], + qr/statement: CREATE ROLE regress_role3 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ROLE regress_user1;/, + 'add a role as a member of the newly created role'); +$node->issues_sql_like( + [ 'createuser', 'regress_user4', '-v', '20291231' ], + qr/statement: CREATE ROLE regress_user4 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN VALID UNTIL \'20291231\';/, + 'create a role with a password expiration date'); +$node->issues_sql_like( + [ 'createuser', 'regress_user5', '--bypassrls' ], + qr/statement: CREATE ROLE regress_user5 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN BYPASSRLS;/, + 'create a BYPASSRLS role'); +$node->issues_sql_like( + [ 'createuser', 'regress_role6', '--no-bypassrls' ], + qr/statement: CREATE ROLE regress_role6 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOBYPASSRLS;/, + 'create a role without BYPASSRLS'); $node->command_fails([ 'createuser', 'regress_user1' ], 'fails if role already exists'); -- 2.32.0