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
\\ list databases \l
\\ list schemas \dn
\\ list default privs \ddp
\\ list roles \du

\\ try 1 -- I didn't get an error, but I wasn't able to determine if this worked
\\           using the \ddp command -- ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES FOR ROLE ops GRANT SELECT ON TABLES TO ops;

\\ list the schemas avail in the sales_tools_data database

\\ try 2 -- This worked, got the same output (ALTER DEFAULT PRIVILEGES) and
\\          was able to see a change in \ddp 
\\ this is wrong, FOR ROLE ops means when the ops user creates a table, but this 
\\ cannot ever happen ... drop 'FOR ROLE ops' from statement.
ALTER DEFAULT PRIVILEGES IN SCHEMA public FOR ROLE ops GRANT SELECT ON TABLES TO ops;

\\ try 3 -- confirmed from #postgresql chan
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO ops;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ro;

\\ test 1 -- CREATE a table and try to INSERT with a user that is part of ops
\\           expect success
CREATE TABLE food (
  name varchar(40) 
);
\\ list tables \d; exit and become an ops user ...
sales_tools_data=> INSERT INTO food ( name ) VALUES ('taco');
\\ INSERT 0 1
sales_tools_data=> SELECT * FROM food;
\\ name 
\\------
\\ taco
\\(1 row)

\\ test 2 -- try to SELECT with a user that is part of ro
\\           expect expect success
SELECT * FROM food;
\\ name 
\\------
\\ taco
\\(1 row)

\\ test 3 -- try to INSERT with a user that is part of ro (cronpostgres user/role)
\\           expect failure
INSERT INTO food ( name ) VALUES ('pizza');
\\ ERROR:  permission denied for relation food