Revoke privileges and drop role.
--- 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$$;
+
+