PostgreSQL 12 : Install2020/09/01 |
|
Install PostgreSQL to configure database server.
|
|
| [1] | Install and start PostgreSQL. |
|
root@dlp:~# apt -y install postgresql-12
|
| [2] | By default setting, it's possible to connect to PostgreSQL Server only from Localhost with [peer] authentication. Refer to the official site below about details of authentication methods. ⇒ https://www.postgresql.jp/document/10/html/auth-pg-hba-conf.html |
|
# listen only localhost by default root@dlp:~# grep listen_addresses /etc/postgresql/12/main/postgresql.conf #listen_addresses = 'localhost' # what IP address(es) to listen on; # authentication methods by default root@dlp:~# grep -v -E "^#|^$" /etc/postgresql/12/main/pg_hba.conf local all postgres peer local all all peer host all all 127.0.0.1/32 md5 host all all ::1/128 md5 local replication all peer host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5 |
| [3] | On [peer] authentication, it needs OS user and PostgreSQL user whose name are the same to connect to PostgreSQL Server. |
|
# add an PostgreSQL user and his Database with PostgreSQL admin user root@dlp:~# su - postgres postgres@dlp:~$ createuser ubuntu postgres@dlp:~$ createdb testdb -O ubuntu
# show users and databases postgres@dlp:~$ psql -c "select usename from pg_user;"
usename ---------- postgres ubuntu (2 rows)[postgres@www ~]$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | ubuntu | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
|
| [4] | Try to connect to PostgreSQL Database with a user added above. |
|
# connect to testdb ubuntu@dlp:~$ psql testdb psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1)) Type "help" for help. # show user roles testdb=> \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} ubuntu | | {} # show databases testdb=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb | ubuntu | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows) # create a test table testdb=> create table test_table (no int, name text); CREATE TABLE # show tables testdb=> \dt List of relations Schema | Name | Type | Owner --------+------------+-------+-------- public | test_table | table | ubuntu (1 row) # insert data to test table testdb=> insert into test_table (no,name) values (01,'Ubuntu'); INSERT 0 1 # confirm testdb=> select * from test_table; no | name ----+-------- 1 | Ubuntu (1 row) # remove test table testdb=> drop table test_table; DROP TABLE testdb=> \dt Did not find any relations. # exit testdb=> \q # remove testdb ubuntu@dlp:~$ dropdb testdb ubuntu@dlp:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
|
| Sponsored Link |