How To Create User Role And Change Password User On Postgresql

Ok in here i want to tell you and to teach you, how can do create new user and give a role until create and alter user password. ok i will teach you step by step.

Now you must be login your pc have postgres, and if you still in root, you must move to user postgres with command "su - postgres", and in here you can follow key "psql" but if show notification "bash script not found" you can do add path folder pgsql in bash_profile, ok follow command:

dba@dba-rizky:~$ ssh root@x.x.x.x

root@x.x.x.x's password:

Last login: Thu Apr 19 16:54:32 2018 from dba

[root@prod-odgpostgres ~]#su - postgres

[postgres@prod-odgpostgres ~]#vi /home/postgres/.bash_profile

Copy this script in your file .bash_profile:

PATH=$PATH:$HOME/.local/bin:$HOME/bin

PATH=$PATH:/usr/local/pgsql/bin

export PATH

After finished copying file I gave in the previous command, you must be save with command ctrl+c → shift+: → wq! , and enter. So after create file,you can do source bash profile.

[postgres@prod-odgpostgres ~]#source /home/postgres/.bash_profile

And so, you can command psql again

[postgres@prod-odgpostgres ~]$ psql

psql (9.6.3)

Type "help" for help.

postgres=# \l

List of databases

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

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

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

(4 rows)

postgres=#\du

in here you can do create new user and choose role but if you want all role you can follow my query but if you not want choose role its no problem.

Example: postgres=#create role [newuser] with {superuser,createrole,createdb,replication,bypassrls} login encrypted password '[newpassword]';

postgres=#create role agung with superuser,createrole,createdb,replication,bypassrls login encrypted password '12345';

And after finished in here you can do show information user created finished maybe. and then if you can change password you can do with query "alter".

Example: postgres=#alter user [newuser] with encrypted password '[newpassword]';

postgres=#alter user agung with encrypted password '54321';

ok thank you for reading and see my tutorial.