blob: 9f9dd9cf81208637806460f9957f107b1cf73f7c (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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
|
