Add --{no-,}bypassrls flags to createuser
Hi,
Add --{no-,}bypassrls flags to createuser.
The following is an example of execution.
--
$ createuser a --bypassrls
$ psql -c "\du a"
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
a | Bypass RLS | {}
--
Do you think?
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v1-add-bypassrls-flag-to-createuser.patchtext/x-diff; name=v1-add-bypassrls-flag-to-createuser.patchDownload
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 17579e50af..6c2ee1e0c6 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -290,6 +290,28 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will have the <literal>BYPASSRLS</literal> privilege,
+ which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--no-bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will not have the <literal>BYPASSRLS</literal>
+ privilege, which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </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..5b363b6f54 100644
--- a/src/bin/scripts/createuser.c
+++ b/src/bin/scripts/createuser.c
@@ -48,6 +48,8 @@ main(int argc, char *argv[])
{"replication", no_argument, NULL, 1},
{"no-replication", no_argument, NULL, 2},
{"interactive", no_argument, NULL, 3},
+ {"bypassrls", no_argument, NULL, 4},
+ {"no-bypassrls", no_argument, NULL, 5},
{"connection-limit", required_argument, NULL, 'c'},
{"pwprompt", no_argument, NULL, 'P'},
{"encrypted", no_argument, NULL, 'E'},
@@ -76,7 +78,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;
@@ -165,6 +168,12 @@ main(int argc, char *argv[])
case 3:
interactive = true;
break;
+ case 4:
+ bypassrls = TRI_YES;
+ break;
+ case 5:
+ bypassrls = TRI_NO;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -304,6 +313,10 @@ 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 (roles.head != NULL)
@@ -366,6 +379,8 @@ help(const char *progname)
" 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"));
At Wed, 13 Apr 2022 14:51:35 +0900, Shinya Kato <Shinya11.Kato@oss.nttdata.com> wrote in
Hi,
Add --{no-,}bypassrls flags to createuser.
The following is an example of execution.
--
$ createuser a --bypassrls
$ psql -c "\du a"
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
a | Bypass RLS | {}--
Do you think?
It is sensible to rig createuser command with full capability of
CREATE ROLE is reasonable.
Only --replication is added by commit 9b8aff8c19 (2010) since
8ae0d476a9 (2005). BYPASSRLS and NOBYPASSRLS were introduced by
491c029dbc (2014) but it seems to have forgotten to add the
corresponding createuser options.
By a quick search, found a few other CREATE ROLE optinos that are not
supported by createuser.
VALID UNTIL
ROLE (IN ROLE is -g/--role)
ADMIN
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Wed, Apr 13, 2022 at 03:46:25PM +0900, Kyotaro Horiguchi wrote:
It is sensible to rig createuser command with full capability of
CREATE ROLE is reasonable.Only --replication is added by commit 9b8aff8c19 (2010) since
8ae0d476a9 (2005). BYPASSRLS and NOBYPASSRLS were introduced by
491c029dbc (2014) but it seems to have forgotten to add the
corresponding createuser options.By a quick search, found a few other CREATE ROLE optinos that are not
supported by createuser.
My question is: is BYPASSRLS common enough to justify having a switch
to createuser? As the development cycle of 15 has just finished and
that we are in feature freeze, you may want to hold on new patches for
a bit. The next commit fest is planned for July.
--
Michael
At Wed, 13 Apr 2022 16:10:01 +0900, Michael Paquier <michael@paquier.xyz> wrote in
On Wed, Apr 13, 2022 at 03:46:25PM +0900, Kyotaro Horiguchi wrote:
It is sensible to rig createuser command with full capability of
CREATE ROLE is reasonable.Only --replication is added by commit 9b8aff8c19 (2010) since
8ae0d476a9 (2005). BYPASSRLS and NOBYPASSRLS were introduced by
491c029dbc (2014) but it seems to have forgotten to add the
corresponding createuser options.By a quick search, found a few other CREATE ROLE optinos that are not
supported by createuser.My question is: is BYPASSRLS common enough to justify having a switch
to createuser? As the development cycle of 15 has just finished and
that we are in feature freeze, you may want to hold on new patches for
a bit. The next commit fest is planned for July.
I don't think there's a definitive criteria (other than feasibility)
for whether each CREATE ROLE option should have the correspondent
option in the createuser command. I don't see a clear reason why
createuser command should not have the option.
As far as schedules are concerned, I don't think this has anything to
do with 15.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Wed, Apr 13, 2022 at 4:35 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
I don't think there's a definitive criteria (other than feasibility)
for whether each CREATE ROLE option should have the correspondent
option in the createuser command. I don't see a clear reason why
createuser command should not have the option.
+1.
--
Robert Haas
EDB: http://www.enterprisedb.com
On 2022-04-13 17:35, Kyotaro Horiguchi wrote:
At Wed, 13 Apr 2022 16:10:01 +0900, Michael Paquier
<michael@paquier.xyz> wrote inOn Wed, Apr 13, 2022 at 03:46:25PM +0900, Kyotaro Horiguchi wrote:
It is sensible to rig createuser command with full capability of
CREATE ROLE is reasonable.Only --replication is added by commit 9b8aff8c19 (2010) since
8ae0d476a9 (2005). BYPASSRLS and NOBYPASSRLS were introduced by
491c029dbc (2014) but it seems to have forgotten to add the
corresponding createuser options.By a quick search, found a few other CREATE ROLE optinos that are not
supported by createuser.My question is: is BYPASSRLS common enough to justify having a switch
to createuser? As the development cycle of 15 has just finished and
that we are in feature freeze, you may want to hold on new patches for
a bit. The next commit fest is planned for July.I don't think there's a definitive criteria (other than feasibility)
for whether each CREATE ROLE option should have the correspondent
option in the createuser command. I don't see a clear reason why
createuser command should not have the option.
Thank you for the review!
I created a new patch containing 'VALID UNTIL', 'ADMIN', and 'ROLE'.
To add the ROLE clause, the originally existing --role option
(corresponding to the IN ROLE clause) is changed to the --in-role
option. Would this not be good from a backward compatibility standpoint?
As far as schedules are concerned, I don't think this has anything to
do with 15.
I have registered this patch for the July commit fest.
--
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v2-add-bypassrls-flag-to-createuser.patchtext/x-diff; name=v2-add-bypassrls-flag-to-createuser.patchDownload
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 17579e50af..fc477605f6 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>
+ The new role will be added immediately as a member with admin option
+ of this role.
+ Multiple roles to which new role will be added as a member with admin
+ option 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>
@@ -132,7 +146,7 @@ PostgreSQL documentation
<varlistentry>
<term><option>-g <replaceable class="parameter">role</replaceable></option></term>
- <term><option>--role=<replaceable class="parameter">role</replaceable></option></term>
+ <term><option>--in-role=<replaceable class="parameter">role</replaceable></option></term>
<listitem>
<para>
Indicates role to which this role will be added immediately as a new
@@ -143,6 +157,19 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-G <replaceable class="parameter">role</replaceable></option></term>
+ <term><option>--role=<replaceable class="parameter">role</replaceable></option></term>
+ <listitem>
+ <para>
+ The new role will be added immediately as a member of this role.
+ Multiple roles to which new role will be added as a member
+ can be specified by writing multiple
+ <option>-G</option> switches.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-i</option></term>
<term><option>--inherit</option></term>
@@ -258,6 +285,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 timestamp after which the role's password is no longer valid.
+ The default is to set no expiration.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-V</option></term>
<term><option>--version</option></term>
@@ -290,6 +328,28 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will have the <literal>BYPASSRLS</literal> privilege,
+ which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--no-bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will not have the <literal>BYPASSRLS</literal>
+ privilege, which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-?</option></term>
<term><option>--help</option></term>
diff --git a/src/bin/pg_basebackup/t/010_pg_basebackup.pl b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
index 7309ebddea..3e8d84de36 100644
--- a/src/bin/pg_basebackup/t/010_pg_basebackup.pl
+++ b/src/bin/pg_basebackup/t/010_pg_basebackup.pl
@@ -587,7 +587,7 @@ ok(-f "$tempdir/backuponserver/base.tar", 'backup tar was created');
rmtree("$tempdir/backuponserver");
$node->command_ok(
- [qw(createuser --replication --role=pg_write_server_files backupuser)],
+ [qw(createuser --replication --in-role=pg_write_server_files backupuser)],
'create backup user');
$node->command_ok(
[ @pg_basebackup_defs, '-U', 'backupuser', '--target', "server:$tempdir/backuponserver", '-X', 'none' ],
diff --git a/src/bin/scripts/createuser.c b/src/bin/scripts/createuser.c
index bfba0d09d1..0a31593055 100644
--- a/src/bin/scripts/createuser.c
+++ b/src/bin/scripts/createuser.c
@@ -31,7 +31,7 @@ main(int argc, char *argv[])
{"host", required_argument, NULL, 'h'},
{"port", required_argument, NULL, 'p'},
{"username", required_argument, NULL, 'U'},
- {"role", required_argument, NULL, 'g'},
+ {"in-role", required_argument, NULL, 'g'},
{"no-password", no_argument, NULL, 'w'},
{"password", no_argument, NULL, 'W'},
{"echo", no_argument, NULL, 'e'},
@@ -51,7 +51,12 @@ main(int argc, char *argv[])
{"connection-limit", required_argument, NULL, 'c'},
{"pwprompt", no_argument, NULL, 'P'},
{"encrypted", no_argument, NULL, 'E'},
- {NULL, 0, NULL, 0}
+ {"bypassrls", no_argument, NULL, 4},
+ {"no-bypassrls", no_argument, NULL, 5},
+ {"valid-until", required_argument, NULL, 'v'},
+ {"role", required_argument, NULL, 'G'},
+ {"admin", required_argument, NULL, 'a'},
+ {NULL, 0, NULL, 0},
};
const char *progname;
@@ -69,6 +74,9 @@ main(int argc, char *argv[])
int conn_limit = -2; /* less than minimum valid value */
bool pwprompt = false;
char *newpassword = NULL;
+ SimpleStringList in_roles = {NULL, NULL};
+ SimpleStringList admins = {NULL, NULL};
+ char *timestamp = 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:G:a:",
long_options, &optindex)) != -1)
{
switch (c)
@@ -104,7 +113,7 @@ main(int argc, char *argv[])
username = pg_strdup(optarg);
break;
case 'g':
- simple_string_list_append(&roles, optarg);
+ simple_string_list_append(&in_roles, optarg);
break;
case 'w':
prompt_password = TRI_NO;
@@ -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':
+ timestamp = pg_strdup(optarg);
+ break;
+ case 'G':
+ simple_string_list_append(&roles, 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,14 +328,34 @@ 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 (roles.head != NULL)
+ if (timestamp != NULL)
+ appendPQExpBuffer(&sql, " VALID UNTIL '%s'", timestamp);
+ if (in_roles.head != NULL)
{
SimpleStringListCell *cell;
appendPQExpBufferStr(&sql, " IN ROLE ");
+ for (cell = in_roles.head; cell; cell = cell->next)
+ {
+ if (cell->next)
+ appendPQExpBuffer(&sql, "%s,", fmtId(cell->val));
+ else
+ appendPQExpBufferStr(&sql, fmtId(cell->val));
+ }
+ }
+ if (roles.head != NULL)
+ {
+ SimpleStringListCell *cell;
+
+ appendPQExpBufferStr(&sql, " ROLE ");
+
for (cell = roles.head; cell; cell = cell->next)
{
if (cell->next)
@@ -320,6 +364,21 @@ main(int argc, char *argv[])
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,11 +405,14 @@ 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\n"
+ " with admin 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"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
- printf(_(" -g, --role=ROLE new role will be a member of this role\n"));
+ printf(_(" -g, --in-role=ROLE new role will be a member of this role\n"));
+ printf(_(" -G, --role=ROLE this role will be a member of new role\n"));
printf(_(" -i, --inherit role inherits privileges of roles it is a\n"
" member of (default)\n"));
printf(_(" -I, --no-inherit role does not inherit privileges\n"));
@@ -361,11 +423,14 @@ help(const char *progname)
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 timestamp 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"));
On 14 Apr 2022, at 09:42, Shinya Kato <Shinya11.Kato@oss.nttdata.com> wrote:
To add the ROLE clause, the originally existing --role option (corresponding to the IN ROLE clause) is changed to the --in-role option. Would this not be good from a backward compatibility standpoint?
- printf(_(" -g, --role=ROLE new role will be a member of this role\n"));
+ printf(_(" -g, --in-role=ROLE new role will be a member of this role\n"));
+ printf(_(" -G, --role=ROLE this role will be a member of new role\n"));Won't this make existing scripts to behave differently after an upgrade? That
seems like something we should avoid at all costs.
--
Daniel Gustafsson https://vmware.com/
On 2022-04-14 18:57, Daniel Gustafsson wrote:
On 14 Apr 2022, at 09:42, Shinya Kato <Shinya11.Kato@oss.nttdata.com>
wrote:To add the ROLE clause, the originally existing --role option
(corresponding to the IN ROLE clause) is changed to the --in-role
option. Would this not be good from a backward compatibility
standpoint?- printf(_(" -g, --role=ROLE new role will be a member of this role\n")); + printf(_(" -g, --in-role=ROLE new role will be a member of this role\n")); + printf(_(" -G, --role=ROLE this role will be a member of new role\n"));Won't this make existing scripts to behave differently after an
upgrade? That
seems like something we should avoid at all costs.
I understand. For backward compatibility, I left the ROLE clause option
as it is and changed the IN ROLE clause option to --membership option.
--
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v3-add-bypassrls-flag-to-createuser.patchtext/x-diff; name=v3-add-bypassrls-flag-to-createuser.patchDownload
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 17579e50af..20c60092c0 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>
+ The new role will be added immediately as a member with admin option
+ of this role.
+ Multiple roles to which new role will be added as a member with admin
+ option 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,19 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-m <replaceable class="parameter">role</replaceable></option></term>
+ <term><option>--membership=<replaceable class="parameter">role</replaceable></option></term>
+ <listitem>
+ <para>
+ The new role will be added immediately as a member of this role.
+ Multiple roles to which new role will be added as a member
+ 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 +285,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 timestamp after which the role's password is no longer valid.
+ The default is to set no expiration.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-V</option></term>
<term><option>--version</option></term>
@@ -290,6 +328,28 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will have the <literal>BYPASSRLS</literal> privilege,
+ which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--no-bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will not have the <literal>BYPASSRLS</literal>
+ privilege, which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </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..f7ec842cb7 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'},
+ {"membership", required_argument, NULL, 'm'},
+ {"admin", required_argument, NULL, 'a'},
{NULL, 0, NULL, 0}
};
@@ -69,6 +74,9 @@ main(int argc, char *argv[])
int conn_limit = -2; /* less than minimum valid value */
bool pwprompt = false;
char *newpassword = NULL;
+ SimpleStringList memberships = {NULL, NULL};
+ SimpleStringList admins = {NULL, NULL};
+ char *timestamp = 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':
+ timestamp = pg_strdup(optarg);
+ break;
+ case 'm':
+ simple_string_list_append(&memberships, 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,14 @@ 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 (timestamp != NULL)
+ appendPQExpBuffer(&sql, " VALID UNTIL '%s'", timestamp);
if (roles.head != NULL)
{
SimpleStringListCell *cell;
@@ -320,6 +350,35 @@ main(int argc, char *argv[])
appendPQExpBufferStr(&sql, fmtId(cell->val));
}
}
+ if (memberships.head != NULL)
+ {
+ SimpleStringListCell *cell;
+
+ appendPQExpBufferStr(&sql, " ROLE ");
+
+ for (cell = memberships.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,26 +405,32 @@ 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\n"
+ " with admin 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"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
- printf(_(" -g, --role=ROLE new role will be a member of this role\n"));
+ printf(_(" -g, --role=ROLE new role will be a member of this role\n"));
printf(_(" -i, --inherit role inherits privileges of roles it is a\n"
" member of (default)\n"));
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, --membership=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 timestamp 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"));
At Fri, 15 Apr 2022 14:55:48 +0900, Shinya Kato <Shinya11.Kato@oss.nttdata.com> wrote in
I understand. For backward compatibility, I left the ROLE clause
option as it is and changed the IN ROLE clause option to --membership
option.
Thanks!
- printf(_(" -g, --role=ROLE new role will be a member of this role\n"));
+ printf(_(" -g, --role=ROLE new role will be a member of this role\n"));This looks lik an unexpected change. We shoudl preserve it, but *I*
think that we can add a synonym of the old --role for
understandability/memorability. (By the way "-g" looks like coming
from "group", which looks somewhat strange..)
printf(_(" -b, --belongs-to=ROLE new role will be a member of this role\n"));
+ printf(_(" -m, --membership=ROLE this role will be a member of new role\n"));
membership sounds somewhat obscure, it seems *to me* members is clearer
printf(_(" -m, --member=ROLE new role will be a member of this role\n"));
I'd like to hear others' opinions.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Fri, Apr 15, 2022 at 2:33 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
printf(_(" -b, --belongs-to=ROLE new role will be a member of this role\n"));
+ printf(_(" -m, --membership=ROLE this role will be a member of new role\n"));
membership sounds somewhat obscure, it seems *to me* members is clearer
printf(_(" -m, --member=ROLE new role will be a member of this role\n"));
I'd like to hear others' opinions.
I think that we need to preserve consistency with the SQL syntax as
much as possible -- and neither MEMBER nor MEMBERSHIP nor BELONGS_TO
appear in that syntax. A lot of the terminology in this area seems
poorly chosen and confusing to me, but having two ways to refer to
something probably won't be an improvement even if the second name is
better-chosen than the first one.
--
Robert Haas
EDB: http://www.enterprisedb.com
Thanks!
At Mon, 18 Apr 2022 09:59:48 -0400, Robert Haas <robertmhaas@gmail.com> wrote in
On Fri, Apr 15, 2022 at 2:33 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:printf(_(" -b, --belongs-to=ROLE new role will be a member of this role\n"));
+ printf(_(" -m, --membership=ROLE this role will be a member of new role\n"));
membership sounds somewhat obscure, it seems *to me* members is clearer
printf(_(" -m, --member=ROLE new role will be a member of this role\n"));
I'd like to hear others' opinions.
I think that we need to preserve consistency with the SQL syntax as
much as possible -- and neither MEMBER nor MEMBERSHIP nor BELONGS_TO
appear in that syntax. A lot of the terminology in this area seems
poorly chosen and confusing to me, but having two ways to refer to
something probably won't be an improvement even if the second name is
better-chosen than the first one.
Hmm.. So, "-r/--role" and "-m/--member(ship)" is the (least worse) way
to go? Or we can give up adding -m for the reason of being hard to
name it..
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Mon, Apr 18, 2022 at 9:50 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
Hmm.. So, "-r/--role" and "-m/--member(ship)" is the (least worse) way
to go? Or we can give up adding -m for the reason of being hard to
name it..
Hmm, yeah, I hadn't quite realized what the problem was when I wrote
that. I honestly don't know what to do about that. Renaming the
existing option is not great, but having the syntax diverge between
SQL and CLI is not great either. Giving up is also not great. Not sure
what is best.
--
Robert Haas
EDB: http://www.enterprisedb.com
At Tue, 19 Apr 2022 12:13:51 -0400, Robert Haas <robertmhaas@gmail.com> wrote in
On Mon, Apr 18, 2022 at 9:50 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:Hmm.. So, "-r/--role" and "-m/--member(ship)" is the (least worse) way
to go? Or we can give up adding -m for the reason of being hard to
name it..Hmm, yeah, I hadn't quite realized what the problem was when I wrote
that. I honestly don't know what to do about that. Renaming the
existing option is not great, but having the syntax diverge between
SQL and CLI is not great either. Giving up is also not great. Not sure
what is best.
Exactly.. So I'm stuckX(
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Tue, Apr 19, 2022 at 12:13:51PM -0400, Robert Haas wrote:
On Mon, Apr 18, 2022 at 9:50 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:Hmm.. So, "-r/--role" and "-m/--member(ship)" is the (least worse) way
to go? Or we can give up adding -m for the reason of being hard to
name it..Hmm, yeah, I hadn't quite realized what the problem was when I wrote
that. I honestly don't know what to do about that. Renaming the
existing option is not great, but having the syntax diverge between
SQL and CLI is not great either. Giving up is also not great. Not sure
what is best.
Changing one existing option to mean something entirely different
should be avoided, as this could lead to silent breakages. As the
origin of the problem is that the option --role means "IN ROLE" in the
SQL grammar, we could keep around --role for compatibility while
marking it deprecated, and add two new options whose names would be
more consistent with each other. One choice could be --role-name and
--in-role-name, where --in-role-name maps to the older --role, just to
give an idea.
--
Michael
On Thu, Apr 21, 2022 at 12:30 AM Michael Paquier <michael@paquier.xyz> wrote:
On Tue, Apr 19, 2022 at 12:13:51PM -0400, Robert Haas wrote:
On Mon, Apr 18, 2022 at 9:50 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:Hmm.. So, "-r/--role" and "-m/--member(ship)" is the (least worse) way
to go? Or we can give up adding -m for the reason of being hard to
name it..Hmm, yeah, I hadn't quite realized what the problem was when I wrote
that. I honestly don't know what to do about that. Renaming the
existing option is not great, but having the syntax diverge between
SQL and CLI is not great either. Giving up is also not great. Not sure
what is best.Changing one existing option to mean something entirely different
should be avoided, as this could lead to silent breakages. As the
origin of the problem is that the option --role means "IN ROLE" in the
SQL grammar, we could keep around --role for compatibility while
marking it deprecated, and add two new options whose names would be
more consistent with each other. One choice could be --role-name and
--in-role-name, where --in-role-name maps to the older --role, just to
give an idea.
I don't think that having both --role and --role-name, doing different
things, is going to be clear at all.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Thu, Apr 21, 2022 at 12:51 PM Robert Haas <robertmhaas@gmail.com> wrote:
On Thu, Apr 21, 2022 at 12:30 AM Michael Paquier <michael@paquier.xyz>
wrote:On Tue, Apr 19, 2022 at 12:13:51PM -0400, Robert Haas wrote:
On Mon, Apr 18, 2022 at 9:50 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:Hmm.. So, "-r/--role" and "-m/--member(ship)" is the (least worse) way
to go? Or we can give up adding -m for the reason of being hard to
name it..Hmm, yeah, I hadn't quite realized what the problem was when I wrote
that. I honestly don't know what to do about that. Renaming the
existing option is not great, but having the syntax diverge between
SQL and CLI is not great either. Giving up is also not great. Not sure
what is best.Changing one existing option to mean something entirely different
should be avoided, as this could lead to silent breakages. As the
origin of the problem is that the option --role means "IN ROLE" in the
SQL grammar, we could keep around --role for compatibility while
marking it deprecated, and add two new options whose names would be
more consistent with each other. One choice could be --role-name and
--in-role-name, where --in-role-name maps to the older --role, just to
give an idea.I don't think that having both --role and --role-name, doing different
things, is going to be clear at all.
-g/--role or maybe/additionally (--in-role)?
-m/--role-to or maybe/additionally (--to-role)?
I'm ok with -m/--member as well (like with --role only one role can be
specified per switch instance so member, not membership, the later meaning,
at least for me, the collective).
That -m doesn't match --role-to is no worse than -g not matching --role, a
short option seems worthwhile, and the -m (membership) mnemonic should be
simple to pick-up.
I don't see the addition of "-name" to the option name being beneficial.
Yes, the standard doesn't use the "TO" prefix for "ROLE" - but taking that
liberty for consistency here is very appealing and there isn't another SQL
clause that it would be confused with.
David J.
On Thu, Apr 21, 2022 at 01:21:57PM -0700, David G. Johnston wrote:
I'm ok with -m/--member as well (like with --role only one role can be
specified per switch instance so member, not membership, the later meaning,
at least for me, the collective).That -m doesn't match --role-to is no worse than -g not matching --role, a
short option seems worthwhile, and the -m (membership) mnemonic should be
simple to pick-up.I don't see the addition of "-name" to the option name being beneficial.
Yes, the standard doesn't use the "TO" prefix for "ROLE" - but taking that
liberty for consistency here is very appealing and there isn't another SQL
clause that it would be confused with.
+1 for "member". It might not be perfect, but IMO it's the clearest
option.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Thank you for the reviews!
On 2022-04-26 05:19, Nathan Bossart wrote:
- printf(_(" -g, --role=ROLE new role will be a member of this role\n")); + printf(_(" -g, --role=ROLE new role will be a member of this role\n")); This looks lik an unexpected change.
I fixed it.
I'm ok with -m/--member as well (like with --role only one role can be
specified per switch instance so member, not membership, the later
meaning,
at least for me, the collective).That -m doesn't match --role-to is no worse than -g not matching
--role, a
short option seems worthwhile, and the -m (membership) mnemonic should
be
simple to pick-up.I don't see the addition of "-name" to the option name being
beneficial.Yes, the standard doesn't use the "TO" prefix for "ROLE" - but taking
that
liberty for consistency here is very appealing and there isn't another
SQL
clause that it would be confused with.+1 for "member". It might not be perfect, but IMO it's the clearest
option.
Thanks! I changed the option "--membership" to "--member".
For now, I also think "-m / --member" is the best choice, although it is
ambiguous:(
I'd like to hear others' opinions.
regards
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v4-add-bypassrls-flag-to-createuser.patchtext/x-diff; name=v4-add-bypassrls-flag-to-createuser.patchDownload
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 17579e50af..189ca5bb67 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>
+ The new role will be added immediately as a member with admin option
+ of this role.
+ Multiple roles to which new role will be added as a member with admin
+ option 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,19 @@ 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>
+ The new role will be added immediately as a member of this role.
+ Multiple roles to which new role will be added as a member
+ 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 +285,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 timestamp after which the role's password is no longer valid.
+ The default is to set no expiration.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-V</option></term>
<term><option>--version</option></term>
@@ -290,6 +328,28 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will have the <literal>BYPASSRLS</literal> privilege,
+ which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--no-bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will not have the <literal>BYPASSRLS</literal>
+ privilege, which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </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..73cd1b479e 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}
};
@@ -69,6 +74,9 @@ main(int argc, char *argv[])
int conn_limit = -2; /* less than minimum valid value */
bool pwprompt = false;
char *newpassword = NULL;
+ SimpleStringList members = {NULL, NULL};
+ SimpleStringList admins = {NULL, NULL};
+ char *timestamp = 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':
+ timestamp = 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,14 @@ 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 (timestamp != NULL)
+ appendPQExpBuffer(&sql, " VALID UNTIL '%s'", timestamp);
if (roles.head != NULL)
{
SimpleStringListCell *cell;
@@ -320,6 +350,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 +405,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\n"
+ " with admin 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 +417,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 timestamp 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"));
On Thu, Apr 28, 2022 at 03:06:30PM +0900, Shinya Kato wrote:
On 2022-04-26 05:19, Nathan Bossart wrote:
+1 for "member". It might not be perfect, but IMO it's the clearest
option.Thanks! I changed the option "--membership" to "--member".
Thanks for the new patch! Would you mind adding some tests for the new
options?
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Dear Shinya,
Too bad there's no --comment parameter to do COMMENT ON ROLE name IS
'Comment';
As you already make such changes in createuser, I would like to ask for
an additional --comment parameter
that will allow sysadmins to set a comment with additional information
about the new DB user.
psql is scary for some. :-)
Overall a very useful patch. I needed bypassrls several times recently.
Shinya Kato wrote on 4/28/2022 8:06 AM:
Thank you for the reviews!
On 2022-04-26 05:19, Nathan Bossart wrote:
- printf(_(" -g, --role=ROLE new role will be a member of this role\n")); + printf(_(" -g, --role=ROLE new role will be a member of this role\n")); This looks lik an unexpected change.I fixed it.
I'm ok with -m/--member as well (like with --role only one role can be
specified per switch instance so member, not membership, the later
meaning,
at least for me, the collective).That -m doesn't match --role-to is no worse than -g not matching
--role, a
short option seems worthwhile, and the -m (membership) mnemonic
should be
simple to pick-up.I don't see the addition of "-name" to the option name being
beneficial.Yes, the standard doesn't use the "TO" prefix for "ROLE" - but
taking that
liberty for consistency here is very appealing and there isn't
another SQL
clause that it would be confused with.+1 for "member". It might not be perfect, but IMO it's the clearest
option.Thanks! I changed the option "--membership" to "--member".
For now, I also think "-m / --member" is the best choice, although it
is ambiguous:(
I'd like to hear others' opinions.regards
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Thanks for reviews and comments!
On 2022-05-06 07:08, Przemysław Sztoch wrote:
Thanks for the new patch! Would you mind adding some tests for the new
options?
I created a new patch to test the new options!
However, not all option tests exist, so it may be necessary to consider
whether to actually add this test.
Too bad there's no --comment parameter to do COMMENT ON ROLE name IS
'Comment';As you already make such changes in createuser, I would like to ask
for an additional --comment parameter
that will allow sysadmins to set a comment with additional information
about the new DB user.
psql is scary for some. :-)
Since the createuser command is a wrapper for the CREATE ROLE command, I
do not think it is appropriate to add options that the CREATE ROLE
command does not have.
--
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v5-add-bypassrls-flag-to-createuser.patchtext/x-diff; name=v5-add-bypassrls-flag-to-createuser.patchDownload
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 17579e50af..189ca5bb67 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>
+ The new role will be added immediately as a member with admin option
+ of this role.
+ Multiple roles to which new role will be added as a member with admin
+ option 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,19 @@ 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>
+ The new role will be added immediately as a member of this role.
+ Multiple roles to which new role will be added as a member
+ 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 +285,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 timestamp after which the role's password is no longer valid.
+ The default is to set no expiration.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-V</option></term>
<term><option>--version</option></term>
@@ -290,6 +328,28 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will have the <literal>BYPASSRLS</literal> privilege,
+ which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--no-bypassrls</option></term>
+ <listitem>
+ <para>
+ The new user will not have the <literal>BYPASSRLS</literal>
+ privilege, which is described more fully in the documentation for <xref
+ linkend="sql-createrole"/>.
+ </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..73cd1b479e 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}
};
@@ -69,6 +74,9 @@ main(int argc, char *argv[])
int conn_limit = -2; /* less than minimum valid value */
bool pwprompt = false;
char *newpassword = NULL;
+ SimpleStringList members = {NULL, NULL};
+ SimpleStringList admins = {NULL, NULL};
+ char *timestamp = 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':
+ timestamp = 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,14 @@ 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 (timestamp != NULL)
+ appendPQExpBuffer(&sql, " VALID UNTIL '%s'", timestamp);
if (roles.head != NULL)
{
SimpleStringListCell *cell;
@@ -320,6 +350,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 +405,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\n"
+ " with admin 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 +417,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 timestamp 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..5df5134a26 100644
--- a/src/bin/scripts/t/040_createuser.pl
+++ b/src/bin/scripts/t/040_createuser.pl
@@ -32,6 +32,22 @@ $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;/,
+ 'create a role and add it to another user with admin option');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_role3', '-m', 'regress_user1' ],
+ qr/statement: CREATE ROLE regress_role3 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ROLE regress_user1;/,
+ 'create a role and add it to another user');
+$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 user with a password expiration timestamp');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user5', '--bypassrls' ],
+ qr/statement: CREATE ROLE regress_user5 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN BYPASSRLS;/,
+ 'create a BYPASSRLS user');
$node->command_fails([ 'createuser', 'regress_user1' ],
'fails if role already exists');
On Wed, May 18, 2022 at 6:35 PM Shinya Kato <Shinya11.Kato@oss.nttdata.com>
wrote:
Too bad there's no --comment parameter to do COMMENT ON ROLE name IS
'Comment';As you already make such changes in createuser, I would like to ask
for an additional --comment parameter
that will allow sysadmins to set a comment with additional information
about the new DB user.
psql is scary for some. :-)Since the createuser command is a wrapper for the CREATE ROLE command, I
do not think it is appropriate to add options that the CREATE ROLE
command does not have.
I think that this feature is at least worth considering - but absent an
existing command that does this I would agree that doing so constitutes a
separate feature.
As an aside, I'd rather overcome this particular objection by having the
CREATE object command all accept an optional "COMMENT IS" clause.
David J.
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
Attachments:
v6-0001-Add-missing-options-to-createuser.patchtext/x-diff; charset=us-asciiDownload
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.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
David G. Johnston wrote on 5/19/2022 3:46 AM:
I think that this feature is at least worth considering - but absent
an existing command that does this I would agree that doing so
constitutes a separate feature.As an aside, I'd rather overcome this particular objection by having
the CREATE object command all accept an optional "COMMENT IS" clause.David J.
The createuser command is typically used by IT personnel unfamiliar with
SQL and unfamiliar with psql.
They often use this command because software installation procedures
require it.
Lack of comment then causes more mess in the configuration of larger
servers.
I still think it's worth adding the --comment argument to execute the
next SQL statement by createuser.
This will simplify the setup scripts and installation instructions for
the final software.
I believe that it is not worth dividing it into a separate program.
The same --comment argument is needed for the createdb command.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
At Sun, 22 May 2022 09:55:37 +0200, Przemys占쏙옙aw Sztoch <przemyslaw@sztoch.pl> wrote in
David G. Johnston wrote on 5/19/2022 3:46 AM:
As an aside, I'd rather overcome this particular objection by having
the CREATE object command all accept an optional "COMMENT IS" clause.I believe that it is not worth dividing it into a separate program.
The same --comment argument is needed for the createdb command.
David didn't say that it should be another "program", but said it
should be another "patch/development", because how we implement the
--comment feature is apparently controversial.
It doesn't seem to be explicity mentioned that "createuser is mere a
shell-substitute for the SQL CREATE ROLE", but I feel the same with
Shinya that it is. We could directly invoke "COMMENT ON" from
createuser command, but I think it is not the way to go in that light.
We can either add COMMENT clause only to "CREATE ROLE" , or "COMMENT
IS" clause to all (or most of) "CREATE object" commands, or something
others. (Perhaps "COMMETN IS" requires "ALTER object" handle comments,
and I'm not sure how we think about the difference of it from "comment
on" command.) We might return to "comment on" in the end..
Anyway, after fixing that issue we will modify the createrole command
so that it uses the new SQL feature. I find no hard obstacles in
reaching there in the 16 cycle.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On 2022-05-21 06:45, Nathan Bossart wrote:
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?
Thanks for updating the patch!
It looks good to me.
On 2022-05-23 10:32, Kyotaro Horiguchi wrote:
Anyway, after fixing that issue we will modify the createrole command
so that it uses the new SQL feature. I find no hard obstacles in
reaching there in the 16 cycle.
+1.
--
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
On Fri, May 20, 2022 at 02:45:19PM -0700, Nathan Bossart wrote:
Thanks for the new patch! I attached a new version with a few small
changes. What do you think?
So you have settled down to --member to emulate the clause ROLE.
Well, this choice is fine by me at the end.
+$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');
May I ask for the addition of tests when one specifies multiple
switches for --admin and --member? This would check the code path
where you build a list of role names. You could check fancier string
patterns, while on it, to look after the use of fmtId(), say with
role names that include whitespaces or such.
--
Michael
On 2022-05-23 16:29, Michael Paquier wrote:
+$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');May I ask for the addition of tests when one specifies multiple
switches for --admin and --member? This would check the code path
where you build a list of role names. You could check fancier string
patterns, while on it, to look after the use of fmtId(), say with
role names that include whitespaces or such.
Thanks!
I changed to the test that describes multiple "-m".
It seems to be working without any problems, how about it?
--
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v7-0001-Add-missing-options-to-createuser.patchtext/x-diff; name=v7-0001-Add-missing-options-to-createuser.patchDownload
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..bb447e7631 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_user4', '-a', 'regress_user1' ],
+ qr/statement: CREATE ROLE regress_user4 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_user5', '-m', 'regress_user2', '-m', 'regress_user3' ],
+ qr/statement: CREATE ROLE regress_user5 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ROLE regress_user2,regress_user3;/,
+ 'add a role as a member of the newly created role');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user6', '-v', '20291231' ],
+ qr/statement: CREATE ROLE regress_user6 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN VALID UNTIL \'20291231\';/,
+ 'create a role with a password expiration date');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user7', '--bypassrls' ],
+ qr/statement: CREATE ROLE regress_user7 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN BYPASSRLS;/,
+ 'create a BYPASSRLS role');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user8', '--no-bypassrls' ],
+ qr/statement: CREATE ROLE regress_user8 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOBYPASSRLS;/,
+ 'create a role without BYPASSRLS');
$node->command_fails([ 'createuser', 'regress_user1' ],
'fails if role already exists');
On Mon, May 23, 2022 at 11:55:43PM +0900, Shinya Kato wrote:
On 2022-05-23 16:29, Michael Paquier wrote:
May I ask for the addition of tests when one specifies multiple
switches for --admin and --member? This would check the code path
where you build a list of role names. You could check fancier string
patterns, while on it, to look after the use of fmtId(), say with
role names that include whitespaces or such.Thanks!
I changed to the test that describes multiple "-m".
It seems to be working without any problems, how about it?
Michael also requested a test for multiple -a switches and for fancier
string patterns. Once that is taken care of, I think this can be marked as
ready-for-committer.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
On Mon, May 23, 2022 at 09:37:35AM -0700, Nathan Bossart wrote:
Michael also requested a test for multiple -a switches and for fancier
string patterns. Once that is taken care of, I think this can be marked as
ready-for-committer.
Looking at v7, this means to extend the tests to process lists for
--admin with more name patterns. And while on it, we could do the
same for the existing command for --role, but this one is on me, being
overly-pedantic while looking at the patch :)
--
Michael
On 2022-05-24 11:09, Michael Paquier wrote:
On Mon, May 23, 2022 at 09:37:35AM -0700, Nathan Bossart wrote:
Michael also requested a test for multiple -a switches and for fancier
string patterns. Once that is taken care of, I think this can be
marked as
ready-for-committer.Looking at v7, this means to extend the tests to process lists for
--admin with more name patterns. And while on it, we could do the
same for the existing command for --role, but this one is on me, being
overly-pedantic while looking at the patch :)
Thanks! I fixed it.
--
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v8-0001-Add-missing-options-to-createuser.patchtext/x-diff; name=v8-0001-Add-missing-options-to-createuser.patchDownload
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..0e6b2b0b97 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_user4', '-a', 'regress_user1', '-a', 'regress_user2' ],
+ qr/statement: CREATE ROLE regress_user4 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ADMIN regress_user1,regress_user2;/,
+ 'add a role as a member with admin option of the newly created role');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user5', '-m', 'regress_user3', '-m', 'regress_user4' ],
+ qr/statement: CREATE ROLE regress_user5 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ROLE regress_user3,regress_user4;/,
+ 'add a role as a member of the newly created role');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user6', '-v', '20291231' ],
+ qr/statement: CREATE ROLE regress_user6 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN VALID UNTIL \'20291231\';/,
+ 'create a role with a password expiration date');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user7', '--bypassrls' ],
+ qr/statement: CREATE ROLE regress_user7 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN BYPASSRLS;/,
+ 'create a BYPASSRLS role');
+$node->issues_sql_like(
+ [ 'createuser', 'regress_user8', '--no-bypassrls' ],
+ qr/statement: CREATE ROLE regress_user8 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOBYPASSRLS;/,
+ 'create a role without BYPASSRLS');
$node->command_fails([ 'createuser', 'regress_user1' ],
'fails if role already exists');
On Tue, May 24, 2022 at 08:07:31PM +0900, Shinya Kato wrote:
On 2022-05-24 11:09, Michael Paquier wrote:
On Mon, May 23, 2022 at 09:37:35AM -0700, Nathan Bossart wrote:
Michael also requested a test for multiple -a switches and for fancier
string patterns. Once that is taken care of, I think this can be
marked as
ready-for-committer.Looking at v7, this means to extend the tests to process lists for
--admin with more name patterns. And while on it, we could do the
same for the existing command for --role, but this one is on me, being
overly-pedantic while looking at the patch :)Thanks! I fixed it.
We're still missing some "fancier" string patterns in the tests, but we
might just be nitpicking at this point.
I noticed that the cfbot tests for this are failing for Windows. I've
looked at the relevant logs a bit, and I'm not sure what is going on. The
expected log messages are indeed missing, but I haven't found any clues for
why those test cases are skipped.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
At Tue, 24 May 2022 10:09:10 -0700, Nathan Bossart <nathandbossart@gmail.com> wrote in
We're still missing some "fancier" string patterns in the tests, but we
might just be nitpicking at this point.
Such "fancier" strings should be properly handled by FmtId() and
appendStringLiteralConn. If this is a privilege escalating command,
we should have ones but this is not.
I noticed that the cfbot tests for this are failing for Windows. I've
looked at the relevant logs a bit, and I'm not sure what is going on. The
expected log messages are indeed missing, but I haven't found any clues for
why those test cases are skipped.
createuser command complains like this.
# Running: createuser regress_user4 -a regress_user1 -a regress_user2
createuser: error: too many command-line arguments (first is "-a")
hint: Try "createuser --help" for more information.
It seems like '-a' is not recognised as an option parameter.
(Fortunately, the ActiveState installer looks like having been fixed,
but something's still wrong..)
I reproduced the same failure at my hand and identified the
cause. Windows' version of getopt_long seems to dislike that
non-optional parameters precedes options.
createuser <user name to create> <options>
The test succeeded if I moved the <user name to create> to the end of
command line.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Wed, May 25, 2022 at 11:07:52AM +0900, Kyotaro Horiguchi wrote:
I reproduced the same failure at my hand and identified the
cause. Windows' version of getopt_long seems to dislike that
non-optional parameters precedes options.
Tweaking the list of arguments in some commands kicked by the TAP
tests to satisfy our implementation of getopt_long() has been the
origin of a couple of portability fixes, like ffd3980.
--
Michael
On 2022-05-25 12:47, Michael Paquier wrote:
On Wed, May 25, 2022 at 11:07:52AM +0900, Kyotaro Horiguchi wrote:
I reproduced the same failure at my hand and identified the
cause. Windows' version of getopt_long seems to dislike that
non-optional parameters precedes options.Tweaking the list of arguments in some commands kicked by the TAP
tests to satisfy our implementation of getopt_long() has been the
origin of a couple of portability fixes, like ffd3980.
Thanks! I fixed it.
On 2022-05-25 11:07, Kyotaro Horiguchi wrote:
At Tue, 24 May 2022 10:09:10 -0700, Nathan Bossart
<nathandbossart@gmail.com> wrote inWe're still missing some "fancier" string patterns in the tests, but
we
might just be nitpicking at this point.Such "fancier" strings should be properly handled by FmtId() and
appendStringLiteralConn. If this is a privilege escalating command,
we should have ones but this is not.
Sorry, I didn't quite understand the "fancier" pattern. Is a string like
this patch correct?
--
Regards,
--
Shinya Kato
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
Attachments:
v9-0001-Add-missing-options-to-createuser.patchtext/x-diff; name=v9-0001-Add-missing-options-to-createuser.patchDownload
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..4a4d59dd90 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', '-a', 'regress_user1', '-a', 'regress_user2', 'regress user #4'],
+ qr/statement: CREATE ROLE "regress user #4" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ADMIN regress_user1,regress_user2;/,
+ 'add a role as a member with admin option of the newly created role');
+$node->issues_sql_like(
+ [ 'createuser', '-m', 'regress_user3', '-m', 'regress user #4', 'REGRESS_USER5' ],
+ qr/statement: CREATE ROLE "REGRESS_USER5" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN ROLE regress_user3,"regress user #4";/,
+ 'add a role as a member of the newly created role');
+$node->issues_sql_like(
+ [ 'createuser', '-v', '20291231', 'regress_user6' ],
+ qr/statement: CREATE ROLE regress_user6 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN VALID UNTIL \'20291231\';/,
+ 'create a role with a password expiration date');
+$node->issues_sql_like(
+ [ 'createuser', '--bypassrls', 'regress_user7' ],
+ qr/statement: CREATE ROLE regress_user7 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN BYPASSRLS;/,
+ 'create a BYPASSRLS role');
+$node->issues_sql_like(
+ [ 'createuser', '--no-bypassrls', 'regress_user8' ],
+ qr/statement: CREATE ROLE regress_user8 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN NOBYPASSRLS;/,
+ 'create a role without BYPASSRLS');
$node->command_fails([ 'createuser', 'regress_user1' ],
'fails if role already exists');
At Thu, 26 May 2022 14:16:37 +0900, Shinya Kato <Shinya11.Kato@oss.nttdata.com> wrote in
On 2022-05-25 12:47, Michael Paquier wrote:
On Wed, May 25, 2022 at 11:07:52AM +0900, Kyotaro Horiguchi wrote:
I reproduced the same failure at my hand and identified the
cause. Windows' version of getopt_long seems to dislike that
non-optional parameters precedes options.Tweaking the list of arguments in some commands kicked by the TAP
tests to satisfy our implementation of getopt_long() has been the
origin of a couple of portability fixes, like ffd3980.Thanks! I fixed it.
On 2022-05-25 11:07, Kyotaro Horiguchi wrote:
At Tue, 24 May 2022 10:09:10 -0700, Nathan Bossart
<nathandbossart@gmail.com> wrote inWe're still missing some "fancier" string patterns in the tests, but
we
might just be nitpicking at this point.Such "fancier" strings should be properly handled by FmtId() and
appendStringLiteralConn. If this is a privilege escalating command,
we should have ones but this is not.Sorry, I didn't quite understand the "fancier" pattern. Is a string
like this patch correct?
FWIW, the "fancy" here causes me to think about something likely to
cause syntax breakage of the query to be sent.
createuser -a 'user"1' -a 'user"2' 'user"3'
createuser -v "2023-1-1'; DROP TABLE public.x; select '" hoge
BUT, thses should be prevented by the functions enumerated above. So,
I don't think we need them.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
On Thu, May 26, 2022 at 02:16:37PM +0900, Shinya Kato wrote:
On 2022-05-25 11:07, Kyotaro Horiguchi wrote:
At Tue, 24 May 2022 10:09:10 -0700, Nathan Bossart
<nathandbossart@gmail.com> wrote inWe're still missing some "fancier" string patterns in the tests, but
we
might just be nitpicking at this point.Such "fancier" strings should be properly handled by FmtId() and
appendStringLiteralConn. If this is a privilege escalating command,
we should have ones but this is not.Sorry, I didn't quite understand the "fancier" pattern. Is a string like
this patch correct?
Yes, thanks. I'm marking this as ready-for-committer.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
On Thu, May 26, 2022 at 04:47:46PM +0900, Kyotaro Horiguchi wrote:
FWIW, the "fancy" here causes me to think about something likely to
cause syntax breakage of the query to be sent.createuser -a 'user"1' -a 'user"2' 'user"3'
createuser -v "2023-1-1'; DROP TABLE public.x; select '" hoge
That would be mostly using spaces here, to make sure that quoting is
correctly applied.
BUT, thses should be prevented by the functions enumerated above. So,
I don't think we need them.
Mostly. For example, the test for --valid-until can use a timestamp
with spaces to validate the use of appendStringLiteralConn(). A
second thing is that --member was checked, but not --admin, so I have
renamed regress_user2 to "regress user2" for that to apply a maximum
of coverage, and applied the patch.
One thing that I found annoying is that this made the list of options
of createuser much harder to follow. That's not something caused by
this patch as many options have accumulated across the years and there
is a kind pattern where the connection options were listed first, but
I have cleaned up that while on it. A second area where this could be
done is createdb, as it could be easily expanded if the backend query
gains support for more stuff, but that can happen when it makes more
sense.
--
Michael
Hi,
Thanks to the developers and reviewers.
The attached small patch fixes the message in "createuser --help" command. The patch has changed to specify a time stamp for the --valid-for option. I don't think the SGML description needs to be modified.
Regards,
Noriyoshi Shinoda
-----Original Message-----
From: Michael Paquier <michael@paquier.xyz>
Sent: Wednesday, July 13, 2022 12:25 PM
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Shinya11.Kato@oss.nttdata.com; nathandbossart@gmail.com; przemyslaw@sztoch.pl; david.g.johnston@gmail.com; robertmhaas@gmail.com; daniel@yesql.se; pgsql-hackers@postgresql.org
Subject: Re: Add --{no-,}bypassrls flags to createuser
On Thu, May 26, 2022 at 04:47:46PM +0900, Kyotaro Horiguchi wrote:
FWIW, the "fancy" here causes me to think about something likely to
cause syntax breakage of the query to be sent.createuser -a 'user"1' -a 'user"2' 'user"3'
createuser -v "2023-1-1'; DROP TABLE public.x; select '" hoge
That would be mostly using spaces here, to make sure that quoting is correctly applied.
BUT, thses should be prevented by the functions enumerated above. So,
I don't think we need them.
Mostly. For example, the test for --valid-until can use a timestamp with spaces to validate the use of appendStringLiteralConn(). A second thing is that --member was checked, but not --admin, so I have renamed regress_user2 to "regress user2" for that to apply a maximum of coverage, and applied the patch.
One thing that I found annoying is that this made the list of options of createuser much harder to follow. That's not something caused by this patch as many options have accumulated across the years and there is a kind pattern where the connection options were listed first, but I have cleaned up that while on it. A second area where this could be done is createdb, as it could be easily expanded if the backend query gains support for more stuff, but that can happen when it makes more sense.
--
Michael
Attachments:
createuser_help_v1.diffapplication/octet-stream; name=createuser_help_v1.diffDownload
diff --git a/src/bin/scripts/createuser.c b/src/bin/scripts/createuser.c
index f8ff133f53..0a2beeece5 100644
--- a/src/bin/scripts/createuser.c
+++ b/src/bin/scripts/createuser.c
@@ -426,7 +426,8 @@ help(const char *progname)
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, --valid-until=TIMESTAMP\n"
+ " 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"));
On Wed, Jul 13, 2022 at 08:14:28AM +0000, Shinoda, Noriyoshi (PN Japan FSIP) wrote:
The attached small patch fixes the message in "createuser --help" command. The patch has changed to specify a time stamp for the --valid-for option. I don't think the SGML description needs to be modified.
Good catch. Apart from a nitpick about the indentation, your patch looks
reasonable to me.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
On Wed, Jul 13, 2022 at 11:38:03AM -0700, Nathan Bossart wrote:
On Wed, Jul 13, 2022 at 08:14:28AM +0000, Shinoda, Noriyoshi (PN Japan FSIP) wrote:
The attached small patch fixes the message in "createuser --help"
command. The patch has changed to specify a time stamp for the
--valid-for option. I don't think the SGML description needs to be
modified.
Thanks, Shinoda-san. Fixed.
Good catch. Apart from a nitpick about the indentation, your patch looks
reasonable to me.
FWIW, one can check that with a simple `git diff --check` or similar
to see what was going wrong here. This simple trick allows me to find
quickly formatting issues in any patch posted.
--
Michael