User Management in PostgreSQL

User Management in PostgreSQL

These commands allow you to create, alter, and manage users and roles in PostgreSQL.

1. Create a User:

To create a new user, you can use the CREATE USER command. Users are also referred to as roles in PostgreSQL.

CREATE USER username WITH PASSWORD 'password';

Replace username with the desired username and password with the user's password.

2. Create a Role with Specific Privileges:

You can create a user as a role with specific privileges like login, create databases, or superuser access.

CREATE ROLE role_name WITH LOGIN PASSWORD 'password' CREATEDB CREATEROLE;

LOGIN: Allows the role to log in (create user).

CREATEDB: Allows the role to create databases.

CREATEROLE: Allows the role to create other roles.

SUPERUSER: Gives the role superuser privileges (use with caution).

3. Grant Permissions to a User:

To grant specific permissions to a user, you use the GRANT command.

Grant Database Access:

GRANT CONNECT ON DATABASE dbname TO username;

Grant Table Permissions (e.g., SELECT, INSERT, UPDATE):

GRANT SELECT, INSERT, UPDATE ON TABLE tablename TO username;

Grant All Permissions on a Schema:

GRANT ALL PRIVILEGES ON SCHEMA schemaname TO username;

4. Revoke Permissions:

To revoke a user’s access or privileges, you use the REVOKE command.

Revoke Specific Permissions on a Table:

REVOKE SELECT, INSERT ON TABLE tablename FROM username;

Revoke All Privileges on a Schema:

REVOKE ALL PRIVILEGES ON SCHEMA schemaname FROM username;

5. Alter User Role:

You can modify a user’s attributes like changing their password or adding/removing permissions.

Change Password:

ALTER USER username WITH PASSWORD 'newpassword';

Grant Superuser Role (making a user a superuser):

ALTER USER username WITH SUPERUSER;

Revoke Superuser Role (making a user non-superuser):

ALTER USER username WITH NOSUPERUSER;

6. Delete User:

To delete a user from the PostgreSQL database, use the DROP USER command.

DROP USER username;

Schema Management in PostgreSQL:

Schemas in PostgreSQL are used to organize database objects like tables, views, and functions.

1. Create a Schema:

To create a new schema, use the CREATE SCHEMA command.

CREATE SCHEMA schema_name;

2. Set a Schema Search Path:

You can specify the default schemas that PostgreSQL should look into when querying objects.

SET search_path TO schema_name;

3. Grant Permissions on Schema:

You can give a user access to a specific schema by using the GRANT command.

GRANT USAGE ON SCHEMA schema_name TO username;

USAGE allows the user to access the schema and its objects.

4. List All Schemas:

To view all schemas in the database, run:

\dn

This will list all the schemas in the current database.

5. List All Tables in a Schema:

To list tables in a specific schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'schema_name';

6. Drop a Schema:

To drop a schema from the database, use the DROP SCHEMA command. Be careful, as this will delete all objects within the schema.

DROP SCHEMA schema_name CASCADE;

The CASCADE option automatically deletes all objects within the schema.

7. Rename a Schema:

To rename a schema:

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

Schema and Object Management:

Managing database objects within a schema, such as tables, views, and sequences.

1. Create a Table:

To create a new table inside a schema:

CREATE TABLE schema_name.table_name (

    column1 datatype PRIMARY KEY,

    column2 datatype,

    column3 datatype

);

2. Modify Table Structure:

To add, modify, or drop columns in a table, use the following commands:

Add a Column:

ALTER TABLE schema_name.table_name ADD COLUMN column_name datatype;

Rename a Column:

ALTER TABLE schema_name.table_name RENAME COLUMN old_column_name TO new_column_name;

Change Data Type of a Column:

ALTER TABLE schema_name.table_name ALTER COLUMN column_name TYPE new_datatype;

Drop a Column:

ALTER TABLE schema_name.table_name DROP COLUMN column_name;

3. Drop a Table:

To delete a table from a schema:

DROP TABLE schema_name.table_name;

4. Create a View:

To create a view, which is a virtual table based on a query:

CREATE VIEW schema_name.view_name AS

SELECT column1, column2

FROM schema_name.table_name

WHERE condition;

5. Drop a View:

To delete a view:

DROP VIEW schema_name.view_name;

6. Create an Index:

To improve query performance, you can create an index on a table’s column(s):

CREATE INDEX index_name ON schema_name.table_name (column_name);

7. Drop an Index:

To delete an index:

DROP INDEX schema_name.index_name;

Miscellaneous Commands:

1. List All Users (Roles):

To list all users (roles) in the database:

\du

2. List All Tables:

To list all tables in the current schema:

\dt

3. Change Database:

To switch to another database:

\c database_name;

Backup and Restore Commands:

1. Backup a Database:

To take a backup of a database, use the pg_dump utility:

pg_dump -U username -W -F t dbname > backupfile.tar

-U username: The username to connect to the database.

-W: Prompts for the password.

-F t: Specifies the format (tar).

dbname: The database you want to back up.

2. Restore a Database:

To restore a database, use the pg_restore utility:

pg_restore -U username -W -d dbname backupfile.tar

-d dbname: The target database where the backup will be restored.

Here’s a comprehensive set of PostgreSQL commands for user management and schema management that will help you manage your database as an administrator.

User Management in PostgreSQL:

These commands allow you to create, alter, and manage users and roles in PostgreSQL.

1. Create a User:

To create a new user, you can use the CREATE USER command. Users are also referred to as roles in PostgreSQL.

CREATE USER username WITH PASSWORD 'password';

Replace username with the desired username and password with the user's password.

2. Create a Role with Specific Privileges:

You can create a user as a role with specific privileges like login, create databases, or superuser access.

CREATE ROLE role_name WITH LOGIN PASSWORD 'password' CREATEDB CREATEROLE;

LOGIN: Allows the role to log in (create user).

CREATEDB: Allows the role to create databases.

CREATEROLE: Allows the role to create other roles.

SUPERUSER: Gives the role superuser privileges (use with caution).

3. Grant Permissions to a User:

To grant specific permissions to a user, you use the GRANT command.

Grant Database Access:

GRANT CONNECT ON DATABASE dbname TO username;

Grant Table Permissions (e.g., SELECT, INSERT, UPDATE):

GRANT SELECT, INSERT, UPDATE ON TABLE tablename TO username;

Grant All Permissions on a Schema:

GRANT ALL PRIVILEGES ON SCHEMA schemaname TO username;

4. Revoke Permissions:

To revoke a user’s access or privileges, you use the REVOKE command.

Revoke Specific Permissions on a Table:

REVOKE SELECT, INSERT ON TABLE tablename FROM username;

Revoke All Privileges on a Schema:

REVOKE ALL PRIVILEGES ON SCHEMA schemaname FROM username;

5. Alter User Role:

You can modify a user’s attributes like changing their password or adding/removing permissions.

Change Password:

ALTER USER username WITH PASSWORD 'newpassword';

Grant Superuser Role (making a user a superuser):

ALTER USER username WITH SUPERUSER;

Revoke Superuser Role (making a user non-superuser):

ALTER USER username WITH NOSUPERUSER;

6. Delete User:

To delete a user from the PostgreSQL database, use the DROP USER command.

DROP USER username;

Schema Management in PostgreSQL:

Schemas in PostgreSQL are used to organize database objects like tables, views, and functions.

1. Create a Schema:

To create a new schema, use the CREATE SCHEMA command.

CREATE SCHEMA schema_name;

2. Set a Schema Search Path:

You can specify the default schemas that PostgreSQL should look into when querying objects.

SET search_path TO schema_name;

3. Grant Permissions on Schema:

You can give a user access to a specific schema by using the GRANT command.

GRANT USAGE ON SCHEMA schema_name TO username;

USAGE allows the user to access the schema and its objects.

4. List All Schemas:

To view all schemas in the database, run:

\dn

This will list all the schemas in the current database.

5. List All Tables in a Schema:

To list tables in a specific schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'schema_name';

6. Drop a Schema:

To drop a schema from the database, use the DROP SCHEMA command. Be careful, as this will delete all objects within the schema.

DROP SCHEMA schema_name CASCADE;

The CASCADE option automatically deletes all objects within the schema.

7. Rename a Schema:

To rename a schema:

ALTER SCHEMA old_schema_name RENAME TO new_schema_name;

Schema and Object Management:

Managing database objects within a schema, such as tables, views, and sequences.

1. Create a Table:

To create a new table inside a schema:

CREATE TABLE schema_name.table_name (

    column1 datatype PRIMARY KEY,

    column2 datatype,

    column3 datatype

);

2. Modify Table Structure:

To add, modify, or drop columns in a table, use the following commands:

Add a Column:

ALTER TABLE schema_name.table_name ADD COLUMN column_name datatype;

Rename a Column:

ALTER TABLE schema_name.table_name RENAME COLUMN old_column_name TO new_column_name;

Change Data Type of a Column:

ALTER TABLE schema_name.table_name ALTER COLUMN column_name TYPE new_datatype;

Drop a Column:

ALTER TABLE schema_name.table_name DROP COLUMN column_name;

3. Drop a Table:

To delete a table from a schema:

DROP TABLE schema_name.table_name;

4. Create a View:

To create a view, which is a virtual table based on a query:

CREATE VIEW schema_name.view_name AS

SELECT column1, column2

FROM schema_name.table_name

WHERE condition;

5. Drop a View:

To delete a view:

DROP VIEW schema_name.view_name;

6. Create an Index:

To improve query performance, you can create an index on a table’s column(s):

CREATE INDEX index_name ON schema_name.table_name (column_name);

7. Drop an Index:

To delete an index:

DROP INDEX schema_name.index_name;

Miscellaneous Commands:

1. List All Users (Roles):

To list all users (roles) in the database:

\du

2. List All Tables:

To list all tables in the current schema:

\dt

3. Change Database:

To switch to another database:

\c database_name;

Backup and Restore Commands:

1. Backup a Database:

To take a backup of a database, use the pg_dump utility:

pg_dump -U username -W -F t dbname > backupfile.tar

-U username: The username to connect to the database.

-W: Prompts for the password.

-F t: Specifies the format (tar).

dbname: The database you want to back up.

2. Restore a Database:

To restore a database, use the pg_restore utility:

pg_restore -U username -W -d dbname backupfile.tar

-d dbname: The target database where the backup will be restored.

Additional Tips for PostgreSQL Admin:

Automate Backups: Schedule regular backups using cron jobs (Linux) or Task Scheduler (Windows).

Use pgAdmin: For GUI-based management, consider using pgAdmin, which provides an intuitive interface for managing users, schemas, tables, and much more.

Logging: Enable and monitor PostgreSQL logs for performance issues or any abnormal behavior.

Database Optimization: Periodically use the VACUUM command to optimize your database.

Basics of Database (PostgreSQL)

Basics of Database (PostgreSQL)

---

1. Introduction to PostgreSQL

Topic: Understand what PostgreSQL is, its features, and its advantages.

Steps:

Install PostgreSQL on your machine (using apt, yum, or downloading from the PostgreSQL website).

Verify installation by running psql --version to check the version.

Understand the PostgreSQL architecture (client-server, process model).

---

2. Database Creation and Management

Topic: Creating databases and managing them.

Steps:

Create a new database:

CREATE DATABASE mydb;

List databases:

\l

Connect to a database:

\c mydb

Drop a database:

DROP DATABASE mydb;

---

3. Tables and Schema Management

Topic: Creating and managing tables and schemas.

Steps:

Create a new schema:

CREATE SCHEMA myschema;

Create a table with columns and data types:

CREATE TABLE employees (

  id SERIAL PRIMARY KEY,

  name VARCHAR(100),

  age INT,

  hire_date DATE

);

Show tables in the current schema:

\dt

Describe table structure:

\d <table_name>

\d employees

Drop a table:

DROP TABLE employees;

---

4. Data Types

Topic: Understanding and using PostgreSQL data types.

Steps:

Common data types: INTEGER, SERIAL, VARCHAR, TEXT, BOOLEAN, DATE, TIMESTAMP, DECIMAL, NUMERIC.

Example of using different data types in a table:

CREATE TABLE product (

  product_id SERIAL PRIMARY KEY,

  product_name VARCHAR(100),

  price NUMERIC(10, 2),

  available BOOLEAN,

  release_date TIMESTAMP

);

---

5. Inserting Data

Topic: Inserting data into tables.

Steps:

Insert single row:

INSERT INTO employees (name, age, hire_date) 

VALUES ('John Doe', 30, '2020-01-15');

Insert multiple rows:

INSERT INTO employees (name, age, hire_date) 

VALUES 

('Alice', 28, '2021-06-20'),

('Bob', 35, '2019-09-10');

---

6. Querying Data

Topic: Basic SELECT queries to retrieve data.

Steps:

Select all columns:

SELECT * FROM employees;

Select specific columns:

SELECT name, age FROM employees;

Use WHERE clause for filtering:

SELECT * FROM employees WHERE age > 30;

Limit the number of rows:

SELECT * FROM employees LIMIT 5;

---

7. Updating Data

Topic: Modifying existing data.

Steps:

Update a specific row:

UPDATE employees SET age = 31 WHERE name = 'John Doe';

Update multiple rows:

UPDATE employees SET age = age + 1 WHERE hire_date < '2021-01-01';

---

8. Deleting Data

Topic: Removing data from tables.

Steps:

Delete a specific row:

DELETE FROM employees WHERE name = 'Bob';

Delete all rows:

DELETE FROM employees;

---

9. Constraints and Keys

Topic: Understanding and applying constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL).

Steps:

Add a PRIMARY KEY:

CREATE TABLE orders (

  order_id SERIAL PRIMARY KEY,

  customer_id INT

);

Add a NOT NULL constraint:

CREATE TABLE customers (

  customer_id SERIAL PRIMARY KEY,

  customer_name VARCHAR(100) NOT NULL

);

Add a UNIQUE constraint:

CREATE TABLE products (

  product_id SERIAL PRIMARY KEY,

  product_code VARCHAR(50) UNIQUE

);

Create a FOREIGN KEY:

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

---

10. Altering Tables

Topic: Modifying the structure of existing tables.

Steps:

Add a new column:

ALTER TABLE employees ADD COLUMN salary NUMERIC(10, 2);

Drop a column:

ALTER TABLE employees DROP COLUMN salary;

Rename a column:

ALTER TABLE employees RENAME COLUMN salary TO annual_salary;

---

11. Indexing

Topic: Creating and using indexes for query performance.

Steps:

Create an index on a column:

CREATE INDEX idx_employee_name ON employees(name);

Drop an index:

DROP INDEX idx_employee_name;

---

12. Basic Joins

Topic: Combining data from multiple tables.

Steps:

INNER JOIN:

SELECT orders.order_id, customers.customer_name

FROM orders

INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN:

SELECT employees.name, orders.order_id

FROM employees

LEFT JOIN orders ON employees.employee_id = orders.employee_id;

---

13. Views

Topic: Creating views to simplify complex queries.

Steps:

Create a view:

CREATE VIEW employee_orders AS 

SELECT employees.name, orders.order_id 

FROM employees 

JOIN orders ON employees.employee_id = orders.employee_id;

Query the view:

SELECT * FROM employee_orders;

---

14. Transactions

Topic: Understanding and using transactions.

Steps:

Start a transaction:

BEGIN;

Commit the transaction:

COMMIT;

Rollback the transaction:

ROLLBACK;

---

1. Database Management

Advanced Database Management

Create a Database with Template:

CREATE DATABASE mydb TEMPLATE template0 ENCODING 'UTF8';

Renaming a Database (after disconnecting users):

ALTER DATABASE mydb RENAME TO newdb;

Clone a Database (using pg_dump and pg_restore for high availability):

pg_dump mydb | psql clonedb

Database Size with Detailed Tablespaces Info:

SELECT pg_size_pretty(pg_database_size('mydb')), pg_tablespace_location(oid) FROM pg_database WHERE datname = 'mydb';

Set Maintenance Mode (Prevent New Connections):

UPDATE pg_database SET datallowconn = false WHERE datname = 'mydb';

---

2. User and Role Management

Advanced User Management

Role with Login and Inheritance:

CREATE ROLE myrole LOGIN INHERIT;

Grant Role to Another Role (Role Hierarchy):

GRANT role1 TO role2;

Alter User with Multiple Attributes:

ALTER USER username WITH PASSWORD 'newpassword' VALID UNTIL '2025-12-31' SUPERUSER;

Set Resource Limits for Users:

ALTER ROLE username SET statement_timeout TO '10min';

ALTER ROLE username SET work_mem TO '50MB';

Audit User Activities (pg_audit):

CREATE EXTENSION pgaudit;

SET pgaudit.log = 'read, write';

---

3. Schema and Table Management

Advanced Schema Management

Transfer Ownership of a Schema:

ALTER SCHEMA myschema OWNER TO new_owner;

Drop a Schema Cascade (with dependent objects):

DROP SCHEMA myschema CASCADE;

Set Schema Search Path:

SET search_path TO myschema, public;

Cluster Tables (for optimizing storage):

CLUSTER mytable USING my_index;

---

4. Backup and Restore

Advanced Backup Techniques

Point-in-Time Recovery (PITR) (Create a Base Backup):

pg_basebackup -D /backupdir -F tar -z -P -X stream

Take a Backup Using pg_dumpall for All Databases:

pg_dumpall > all_databases_backup.sql

Restore a Database to a Specific Point in Time (Using restore_command):

restore_command = 'cp /backupdir/%f %p'

Parallel Backup/Restore (for large databases):

pg_dump -j 4 -F d -f /backupdir mydb

pg_restore -j 4 -d mydb /backupdir/mydb

---

5. Monitoring and Logs

Advanced Monitoring

Track Disk Usage of Tables and Indexes:

SELECT 

  relname AS "Relation", 

  pg_size_pretty(pg_total_relation_size(relid)) AS "Total Size",

  pg_size_pretty(pg_indexes_size(relid)) AS "Index Size"

FROM pg_catalog.pg_statio_user_tables;

Log File Analysis (Identifying Slow Queries):

SELECT * FROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '5 min';

Real-time Query Performance Monitoring:

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Monitor Query Performance with Detailed Execution Plans:

EXPLAIN ANALYZE SELECT * FROM employees WHERE name = 'John Doe';

Detect Deadlocks in Real Time:

SELECT * FROM pg_locks WHERE NOT granted;

---

6. Performance and Optimization

Advanced Performance Tuning

Set Max Parallel Workers (Adjust for complex queries):

ALTER SYSTEM SET max_parallel_workers_per_gather = 4;

Configure work_mem and shared_buffers for High Performance:

ALTER SYSTEM SET work_mem = '128MB';

ALTER SYSTEM SET shared_buffers = '8GB';

Monitor Autovacuum Activity (To prevent table bloat):

SELECT relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE autovacuum_count > 0;

Enable Query Caching (via pg_stat_statements):

CREATE EXTENSION pg_stat_statements;

Create Custom Indexes for Query Optimization:

CREATE INDEX idx_employee_name_age ON employees (name, age);

---

7. Security Management

Advanced Security Configuration

Configure SSL Encryption for Connections:

In postgresql.conf:

ssl = on

ssl_cert_file = '/etc/ssl/certs/mycert.crt'

ssl_key_file = '/etc/ssl/private/mykey.key'

Create and Manage SSL Certificates:

openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout mykey.key -out mycert.crt

Enable Row-Level Security (RLS) for fine-grained access control:

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY employee_policy ON employees FOR SELECT USING (age > 30);

Audit and Log Database Access (pgAudit Extension):

CREATE EXTENSION pgaudit;

SET pgaudit.log = 'read, write, ddl';

---

8. Clustering & Replication

Advanced Replication Management

Set up Streaming Replication (Master-Slave):

Master postgresql.conf settings:

wal_level = replica

max_wal_senders = 5

hot_standby = on

On Slave:

standby_mode = on

primary_conninfo = 'host=master_ip_address port=5432 user=replica password=replica_password'

Promote a Replica to Master:

pg_ctl promote -D /var/lib/postgresql/data

Set Up Logical Replication (for selective data replication):

CREATE PUBLICATION mypublication FOR TABLE employees;

CREATE SUBSCRIPTION mysubscription CONNECTION 'host=master dbname=mydb user=replica' PUBLICATION mypublication;

How to list all tables?

\dtS

\dtS *

SELECT * FROM pg_catalog.pg_tables;

SELECT n.nspname as "Schema",

c.relname as "Name", 

CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",

pg_catalog.pg_get_userbyid(c.relowner) as "Owner"

FROM pg_catalog.pg_class c

    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','')

    AND n.nspname <> 'pg_catalog'

    AND n.nspname <> 'information_schema'

    AND n.nspname !~ '^pg_toast'

AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;   

---

9. High Availability & Disaster Recovery

Setting Up Hot Standby with Replication

Start Standby Server (on Slave):

pg_ctl -D /data_directory start

Enable Automatic Failover (Using Patroni or Pacemaker):

Configure Patroni (for failover automation):

postgresql:

  listen: 0.0.0.0

  connect_address: 'postgresql://node1'

  replication:

    username: replication

    password: replication_pass

---

10. Maintenance & Troubleshooting

Advanced Maintenance Operations

Monitor and Prevent Table Bloat (using pgstattuple extension):

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('employees');

Check for and Remove Orphaned Objects (useful for cleanup):

SELECT relname FROM pg_stat_user_tables WHERE n_tup_ins = 0;

Force a Database to Be Consistent (in case of system crash):

pg_rewind --target-pgdata=/target_pg_data --source-server='host=primary_db_address port=5432'

Handle Long-Running Queries (and terminate them if necessary):

SELECT pid, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 minutes';

SELECT pg_terminate_backend(pid);