created a base rocky 9.3 linux,
let's update the system up-to-date
add the postgreSQL repository
disable the built-in postgresql module, rocky linux coms with a default module for postgresql but we will use postgresql global development group PGDG repository to get the latest version
[root@postgresql ~]# dnf update -y
#dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# dnf -qy module disable postgresql
Install postgreSQL
Installed:
lz4-1.9.3-5.el9.x86_64
postgresql15-15.7-1PGDG.rhel9.x86_64
postgresql15-libs-15.7-1PGDG.rhel9.x86_64
postgresql15-server-15.7-1PGDG.rhel9.x86_64
Complete!
[root@postgresql ~]# dnf install -y postgresql15-server
Initialize postgresql db
[root@postgresql ~]# sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK
enable and start postgresql
[root@postgresql ~]# systemctl enable postgresql-15 && systemctl start postgresql-15
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-15.service → /usr/lib/systemd/system/postgresql-15.service.
[root@postgresql ~]# systemctl status postgresql-15.service
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; p>
Active: active (running) since Tue 2024-05-28 22:43:41 CEST; 25s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 33311 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir $>
Main PID: 33316 (postmaster)
Tasks: 7 (limit: 16134)
Memory: 17.3M
CPU: 136ms
CGroup: /system.slice/postgresql-15.service
├─33316 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
├─33317 "postgres: logger "
├─33318 "postgres: checkpointer "
├─33319 "postgres: background writer "
├─33321 "postgres: walwriter "
├─33322 "postgres: autovacuum launcher "
└─33323 "postgres: logical replication launcher "
May 28 22:43:41 postgresql.boobi.com systemd[1]: Starting PostgreSQL 15 databa>
May 28 22:43:41 postgresql.boobi.com postmaster[33316]: 2024-05-28 22:43:41.70>
May 28 22:43:41 postgresql.boobi.com postmaster[33316]: 2024-05-28 22:43:41.70>
May 28 22:43:41 postgresql.boobi.com systemd[1]: Started PostgreSQL 15 databas>
lines 1-22/22 (END)
///now configure postgresql//
//switch to the postgres user (here we can use #su postgres) but it's give full sudo permission
[root@postgresql ~]# sudo -i -u postgres
[postgres@postgresql ~]$
///now open the postgresql command line interface
[postgres@postgresql ~]$ psql
psql (15.7)
Type "help" for help.
postgres=# \password postgres
Enter new password for user "postgres":
Enter it again:
////here setup the password for postgres user
//now create a database
////how to check how many users i have inside psql
postgres-# \du
List of roles
Role name | Attributes | Membe
r of
-----------+------------------------------------------------------------+------
-----
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres-#
///if we want to check more information about each user
postgres=# select * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreated
b | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil |
rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-----------
--+-------------+----------------+--------------+-------------+---------------+
--------------+-----------+------
pg_database_owner | f | t | f | f
| f | f | -1 | ******** | |
f | | 6171
pg_read_all_data | f | t | f | f
| f | f | -1 | ******** | |
f | | 6181
pg_write_all_data | f | t | f | f
| f | f | -1 | ******** | |
f | | 6182
pg_monitor | f | t | f | f
| f | f | -1 | ******** | |
f | | 3373
pg_read_all_settings | f | t | f | f
| f | f | -1 | ******** | |
f | | 3374
pg_read_all_stats | f | t | f | f
| f | f | -1 | ******** | |
f | | 3375
pg_stat_scan_tables | f | t | f | f
| f | f | -1 | ******** | |
f | | 3377
pg_read_server_files | f | t | f | f
| f | f | -1 | ******** | |
f | | 4569
pg_write_server_files | f | t | f | f
| f | f | -1 | ******** | |
f | | 4570
pg_execute_server_program | f | t | f | f
--More--
\\\\\ postgres are the super user
//create a new user in psql
postgres=# CREATE USER boobalan WITH PASSWORD '*****';
CREATE ROLE
postgres#
//if i want to give permission or role to any db
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
/////check the user
postgres=# \du
List of roles
Role name | Attributes | Membe
r of
-----------+------------------------------------------------------------+------
-----
boobalan | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
//create a new role #CREATE ROLE myrole;
//give the role to user #GRANT myrole TO myuser;
//let me give one of the existing role to my newly created user
postgres=# ALTER ROLE boobalan WITH SUPERUSER;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Membe
r of
-----------+------------------------------------------------------------+------
-----
boobalan | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# ALTER ROLE boobalan WITH CREATEROLE;
ALTER ROLE
///before that let's create a database
CREATE DATABASE
postgres=# create user dbuser with encrypted password 'boobi';
CREATE ROLE
postgres=# grant all privileges on database mydb to dbuser;
GRANT
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Membe
r of
-----------+------------------------------------------------------------+------
-----
boobalan | Superuser, Create role | {}
dbuser | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
/////to exit from psql
////to check the databases
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Loc
ale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+----
-------------+-----------------------
mydb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | lib
c | =Tc/postgres +
| | | | | |
| postgres=CTc/postgres+
| | | | | |
| dbuser=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | lib
c |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | lib
c | =c/postgres +
| | | | | |
| postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | lib
c | =c/postgres +
| | | | | |
| postgres=CTc/postgres
(4 rows)
/////to connect the databases;
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
[postgres@postgresql ~]$ netstat -tuln | grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp6 0 0 ::1:5432 :::*
[root@postgresql ~]# netstat -tuln | grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp6 0 0 ::1:5432 :::*
///if you can't able to connect this from dbeaver
[root@postgresql ~]# cat /var/lib/pgsql/15/data/postgresql.conf | grep -in 'listen_addresses'
60:#listen_addresses = 'localhost' # what IP address(es) to listen on;
//// here the localhost need to be changed to '*'
////vi filename ---> 60G -> go to the line and removed the # and replace *
[root@postgresql ~]# cat /var/lib/pgsql/15/data/postgresql.conf | grep -in 'listen_addresses'
60:listen_addresses = '*' # what IP address(es) to listen on;
[root@postgresql ~]#
//restart service
[root@postgresql ~]# systemctl restart postgresql-15.service
[root@postgresql ~]# netstat -tuln | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:
//connect in dbeaver
//we need to edit and add the db and user details on pg_hba.conf file to avoid this error
/// last line has been updated
[root@postgresql ~]# vi /var/lib/pgsql/15/data/pg_hba.conf
[root@postgresql ~]# tail /var/lib/pgsql/15/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host mydb postgres 192.168.198.1/32 md5
[root@postgresql ~]#systemctl restart postgresql-15.service
////now successfully connected
//need to check further steps
//some basics
0 Comments