CREATE USER | ROLES | PERMISSION GRANT| PRIVILEGES ON DATABASE

Connect postgres admin

[root@dev ~]# su - postgres
Last login: Sat Feb 13 10:37:11 IST 2021 on pts/1
-bash-4.2$ psql
psql (9.2.24)
Type "help" for help.

postgres=#

How to create database in postgres

sudo -u postgres psql
postgres=# create database mydb;

Check USER list 

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 john      | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 tarun     |    

Create User and Grant at database

postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

Set Super user privileges 

[root@swims-testdb62 ~]# psql -U postgres -h 192.168.105.62
Password for user postgres:
psql (9.2.24)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 db_user   |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# alter user db_user with Superuser ;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 db_user   | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=#

Remove Super user privileges 

[root@swims-testdb62 ~]# psql -U postgres -h 192.168.105.62
Password for user postgres:
psql (9.2.24)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 db_user   | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# alter USER db_user with NOSUPERUSER;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 db_user   |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=#

 Connect with Remote machine

[root@dev ~]# psql -h 192.168.105.62 -d mydb -U tarun
Password for user tarun:
psql (9.2.24)
Type "help" for help.

mydb=> 

 

Connect Remotely Server and Show tables

saurabh@server:~$ psql -h 192.168.105.62 -d test_db -U db_user
Password for user db_user:
psql (9.5.24, server 9.2.24)
Type "help" for help.

test_db=> \d
         List of relations
 Schema |  Name   | Type  |  Owner  
--------+---------+-------+---------
 public | station | table | db_user
(1 row)

test_db=> select * from station;
 id | state | district
----+-------+----------
(0 rows)