Category: Postgresql

Create Postgresql database and allow remote access on Ubuntu

  1. Log in to the postgres server
    ssh me@pgserver
  2. Become postgres user.
    sudo su - postgres
  3. Create database
    createdb mynewdatabase
  4. Create new user
    createuser -P newuser
  5. Give the new user access over network
    Edit /etc/postgresql/11/main/pg_hba.conf file. (well, 11 depends on the version of postgres you are using.) The CIDR depends on your network.
#
host    all             newuser        192.168.1.0/24         md5
  1. Make sure the server is listening to remote
    Edit /etc/postgresql/11/main/postgresql.conf.
listen_address = '*'
  1. Restart postgresql
    sudo systemctl restart postgresql

So what just happened here. When you create a new user, Postgres doesn’t know the access right of the user. It kind of sucks that you need to edit pg_hba.conf file in order to just add a new remote user.

Now, try
psql -h pgserver -U newuser newdatabase