Postgresql 10: Create database and user

Postgresql provides creation of database schemas with a specific role or user assigned to it. This is great where there will be a few users who need to access one or another database on the same server.

Default user created by Postgresql is named postgres, that can be used to create any database schema and user roles. For this tutorial, Postgresql is installed on Centos Linux 7. To start postgresql client and access the default user, at the terminal type;

sudo -u postgres psql

Steps to create the database are as follows;

1. Create the database. In this case we call it student_management.
2. Create user and assign password for login. We call out user student and password is 123456
3. Assign the access to the database. We give full privileges to read, write.
4. Enable the user to login.
5. Exit from client (optional)

CREATE DATABASE student_management;
CREATE ROLE student WITH PASSWORD '123456';
GRANT ALL PRIVILEGES ON DATABASE student_management TO student;
ALTER ROLE student WITH LOGIN;

Next, test by login with that user account and password.

There is an alternative approach for a dedicated database server that require users to have their own databases. This approach doesn't require you to use the psql client. It is useful when you plan to have scripts that auto generate the process.

sudo -u postgres createdb developer
sudo -u postgres createuser --interactive
sudo adduser developer
sudo passwd developer

The user "developer" can login and get connected to the default database with the same name as the user. In this case the database name is "developer".

Login as the user.

sudo -i -u developer psql