From: Nathan Bossart <nathandbossart@gmail.com>
To: Shinya Kato <Shinya11.Kato@oss.nttdata.com>
Cc: Przemysław Sztoch <przemyslaw@sztoch.pl>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Add --{no-,}bypassrls flags to createuser
Date: Fri, 20 May 2022 14:45:19 -0700
Message-ID: <20220520214519.GA832375@nathanxps13> (raw)
In-Reply-To: <4975ec63b73cfe4034867e90edbe16f8@oss.nttdata.com>
References: <CA+TgmoYO090Xkwp--vCo0FGrFZBTG=V7wE3EF5WEAPcrFTpbSg@mail.gmail.com>
<20220419.105012.1677795152077894444.horikyota.ntt@gmail.com>
<CA+TgmoaTNwzZ6LXgaMzB03J6iijAPqSys39xHd7e6Fwb__3V1g@mail.gmail.com>
<YmDdxXPVNR8Km0v/@paquier.xyz>
<CA+TgmoYeZg5deFv+r-dDvWQLuSOiTNAUbcsx4kY2XhGSeGz7FA@mail.gmail.com>
<CAKFQuwa7kaFmEMszAAkpy-RiYZjW4M3MGo4-EidExZbOAgwQkg@mail.gmail.com>
<20220425201947.GA2935432@nathanxps13>
<d2d771c6cf1ed0f59513eb01c0cc0c78@oss.nttdata.com>
<a720383d-9bf2-fcb7-22fa-aeea114c86a5@sztoch.pl>
<4975ec63b73cfe4034867e90edbe16f8@oss.nttdata.com>
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.comAttachments:
[text/x-diff] v6-0001-Add-missing-options-to-createuser.patch (11.1K, ../20220520214519.GA832375@nathanxps13/2-v6-0001-Add-missing-options-to-createuser.patch)
download | inline diff:
From f1c00de3e3daf0150fdaa6ed5fe4215ebf928b6a Mon Sep 17 00:00:00 2001
From: Nathan Bossart <bossartn@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.sgmlindex 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.cindex 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.plindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, Shinya11.Kato@oss.nttdata.com, przemyslaw@sztoch.pl, david.g.johnston@gmail.com, robertmhaas@gmail.com, michael@paquier.xyz, horikyota.ntt@gmail.com, daniel@yesql.se
Subject: Re: Add --{no-,}bypassrls flags to createuser
In-Reply-To: <20220520214519.GA832375@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox