-
Notifications
You must be signed in to change notification settings - Fork 0
/
POSTGRES
97 lines (80 loc) · 2.58 KB
/
POSTGRES
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
PostgreSQL Configuration, User Management, and Testing
PostgreSQL Configuration
PostgreSQL Configuration Files
postgresql.conf file is used to configure PostgreSQL's general settings.
pg_hba.conf file controls client authentication and access to the database.
Listen on All Addresses
To allow connections from all IP addresses, set listen_addresses to '*' in postgresql.conf.
listen_addresses = '*'
pg_hba.conf Rule to Accept All IP Addresses
Add a rule in pg_hba.conf to accept connections from all IP addresses with md5 authentication.
plaintext
Copy code
host all all 0.0.0.0/0 md5
PostgreSQL User Management
Create a User
To create a PostgreSQL user named "zuri," use the following SQL command:
sql
Copy code
CREATE USER zuri;
Grant Superuser Privileges
To make a user an administrator, grant the SUPERUSER role:
sql
Copy code
ALTER USER zuri WITH SUPERUSER;
Create a Database and Grant Access
Create a database and attach it to a user using SQL commands. For example:
sql
Copy code
CREATE DATABASE your_database;
GRANT ALL PRIVILEGES ON DATABASE your_database TO zuri;
Access PostgreSQL Console (psql)
To access the PostgreSQL console, run:
bash
Copy code
sudo -u postgres psql
From within the PostgreSQL console, you can execute SQL commands.
Testing the Connection
Programmatic Testing with Node.js
You can use the pg library in Node.js to programmatically test the connection:
javascript
Copy code
const { Pool } = require('pg');
const pool = new Pool({
user: 'your_postgres_user',
host: 'your_postgres_host',
database: 'your_database_name',
password: 'your_postgres_password',
port: 5432
});
pool.query('SELECT NOW()', (err, result) => {
if (err) {
console.error('Error connecting to the database:', err);
} else {
console.log('Connected to the database. Current timestamp:', result.rows[0].now);
}
pool.end();
});
Command-Line Testing with psql
To test the connection using the psql command-line tool, run:
bash
Copy code
psql -h your_postgres_host -U your_postgres_user -d your_database_name
Replace the placeholders with your actual PostgreSQL server information.
Linux Shell Commands
Update postgresql.conf
Edit the postgresql.conf file to set listen_addresses:
bash
Copy code
sudo nano /etc/postgresql/{version}/main/postgresql.conf
Update pg_hba.conf
Edit the pg_hba.conf file to configure client authentication:
bash
Copy code
sudo nano /etc/postgresql/{version}/main/pg_hba.conf
Restart PostgreSQL
After making configuration changes, restart PostgreSQL:
bash
Copy code
sudo service postgresql restart
Feel free to use this comprehensive Markdown summary as a reference for PostgreSQL