100-Days-Of-DevOps-Challenge-KodeKloud

Install and Configure PostgreSQL

The Nautilus application development team has shared that they are planning to deploy one newly developed application on Nautilus infra in Stratos DC. The application uses PostgreSQL database, so as a pre-requisite we need to set up PostgreSQL database server as per requirements shared below:

PostgreSQL database server is already installed on the Nautilus database server.

• Create a database user kodekloud_aim and set its password to your-password.
• Create a database kodekloud_db6 and grant full permissions to user kodekloud_aim on this database.

Please do not try to restart PostgreSQL server service.

Steps

1. Login into Database server

    ssh user@db_host
   

2. Switch to postgres user and run following commands:

    sudo -i -u postgres
    psql -c "CREATE DATABASE kodekloud_db6;"
    psql -c "CREATE ROLE kodekloud_aim LOGIN PASSWORD 'your-password';"
    psql -c "GRANT ALL PRIVILEGES ON DATABASE kodekloud_db6 TO kodekloud_aim;"
    psql -c "ALTER DATABASE kodekloud_db6 OWNER TO kodekloud_aim;"
   

   This is based on terminal, you don’t have to login inside pgsql

   • It will create database
   • Create user and grant all privileges for the database

3. Alternatively, login inside the postgres query and run the following commands:

    sudo psql -U postgres
   

    CREATE DATABASE kodekloud_db6;
    CREATE ROLE kodekloud_aim LOGIN PASSWORD 'your-password';
    GRANT ALL PRIVILEGES ON DATABASE kodekloud_db6 TO kodekloud_aim;
    ALTER DATABASE kodekloud_db6 OWNER TO kodekloud_aim;
   

Good to Know?

PostgreSQL Fundamentals

• ACID Compliance: Atomicity, Consistency, Isolation, Durability
• Multi-Version Concurrency Control (MVCC): Handle concurrent transactions
• Extensibility: Custom data types, functions, operators
• Standards Compliance: SQL standard compliant

User and Role Management

• Roles: PostgreSQL uses roles instead of separate users/groups
• LOGIN: Role attribute allowing database connections
• Superuser: Full administrative privileges
• Inheritance: Roles can inherit privileges from other roles

Database Permissions

• GRANT ALL: Full privileges on database
• Specific Grants: SELECT, INSERT, UPDATE, DELETE, CREATE
• Schema Permissions: Control access to schemas within database
• Table-level: Fine-grained permissions on individual tables

Best Practices

• Principle of Least Privilege: Grant minimum required permissions
• Role-based Access: Use roles for permission management
• Connection Limits: Set connection limits for users
• Password Policies: Enforce strong password requirements

This site is open source. Improve this page.