How to install Postgresql on Ubuntu
PostgreSQL is a high-performance, SQL- compatible database management system. PostgreSQL is used by many applications. PostgreSQL is installed on Ubuntu with one command:
sudo apt install postgresql
During installation, a postgres user is created with local authentication.
We enter the database as the postgres user:
sudo –u postgres psql
Creating a superuser with password authentication
Create a new user:
ddos – username;
Dvs@$)$ – password.
CREATE USER ddos WITH password ‘Dvs@$)$’;
Grant superuser rights:
ALTER USER ddos WITH SUPERUSER;
\q
psql –h localhost –U ddos –d postgres –W
-h – server;
-U – username;
-d – database;
-W – use password authentication.
Create user and database
Go to the psql management interface and create another user:
username – username
MyPassword – password
CREATE USER username WITH password ‘MyPassword’;
Create a database:
user_db – database name;
MyPassword1 – password.
CREATE DATABASE user_db;
We give the username user rights to the user_db database:
GRANT ALL ON DATABASE user_db TO user;
Exiting postgres:
\q
Table operations
Create a users table with login and password fields :
CREATE TABLE users (login CHAR(64), password CHAR(64));
Delete table:
DROP TABLE users;
Useful psql commands :
- Show users: \du
- Show Databases: \l
- Show tables in the current database: \dt
- Show columns in a table: \d table_name
- Show the PostgreSQL version: SELECT version();
- help for SQL statements: \h
- help for psql commands: \?
