VPC -- Virtual Private Cloud

 Amazon VPC gives you your own private space in the cloud. When you create a VPC, you have the option of carving out your own data center in the cloud.

 The first step of creating a VPC is deciding the IP range by providing a Classless Inter-Domain Routing (CIDR) block.

 VPC now supports both IPv4 and IPv6, so you can have both IP ranges as part of your VPC

. When you choose an IPv4 CIDR range, you can choose anything between /16, which corresponds to 65,536 IP addresses (for example 10.0.0.0/16), and /28, which corresponds to 16 IP addresses.

 If you choose IPv6, the size of the IPv6 CIDR block is fixed to /56. The range of IPv6 addresses is automatically allocated from Amazon's pool of IPv6 addresses; at this time, you cannot select the range yourself. As of now, having a CIDR block for IPv6 is optional; however, you need an IPv4 CIDR block. It is important to note that once you create a VPC, you can't alter the size of it. If you create a VPC with a small size and later realize that you need more IP addresses, you can create a new VPC with a bigger IP address range and then migrate your applications from the old VPC to the new one.

A VPC is limited to a region, which means you can't have a VPC spanning regions. Within a VPC, you have all the AZs that are part of the region where the VPC belongs.  VPC spanning three AZs within a region with a CIDR block of /16. This figure also shows the main route table of the virtual private cloud

source: google

how to check the privileges assigned to a role in postgresql

 SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges 

WHERE grantee = 'MY_USER'

SELECT grantee,table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges 
WHERE  grantee not in ('pg_monitor','PUBLIC');

select list of roles in postgresql:

SELECT rolname FROM pg_roles;

locate HBA files

+++++++++++++++

SELECT name, setting
FROM pg_settings WHERE name LIKE '%hba%';

*****************************************************

To check user having password:

select usename,passwd from pg_shadow where passwd IS NOT NULL

*********************************************************

create table inside the postgresql db

Mysql4=# show data_directory;

     data_directory

------------------------

 /var/lib/pgsql/12/data

(1 row)

 mysql4=# \dt+ myschema.table20

                        List of relations

  Schema  |  Name   | Type  |  Owner   |    Size    | Description

----------+---------+-------+----------+------------+-------------

 myschema | table20 | table | postgres | 8192 bytes |

(1 row)

mysql4=# create table test.emp(empno int primary key NOT NULL,ename varchar NOT NULL,job varchar,dob DATE,deptno int);

ERROR:  schema "test" does not exist

LINE 1: create table test.emp(empno int primary key NOT NULL,ename v...

                     ^

mysql4=# create table myschema.emp(empno int primary key NOT NULL,ename varchar NOT NULL,job varchar,dob DATE,deptno int);

CREATE TABLE

mysql4=#

mysql4=#

mysql4=# \dt +

ERROR:  invalid regular expression: quantifier operand invalid

mysql4=#

mysql4=# select schemaname,relname from pg_stat_user_tables;

 schemaname | relname

------------+---------

 myschema   | table20

 myschema   | emp

(2 rows)

mysql4=# create materialized view myschema.emp_mv as select * from myschema.emp;

SELECT 0

mysql4=#

mysql4=# select * from myschema.emp_mv;

 empno | ename | job | dob | deptno

-------+-------+-----+-----+--------

(0 rows)

mysql4=# insert into myschema.emp values(100,'Ram','Admin','10-Jan-1973',10);

INSERT 0 1

mysql4=# insert into myschema.emp values(101,'ravan','Admin','10-Jan-1972',11);

INSERT 0 1

mysql4=# select * from myschema.emp_mv;

 empno | ename | job | dob | deptno

-------+-------+-----+-----+--------

(0 rows)

mysql4=# refresh materialized view myschema.emp_mv;

REFRESH MATERIALIZED VIEW

mysql4=# select * from myschema.emp_mv;

 empno |  ename   |  job  |    dob     | deptno

-------+----------+-------+------------+--------

   100 | Ram | Admin | 1973-01-10 |     10

   101 | ravan  | Admin | 1982-01-10 |     11

Database creation ,table creation and connectivity in postgres

 Database connectivity:

-bash-4.2$ psql

psql (9.2.24, server 12.5)

WARNING: psql version 9.2, server version 12.0.

         Some psql features might not work.

Type "help" for help.

postgres=#

check the version

postgres=# show server_version;

 server_version

----------------

 12.5

(1 row)

Database creation:
-----------------

postgres=# create database mysql4;

CREATE DATABASE

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 mysql4    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

To know the connection details:

--------------------------------

postgres-# \c

psql (9.2.24, server 12.5)

WARNING: psql version 9.2, server version 12.0.

         Some psql features might not work.

You are now connected to database "postgres" as user "postgres".

postgres-#

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 mysql4    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

connect to database:

postgres=# \c mysql4

psql (9.2.24, server 12.5)

WARNING: psql version 9.2, server version 12.0.

         Some psql features might not work.

You are now connected to database "mysql4" as user "postgres".

--create a schema

mysql4=# create schema myschema;

CREATE SCHEMA

create a table inside the schema

--

mysql4=# create table myschema.table20( int integer, password char(10));

CREATE TABLE

select the tables

                      ^

mysql4=# select * from pg_catalog.pg_tables;

     schemaname     |        tablename        | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity

--------------------+-------------------------+------------+------------+------------+----------+-------------+-------------

 myschema           | table20                 | postgres   |            | f          | f        | f           | f

 pg_catalog         | pg_statistic            | postgres   |            | t          | f        | f           | f

 pg_catalog         | pg_type                 | postgres   |            | t          | f        | f           | f

 pg_catalog         | pg_foreign_server       | postgres   |            | t          | f        | f           | f

 pg_catalog         | pg_authid               | postgres   | pg_global  | t   

or

mysql4=# select * from pg_catalog.pg_tables where schemaname !='information_schema' and schemaname !='pg_catalog';

 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity

------------+-----------+------------+------------+------------+----------+-------------+-------------

 myschema   | table20   | postgres   |            | f          | f        | f           | f

(1 row)

mysql4=#

mysql4=# insert into myschema.table20 values(1,'hi');

INSERT 0 1

mysql4=# insert into myschema.table20 values(2,'howru');

INSERT 0 1

mysql4=# select * from myschema.table20;

 int |  password

-----+------------

   1 | hi

   2 | howru

(2 rows)

PostgreSQL Commands

 

Command	Description
\l   or \list	List all the databases created
\c  database_name	To connect a database
\dt  schema.*	List all the tables in a schema
\q	Quit
\db	List of tablespaces
\dn	List all schemas in postgres
\d	List of relations / Tables
\d  schema.table_name	Describe structure of the table
\dt *	All Tables
\dv *	All views
\ds *	All sequences
\e	(Edit) Modify the query buffer
\g	(Go) Execute the query buffer
\s	(Search) Last commands used
\r	(Reset) Clears the query buffer
SELECT spcname FROM pg_tablespace;	List all tablespaces
select * from pg_namespace;	List all schemas
\h	Display help on Command
\i	read name into query buffer (input)
\o name	Send output to name
\p	Display the query buffer
\t	Toggle the output headers on/ off
\w name	Write the query buffer to name
\!	Execute the Linux command
\?	Help on meta-commands
\x	display in transpose mode

Most of cases you can use '+' to display more information for same command.
e.f. \dt (display table information) \dt+ ( display table information with addition columns)

Postgres installation in redhat 12

 [root@postgres1 ~]# sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Loaded plugins: langpacks, ulninfo

pgdg-redhat-repo-latest.noarch.rpm                                                                     | 6.8 kB  00:00:00

Examining /var/tmp/yum-root-06PtS6/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-14.noarch

Marking /var/tmp/yum-root-06PtS6/pgdg-redhat-repo-latest.noarch.rpm to be installed

Resolving Dependencies

--> Running transaction check

---> Package pgdg-redhat-repo.noarch 0:42.0-14 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================

 Package                       Arch                Version                 Repository                                    Size

==============================================================================================================================

Installing:

 pgdg-redhat-repo              noarch              42.0-14                 /pgdg-redhat-repo-latest.noarch               11 k

Transaction Summary

==============================================================================================================================

Install  1 Package

Total size: 11 k

Installed size: 11 k

Downloading packages:

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : pgdg-redhat-repo-42.0-14.noarch                                                                            1/1

  Verifying  : pgdg-redhat-repo-42.0-14.noarch                                                                            1/1

Installed:

  pgdg-redhat-repo.noarch 0:42.0-14

Complete!

[root@postgres1 ~]#

[root@postgres1 ~]# sudo yum install -y postgresql12-server

Loaded plugins: langpacks, ulninfo

pgdg-common                                                                                            | 2.9 kB  00:00:00

pgdg10                                                                                                 | 3.6 kB  00:00:00

pgdg11                                                                                                 | 3.6 kB  00:00:00

pgdg12                                                                                                 | 3.6 kB  00:00:00

pgdg13                                                                                                 | 3.6 kB  00:00:00

pgdg95                                                                                                 | 3.6 kB  00:00:00

pgdg96                                                                                                 | 3.6 kB  00:00:00

(1/13): pgdg11/7Server/x86_64/group_gz                                                                 |  245 B  00:00:01

(2/13): pgdg10/7Server/x86_64/group_gz                                                                 |  245 B  00:00:01

(3/13): pgdg12/7Server/x86_64/group_gz                                                                 |  245 B  00:00:00

(4/13): pgdg13/7Server/x86_64/group_gz                                                                 |  246 B  00:00:00

(5/13): pgdg-common/7Server/x86_64/primary_db                                                          | 146 kB  00:00:02

(6/13): pgdg95/7Server/x86_64/group_gz                                                                 |  249 B  00:00:01

(7/13): pgdg13/7Server/x86_64/primary_db                                                               |  74 kB  00:00:03

(8/13): pgdg96/7Server/x86_64/group_gz                                                                 |  249 B  00:00:00

(9/13): pgdg12/7Server/x86_64/primary_db                                                               | 168 kB  00:00:05

(10/13): pgdg11/7Server/x86_64/primary_db                                                              | 305 kB  00:00:08

(11/13): pgdg10/7Server/x86_64/primary_db                                                              | 290 kB  00:00:08

(12/13): pgdg95/7Server/x86_64/primary_db                                                              | 258 kB  00:00:04

(13/13): pgdg96/7Server/x86_64/primary_db                                                              | 280 kB  00:00:02

Resolving Dependencies

--> Running transaction check

---> Package postgresql12-server.x86_64 0:12.5-1PGDG.rhel7 will be installed

--> Processing Dependency: postgresql12-libs(x86-64) = 12.5-1PGDG.rhel7 for package: postgresql12-server-12.5-1PGDG.rhel7.x86_64

--> Processing Dependency: postgresql12(x86-64) = 12.5-1PGDG.rhel7 for package: postgresql12-server-12.5-1PGDG.rhel7.x86_64

--> Running transaction check

---> Package postgresql12.x86_64 0:12.5-1PGDG.rhel7 will be installed

---> Package postgresql12-libs.x86_64 0:12.5-1PGDG.rhel7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================

 Package                              Arch                    Version                           Repository               Size

==============================================================================================================================

Installing:

 postgresql12-server                  x86_64                  12.5-1PGDG.rhel7                  pgdg12                  5.1 M

Installing for dependencies:

 postgresql12                         x86_64                  12.5-1PGDG.rhel7                  pgdg12                  1.6 M

 postgresql12-libs                    x86_64                  12.5-1PGDG.rhel7                  pgdg12                  370 k

Transaction Summary

==============================================================================================================================

Install  1 Package (+2 Dependent packages)

Total download size: 7.0 M

Installed size: 30 M

Downloading packages:

warning: /var/cache/yum/x86_64/7Server/pgdg12/packages/postgresql12-libs-12.5-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

Public key for postgresql12-libs-12.5-1PGDG.rhel7.x86_64.rpm is not installed

(1/3): postgresql12-libs-12.5-1PGDG.rhel7.x86_64.rpm                                                   | 370 kB  00:00:05

(2/3): postgresql12-12.5-1PGDG.rhel7.x86_64.rpm                                                        | 1.6 MB  00:00:19

(3/3): postgresql12-server-12.5-1PGDG.rhel7.x86_64.rpm                                                 | 5.1 MB  00:00:28

------------------------------------------------------------------------------------------------------------------------------

Total                                                                                         212 kB/s | 7.0 MB  00:00:33

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Importing GPG key 0x442DF0F8:

 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"

 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8

 Package    : pgdg-redhat-repo-42.0-14.noarch (@/pgdg-redhat-repo-latest.noarch)

 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : postgresql12-libs-12.5-1PGDG.rhel7.x86_64                                                                  1/3

  Installing : postgresql12-12.5-1PGDG.rhel7.x86_64                                                                       2/3

failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink

failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink

failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb: /usr/bin/createdb exists and it is not a symlink

failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser: /usr/bin/createuser exists and it is not a symlink

failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb: /usr/bin/dropdb exists and it is not a symlink

failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser: /usr/bin/dropuser exists and it is not a symlink

failed to link /usr/bin/pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup: /usr/bin/pg_basebackup exists and it is not a symlink

failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump: /usr/bin/pg_dump exists and it is not a symlink

failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall: /usr/bin/pg_dumpall exists and it is not a symlink

failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore: /usr/bin/pg_restore exists and it is not a symlink

failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb: /usr/bin/reindexdb exists and it is not a symlink

failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb: /usr/bin/vacuumdb exists and it is not a symlink

  Installing : postgresql12-server-12.5-1PGDG.rhel7.x86_64                                                                3/3

  Verifying  : postgresql12-12.5-1PGDG.rhel7.x86_64                                                                       1/3

  Verifying  : postgresql12-server-12.5-1PGDG.rhel7.x86_64                                                                2/3

  Verifying  : postgresql12-libs-12.5-1PGDG.rhel7.x86_64                                                                  3/3

Installed:

  postgresql12-server.x86_64 0:12.5-1PGDG.rhel7

Dependency Installed:

  postgresql12.x86_64 0:12.5-1PGDG.rhel7                      postgresql12-libs.x86_64 0:12.5-1PGDG.rhel7

Complete!

[root@postgres1 ~]# sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

Initializing database ... OK

[root@postgres1 ~]# sudo systemctl enable postgresql-12

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.

[root@postgres1 ~]# sudo systemctl start postgresql-12

[root@postgres1 ~]# systemctl status postgresql-12

● postgresql-12.service - PostgreSQL 12 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)

   Active: active (running) since Sat 2020-12-12 17:10:06 IST; 12s ago

     Docs: https://www.postgresql.org/docs/12/static/

  Process: 6112 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

 Main PID: 6122 (postmaster)

    Tasks: 8

   CGroup: /system.slice/postgresql-12.service

           ├─6122 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/

           ├─6124 postgres: logger

           ├─6126 postgres: checkpointer

           ├─6127 postgres: background writer

           ├─6128 postgres: walwriter

           ├─6129 postgres: autovacuum launcher

           ├─6130 postgres: stats collector

           └─6131 postgres: logical replication launcher

Dec 12 17:10:06 postgres1.localdomain systemd[1]: Starting PostgreSQL 12 database server...

Dec 12 17:10:06 postgres1.localdomain postmaster[6122]: 2020-12-12 17:10:06.625 IST [6122] LOG:  starting PostgreSQL 12...-bit

Dec 12 17:10:06 postgres1.localdomain postmaster[6122]: 2020-12-12 17:10:06.626 IST [6122] LOG:  listening on IPv6 addr...5432

Dec 12 17:10:06 postgres1.localdomain postmaster[6122]: 2020-12-12 17:10:06.626 IST [6122] LOG:  listening on IPv4 addr...5432

Dec 12 17:10:06 postgres1.localdomain postmaster[6122]: 2020-12-12 17:10:06.630 IST [6122] LOG:  listening on Unix sock...432"

Dec 12 17:10:06 postgres1.localdomain postmaster[6122]: 2020-12-12 17:10:06.635 IST [6122] LOG:  listening on Unix sock...432"

Dec 12 17:10:06 postgres1.localdomain postmaster[6122]: 2020-12-12 17:10:06.648 IST [6122] LOG:  redirecting log output...cess

Dec 12 17:10:06 postgres1.localdomain postmaster[6122]: 2020-12-12 17:10:06.648 IST [6122] HINT:  Future log output wil...og".

Dec 12 17:10:06 postgres1.localdomain systemd[1]: Started PostgreSQL 12 database server.

Hint: Some lines were ellipsized, use -l to show in full.

[root@postgres1 ~]#

[root@postgres1 ~]#

[root@postgres1 ~]# cat /etc/passwd |grep -i postgre

postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

postgre:x:1000:1000:postgre:/home/postgre:/bin/bash

[root@postgres1 ~]# cat /etc/passwd |grep -i post

postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

postfix:x:89:89::/var/spool/postfix:/sbin/nologin

postgre:x:1000:1000:postgre:/home/postgre:/bin/bash

[root@postgres1 ~]# id

uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[root@postgres1 ~]# sudo -i -u postgres

-bash-4.2$

-bash-4.2$

-bash-4.2$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

-bash-4.2$ exit

logout

[root@postgres1 ~]# sudo -i -u postgre

[postgre@postgres1 ~]$

[postgre@postgres1 ~]$

[postgre@postgres1 ~]$ id

uid=1000(postgre) gid=1000(postgre) groups=1000(postgre),10(wheel) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[postgre@postgres1 ~]$ psql

psql: FATAL:  role "postgre" does not exist

[postgre@postgres1 ~]$

[postgre@postgres1 ~]$

[postgre@postgres1 ~]$ exit

logout

[root@postgres1 ~]# sudo -i -u postgres

-bash-4.2$ whoami

postgres

-bash-4.2$

-bash-4.2$ psql

psql (9.2.24, server 12.5)

WARNING: psql version 9.2, server version 12.0.

         Some psql features might not work.

Type "help" for help.

postgres=#

postgres=#

postgres=# /du

postgres-# \du

                                                               List of roles

         Role name         |                   Attributes                   |                          Member of

---------------------------+------------------------------------------------+-------------------------------------------------

-------------

 pg_execute_server_program | Cannot login                                   | {}

 pg_monitor                | Cannot login                                   | {pg_read_all_settings,pg_read_all_stats,pg_stat_

scan_tables}

 pg_read_all_settings      | Cannot login                                   | {}

 pg_read_all_stats         | Cannot login                                   | {}

 pg_read_server_files      | Cannot login                                   | {}

 pg_signal_backend         | Cannot login                                   | {}

 pg_stat_scan_tables       | Cannot login                                   | {}

 pg_write_server_files     | Cannot login                                   | {}

 postgres                  | Superuser, Create role, Create DB, Replication | {}

postgres-# help

postgres-# \help

Available help:

  ABORT                            CREATE FUNCTION                  DROP TABLE

  ALTER AGGREGATE                  CREATE GROUP                     DROP TABLESPACE

  ALTER COLLATION                  CREATE INDEX                     DROP TEXT SEARCH CONFIGURATION

  ALTER CONVERSION                 CREATE LANGUAGE                  DROP TEXT SEARCH DICTIONARY

  ALTER DATABASE                   CREATE OPERATOR                  DROP TEXT SEARCH PARSER

  ALTER DEFAULT PRIVILEGES         CREATE OPERATOR CLASS            DROP TEXT SEARCH TEMPLATE

  ALTER DOMAIN                     CREATE OPERATOR FAMILY           DROP TRIGGER

  ALTER EXTENSION                  CREATE ROLE                      DROP TYPE

  ALTER FOREIGN DATA WRAPPER       CREATE RULE                      DROP USER

  ALTER FOREIGN TABLE              CREATE SCHEMA                    DROP USER MAPPING

  ALTER FUNCTION                   CREATE SEQUENCE                  DROP VIEW

  ALTER GROUP                      CREATE SERVER                    END

  ALTER INDEX                      CREATE TABLE                     EXECUTE

  ALTER LANGUAGE                   CREATE TABLE AS                  EXPLAIN

  ALTER LARGE OBJECT               CREATE TABLESPACE                FETCH

  ALTER OPERATOR                   CREATE TEXT SEARCH CONFIGURATION GRANT

  ALTER OPERATOR CLASS             CREATE TEXT SEARCH DICTIONARY    INSERT

  ALTER OPERATOR FAMILY            CREATE TEXT SEARCH PARSER        LISTEN

  ALTER ROLE                       CREATE TEXT SEARCH TEMPLATE      LOAD

  ALTER SCHEMA                     CREATE TRIGGER                   LOCK

  ALTER SEQUENCE                   CREATE TYPE                      MOVE

  ALTER SERVER                     CREATE USER                      NOTIFY

  ALTER TABLE                      CREATE USER MAPPING              PREPARE

  ALTER TABLESPACE                 CREATE VIEW                      PREPARE TRANSACTION

  ALTER TEXT SEARCH CONFIGURATION  DEALLOCATE                       REASSIGN OWNED

  ALTER TEXT SEARCH DICTIONARY     DECLARE                          REINDEX

  ALTER TEXT SEARCH PARSER         DELETE                           RELEASE SAVEPOINT

  ALTER TEXT SEARCH TEMPLATE       DISCARD                          RESET

  ALTER TRIGGER                    DO                               REVOKE

  ALTER TYPE                       DROP AGGREGATE                   ROLLBACK

  ALTER USER                       DROP CAST                        ROLLBACK PREPARED

  ALTER USER MAPPING               DROP COLLATION                   ROLLBACK TO SAVEPOINT

  ALTER VIEW                       DROP CONVERSION                  SAVEPOINT

  ANALYZE                          DROP DATABASE                    SECURITY LABEL

  BEGIN                            DROP DOMAIN                      SELECT

  CHECKPOINT                       DROP EXTENSION                   SELECT INTO

  CLOSE                            DROP FOREIGN DATA WRAPPER        SET

  CLUSTER                          DROP FOREIGN TABLE               SET CONSTRAINTS

  COMMENT                          DROP FUNCTION                    SET ROLE

  COMMIT                           DROP GROUP                       SET SESSION AUTHORIZATION

  COMMIT PREPARED                  DROP INDEX                       SET TRANSACTION

  COPY                             DROP LANGUAGE                    SHOW

  CREATE AGGREGATE                 DROP OPERATOR                    START TRANSACTION

  CREATE CAST                      DROP OPERATOR CLASS              TABLE

  CREATE COLLATION                 DROP OPERATOR FAMILY             TRUNCATE

  CREATE CONVERSION                DROP OWNED                       UNLISTEN

  CREATE DATABASE                  DROP ROLE                        UPDATE

  CREATE DOMAIN                    DROP RULE                        VACUUM

  CREATE EXTENSION                 DROP SCHEMA                      VALUES

  CREATE FOREIGN DATA WRAPPER      DROP SEQUENCE                    WITH

  CREATE FOREIGN TABLE             DROP SERVER

postgres-#

postgres-#

postgres-# /q

postgres-# \q

-bash-4.2$ createuser --interactive

Enter name of role to add: testuser

Shall the new role be a superuser? (y/n) n

Shall the new role be allowed to create databases? (y/n) n

Shall the new role be allowed to create more new roles? (y/n) n

-bash-4.2$

-bash-4.2$ psql

psql (9.2.24, server 12.5)

WARNING: psql version 9.2, server version 12.0.

         Some psql features might not work.

Type "help" for help.

postgres=# \c

psql (9.2.24, server 12.5)

WARNING: psql version 9.2, server version 12.0.

         Some psql features might not work.

You are now connected to database "postgres" as user "postgres".

postgres=# \du

                                                               List of roles

         Role name         |                   Attributes                   |                          Member of

---------------------------+------------------------------------------------+-------------------------------------------------

-------------

 pg_execute_server_program | Cannot login                                   | {}

 pg_monitor                | Cannot login                                   | {pg_read_all_settings,pg_read_all_stats,pg_stat_

scan_tables}

 pg_read_all_settings      | Cannot login                                   | {}

 pg_read_all_stats         | Cannot login                                   | {}

 pg_read_server_files      | Cannot login                                   | {}

 pg_signal_backend         | Cannot login                                   | {}

 pg_stat_scan_tables       | Cannot login                                   | {}

 pg_write_server_files     | Cannot login                                   | {}

 postgres                  | Superuser, Create role, Create DB, Replication | {}

 testuser                  |                                                | {}

Thanks