Create Database and User¶
Let us create database and user using psql CLI.
Postgres is multi tenant database.
We typically follow these steps to create a database which can be used by connecting as specific user.
Connect to postgres server as user postgres (super user)
Create database -
sms_dbCreate user with password -
sms_userGrant permissions on database to user
docker exec -it itv_pg psql -U postgres
CREATE DATABASE sms_db;
CREATE USER sms_user WITH ENCRYPTED PASSWORD 'sms_password';
GRANT ALL ON DATABASE sms_db TO sms_user;
\l --to list databases
\q --to quit from postgres CLI
Make sure to validate by connecting using sms_user.
When we use psql directly with in the container, you might be able to connect to database even with out password. Don’t worry about it for now.
Connect to postgres using newly created user
docker exec -it itv_pg psql -U sms_user -d sms_db -W
SELECT current_database();
CREATE TABLE t (i INT);
INSERT INTO t VALUES (1);
SELECT * FROM t;
\d
\d t
DROP TABLE t;