Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nsARG-0005qx-0E for pgsql-hackers@arkaria.postgresql.org; Fri, 20 May 2022 21:45:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nsARE-00078W-Bj for pgsql-hackers@arkaria.postgresql.org; Fri, 20 May 2022 21:45:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nsARD-00078J-VQ for pgsql-hackers@lists.postgresql.org; Fri, 20 May 2022 21:45:36 +0000 Received: from mail-pl1-x633.google.com ([2607:f8b0:4864:20::633]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nsAR2-0001CL-OM for pgsql-hackers@postgresql.org; Fri, 20 May 2022 21:45:35 +0000 Received: by mail-pl1-x633.google.com with SMTP id m12so8380890plb.4 for ; Fri, 20 May 2022 14:45:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=SIWRvEwlj1iyF/RWCEGEoLCG33xbTjhd+nttg/0hlxc=; b=A9zKScMx8IYnIdLSvkmC9egMqM/6L8VfR9iJHl2k3t5JPzpQhu6CNkGO+tt8wUMFa0 CLAqzCMMCSVMq6Kfb0oMbYOvBGuTjxJiGCmWwsaNyfLyag7u7T0QsqOVVv502wOMn52b YmSz5QpRSBNbhhtDOzC72Mt5P3ytXV5Xf5sIYcPZcKUAbc/3tP2t46Gh+kSeLmCro9Of hHccRth3gBiCz1FikDmcl6yOw3O8acd+QAxXpe/bzKeIgfZUve70tzoOaJludPHK6wRF CreykNdNuTli/BD8XINdd3gwcjZlvl1PdjVQ3/OhNtvvWdyUoVz11lMxDY+yzS6upFat R3Hg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=SIWRvEwlj1iyF/RWCEGEoLCG33xbTjhd+nttg/0hlxc=; b=XpykUOXo5U7v9UYz2LsoelRiF6yrvHyLjONYrF3MeTG1x7xDPv9DwZtgAqP9bFi5Lc O5L6KGeakHU68Zbr0Z7qx04mKrKdU0h2eRmAnml7rNcYpPUxLovSnnsNzwcEoJ6kVm4B JpWuLAWoAFOcTt5g3er2qCiEBtuEwxZjqetkqjeXD9eUTOpRdeUc8fngbJkA8/gptFSw rD+jMegKWj5bmdQEMlmqv6z2I6rgBaRV3EKicvoJZPwR2FFP+t5lpscHlNxcfdl03aF7 y+lEP72z+fQb02Ajvqk2RKkJ9NZ6rPzmJMtyNtkfQwaEzi2W27pS5ZZtfBUGf8/a72ef RcVQ== X-Gm-Message-State: AOAM530EiDoZ7r/8kMdHiAQ+5CBCzWLY9FknOJn9VrA0uwmQzAU0SmMg yvr8MlejEwUQISGmrhTBV3Q= X-Google-Smtp-Source: ABdhPJwNFHXPZ+kxWWeA8Ogg1aNxX9adCyTPZadb+4q8PKoV+1xstDg1StFZptvdtrTEiKNiYVINBw== X-Received: by 2002:a17:90a:6f06:b0:1df:c19b:627d with SMTP id d6-20020a17090a6f0600b001dfc19b627dmr13226391pjk.95.1653083122392; Fri, 20 May 2022 14:45:22 -0700 (PDT) Received: from nathanxps13 ([50.54.155.70]) by smtp.gmail.com with ESMTPSA id d66-20020a621d45000000b005182fd977e4sm2288463pfd.108.2022.05.20.14.45.21 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 20 May 2022 14:45:21 -0700 (PDT) Date: Fri, 20 May 2022 14:45:19 -0700 From: Nathan Bossart To: Shinya Kato Cc: =?utf-8?Q?Przemys=C5=82aw?= Sztoch , "David G. Johnston" , Robert Haas , Michael Paquier , Kyotaro Horiguchi , Daniel Gustafsson , pgsql-hackers@postgresql.org Subject: Re: Add --{no-,}bypassrls flags to createuser Message-ID: <20220520214519.GA832375@nathanxps13> References: <20220419.105012.1677795152077894444.horikyota.ntt@gmail.com> <20220425201947.GA2935432@nathanxps13> <4975ec63b73cfe4034867e90edbe16f8@oss.nttdata.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="yrj/dFKFPuw6o+aM" Content-Disposition: inline In-Reply-To: <4975ec63b73cfe4034867e90edbe16f8@oss.nttdata.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --yrj/dFKFPuw6o+aM Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 --yrj/dFKFPuw6o+aM Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0001-Add-missing-options-to-createuser.patch" From f1c00de3e3daf0150fdaa6ed5fe4215ebf928b6a Mon Sep 17 00:00:00 2001 From: Nathan Bossart 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 + + + + + + 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 + switches. + + + + @@ -204,6 +218,18 @@ PostgreSQL documentation + + + + + + 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 switches. + + + + @@ -258,6 +284,17 @@ PostgreSQL documentation + + + + + + Set a date and time after which the role's password is no longer valid. + The default is to set no password expiry date. + + + + @@ -290,6 +327,25 @@ PostgreSQL documentation + + + + + The new user will bypass every row-level security (RLS) policy. + + + + + + + + + The new user will not bypass row-level security (RLS) policies. This is + the default. + + + + 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 --yrj/dFKFPuw6o+aM--