PostgreSQL
Installing PostgreSQL and Configuring User Access
This guide covers installing PostgreSQL on Ubuntu and configuring it similarly to MySQL, with detailed steps for user creation and connection.
1. Install PostgreSQL
# Update package list

sudo apt update
# Install PostgreSQL and its contrib package (includes additional utilities)
sudo apt install postgresql postgresql-contrib
# Check PostgreSQL service status
sudo systemctl status postgresql
2. Configure Remote Access
# Edit the main PostgreSQL configuration file
sudo vim /etc/postgresql/*/main/postgresql.conf
# Find and modify the following line (around line 59):
listen_addresses = '*'
# Edit the client authentication configuration file
sudo vim /etc/postgresql/*/main/pg_hba.conf
# Add the following lines at the end to allow access from all IPs:
host all all 0.0.0.0/0 md5
host all all ::/0 md5
3. Restart PostgreSQL Service
# Restart the service to apply configuration changes
sudo systemctl restart postgresql
# Allow port 5432 through the firewall if needed
sudo ufw allow 5432
4. PostgreSQL Commands (MySQL Counterparts)
Connecting to PostgreSQL (Using the default postgres system user)
sudo -u postgres psql
PostgreSQL SQL Commands:
-- Create a user (equivalent to MySQL's CREATE USER)
CREATE USER test WITH PASSWORD 'test';
-- Create a database (specifying encoding for better compatibility)
CREATE DATABASE test
WITH
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
TEMPLATE = template0;
-- Or simply create a database
CREATE DATABASE test;
-- Grant all privileges on the database (equivalent to MySQL's GRANT ALL PRIVILEGES ON database.*)
GRANT ALL PRIVILEGES ON DATABASE test TO test;
-- Connect to the test database
\c test
-- Grant privileges on all existing objects within the 'public' schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO test;
-- Grant the user permission to create new tables in the schema
GRANT CREATE ON SCHEMA public TO test;
-- Set default privileges so that future objects also grant permissions automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON FUNCTIONS TO test;
-- View user permissions (equivalent to MySQL's SHOW GRANTS)
\du test
-- View database privileges
\l test
5. Complete Setup Script Example
Create a script file setup_postgres.sh:
#!/bin/bash
# Switch to the postgres user and execute SQL commands
sudo -u postgres psql <<EOF
-- Drop user if exists
DROP USER IF EXISTS test;
-- Drop database if exists
DROP DATABASE IF EXISTS test;
-- Create user with password
CREATE USER test WITH PASSWORD 'test';
-- Create database with proper encoding
CREATE DATABASE test
WITH
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
TEMPLATE = template0;
-- Grant database privileges
GRANT ALL PRIVILEGES ON DATABASE test TO test;
-- Connect to the database to set schema permissions
\c test
-- Grant schema privileges
GRANT CREATE ON SCHEMA public TO test;
-- Grant privileges on existing objects
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO test;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO test;
-- Set default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO test;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON FUNCTIONS TO test;
EOF
echo "PostgreSQL user and database setup complete!"
6. Connection Testing
# Connect using the new user (will prompt for password)
psql -h localhost -U test -d test -W
# Or connect using a specific IP address
psql -h [server_ip] -U test -d test -W
Key Differences from MySQL:
- Authentication System: PostgreSQL uses a role-based system, and by default, it uses "peer authentication" for local connections (matching the system user).
- No
FLUSH PRIVILEGES: Permission changes take effect immediately. - Encoding: It's often recommended to specify encoding during database creation.
- Schema Concept: PostgreSQL organizes objects into schemas within a database (the default is
public).
7. Quick Database Reset (Similar to MySQL's DROP DATABASE)
-- Execute in psql to terminate connections and recreate the database
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'test'
AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
GRANT ALL PRIVILEGES ON DATABASE test TO test;
Configuring Passwords and Connecting
PostgreSQL's authentication mechanism differs from MySQL. By default, it uses peer authentication for local connections. Here’s how to manage passwords and connect.
1. Default PostgreSQL User
- Default superuser:
postgres - Default password: None. Authentication relies on the system user.
- Default access: Only local connections via the system user.
2. Solutions for Connection Issues
Solution 1: Connect Using the System User (Recommended)
# Connect via the system 'postgres' user (no password required)
sudo -u postgres psql
# Or switch to the postgres user first
sudo su - postgres
psql
Solution 2: Set a Password for the postgres User
Within the PostgreSQL command line:
-- Log in as the postgres user
sudo -u postgres psql
-- Change the postgres user's password
ALTER USER postgres WITH PASSWORD 'new_password';
Solution 3: Use the test User You Created
If you've created the test user, connect like this:
# Method 1: Specify username and database
psql -h 10.0.0.18 -U test -d test -W
# Method 2: Use a connection string
psql postgresql://test:test@10.0.0.18/test
3. Full Workflow to Create a User with a Password
# 1. Log in as the system postgres user
sudo -u postgres psql
# 2. Execute SQL commands within PostgreSQL
-- Create a new user with a password
CREATE USER test WITH PASSWORD 'test';
-- Create a database
CREATE DATABASE test;
-- Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE test TO test;
-- Grant privileges on the public schema for the new user
\c test
GRANT ALL ON SCHEMA public TO test;
-- Exit
\q
4. Different Ways to Test Connections
Local Connection (Without Specifying Host)
# Method 1: Using the postgres system user
sudo -u postgres psql
# Method 2: Create a .pgpass file for the new user for password-less login
echo "localhost:5432:test:test:test" >> ~/.pgpass
chmod 600 ~/.pgpass
psql -h localhost -U test -d test
Remote Connection
# Method 1: Interactive password prompt
psql -h 10.0.0.18 -U test -d test -W
# Method 2: Set password via environment variable
PGPASSWORD=test psql -h 10.0.0.18 -U test -d test
# Method 3: Use a full connection string
psql "host=10.0.0.18 port=5432 dbname=test user=test password=test"
5. Troubleshooting Common Issues
Check if the User Was Created
sudo -u postgres psql -c "\du"
Check Database Privileges
sudo -u postgres psql -c "\l"
Verify Authentication Configuration
# View the pg_hba.conf configuration for the test user
sudo grep -A 5 -B 5 "test" /etc/postgresql/*/main/pg_hba.conf
# Or view the entire authentication config
sudo cat /etc/postgresql/*/main/pg_hba.conf
6. Quick Setup and Reset Script
Create setup_postgres_user.sh:
#!/bin/bash
# Set variables
DB_USER="test"
DB_PASS="test"
DB_NAME="test"
PG_HOST="localhost"
echo "Setting up PostgreSQL user and database..."
# Execute SQL commands as the postgres user
sudo -u postgres psql <<EOF
-- Terminate all connections to the target database
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '${DB_NAME}'
AND pid <> pg_backend_pid();
-- Drop user and database if they exist
DROP USER IF EXISTS ${DB_USER};
DROP DATABASE IF EXISTS ${DB_NAME};
-- Create user and database
CREATE USER ${DB_USER} WITH PASSWORD '${DB_PASS}';
CREATE DATABASE ${DB_NAME} OWNER ${DB_USER};
-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} TO ${DB_USER};
-- Output creation info
\echo "User ${DB_USER} and database ${DB_NAME} created successfully!"
EOF
echo "Setup complete!"
echo "Connection commands:"
echo "Local connection: psql -h ${PG_HOST} -U ${DB_USER} -d ${DB_NAME} -W"
echo "Remote connection: psql -h [server_ip] -U ${DB_USER} -d ${DB_NAME} -W"
Make the script executable and run it:
chmod +x setup_postgres_user.sh
./setup_postgres_user.sh
7. Important Notes
- No
FLUSH PRIVILEGES: Permission changes are immediate. - Multiple Authentication Methods: Can be configured for password, certificate, system (peer), etc.
- Default Port 5432: Different from MySQL's 3306.
- Connection String Format:
postgresql://user:password@host:port/database
Now you should be able to connect successfully. If you still encounter issues, please provide the specific error message.