Skip to content

Instantly share code, notes, and snippets.

@abelsouzacosta

abelsouzacosta/Postgres Database Configuration.md

Created May 7, 2024 00:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save abelsouzacosta/ac721aee2176e406314e9d5e0197196d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save abelsouzacosta/ac721aee2176e406314e9d5e0197196d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. abelsouzacosta created this gist May 7, 2024.
    49 changes: 49 additions & 0 deletions Postgres Database Configuration.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,49 @@
    ## Steps necessary to configure a Postgres Database

    ### 1. Download image from registry

    We can download the image from docker registry with the command:
    `docker pull postgres:latest`

    ### 2. Create the container with the image

    We must create a container with the image downloaded from the registry, to perform this operation we must execute
    `docker container run` command providing the flags to configure the container properly, as we can see below:

    `docker container run -d --volume /opt/volume/postgressql:/var/lib/postgressql/data -p 5432:5432 -e POSTGRES_PASSWORD=<password> -e POSTGRES_USER=<user> --name postgres postgres:latest`

    The flags used here were:

    - `-d`: to detatch the container from the current shell session
    - `--volume`: to provide a volume to the container, in this case will be the directory `/opt/volume/postgressql`
    - `-p`: to map the ports to the conainer
    - `-e`: to stablish environment variables, in this case we are estabilishing an **user** and also a **passsword**
    - `--name`: to provide a name for the container

    ### 3. Configure security policies

    To configure the security policies we must enter in the container create a new database to our application and configure the
    ecurity policies to this application.

    1. Enter the container with the default credentials: `docker exec -ti <container_name> psql -U <user>`
    2. Create a database specially for our application and then create a user also for the application:
    ```sql
    CREATE DATABASE <appname>;
    CREATE USER <appuser> WITH ENCRYPTED PASSWORD '<password>;'
    GRANT ALL PRIVILEGES ON DATABASE <appname> TO <appuser>;
    ```
    3. Log out from the current session on container and re-enter as the new user created in the step above:
    `docker container exec -ti <container_name> psql -U <appuser> -d <appname>`
    4. Grant CRUD and table management privileges to your user:
    ```sql
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <appuser>;
    GRANT CREATE, USAGE ON SCHEMA public TO <appuser>;
    GRANT TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA public TO <appuser>;
    ```
    5. Log out again an re-enter in the container with the default creadentials: `docker container exec -ti <container_name> psql -U <user>`
    6. Change the database owner to the application:
    ```sql
    ALTER DATABASE <appname> OWNER TO <appuser>;
    ```

    Now we have an valid database for any application that we want to run.