* Andres Freund (and...@anarazel.de) wrote: > I find that a somewhat ugly coding pattern, but since the rest of the > function is written that way...
Agreed, but not going to change it at this point.
Would love feedback on the attached. I included the variable renames
discussed previously with Noah as they're quite minor changes.
Had no trouble cherry-picking this back to 9.5.
> > I'll do that and add regression tests for it and any others which don't
> > get tested. My thinking on the test is to independently change each
> > value and then poll for all role attributes set and verify that the only
> > change made was the change expected.
>
> Do that if you like, but what I really think we should have is a test
> that tries to bypass rls and fails, then the user gets changes and it
> succeeds, and then it gets disabled and fails again. This really seems
> test-worthy behaviour to me.
I'll look at doing this also in the rowsecurity regression suite, but I
really like having this coverage of CREATE/ALTER ROLE too, plus testing
the role dump/restore paths in pg_dumpall which I don't think were being
covered at all previously...
Thanks!
Stephen
From ab44660e9b8fc5b10f84ce6ba99a8340047ac8a5 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Wed, 12 Aug 2015 15:50:54 -0400
Subject: [PATCH] In AlterRole, make bypassrls an int
When reworking bypassrls in AlterRole to operate the same way the other
attribute handling is done, I missed that the variable was incorrectly a
bool rather than an int. This meant that on platforms with an unsigned
char, we could end up with incorrect behavior during ALTER ROLE.
Pointed out by Andres thanks to tests he did changing our bool to be the
one from stdbool.h which showed this and a number of other issues.
Add regression tests to test CREATE/ALTER role for the various role
attributes. Arrange to leave roles behind for testing pg_dumpall, but
none which have the LOGIN attribute.
In passing, to avoid confusion, rename CreatePolicyStmt's 'cmd' to
'cmd_name', parse_policy_command's 'cmd' to 'polcmd', and
AlterPolicy's 'cmd_datum' to 'polcmd_datum', per discussion with Noah
and as a follow-up to his correction of copynodes/equalnodes handling of
the CreatePolicyStmt 'cmd' field.
Back-patch to 9.5 where the AlterRole bug exists and to avoid the code
diverging due to minor changes while in alpha.
---
src/backend/commands/policy.c | 22 +--
src/backend/commands/user.c | 2 +-
src/backend/nodes/copyfuncs.c | 2 +-
src/backend/nodes/equalfuncs.c | 2 +-
src/backend/parser/gram.y | 2 +-
src/include/nodes/parsenodes.h | 2 +-
src/test/regress/expected/roleattributes.out | 236 +++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/roleattributes.sql | 85 ++++++++++
10 files changed, 339 insertions(+), 17 deletions(-)
create mode 100644 src/test/regress/expected/roleattributes.out
create mode 100644 src/test/regress/sql/roleattributes.sql
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index bcf4a8f..45326a3 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -108,25 +108,25 @@ RangeVarCallbackForPolicy(const RangeVar *rv, Oid relid, Oid oldrelid,
static char
parse_policy_command(const char *cmd_name)
{
- char cmd;
+ char polcmd;
if (!cmd_name)
elog(ERROR, "unrecognized policy command");
if (strcmp(cmd_name, "all") == 0)
- cmd = '*';
+ polcmd = '*';
else if (strcmp(cmd_name, "select") == 0)
- cmd = ACL_SELECT_CHR;
+ polcmd = ACL_SELECT_CHR;
else if (strcmp(cmd_name, "insert") == 0)
- cmd = ACL_INSERT_CHR;
+ polcmd = ACL_INSERT_CHR;
else if (strcmp(cmd_name, "update") == 0)
- cmd = ACL_UPDATE_CHR;
+ polcmd = ACL_UPDATE_CHR;
else if (strcmp(cmd_name, "delete") == 0)
- cmd = ACL_DELETE_CHR;
+ polcmd = ACL_DELETE_CHR;
else
elog(ERROR, "unrecognized policy command");
- return cmd;
+ return polcmd;
}
/*
@@ -480,7 +480,7 @@ CreatePolicy(CreatePolicyStmt *stmt)
int i;
/* Parse command */
- polcmd = parse_policy_command(stmt->cmd);
+ polcmd = parse_policy_command(stmt->cmd_name);
/*
* If the command is SELECT or DELETE then WITH CHECK should be NULL.
@@ -674,7 +674,7 @@ AlterPolicy(AlterPolicyStmt *stmt)
bool replaces[Natts_pg_policy];
ObjectAddress target;
ObjectAddress myself;
- Datum cmd_datum;
+ Datum polcmd_datum;
char polcmd;
bool polcmd_isnull;
int i;
@@ -775,11 +775,11 @@ AlterPolicy(AlterPolicyStmt *stmt)
RelationGetRelationName(target_table))));
/* Get policy command */
- cmd_datum = heap_getattr(policy_tuple, Anum_pg_policy_polcmd,
+ polcmd_datum = heap_getattr(policy_tuple, Anum_pg_policy_polcmd,
RelationGetDescr(pg_policy_rel),
&polcmd_isnull);
Assert(!polcmd_isnull);
- polcmd = DatumGetChar(cmd_datum);
+ polcmd = DatumGetChar(polcmd_datum);
/*
* If the command is SELECT or DELETE then WITH CHECK should be NULL.
diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index afbf276..295e0b0 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -493,7 +493,7 @@ AlterRole(AlterRoleStmt *stmt)
char *validUntil = NULL; /* time the login is valid until */
Datum validUntil_datum; /* same, as timestamptz Datum */
bool validUntil_null;
- bool bypassrls = -1;
+ int bypassrls = -1;
DefElem *dpassword = NULL;
DefElem *dissuper = NULL;
DefElem *dinherit = NULL;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 1c8425d..bd2e80e 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4083,7 +4083,7 @@ _copyCreatePolicyStmt(const CreatePolicyStmt *from)
COPY_STRING_FIELD(policy_name);
COPY_NODE_FIELD(table);
- COPY_STRING_FIELD(cmd);
+ COPY_STRING_FIELD(cmd_name);
COPY_NODE_FIELD(roles);
COPY_NODE_FIELD(qual);
COPY_NODE_FIELD(with_check);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 1d6c43c..19412fe 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2074,7 +2074,7 @@ _equalCreatePolicyStmt(const CreatePolicyStmt *a, const CreatePolicyStmt *b)
{
COMPARE_STRING_FIELD(policy_name);
COMPARE_NODE_FIELD(table);
- COMPARE_STRING_FIELD(cmd);
+ COMPARE_STRING_FIELD(cmd_name);
COMPARE_NODE_FIELD(roles);
COMPARE_NODE_FIELD(qual);
COMPARE_NODE_FIELD(with_check);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 426a09d..1efc6d6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4613,7 +4613,7 @@ CreatePolicyStmt:
CreatePolicyStmt *n = makeNode(CreatePolicyStmt);
n->policy_name = $3;
n->table = $5;
- n->cmd = $6;
+ n->cmd_name = $6;
n->roles = $7;
n->qual = $8;
n->with_check = $9;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 151c93a..f0dcd2f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2039,7 +2039,7 @@ typedef struct CreatePolicyStmt
NodeTag type;
char *policy_name; /* Policy's name */
RangeVar *table; /* the table name the policy applies to */
- char *cmd; /* the command name the policy applies to */
+ char *cmd_name; /* the command name the policy applies to */
List *roles; /* the roles associated with the policy */
Node *qual; /* the policy's condition */
Node *with_check; /* the policy's WITH CHECK condition. */
diff --git a/src/test/regress/expected/roleattributes.out b/src/test/regress/expected/roleattributes.out
new file mode 100644
index 0000000..b7fded9
--- /dev/null
+++ b/src/test/regress/expected/roleattributes.out
@@ -0,0 +1,236 @@
+-- default for superuser is false
+CREATE ROLE test_def_superuser;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_superuser | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+CREATE ROLE test_superuser WITH SUPERUSER;
+SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_superuser | t | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_superuser WITH NOSUPERUSER;
+SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_superuser | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_superuser WITH SUPERUSER;
+SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_superuser | t | t | f | f | f | f | f | -1 | |
+(1 row)
+
+-- default for inherit is true
+CREATE ROLE test_def_inherit;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_inherit | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+CREATE ROLE test_inherit WITH NOINHERIT;
+SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_inherit | f | f | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_inherit WITH INHERIT;
+SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_inherit | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_inherit WITH NOINHERIT;
+SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_inherit | f | f | f | f | f | f | f | -1 | |
+(1 row)
+
+-- default for create role is false
+CREATE ROLE test_def_createrole;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_createrole | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+CREATE ROLE test_createrole WITH CREATEROLE;
+SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_createrole | f | t | t | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_createrole WITH NOCREATEROLE;
+SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_createrole | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_createrole WITH CREATEROLE;
+SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_createrole | f | t | t | f | f | f | f | -1 | |
+(1 row)
+
+-- default for create database is false
+CREATE ROLE test_def_createdb;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_createdb | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+CREATE ROLE test_createdb WITH CREATEDB;
+SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_createdb | f | t | f | t | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_createdb WITH NOCREATEDB;
+SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_createdb | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_createdb WITH CREATEDB;
+SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_createdb | f | t | f | t | f | f | f | -1 | |
+(1 row)
+
+-- default for can login is false for role
+CREATE ROLE test_def_role_canlogin;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_role_canlogin | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+CREATE ROLE test_role_canlogin WITH LOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_role_canlogin | f | t | f | f | t | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_role_canlogin WITH NOLOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_role_canlogin | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_role_canlogin WITH LOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_role_canlogin | f | t | f | f | t | f | f | -1 | |
+(1 row)
+
+-- default for can login is true for user
+CREATE USER test_def_user_canlogin;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_user_canlogin | f | t | f | f | t | f | f | -1 | |
+(1 row)
+
+CREATE USER test_user_canlogin WITH NOLOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_user_canlogin | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER USER test_user_canlogin WITH LOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_user_canlogin | f | t | f | f | t | f | f | -1 | |
+(1 row)
+
+ALTER USER test_user_canlogin WITH NOLOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_user_canlogin | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+-- default for replication is false
+CREATE ROLE test_def_replication;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_replication | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+CREATE ROLE test_replication WITH REPLICATION;
+SELECT * FROM pg_authid WHERE rolname = 'test_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_replication | f | t | f | f | f | t | f | -1 | |
+(1 row)
+
+ALTER ROLE test_replication WITH NOREPLICATION;
+SELECT * FROM pg_authid WHERE rolname = 'test_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_replication | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_replication WITH REPLICATION;
+SELECT * FROM pg_authid WHERE rolname = 'test_replication';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_replication | f | t | f | f | f | t | f | -1 | |
+(1 row)
+
+-- default for bypassrls is false
+CREATE ROLE test_def_bypassrls;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_def_bypassrls | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+CREATE ROLE test_bypassrls WITH BYPASSRLS;
+SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_bypassrls | f | t | f | f | f | f | t | -1 | |
+(1 row)
+
+ALTER ROLE test_bypassrls WITH NOBYPASSRLS;
+SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_bypassrls | f | t | f | f | f | f | f | -1 | |
+(1 row)
+
+ALTER ROLE test_bypassrls WITH BYPASSRLS;
+SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
+----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
+ test_bypassrls | f | t | f | f | f | f | t | -1 | |
+(1 row)
+
+-- remove the one role with LOGIN rights
+DROP ROLE test_role_canlogin;
+-- other roles not removed to test pg_dumpall role dump through
+-- pg_upgrade
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df15de..6fc5d1e 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -60,7 +60,7 @@ test: create_index create_view
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames
+test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 15d74d4..2ae51cf 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -73,6 +73,7 @@ test: vacuum
test: drop_if_exists
test: updatable_views
test: rolenames
+test: roleattributes
test: sanity_check
test: errors
test: select
diff --git a/src/test/regress/sql/roleattributes.sql b/src/test/regress/sql/roleattributes.sql
new file mode 100644
index 0000000..9f9dd9c
--- /dev/null
+++ b/src/test/regress/sql/roleattributes.sql
@@ -0,0 +1,85 @@
+-- default for superuser is false
+CREATE ROLE test_def_superuser;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_superuser';
+CREATE ROLE test_superuser WITH SUPERUSER;
+SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
+ALTER ROLE test_superuser WITH NOSUPERUSER;
+SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
+ALTER ROLE test_superuser WITH SUPERUSER;
+SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
+
+-- default for inherit is true
+CREATE ROLE test_def_inherit;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_inherit';
+CREATE ROLE test_inherit WITH NOINHERIT;
+SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
+ALTER ROLE test_inherit WITH INHERIT;
+SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
+ALTER ROLE test_inherit WITH NOINHERIT;
+SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
+
+-- default for create role is false
+CREATE ROLE test_def_createrole;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_createrole';
+CREATE ROLE test_createrole WITH CREATEROLE;
+SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
+ALTER ROLE test_createrole WITH NOCREATEROLE;
+SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
+ALTER ROLE test_createrole WITH CREATEROLE;
+SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
+
+-- default for create database is false
+CREATE ROLE test_def_createdb;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_createdb';
+CREATE ROLE test_createdb WITH CREATEDB;
+SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
+ALTER ROLE test_createdb WITH NOCREATEDB;
+SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
+ALTER ROLE test_createdb WITH CREATEDB;
+SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
+
+-- default for can login is false for role
+CREATE ROLE test_def_role_canlogin;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_role_canlogin';
+CREATE ROLE test_role_canlogin WITH LOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
+ALTER ROLE test_role_canlogin WITH NOLOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
+ALTER ROLE test_role_canlogin WITH LOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
+
+-- default for can login is true for user
+CREATE USER test_def_user_canlogin;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_user_canlogin';
+CREATE USER test_user_canlogin WITH NOLOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
+ALTER USER test_user_canlogin WITH LOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
+ALTER USER test_user_canlogin WITH NOLOGIN;
+SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
+
+-- default for replication is false
+CREATE ROLE test_def_replication;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_replication';
+CREATE ROLE test_replication WITH REPLICATION;
+SELECT * FROM pg_authid WHERE rolname = 'test_replication';
+ALTER ROLE test_replication WITH NOREPLICATION;
+SELECT * FROM pg_authid WHERE rolname = 'test_replication';
+ALTER ROLE test_replication WITH REPLICATION;
+SELECT * FROM pg_authid WHERE rolname = 'test_replication';
+
+-- default for bypassrls is false
+CREATE ROLE test_def_bypassrls;
+SELECT * FROM pg_authid WHERE rolname = 'test_def_bypassrls';
+CREATE ROLE test_bypassrls WITH BYPASSRLS;
+SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
+ALTER ROLE test_bypassrls WITH NOBYPASSRLS;
+SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
+ALTER ROLE test_bypassrls WITH BYPASSRLS;
+SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
+
+-- remove the one role with LOGIN rights
+DROP ROLE test_role_canlogin;
+
+-- other roles not removed to test pg_dumpall role dump through
+-- pg_upgrade
--
1.9.1
signature.asc
Description: Digital signature
