Creating a PostgreSQL user and database, and connecting with psql

On a VPS with PostgreSQL already installed, enter as the system user postgres with sudo -u postgres psql, then create the user and the database with two commands: CREATE USER ... and CREATE DATABASE ... OWNER .... Then connect with psql -h 127.0.0.1 -U user -d database. This assumes a VPS with root access, preferably running Ubuntu 22.04 LTS, and the installation is yours. If you have not installed it yet, start with installing PostgreSQL.

Step by step

1 Enter PostgreSQL: sudo -u postgres psql. The prompt becomes postgres=#.
2 Create the user with a strong password (the password generator will do):CREATE USER shop WITH PASSWORD 'a-strong-password';
3 Create the database and hand it over to that user:CREATE DATABASE shop OWNER shop;
4 Check with \l (lists databases) and \du (lists users). Leave with \q.
5 Connect as the new user, over the local network:psql -h 127.0.0.1 -U shop -d shopThen psql asks for the password.
6 In the application use the host 127.0.0.1, the port 5432, and the three values: database, user and password.

Commands you will use often

Command What it does
\l Lists the databases.
\c name Switches to another database.
\dt Lists the tables in the current database.
\du Lists users (roles).
ALTER USER shop WITH PASSWORD 'new'; Changes the password.
DROP DATABASE shop; Deletes the database. There is no undo.
“Peer authentication failed” shows up when you try psql -U shop without -h 127.0.0.1. Without -h, psql connects through a local file and expects the system user to have the same name. With -h 127.0.0.1 it asks for the password, which is what you want. Another trap: SQL commands end with a semicolon. Without it psql waits and looks frozen.

Giving another user read-only access

For a second user who only reads (a reporting dashboard, say), create it and, inside the database (\c shop), give it the essentials: GRANT CONNECT ON DATABASE shop TO reader; and GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;. The first lets it in, the second lets it read the tables that already exist. Tables created later are not covered automatically: you have to repeat the command or set default privileges. To take access away there is REVOKE, which does the opposite of GRANT.

Do not give the application the postgres user. That one administers everything. One user per application, owner of its own database only, limits the damage if something goes wrong. To keep the password out of the command, psql reads it from a ~/.pgpass file, which should be yours alone (chmod 600).

PostgreSQL is running on your VPS and the application will not connect? Show us the error message, without the password.

Open a support ticket

SEE ALSO

Allowing remote PostgreSQL connections on a VPS, safely

Backing up with mysqldump and pg_dump, and restoring the copy

How far our support goes: what we handle and what is yours

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from $6.60/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
Allowing remote PostgreSQL connections on a VPS, safely By default PostgreSQL listens only on 127.0.0.1. Accepting connections from outside takes three adjustments: tell it whi...