# HG changeset patch
# User Oleksandr Gavenko <gavenkoa@gmail.com>
# Date 1568661199 -10800
# Node ID 20b65f046002472139cd14cd4198124e45cfcda7
# Parent  c2bfaae890f836663bb912a687637a553269fc3e
Revoke privileges and drop role.

diff -r c2bfaae890f8 -r 20b65f046002 postgres.rst
--- a/postgres.rst	Sun Sep 15 23:40:51 2019 +0300
+++ b/postgres.rst	Mon Sep 16 22:13:19 2019 +0300
@@ -74,6 +74,24 @@
   TOAST is a mechanism PostgreSQL uses to keep physical data rows from exceeding the size of a data
   block (typically 8KB).
 
+List privileges
+===============
+
+List databases with privileges::
+
+  \l
+
+List users with privileges::
+
+  \du
+
+List privileges to objects (tables, views, sequences)::
+
+  \dp
+
+  SELECT * FROM information_schema.role_table_grants
+  WHERE table_name = 'table_name';
+
 Grant role options
 ==================
 ::
@@ -88,6 +106,7 @@
 
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rw_user;
+  GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO rw_user;
 
 Making read-only role::
 
@@ -95,6 +114,52 @@
   GRANT USAGE ON SCHEMA myschema TO ro;
   GRANT SELECT ON TABLE myschema.myt1, myschema.myt2 TO ro;
 
+Granting default privileges for new objects::
+
+  ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user;
+  ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO rw_user;
+
+Revoke privileges and drop role
+===============================
+
+List roles::
+
+  SELECT * FROM pg_roles;
+
+Revoke privileges::
+
+  REVOKE ALL PRIVILEGES ON DATABASE mydb FROM xuser;
+  REVOKE ALL PRIVILEGES ON SCHEMA myschem FROM xuser;
+  REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschem FROM xuser;
+  REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschem FROM xuser;
+
+  do $$
+  DECLARE r record;
+  begin
+      for r in select * from pg_views where schemaname = 'myschem'
+      loop
+        execute 'revoke all on ' || quote_ident(r.schemaname) ||'.'|| quote_ident(r.viewname) || ' from "xuser"';
+      end loop;
+  end $$;
+
+To forbid any operations (read/write) without dealing with individual privileges (any pending user
+transactions will fail!)::
+
+  REVOKE USAGE ON SCHEMA myschem FROM xuser;
+
+To forbid connection remove privilege from ``public`` role as it inherited by all users and then from user::
+
+  REVOKE CONNECT ON DATABASE mydb FROM public;
+  REVOKE CONNECT ON DATABASE mydb FROM xuser;
+
+Role can't be dropped until it owns objects, need to reassign owner::
+
+  REASSIGN OWNED BY xuser TO myuser;
+
+Remove role::
+
+  DROP ROLE xuser;
+
 Change password
 ===============
 ::
@@ -438,4 +503,21 @@
 https://www.postgresql.org/docs/current/plpgsql-control-structures.html
   plpgsql control structures.
 https://www.postgresql.org/docs/current/errcodes-appendix.html
-  PostgreSQL Error Codes.
\ No newline at end of file
+  PostgreSQL Error Codes.
+
+Anonymous block::
+
+  do $$
+  begin
+      null;
+  end $$;
+
+You cannot perform ``select`` without assigning resultset, special keyword ``perform`` is used
+insted of ``select`` in PLPQGSL::
+
+  do $$
+  begin
+      perform 1 union all select 2;
+  end$$;
+
+