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
|
RECOMMENDED PRODUCT Web hosting with cPanel Domain and SSL included, daily backups and the panel you already know. from 321,75 MT/mo (3-year plan, with coupon) See plans |
Português
English
