Иди на текст

Uputstvo za instalaciju i konfiguraciju PostgreSQL baze podataka za potrebe eduroam Davaoca Resursa (RP)

PostgreSQL softver će se koristiti za potrebe skladištenja log poruka vezanih za upotrebu eduroam Davaoca Resursa.

  • Verzija PostgreSQL softvera: 13.9
  • Operativni sistem: Debian 11

Nakon što ste ispratili uputstvo za instalaciju i konfiguraciju eduroam Davaoca Resursa, možete na istoj VM nastaviti instalaciju i konfiguraciju PostgreSQL baze

U okviru ovog uputstva podrazumeva se da instalirate PostgreSQL bazu na VM gde ste već instalirali i konfigurisali eduroam Davalac Resursa. Deo Priprema VM vam samo daje mogućnost da instalirate bazu na zasebnu VM. Ukoliko vam to nije potrebno, preskočiti deo Priprema VM i pratiti od dela Instalacija PostgreSQL baze podataka

Priprema VM

Ukoliko je potrebno imate mogućnost da PostgreSQL bazu instalirate na zasebnoj VM

  • U tom slučaju neophodno je da izvršite sva inicijalna podešavanja VM što podrazumeva: podesiti hostname VM, podešavanje IPTABLES pravila tako da se otvori port 5432 putem kog bi radius server pristupao bazi i skladištio log poruke.

Podesiti hosta

U okviru čitavog uputstva smatraće se da je domen VM postgresql.institucija.ac.rs, tako da na svim mestima umesto ovog naziva domena potrebno je da unesete naziv po vašem izboru.

Veoma je važno da FQDN (Fully Qualified Domain Name) i pun hostname (# hostname -f) ove VM, npr. postgresql.institucija.ac.rs, koji se podešava u okviru ove sekcije bude isti kao i domen koji je adekvatno podešen na DNS serveru, a taj domen će se takođe kasnije koristiti za kreiranje sertifikata.

Podešavanje host podataka

Dat je primer postavljanja FQDN podatka postgresql.institucija.ac.rs i postavljanja HOSTNAME podatka postgresql :

vim /etc/hosts

Na kraju fajla se unosi linija u formi: IP ADRESA FQDN HOSTNAME Npr:

xxx.xxx.xxx.xxx postgresql.institucija.ac.rs postgresql
Postaviti hostname

Koristi se komanda sledeće forme: hostnamectl set-hostname HOSTNAME 

hostnamectl set-hostname postgres

Sačuvati izmene:

reboot

Podesiti IPTABLES

Ažurirati instalirane pakete:

apt update && apt-get upgrade -y --no-install-recommends
Najpre je potrebno instalirati iptables softverski paket i izvršiti početna podešavanja ukoliko već niste. 
apt-get remove --auto-remove nftables

apt-get remove --auto-remove nftables

apt-get purge nftables

apt-get install iptables

apt-get install iptables-persistent

Otvoriti port 5432 prema instaliranom RADIUS Davaocu Resursa. Zaštitite na ovaj način pristup bazi tako da bude dostupan samo RADIUS Davaocu Resursa.

Jedna od mogućnosti kako se to može uraditi: 
nano /etc/iptables/rules.v4

Otvoriti navedeni port dodavanjem sledeće linije u fajl:

-A INPUT -s 1xx.xxx.xxx.xxx/255.255.255.255 -p tcp -m tcp --dport 5432 -j ACCEPT

Napomena: Navedene linije je potrebno uneti bavezno iznad linije: -A INPUT -j REJECT --reject-with icmp-host-prohibited

Sačuvati izmene: CTRL+X+Y+ENTER

iptables-restore < /etc/iptables/rules.v4

systemctl restart iptables

iptables -L

Instalacija PostgreSQL baze podataka

Napomena: Možete da instalirate bilo koju verziju PostgreSQL baze veću od 9.5, a u ovom uputstvu je korišćena najnovija verzija PostgreSQL baze 13.9 

apt update && apt upgrade -y

apt install vim postgresql postgresql-contrib -y

Zatim je potrebno pokrenuti postgresql proces:

systemctl enable postgresql

systemctl start postgresql

Podešavanje PostgreSQL baze podataka

Fajl postgresql.conf

Ne treba menjati ovaj fajl ukoliko se PostgreSQL baza instalira na istoj VM gde ste instalirali RADIUS server.

Ovaj fajl je potrebno izmeniti samo ukoliko PostgreSQL bazu instalirate na zasebnoj VM. Putem ovog fajla se omogućava konekcija sa RADIUS RP

Potrebno je izmeniti deo CONNECTIONS AND AUTHENTICATION

nano /etc/postgresql/13/main/postgresql.conf
Ispod linije: #listen_addresses = 'localhost' dodati liniju:

listen_addresses = '*'

Sačuvati izmene fajla.

Restart postgresql procesa: 

systemctl restart postgresql

Kreiranje radius korisnika

Ukoliko se naredne komande izvrše sa putanje /root prikazalo bi se obaveštenje could not change directory to "/root": Permission denied

  • To obaveštenje ne utiče na rezultat komandi tako da nije od presudog značaja

  • Bilo bi ipak preporuka pokrenuti ove komande sa putanje /etc/postgresql/

root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql
psql (13.9 (Debian 13.9-0+deb11u1))
Type "help" for help.

postgres=#

Potrebno je kreirati radius korisnika u instaliranoj PostgreSQL bazi podataka

postgres=# CREATE USER radius WITH NOSUPERUSER NOCREATEDB NOCREATEROLE PASSWORD 'L0z1nk@123';

Pregled kreiranog radius korisnika: 

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 radius    |                                                            | {}

Kreiranje radius baze u okviru PostgreSQL baze podataka

Potrebno je kreirati radius bazu u instaliranoj PostgreSQL bazi podataka

postgres=# CREATE DATABASE radius;
postgres=# GRANT ALL PRIVILEGES ON DATABASE radius TO radius;

Pregled kreirane radius baze: 

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 radius    | radius   | 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
(4 rows)

Za sada je radius baza prazna i potrebno je importovati u nju odgovarajuće tabele u nastavku uputstva 

postgres=# \c radius
You are now connected to database "radius" as user "postgres".
radius=# \dt
Did not find any relations.

Izlaz iz postgres baze podataka:

postgres=# \q
exit

Fajl pg_hba.conf

Izmene fajla pg_hba.conf kada su RADIUS server i PostgreSQL baza podataka na istoj VM

Da bi se importovale tabele u okviru radius baze potrebno je editovati fajl pg_hba.conf 

cd /etc/postgresql/13/main

vim pg_hba.conf
  • Potrebno je zakomentarisati liniju local all postgres peer i ispod nje dodati liniju :
local   all             postgres                                md5
  • Potrebno je zakomentarisati liniju local all all peer i ispod nje dodati liniju :
local   all             all                                     md5
  • Potrebno je zakomentarisati linije: 
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

Sačuvati izmene fajla.

Restart postgresql procesa: 

systemctl restart postgresql

Izmene fajla pg_hba.conf kada su RADIUS server i PostgreSQL baza podataka na različitim VM

Da bi se importovale tabele u okviru radius baze potrebno je editovati fajl pg_hba.conf 

cd /etc/postgresql/13/main

vim pg_hba.conf
  • Potrebno je zakomentarisati liniju local all postgres peer i ispod nje dodati liniju :
local   all             postgres                                md5
  • Potrebno je zakomentarisati liniju local all all peer i ispod nje dodati liniju :
local   all             all                                     md5
  • Potrebno je zakomentarisati liniju host all all 127.0.0.1/32 md5 i ispod nje dodati liniju gde deo 1xx.xxx.xxx.xxx predstavlja IP adresu RADIUS servera:
host    all             all             1xx.xxx.xxx.xxx/32            md5
  • Potrebno je zakomentarisati linije: 
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5

# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             all             1xx.xxx.xxx.xxx/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

Sačuvati izmene fajla.

Restart postgresql procesa: 

systemctl restart postgresql

Import tabela radacct, radcheck, radgroupcheck, radreply, radgroupreply, radusergroup, radpostauth i nas

Import navedenih tabela kada su RADIUS server i PostgreSQL baza podataka na istoj VM

Potrebno je otići na sledeću putanju:

cd /usr/local/etc/raddb/mods-config/sql/main/postgresql

Importovati fajl schema.sql koji sadrži potrebne tabele:

root@postgresql:/usr/local/etc/raddb/mods-config/sql/main/postgresql# su postgres
postgres@postgresql:/usr/local/etc/raddb/mods-config/sql/main/postgresql$ psql -U radius radius < /tmp/schema/schema.sql
Password for user radius:
Napomena: Uneta komanda traži unos lozinke radius korisnika koji je kreiran u prethodnim koracima.

Izveštaj: 
root@postgresql:/usr/local/etc/raddb/mods-config/sql/main/postgresql# su postgres
postgres@postgresql:/usr/local/etc/raddb/mods-config/sql/main/postgresql$ psql -U radius radius < /tmp/schema/schema.sql
Password for user radius:
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX

Izlaz iz PostgreSQL baze podataka:

postgres@postgresql:/usr/local/etc/raddb/mods-config/sql/main/postgresql$ exit

Import navedenih tabela kada su RADIUS server i PostgreSQL baza podataka na različitim VM

Preskočiti ovaj deo ukoliko se PostgreSQL baza instalira na istoj VM gde ste instalirali RADIUS server.

U ovom slučaju potrebno je preneti schema.sql fajl sa RADIUS servera na ovu VM. Možete da kreirate direktorijum /tmp/schema/ u svrhu skladištenja šeme:

mkdir /tmp/schema/

Zatim sa lokacije /usr/local/etc/raddb/mods-config/sql/main/postgresql na RADIUS serveru preneti fajl schema.sql u novokreirani direktorijum na VM gde je PostgreSQL baza.

Vratiti se u direktorijum /etc/postgresql/ :

cd /etc/postgresql/

Importovati fajl schema.sql koji sadrži potrebne tabele u radius bazu korišćenjem radius korinika i njegove lozinke:

root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql -U radius radius < /tmp/schema/schema.sql
Password for user radius:
Napomena: Uneta komanda traži unos lozinke radius korisnika koji je kreiran u prethodnim koracima.

Izveštaj: 
root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql -U radius radius < /tmp/schema/schema.sql
Password for user radius:
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE TABLE
CREATE INDEX

Izlaz iz PostgreSQL baze podataka:

postgres@postgresql:/etc/postgresql$ exit

Import tabele cui

Import tabele cui kada su RADIUS server i PostgreSQL baza podataka na istoj VM

Potrebno je otići na sledeću putanju:

cd /usr/local/etc/raddb/mods-config/sql/cui/postgresql/

Importovati fajl schema.sql koji sadrži potrebne tabele:

root@postgresql:/usr/local/etc/raddb/mods-config/sql/cui/postgresql# su postgres
postgres@postgresql:/usr/local/etc/raddb/mods-config/sql/cui/postgresql$ psql -U radius radius < /tmp/schema/schema.sql
Password for user radius:
Napomena: Uneta komanda traži unos lozinke radius korisnika koji je kreiran u prethodnim koracima.

Izveštaj: 
root@postgresql:/usr/local/etc/raddb/mods-config/sql/cui/postgresql# su postgres
postgres@postgresql:/usr/local/etc/raddb/mods-config/sql/cui/postgresql$ psql -U radius radius < /tmp/schema/cui/schema.sql
Password for user radius:
CREATE TABLE
CREATE RULE

Izlaz iz PostgreSQL baze podataka:

postgres@postgresql:/usr/local/etc/raddb/mods-config/sql/cui/postgresql$ exit

Import tabele cui kada su RADIUS server i PostgreSQL baza podataka na različitim VM

Preskočiti ovaj deo ukoliko se PostgreSQL baza instalira na istoj VM gde ste instalirali RADIUS server.

U ovom slučaju potrebno je preneti schema.sql fajl sa RADIUS servera na ovu VM. Možete da kreirate direktorijum /tmp/schema/cui u svrhu skladištenja šeme:

mkdir /tmp/schema/cui

Zatim sa lokacije /usr/local/etc/raddb/mods-config/sql/cui/postgresql/ na RADIUS serveru preneti fajl schema.sql u novokreirani direktorijum na VM gde je PostgreSQL baza.

Vratiti se u direktorijum /etc/postgresql/ :

cd /etc/postgresql/

Importovati fajl schema.sql koji sadrži potrebne tabele u radius bazu korišćenjem radius korinika i njegove lozinke:

root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql -U radius radius < /tmp/schema/cui/schema.sql
Password for user radius:
Napomena: Uneta komanda traži unos lozinke radius korisnika koji je kreiran u prethodnim koracima.

Izveštaj: 
root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql -U radius radius < /tmp/schema/schema.sql
Password for user radius:
CREATE TABLE
CREATE RULE

Izlaz iz PostgreSQL baze podataka:

postgres@postgresql:/etc/postgresql$ exit

Ponovna izmena fajla pg_hba.conf

cd /etc/postgresql/13/main

vim pg_hba.conf

  • Potrebno je otkomentarisati liniju local all postgres peer

  • Potrebno je zakomentarisati liniju local all postgres md5

  • Potrebno je otkomentarisati liniju local all all peer

  • Potrebno je zakomentarisati liniju local all all md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# Database administrative login by Unix domain socket
local   all             postgres                                peer
#local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
#local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

Sačuvati izmene fajla.

Restart postgresql procesa: 

systemctl restart postgresql

Izmene fajla pg_hba.conf kada su RADIUS server i PostgreSQL baza podataka na istoj VM

cd /etc/postgresql/13/main

vim pg_hba.conf

  • Potrebno je otkomentarisati liniju local all postgres peer

  • Potrebno je zakomentarisati liniju local all postgres md5

  • Potrebno je otkomentarisati liniju local all all peer

  • Potrebno je zakomentarisati liniju local all all md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
# Database administrative login by Unix domain socket
local   all             postgres                                peer
#local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
#local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

Sačuvati izmene fajla.

Restart postgresql procesa: 

systemctl restart postgresql

Izmene fajla pg_hba.conf kada su RADIUS server i PostgreSQL baza podataka na različitim VM

Da bi se importovale tabele u okviru radius baze potrebno je editovati fajl pg_hba.conf 

cd /etc/postgresql/13/main

vim pg_hba.conf

  • Potrebno je otkomentarisati liniju local all postgres peer

  • Potrebno je zakomentarisati liniju local all postgres md5

  • Potrebno je otkomentarisati liniju local all all peer

  • Potrebno je zakomentarisati liniju local all all md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# Database administrative login by Unix domain socket
#local   all             postgres                                peer
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                     peer
local   all             all                                     md5

# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             all             1xx.xxx.xxx.xxx/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# Database administrative login by Unix domain socket
local   all             postgres                                peer
#local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
#local   all             all                                     md5

# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             all             1xx.xxx.xxx.xxx/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5

Sačuvati izmene fajla.

Restart postgresql procesa: 

systemctl restart postgresql

Provera importovanih tabela

root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql
psql (13.9 (Debian 13.9-0+deb11u1))
Type "help" for help.

postgres=# \c radius
You are now connected to database "radius" as user "postgres".
radius=# \dt
            List of relations
 Schema |     Name      | Type  | Owner
--------+---------------+-------+--------
 public | cui           | table | radius
 public | nas           | table | radius
 public | radacct       | table | radius
 public | radcheck      | table | radius
 public | radgroupcheck | table | radius
 public | radgroupreply | table | radius
 public | radpostauth   | table | radius
 public | radreply      | table | radius
 public | radusergroup  | table | radius
(9 rows)

Dodatno podešavanje cui tabele

Otići na lokaciju /etc/postgresql

cd /etc/postgresql

Pristupiti PostgreSQL bazi :

root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql
psql (13.9 (Debian 13.9-0+deb11u1))
Type "help" for help.

Otvoriti radius bazu:

  • radius baza se otvara korišćenjem komande: \c radius
postgres=# \c radius
You are now connected to database "radius" as user "postgres".

Trenutne karakteristike cui tabele:

  • Za ispis karakteristika tabele koristi se komanda: \d cui nakon što se uđe u radius bazu.

  • Tu se mogu videti veličine, tipovi polja, određena pravila koja se vezuju za tu tabelu i sl.

Prikaz karakteristika cui tabele: 

radius=# \d cui
                                                       Table "public.cui"
      Column      |           Type           | Collation | Nullable |                          Default
------------------+--------------------------+-----------+----------+-----------------------------------------------------------
 clientipaddress  | inet                     |           | not null | '0.0.0.0'::inet
 callingstationid | character varying(50)    |           | not null | ''::character varying
 username         | character varying(64)    |           | not null | ''::character varying
 cui              | character varying(32)    |           | not null | ''::character varying
 creationdate     | timestamp with time zone |           | not null | '2023-01-12 12:06:02.704965+01'::timestamp with time zone
 lastaccounting   | timestamp with time zone |           | not null | '-infinity'::timestamp without time zone
Indexes:
    "cui_pkey" PRIMARY KEY, btree (username, clientipaddress, callingstationid)
Rules:
    postauth_query AS
    ON INSERT TO cui
   WHERE (EXISTS ( SELECT 1
           FROM cui cui_1
          WHERE cui_1.username::text = new.username::text AND cui_1.clientipaddress = new.clientipaddress AND cui_1.callingstationid::text = new.callingstationid::text)) DO INSTEAD  UPDATE cui SET lastaccounting = '-infinity'::timestamp with time zone, cui = new.cui
  WHERE cui.username::text = new.username::text AND cui.clientipaddress = new.clientipaddress AND cui.callingstationid::text = new.callingstationid::text

Nedovoljno je da cui polje zauzima dužinu od 32 karaktera i ima tip varchar(32) i to u nastavku treba povećati na 128:

  • Najpre treba odbaciti prisutno pravilo komandom: DROP RULE postauth_query ON cui;

  • Zatim povećati broj karaktera polja komandom: ALTER TABLE cui ALTER COLUMN cui TYPE varchar(128);

  • Za ispis novih karakteristika tabele koristi se komanda: \d cui

Prikaz izvršenih komandi: 

radius=# DROP RULE postauth_query ON cui;
DROP RULE
radius=# ALTER TABLE cui ALTER COLUMN cui TYPE varchar(128);
ALTER TABLE
radius=# \d cui
                                                       Table "public.cui"
      Column      |           Type           | Collation | Nullable |                          Default
------------------+--------------------------+-----------+----------+-----------------------------------------------------------
 clientipaddress  | inet                     |           | not null | '0.0.0.0'::inet
 callingstationid | character varying(50)    |           | not null | ''::character varying
 username         | character varying(64)    |           | not null | ''::character varying
 cui              | character varying(128)   |           | not null | ''::character varying
 creationdate     | timestamp with time zone |           | not null | '2023-01-12 12:06:02.704965+01'::timestamp with time zone
 lastaccounting   | timestamp with time zone |           | not null | '-infinity'::timestamp without time zone
Indexes:
    "cui_pkey" PRIMARY KEY, btree (username, clientipaddress, callingstationid)

Pošto smo u prethodnim koracima odbacili postojeće pravilo da bismo izmenili veličinu polja, potrebno je vratiti to pravilo:

  • Napomena: Ukoliko instalirate neku drugu verziju PostgreSQL baze može ovo pravilo biti za nijansu drugačije, zbog toga iskoristite pravilo koje je Vaša verzija baze imala.

  • Pravilo se vraća komandom:

CREATE RULE postauth_query AS ON INSERT TO cui
   WHERE (EXISTS ( SELECT 1
           FROM cui cui_1
          WHERE cui_1.username::text = new.username::text AND cui_1.clientipaddress = new.clientipaddress AND cui_1.callingstationid::text = new.callingstationid::text)) DO INSTEAD  UPDATE cui SET lastaccounting = '-infinity'::timestamp with time zone, cui = new.cui
  WHERE cui.username::text = new.username::text AND cui.clientipaddress = new.clientipaddress AND cui.callingstationid::text = new.callingstationid::text;

Prikaz ponovnog uvođenja pravila: 

radius=# CREATE RULE postauth_query AS ON INSERT TO cui
   WHERE (EXISTS ( SELECT 1
           FROM cui cui_1
          WHERE cui_1.username::text = new.username::text AND cui_1.clientipaddress = new.clientipaddress AND cui_1.callingstationid::text = new.callingstationid::text)) DO INSTEAD  UPDATE cui SET lastaccounting = '-infinity'::timestamp with time zone, cui = new.cui
  WHERE cui.username::text = new.username::text AND cui.clientipaddress = new.clientipaddress AND cui.callingstationid::text = new.callingstationid::text;
CREATE RULE
radius=# \d cui
                                                       Table "public.cui"
      Column      |           Type           | Collation | Nullable |                          Default
------------------+--------------------------+-----------+----------+-----------------------------------------------------------
 clientipaddress  | inet                     |           | not null | '0.0.0.0'::inet
 callingstationid | character varying(50)    |           | not null | ''::character varying
 username         | character varying(64)    |           | not null | ''::character varying
 cui              | character varying(128)   |           | not null | ''::character varying
 creationdate     | timestamp with time zone |           | not null | '2023-01-12 12:06:02.704965+01'::timestamp with time zone
 lastaccounting   | timestamp with time zone |           | not null | '-infinity'::timestamp without time zone
Indexes:
    "cui_pkey" PRIMARY KEY, btree (username, clientipaddress, callingstationid)
Rules:
    postauth_query AS
    ON INSERT TO cui
   WHERE (EXISTS ( SELECT 1
           FROM cui cui_1
          WHERE cui_1.username::text = new.username::text AND cui_1.clientipaddress = new.clientipaddress AND cui_1.callingstationid::text = new.callingstationid::text)) DO INSTEAD  UPDATE cui SET lastaccounting = '-infinity'::timestamp with time zone, cui = new.cui
  WHERE cui.username::text = new.username::text AND cui.clientipaddress = new.clientipaddress AND cui.callingstationid::text = new.callingstationid::text

Izlaz iz PostgreSQL baze podataka:

postgres=# \q
exit

Pregled logova

Pregled upisanih logova u PostgreSQL bazu podataka

Sledeći koraci odnose se na povezivanje sa PostgreSQL bazom i otvaranje radius baze u okviru nje:

root@postgresql:/etc/postgresql# su postgres
postgres@postgresql:/etc/postgresql$ psql
psql (13.9 (Debian 13.9-0+deb11u1))
Type "help" for help.

postgres=# \c radius
You are now connected to database "radius" as user "postgres".

Pregled radacct tabele:

radius=# select * from radacct;

Pregled cui tabele :

radius=# select * from cui;

Izlaz iz PostgreSQL baze podataka:

postgres=# \q
exit