PostgreSQL: setup User and Role in Linux (Mint)

After installing PostgreSQL, type the following command:

$ psql -U postgres

postgres is the default super user. We login as Postgresql database system by using this super user.

Display existing user's roles:

postgres-# \du+

Create ps_admin role:

postgres=# CREATE ROLE ps_admin WITH LOGIN CREATEDB PASSWORD 'admin';

The ps_admin can login with password 'admin' and can create new database.

Note: Here, password is simply 'admin'. In the real life the password should be complex combination of words, symbols and numbers.

Display list of roles:

postgres=# \du+

You will see something like below:

Role Name: ps_admin

List of roles Attributes: Create DB

Create Database:

$createdb testdb -p 5432 -h localhost -U ps_admin;

The createdb is command line command. The database name testdb will be created under ps_admin user. It should be run in the console.

-p = port (default port is 5432)

-h = hostname (default hostname is loclhost)

-U = username (ps_admin)

It will be prompted for password. Here, I put admin as password.

Connection Testing:

$ psql -p 5432 -U ps_admin -d testdb -h localhost;

If the connection is successful, we will see the following output on the console;

testdb=>

Great JOB. we are done.

Now, we want to create schema under current role:

create schema medr AUTHORIZATION CURRENT_USER;

Show schemas:

SELECT schema_name FROM information_schema.schemata;

Display table of schema:

\dt schema_name.*

The Daily Programming