72 |
72 |
73 https://wiki.postgresql.org/wiki/TOAST |
73 https://wiki.postgresql.org/wiki/TOAST |
74 TOAST is a mechanism PostgreSQL uses to keep physical data rows from exceeding the size of a data |
74 TOAST is a mechanism PostgreSQL uses to keep physical data rows from exceeding the size of a data |
75 block (typically 8KB). |
75 block (typically 8KB). |
76 |
76 |
|
77 List privileges |
|
78 =============== |
|
79 |
|
80 List databases with privileges:: |
|
81 |
|
82 \l |
|
83 |
|
84 List users with privileges:: |
|
85 |
|
86 \du |
|
87 |
|
88 List privileges to objects (tables, views, sequences):: |
|
89 |
|
90 \dp |
|
91 |
|
92 SELECT * FROM information_schema.role_table_grants |
|
93 WHERE table_name = 'table_name'; |
|
94 |
77 Grant role options |
95 Grant role options |
78 ================== |
96 ================== |
79 :: |
97 :: |
80 |
98 |
81 ALTER USER me WITH SUPERUSER; |
99 ALTER USER me WITH SUPERUSER; |
86 |
104 |
87 Granting selects:: |
105 Granting selects:: |
88 |
106 |
89 GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user; |
107 GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user; |
90 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rw_user; |
108 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rw_user; |
|
109 GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO rw_user; |
91 |
110 |
92 Making read-only role:: |
111 Making read-only role:: |
93 |
112 |
94 GRANT CONNECT ON DATABASE mydb TO ro; |
113 GRANT CONNECT ON DATABASE mydb TO ro; |
95 GRANT USAGE ON SCHEMA myschema TO ro; |
114 GRANT USAGE ON SCHEMA myschema TO ro; |
96 GRANT SELECT ON TABLE myschema.myt1, myschema.myt2 TO ro; |
115 GRANT SELECT ON TABLE myschema.myt1, myschema.myt2 TO ro; |
|
116 |
|
117 Granting default privileges for new objects:: |
|
118 |
|
119 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user; |
|
120 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO rw_user; |
|
121 |
|
122 Revoke privileges and drop role |
|
123 =============================== |
|
124 |
|
125 List roles:: |
|
126 |
|
127 SELECT * FROM pg_roles; |
|
128 |
|
129 Revoke privileges:: |
|
130 |
|
131 REVOKE ALL PRIVILEGES ON DATABASE mydb FROM xuser; |
|
132 REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM xuser; |
|
133 REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM xuser; |
|
134 REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM xuser; |
|
135 |
|
136 do $$ |
|
137 DECLARE r record; |
|
138 begin |
|
139 for r in select * from pg_views where schemaname = 'myschem' |
|
140 loop |
|
141 execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| quote_ident(r.viewname) || ' from "xuser"'; |
|
142 end loop; |
|
143 end $$; |
|
144 |
|
145 To forbid any operations (read/write) without dealing with individual privileges (any pending user |
|
146 transactions will fail!):: |
|
147 |
|
148 REVOKE USAGE ON SCHEMA myschem FROM xuser; |
|
149 |
|
150 To forbid connection remove privilege from ``public`` role as it inherited by all users and then from user:: |
|
151 |
|
152 REVOKE CONNECT ON DATABASE mydb FROM public; |
|
153 REVOKE CONNECT ON DATABASE mydb FROM xuser; |
|
154 |
|
155 Role can't be dropped until it owns objects, need to reassign owner:: |
|
156 |
|
157 REASSIGN OWNED BY xuser TO myuser; |
|
158 |
|
159 Remove role:: |
|
160 |
|
161 DROP ROLE xuser; |
97 |
162 |
98 Change password |
163 Change password |
99 =============== |
164 =============== |
100 :: |
165 :: |
101 |
166 |
437 ``CREATE FUNCTION``. |
502 ``CREATE FUNCTION``. |
438 https://www.postgresql.org/docs/current/plpgsql-control-structures.html |
503 https://www.postgresql.org/docs/current/plpgsql-control-structures.html |
439 plpgsql control structures. |
504 plpgsql control structures. |
440 https://www.postgresql.org/docs/current/errcodes-appendix.html |
505 https://www.postgresql.org/docs/current/errcodes-appendix.html |
441 PostgreSQL Error Codes. |
506 PostgreSQL Error Codes. |
|
507 |
|
508 Anonymous block:: |
|
509 |
|
510 do $$ |
|
511 begin |
|
512 null; |
|
513 end $$; |
|
514 |
|
515 You cannot perform ``select`` without assigning resultset, special keyword ``perform`` is used |
|
516 insted of ``select`` in PLPQGSL:: |
|
517 |
|
518 do $$ |
|
519 begin |
|
520 perform 1 union all select 2; |
|
521 end$$; |
|
522 |
|
523 |