changeset 2329:738316fb865f

Grant role options.
author Oleksandr Gavenko <gavenkoa@gmail.com>
date Thu, 03 Jan 2019 09:39:56 +0200
parents 71c08bde5a3c
children 03d20db65d79
files postgres.rst
diffstat 1 files changed, 59 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/postgres.rst	Thu Jan 03 00:32:48 2019 +0200
+++ b/postgres.rst	Thu Jan 03 09:39:56 2019 +0200
@@ -67,6 +67,22 @@
 
    SELECT * FROM pg_available_extensions;
 
+Grant role options
+==================
+::
+
+   ALTER USER me WITH SUPERUSER;
+   ALTER USER me WITH CREATEDB CREATEROLE LOGIN;
+
+https://www.postgresql.org/docs/current/sql-createrole.html
+  ``CREATE ROLE``.
+
+Change password
+===============
+::
+
+   ALTER USER me WITH PASSWORD 'PassWord';
+
 List databases, schemas and tables
 ==================================
 
@@ -121,6 +137,49 @@
   select * from pg_indexes where schemaname = '...' and tablename = '...';
   select * from pg_indexes where schemaname = '...' and indexname = '...';
 
+  select
+    t.relname as table_name,
+    i.relname as index_name,
+    a.attname as column_name
+  from
+    pg_class t,
+    pg_class i,
+    pg_index ix,
+    pg_attribute a
+  where
+    t.oid = ix.indrelid
+    and i.oid = ix.indexrelid
+    and a.attrelid = t.oid
+    and a.attnum = ANY(ix.indkey)
+    and t.relkind = 'r'
+    and t.relname like 'test%'
+  order by
+    t.relname,
+    i.relname;
+
+  select
+    t.relname as table_name,
+    i.relname as index_name,
+    array_to_string(array_agg(a.attname), ', ') as column_names
+  from
+    pg_class t,
+    pg_class i,
+    pg_index ix,
+    pg_attribute a
+  where
+    t.oid = ix.indrelid
+    and i.oid = ix.indexrelid
+    and a.attrelid = t.oid
+    and a.attnum = ANY(ix.indkey)
+    and t.relkind = 'r'
+    and t.relname like 'test%'
+  group by
+    t.relname,
+    i.relname
+  order by
+    t.relname,
+    i.relname;
+
 List functions::
 
   \df