POSTGRES install failed (What was wrong)

thor@jump_host / ssh peter@172.16.239.10 The authenticity of host '172.16.239.10 (172.16.239.10)' can't be established. ECDSA key fingerprint is SHA256:OkT920MxjPBxXBDhai2+G7HEFw62v9JNFSB6jmnvQmE. ECDSA key fingerprint is MD5:4e:2f:5d:0f:b9:9c:37:fd:16:c7:9b:9f:bd:63:65:32. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '172.16.239.10' (ECDSA) to the list of known hosts. peter@172.16.239.10's password: [peter@stdb01 ~] sudo yum install postgresql-server postgresql-contrib

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

#1) Respect the privacy of others.
#2) Think before you type.    #3) With great power comes great responsibility.

[sudo] password for peter:
Sorry, try again.
[sudo] password for peter:
Loaded plugins: fastestmirror, ovl
Determining fastest mirrors

  • base: ftp.plusline.net
  • extras: mirror.alpix.eu
  • updates: mirror.checkdomain.de
    base | 3.6 kB 00:00:00
    extras | 2.9 kB 00:00:00
    updates | 2.9 kB 00:00:00
    (1/4): extras/7/x86_64/primary_db | 164 kB 00:00:00
    (2/4): base/7/x86_64/group_gz | 165 kB 00:00:00
    (3/4): updates/7/x86_64/primary_db | 6.7 MB 00:00:00
    (4/4): base/7/x86_64/primary_db | 6.0 MB 00:00:00
    Resolving Dependencies
    –> Running transaction check
    —> Package postgresql-contrib.x86_64 0:9.2.24-2.el7_7 will be installed
    –> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-2.el7_7 for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    –> Processing Dependency: postgresql(x86-64) = 9.2.24-2.el7_7 for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    –> Processing Dependency: libxslt.so.1(LIBXML2_1.0.22)(64bit) for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    –> Processing Dependency: libxslt.so.1(LIBXML2_1.0.18)(64bit) for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    –> Processing Dependency: libxslt.so.1(LIBXML2_1.0.11)(64bit) for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    –> Processing Dependency: libxslt.so.1()(64bit) for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    –> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    –> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql-contrib-9.2.24-2.el7_7.x86_64
    —> Package postgresql-server.x86_64 0:9.2.24-2.el7_7 will be installed
    –> Processing Dependency: systemd-sysv for package: postgresql-server-9.2.24-2.el7_7.x86_64
    –> Running transaction check
    —> Package libxslt.x86_64 0:1.1.28-5.el7 will be installed
    —> Package postgresql.x86_64 0:9.2.24-2.el7_7 will be installed
    —> Package postgresql-libs.x86_64 0:9.2.24-2.el7_7 will be installed
    —> Package systemd-sysv.x86_64 0:219-67.el7_7.3 will be installed
    –> Processing Dependency: systemd = 219-67.el7_7.3 for package: systemd-sysv-219-67.el7_7.3.x86_64
    —> Package uuid.x86_64 0:1.6.2-26.el7 will be installed
    –> Running transaction check
    —> Package systemd.x86_64 0:219-62.el7_6.9 will be updated
    —> Package systemd.x86_64 0:219-67.el7_7.3 will be an update
    –> Processing Dependency: systemd-libs = 219-67.el7_7.3 for package: systemd-219-67.el7_7.3.x86_64
    –> Running transaction check
    —> Package systemd-libs.x86_64 0:219-62.el7_6.9 will be updated
    —> Package systemd-libs.x86_64 0:219-67.el7_7.3 will be an update
    –> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================
Package Arch Version Repository Size

Installing:
postgresql-contrib x86_64 9.2.24-2.el7_7 updates 552 k
postgresql-server x86_64 9.2.24-2.el7_7 updates 3.8 M
Installing for dependencies:
libxslt x86_64 1.1.28-5.el7 base 242 k
postgresql x86_64 9.2.24-2.el7_7 updates 3.0 M
postgresql-libs x86_64 9.2.24-2.el7_7 updates 234 k
systemd-sysv x86_64 219-67.el7_7.3 updates 88 k
uuid x86_64 1.6.2-26.el7 base 55 k
Updating for dependencies:
systemd x86_64 219-67.el7_7.3 updates 5.1 M
systemd-libs x86_64 219-67.el7_7.3 updates 411 k

Transaction Summary

Install 2 Packages (+5 Dependent packages)
Upgrade ( 2 Dependent packages)

Total download size: 13 M
Is this ok [y/d/N]: y
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
(1/9): libxslt-1.1.28-5.el7.x86_64.rpm | 242 kB 00:00:00
(2/9): postgresql-contrib-9.2.24-2.el7_7.x86_64.rpm | 552 kB 00:00:00
(3/9): postgresql-libs-9.2.24-2.el7_7.x86_64.rpm | 234 kB 00:00:00
(4/9): postgresql-9.2.24-2.el7_7.x86_64.rpm | 3.0 MB 00:00:00
(5/9): postgresql-server-9.2.24-2.el7_7.x86_64.rpm | 3.8 MB 00:00:00
(6/9): systemd-libs-219-67.el7_7.3.x86_64.rpm | 411 kB 00:00:00
(7/9): systemd-219-67.el7_7.3.x86_64.rpm | 5.1 MB 00:00:00
(8/9): systemd-sysv-219-67.el7_7.3.x86_64.rpm | 88 kB 00:00:00
(9/9): uuid-1.6.2-26.el7.x86_64.rpm | 55 kB 00:00:00

Total 46 MB/s | 13 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-libs-9.2.24-2.el7_7.x86_64 1/11
Installing : postgresql-9.2.24-2.el7_7.x86_64 2/11
Installing : uuid-1.6.2-26.el7.x86_64 3/11
Installing : libxslt-1.1.28-5.el7.x86_64 4/11
Updating : systemd-libs-219-67.el7_7.3.x86_64 5/11
Updating : systemd-219-67.el7_7.3.x86_64 6/11
Installing : systemd-sysv-219-67.el7_7.3.x86_64 7/11
Installing : postgresql-server-9.2.24-2.el7_7.x86_64 8/11
Installing : postgresql-contrib-9.2.24-2.el7_7.x86_64 9/11
Cleanup : systemd-219-62.el7_6.9.x86_64 10/11
Cleanup : systemd-libs-219-62.el7_6.9.x86_64 11/11
Verifying : systemd-libs-219-67.el7_7.3.x86_64 1/11
Verifying : postgresql-server-9.2.24-2.el7_7.x86_64 2/11
Verifying : systemd-219-67.el7_7.3.x86_64 3/11
Verifying : postgresql-libs-9.2.24-2.el7_7.x86_64 4/11
Verifying : libxslt-1.1.28-5.el7.x86_64 5/11
Verifying : uuid-1.6.2-26.el7.x86_64 6/11
Verifying : systemd-sysv-219-67.el7_7.3.x86_64 7/11
Verifying : postgresql-contrib-9.2.24-2.el7_7.x86_64 8/11
Verifying : postgresql-9.2.24-2.el7_7.x86_64 9/11
Verifying : systemd-219-62.el7_6.9.x86_64 10/11
Verifying : systemd-libs-219-62.el7_6.9.x86_64 11/11

Installed:
postgresql-contrib.x86_64 0:9.2.24-2.el7_7 postgresql-server.x86_64 0:9.2.24-2.el7_7

Dependency Installed:
libxslt.x86_64 0:1.1.28-5.el7 postgresql.x86_64 0:9.2.24-2.el7_7
postgresql-libs.x86_64 0:9.2.24-2.el7_7 systemd-sysv.x86_64 0:219-67.el7_7.3
uuid.x86_64 0:1.6.2-26.el7

Dependency Updated:
systemd.x86_64 0:219-67.el7_7.3 systemd-libs.x86_64 0:219-67.el7_7.3

Complete!
[peter@stdb01 ~]$ sudo postgresql-setup initdb
Initializing database … OK

[peter@stdb01 ~] sudo systemctl start postgresql [peter@stdb01 ~] sudo systemctl enable postgresql
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[peter@stdb01 ~] psql postgres psql: FATAL: role "peter" does not exist [peter@stdb01 ~] sudo psql postgres
psql: FATAL: role “root” does not exist
[peter@stdb01 ~] sudo passwd postgres Changing password for user postgres. New password: BAD PASSWORD: The password fails the dictionary check - it is based on a dictionary word Retype new password: passwd: all authentication tokens updated successfully. [peter@stdb01 ~] psql postgres
psql: FATAL: role “peter” does not exist
[peter@stdb01 ~] su - postgres Password: -bash-4.2 psql postgres
psql (9.2.24)
Type “help” for help.

postgres=# createuser kodekloud_top –pwprompt
postgres-# createdb kodekloud_db6 -O kodekloud_top
postgres-# psql kodekloud_db6 -U kodekloud_top
postgres-# \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)

postgres-# eit
postgres-# exit
postgres-# quit
postgres-# help
postgres-# psql kodekloud_top
postgres-# \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)

postgres-# psql postgres
postgres-# \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)

postgres-# \q
-bash-4.2$ quit
-bash: quit: command not found
-bash-4.2$ exit
logout
[peter@stdb01 ~]$ sudo -u postgres psql
[sudo] password for peter:
could not change directory to “/home/peter”
psql (9.2.24)
Type “help” for help.

postgres=# \q
[peter@stdb01 ~] sudo -u postgres createuser kodekloud_top could not change directory to "/home/peter" [peter@stdb01 ~] sudo -u postgres psql
could not change directory to “/home/peter”
psql (9.2.24)
Type “help” for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# ?
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql

Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file

Input/Output
\copy … perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)

Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\ddp [PATTERN] list default privileges
\dD[S+] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\l[+] list all databases
\sf[+] FUNCNAME show a function’s definition
\z [PATTERN] same as \dp

Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE] set table output option
(NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML

tag attributes, or unset if none
\x [on|off|auto] toggle expanded output (currently off)

Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently “postgres”)
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
\conninfo display information about current connection

Operating System
\cd [DIR] change the current working directory
\setenv NAME [VALUE] set or unset environment variable
\timing [on|off] toggle timing of commands (currently off)
! [COMMAND] execute command in shell or start interactive shell

Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable

Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
postgres=#
postgres=#
postgres=# createuser --interactive
postgres-# \h
Available help:
ABORT CREATE FUNCTION DROP TABLE
ALTER AGGREGATE CREATE GROUP DROP TABLESPACE
ALTER COLLATION CREATE INDEX DROP TEXT SEARCH CONFIGURATION
ALTER CONVERSION CREATE LANGUAGE DROP TEXT SEARCH DICTIONARY
ALTER DATABASE CREATE OPERATOR DROP TEXT SEARCH PARSER
ALTER DEFAULT PRIVILEGES CREATE OPERATOR CLASS DROP TEXT SEARCH TEMPLATE
ALTER DOMAIN CREATE OPERATOR FAMILY DROP TRIGGER
ALTER EXTENSION CREATE ROLE DROP TYPE
ALTER FOREIGN DATA WRAPPER CREATE RULE DROP USER
ALTER FOREIGN TABLE CREATE SCHEMA DROP USER MAPPING
ALTER FUNCTION CREATE SEQUENCE DROP VIEW
ALTER GROUP CREATE SERVER END
ALTER INDEX CREATE TABLE EXECUTE
ALTER LANGUAGE CREATE TABLE AS EXPLAIN
ALTER LARGE OBJECT CREATE TABLESPACE FETCH
ALTER OPERATOR CREATE TEXT SEARCH CONFIGURATION GRANT
ALTER OPERATOR CLASS CREATE TEXT SEARCH DICTIONARY INSERT
ALTER OPERATOR FAMILY CREATE TEXT SEARCH PARSER LISTEN
ALTER ROLE CREATE TEXT SEARCH TEMPLATE LOAD
ALTER SCHEMA CREATE TRIGGER LOCK
ALTER SEQUENCE CREATE TYPE MOVE
ALTER SERVER CREATE USER NOTIFY
ALTER TABLE CREATE USER MAPPING PREPARE
ALTER TABLESPACE CREATE VIEW PREPARE TRANSACTION
ALTER TEXT SEARCH CONFIGURATION DEALLOCATE REASSIGN OWNED
ALTER TEXT SEARCH DICTIONARY DECLARE REINDEX
ALTER TEXT SEARCH PARSER DELETE RELEASE SAVEPOINT
ALTER TEXT SEARCH TEMPLATE DISCARD RESET
ALTER TRIGGER DO REVOKE
ALTER TYPE DROP AGGREGATE ROLLBACK
ALTER USER DROP CAST ROLLBACK PREPARED
ALTER USER MAPPING DROP COLLATION ROLLBACK TO SAVEPOINT
ALTER VIEW DROP CONVERSION SAVEPOINT
ANALYZE DROP DATABASE SECURITY LABEL
BEGIN DROP DOMAIN SELECT
CHECKPOINT DROP EXTENSION SELECT INTO
CLOSE DROP FOREIGN DATA WRAPPER SET
CLUSTER DROP FOREIGN TABLE SET CONSTRAINTS
COMMENT DROP FUNCTION SET ROLE
COMMIT DROP GROUP SET SESSION AUTHORIZATION
COMMIT PREPARED DROP INDEX SET TRANSACTION
COPY DROP LANGUAGE SHOW
CREATE AGGREGATE DROP OPERATOR START TRANSACTION
CREATE CAST DROP OPERATOR CLASS TABLE
CREATE COLLATION DROP OPERATOR FAMILY TRUNCATE
CREATE CONVERSION DROP OWNED UNLISTEN
CREATE DATABASE DROP ROLE UPDATE
CREATE DOMAIN DROP RULE VACUUM
CREATE EXTENSION DROP SCHEMA VALUES
CREATE FOREIGN DATA WRAPPER DROP SEQUENCE WITH
CREATE FOREIGN TABLE DROP SERVER
postgres-# \h CREATE USER
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ … ] ]

where option can be:

  SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid

postgres-# createuser kodekloud_top -pwprompt
postgres-# ALTER USER kodekloud_top WITH PASSWORD ‘BruCStnMT5’
postgres-# CREATE DATABASE kodekloud_db6
postgres-# \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)

postgres-# CREATE DATABASE kodekloud_db6;
ERROR: syntax error at or near “createuser”
LINE 1: createuser
^
postgres=# createuser kodekloud_top;
ERROR: syntax error at or near “createuser”
LINE 1: createuser kodekloud_top;
^
postgres=# CREATE USER kodekloud_top;
ERROR: role “kodekloud_top” already exists
postgres=# ALTER USER kodekloud_top WITH PASSWORD ‘BruCStnMT5’;
ALTER ROLE
postgres=# CREATE DATABASE kodekloud_db6;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------±---------±---------±------------±------------±----------------------
kodekloud_db6 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
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
(4 rows)

postgres=# GRANT ALL PRIVILEGES ON DATABASE kodekloud_db6 TO kodekloud_top;
GRANT
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------±---------±---------±------------±------------±---------------------------
kodekloud_db6 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | kodekloud_top=CTc/postgres
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
(4 rows)

postgres=# ALTER USER kodekloud_top WITH ENCRYPTED PASSWORD ‘BruCStnMT5’;
ALTER ROLE
postgres=#

Hi @labheshp

I think you missed the last point entirely:

d. Make appropriate settings to allow local clients to connect to the kodekloud_db6 database through kodekloud_top user using md5 encrypted password for authentication.