Ubuntu 24.04
Sponsored Link

PostgreSQL 16 : Install2024/05/30

 

Install PostgreSQL to configure database server.

[1] Install and start PostgreSQL.
root@www:~#
apt -y install postgresql-16
[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@www:~#
grep listen_addresses /etc/postgresql/16/main/postgresql.conf

#listen_addresses = 'localhost' # what IP address(es) to listen on;
# authentication methods by default

root@www:~#
grep -v -E "^#|^$" /etc/postgresql/16/main/pg_hba.conf

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
[3] On [peer] authentication, it needs OS user and PostgreSQL user whose name are the same to connect to PostgreSQL Server.
# add an OS user

root@www:~#
adduser ubuntu
# add an PostgreSQL user and his Database with PostgreSQL admin user

root@www:~#
su - postgres

postgres@www:~$
createuser ubuntu

postgres@www:~$
createdb testdb -O ubuntu
# show users and databases

postgres@www:~$
psql -c "select usename from pg_user;"
 usename
----------
 postgres
 ubuntu
(2 rows)

postgres@www:~$
psql -l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 testdb    | ubuntu   | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
(4 rows)
[4] Try to connect to PostgreSQL Database with a user added above.
# connect to testdb

ubuntu@www:~$
psql testdb

psql (16.2 (Ubuntu 16.2-1ubuntu4))
Type "help" for help.

# show user roles
testdb=> \du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 ubuntu    |

# show databases
testdb=> \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 testdb    | ubuntu   | UTF8     | libc            | C.UTF-8 | C.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@www:~$
dropdb testdb

ubuntu@www:~$
psql -l

                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
(3 rows)
Matched Content