1 files changed, 85 insertions, 0 deletions
diff --git a/src/test/regress/sql/roleattributes.sql b/src/test/regress/sql/roleattributes.sql
new file mode 100644
index 0000000000..9f9dd9cf81
--- /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