Skip to content

Instantly share code, notes, and snippets.

@luizomf

luizomf/postgresql_13_pgadmin_4_ubuntu_20_04.sh

Last active March 24, 2025 15:58
Show Gist options
  • Save luizomf/1a7994cf4263e10dce416a75b9180f01 to your computer and use it in GitHub Desktop.
Save luizomf/1a7994cf4263e10dce416a75b9180f01 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. luizomf revised this gist Nov 13, 2020. No changes.
  2. luizomf revised this gist Nov 2, 2020. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions postgresql_13_pgadmin_4_ubuntu_20_04.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -27,6 +27,8 @@ sudo nano /etc/postgresql/13/main/postgresql.conf
    sudo nano /etc/postgresql/13/main/pg_hba.conf
    # add line at the end (change 192.168.0.0/24 to your network or 0.0.0.0/0 to all)
    host all all 192.168.0.0/24 md5
    # FOR SSL: add line at the end (change 192.168.0.0/24 to your network or 0.0.0.0/0 to all)
    hostssl all all 192.168.0.0/24 md5 clientcert=1

    # Restart postgres
    sudo systemctl restart postgresql
  3. luizomf revised this gist Oct 8, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgresql_13_pgadmin_4_ubuntu_20_04.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -41,7 +41,7 @@ alter user postgres with encrypted password 'the_postgres_user_password';
    create user your_username with encrypted password 'your_user_password';

    # OR a superuser
    CREATE ROLE your_username WITH LOGIN SUPERUSER CREATEDB CREATEROLE ENCRYPTED PASSWORD 'your_user_password';
    CREATE ROLE your_username WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'your_user_password';

    # Create a database
    CREATE DATABASE db_name2 WITH OWNER your_username;
  4. luizomf revised this gist Oct 8, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgresql_13_pgadmin_4_ubuntu_20_04.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -41,7 +41,7 @@ alter user postgres with encrypted password 'the_postgres_user_password';
    create user your_username with encrypted password 'your_user_password';

    # OR a superuser
    CREATE ROLE your_username WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'your_user_password';
    CREATE ROLE your_username WITH LOGIN SUPERUSER CREATEDB CREATEROLE ENCRYPTED PASSWORD 'your_user_password';

    # Create a database
    CREATE DATABASE db_name2 WITH OWNER your_username;
  5. luizomf revised this gist Oct 8, 2020. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgresql_13_pgadmin_4_ubuntu_20_04.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -40,6 +40,9 @@ alter user postgres with encrypted password 'the_postgres_user_password';
    # Create user
    create user your_username with encrypted password 'your_user_password';

    # OR a superuser
    CREATE ROLE your_username WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'your_user_password';

    # Create a database
    CREATE DATABASE db_name2 WITH OWNER your_username;

  6. luizomf revised this gist Sep 26, 2020. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion postgresql_13_pgadmin_4_ubuntu_20_04.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -44,4 +44,7 @@ create user your_username with encrypted password 'your_user_password';
    CREATE DATABASE db_name2 WITH OWNER your_username;

    # Grant permissions to user on database
    GRANT ALL PRIVILEGES ON DATABASE db_name TO your_username;
    GRANT ALL PRIVILEGES ON DATABASE db_name TO your_username;

    # Read security tips here
    # https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-against-automated-attacks
  7. luizomf created this gist Sep 26, 2020.
    47 changes: 47 additions & 0 deletions postgresql_13_pgadmin_4_ubuntu_20_04.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,47 @@
    # Update all your packages
    sudo apt update
    sudo apt upgrade -y

    # Add postgresql repository and key
    sudo sh -c 'echo "deb [arch=$(dpkg --print-architecture)] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

    # Update again
    sudo apt-get update

    # Install pgadmin4
    sudo apt install pgadmin4

    # Install postgresql-13
    sudo apt-get -y install postgresql-13

    # Check version to see if it's correct
    psql --Version

    # Allow remote connections
    # edit line #listen_addresses to listen_addresses = '*'
    sudo nano /etc/postgresql/13/main/postgresql.conf

    # edit file
    sudo nano /etc/postgresql/13/main/pg_hba.conf
    # add line at the end (change 192.168.0.0/24 to your network or 0.0.0.0/0 to all)
    host all all 192.168.0.0/24 md5

    # Restart postgres
    sudo systemctl restart postgresql

    # Access psql to create users, databases and passwords
    sudo -u postgres psql

    # Add a stronger password to default postgres user
    alter user postgres with encrypted password 'the_postgres_user_password';

    # Create user
    create user your_username with encrypted password 'your_user_password';

    # Create a database
    CREATE DATABASE db_name2 WITH OWNER your_username;

    # Grant permissions to user on database
    GRANT ALL PRIVILEGES ON DATABASE db_name TO your_username;