PostgreSQL, often simply Postgres, is an object-relational database management system with an emphasis on extensibility and standards compliance.PostgreSQL is used by many applications. PostgreSQL is installed on Ubuntu/Debian with one command:
apt–get install postgresql
When installed, a postgres user with a local authentication of the Ident Authentication type is created.
We enter the database under the user postgres:
sudo –u postgres psql
Creating a Superuser with Password Authentication
Create a new user:
root – username;
MyPassword is the password.
CREATE USER root WITH password ‘MyPassword’;
We give the rights of the superuser:
ALTER USER root WITH SUPERUSER;
Exit from psql :
\q
Now you can connect to a user root with password authentication:
Since user root does not have its own database, we will connect to the postgres database :
psql –h localhost –U root –d postgres –W
Parameters:
-h – server;
-U – username;
-d – database;
-W – use password authentication.
Creating a User and Database
Again, go into the management interface psql and create another user:
user – the username;
CREATE USER user WITH password ‘MyPassword1’;
Create a database:
user_db – the name of the database;
MyPassword1 is the password.
CREATE DATABASE user_db;
give the user user rights to the user_db database :
GRANT ALL ON DATABASE user_db TO user;
Exit from psql :
\q
Now you can connect to the created database:
psql -h localhost -U user -d user_db -W
Operations with tables
Create a users table with the login and password fields :
CREATE TABLE users (login CHAR(64), password CHAR(64));
Delete table:
DROP TABLE users;
Useful commands in the psql interface :
Show users:
\du
Show databases:
\l
Show tables in the current database:
\dt
Show columns in the table:
\d table_name
Show PostgreSQL version:
SELECT version();
Show the help of SQL statements:
\h
Show the help of psql commands:
\?
