PostgreSQL User and Database Management Guide

Introduction

PostgreSQL is a powerful, open-source relational database management system that provides robust user management and database administration capabilities. This comprehensive guide covers the essential aspects of managing PostgreSQL users and databases, including creation, modification, permissions, and best practices for database administration.

PostgreSQL uses a role-based access control system where users are essentially roles that can log in. Understanding this concept is fundamental to effective PostgreSQL administration.

User Management

Creating Users

PostgreSQL provides multiple methods to create users, each with different syntax and capabilities.

#### Using CREATE USER Statement

`sql CREATE USER username WITH PASSWORD 'password'; `

#### Using CREATE ROLE Statement

`sql CREATE ROLE username WITH LOGIN PASSWORD 'password'; `

Note: The CREATE USER statement is essentially equivalent to CREATE ROLE with the LOGIN attribute automatically included.

#### Advanced User Creation Options

`sql CREATE USER username WITH PASSWORD 'secure_password' CREATEDB CREATEROLE VALID UNTIL '2024-12-31' CONNECTION LIMIT 10; `

User Attributes and Permissions

PostgreSQL users can have various attributes that control their capabilities within the database system.

| Attribute | Description | Example | |-----------|-------------|---------| | LOGIN | Allows the role to log in | CREATE ROLE user1 WITH LOGIN; | | SUPERUSER | Grants all privileges | CREATE USER admin WITH SUPERUSER; | | CREATEDB | Can create databases | CREATE USER dev WITH CREATEDB; | | CREATEROLE | Can create other roles | CREATE USER manager WITH CREATEROLE; | | REPLICATION | Can initiate replication | CREATE USER replica WITH REPLICATION; | | BYPASSRLS | Can bypass row-level security | CREATE USER auditor WITH BYPASSRLS; | | CONNECTION LIMIT | Limits concurrent connections | CREATE USER app WITH CONNECTION LIMIT 5; | | VALID UNTIL | Sets expiration date | CREATE USER temp WITH VALID UNTIL '2024-06-30'; |

Modifying Existing Users

#### Changing User Passwords

`sql ALTER USER username WITH PASSWORD 'new_password'; `

#### Modifying User Attributes

`sql -- Grant database creation privilege ALTER USER username CREATEDB;

-- Remove superuser privilege ALTER USER username NOSUPERUSER;

-- Set connection limit ALTER USER username CONNECTION LIMIT 20;

-- Set password expiration ALTER USER username VALID UNTIL '2025-01-01'; `

#### Renaming Users

`sql ALTER USER old_username RENAME TO new_username; `

Viewing User Information

#### List All Users

`sql -- Using psql meta-command \du

-- Using SQL query SELECT usename, usecreatedb, usesuper, userepl FROM pg_user; `

#### Detailed User Information

`sql SELECT rolname as username, rolsuper as is_superuser, rolcreatedb as can_create_db, rolcreaterole as can_create_role, rolcanlogin as can_login, rolconnlimit as connection_limit, rolvaliduntil as valid_until FROM pg_roles WHERE rolcanlogin = true; `

Deleting Users

#### Basic User Deletion

`sql DROP USER username; `

#### Handling Dependencies

Before dropping a user, ensure they don't own any database objects:

`sql -- Check for owned objects SELECT schemaname, tablename, tableowner FROM pg_tables WHERE tableowner = 'username';

-- Reassign ownership REASSIGN OWNED BY old_user TO new_user;

-- Drop remaining privileges DROP OWNED BY username;

-- Finally drop the user DROP USER username; `

Database Management

Creating Databases

#### Basic Database Creation

`sql CREATE DATABASE database_name; `

#### Advanced Database Creation

`sql CREATE DATABASE production_db WITH OWNER = app_user TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = 100; `

Database Configuration Options

| Option | Description | Example | |--------|-------------|---------| | OWNER | Database owner | OWNER = postgres | | TEMPLATE | Template database | TEMPLATE = template1 | | ENCODING | Character encoding | ENCODING = 'UTF8' | | LC_COLLATE | Collation order | LC_COLLATE = 'C' | | LC_CTYPE | Character classification | LC_CTYPE = 'C' | | TABLESPACE | Storage tablespace | TABLESPACE = fast_storage | | CONNECTION LIMIT | Max connections | CONNECTION LIMIT = 50 |

Modifying Databases

#### Renaming Databases

`sql ALTER DATABASE old_name RENAME TO new_name; `

#### Changing Database Owner

`sql ALTER DATABASE database_name OWNER TO new_owner; `

#### Modifying Database Settings

`sql -- Set connection limit ALTER DATABASE mydb CONNECTION LIMIT 200;

-- Set default tablespace ALTER DATABASE mydb SET default_tablespace = 'fast_storage';

-- Set session parameters ALTER DATABASE mydb SET shared_preload_libraries = 'pg_stat_statements'; `

Viewing Database Information

#### List All Databases

`sql -- Using psql meta-command \l

-- Using SQL query SELECT datname as database_name, datowner as owner_id, pg_get_userbyid(datowner) as owner_name, encoding, datcollate as collate, datctype as ctype, datconnlimit as connection_limit FROM pg_database; `

#### Database Size Information

`sql SELECT datname as database_name, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database ORDER BY pg_database_size(datname) DESC; `

Database Connection Management

#### Viewing Active Connections

`sql SELECT datname as database, usename as username, client_addr as client_ip, state, query_start, query FROM pg_stat_activity WHERE state = 'active'; `

#### Terminating Connections

`sql -- Terminate specific connection SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'database_name' AND pid <> pg_backend_pid();

-- Terminate all connections to a database SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'target_database' AND pid <> pg_backend_pid(); `

Deleting Databases

#### Basic Database Deletion

`sql DROP DATABASE database_name; `

Note: You cannot drop a database while connected to it or while other users are connected to it.

#### Force Database Deletion (PostgreSQL 13+)

`sql DROP DATABASE database_name WITH (FORCE); `

Permission Management

Database-Level Permissions

#### Granting Database Permissions

`sql -- Grant connection permission GRANT CONNECT ON DATABASE mydb TO username;

-- Grant temporary table creation GRANT TEMPORARY ON DATABASE mydb TO username;

-- Grant all database privileges GRANT ALL PRIVILEGES ON DATABASE mydb TO username; `

#### Revoking Database Permissions

`sql -- Revoke connection permission REVOKE CONNECT ON DATABASE mydb FROM username;

-- Revoke all privileges REVOKE ALL PRIVILEGES ON DATABASE mydb FROM username; `

Schema-Level Permissions

#### Granting Schema Permissions

`sql -- Grant usage on schema GRANT USAGE ON SCHEMA public TO username;

-- Grant create permission on schema GRANT CREATE ON SCHEMA public TO username;

-- Grant all schema privileges GRANT ALL ON SCHEMA public TO username; `

Table-Level Permissions

#### Common Table Permissions

| Permission | Description | SQL Command | |------------|-------------|-------------| | SELECT | Read data | GRANT SELECT ON table_name TO user; | | INSERT | Add new rows | GRANT INSERT ON table_name TO user; | | UPDATE | Modify existing rows | GRANT UPDATE ON table_name TO user; | | DELETE | Remove rows | GRANT DELETE ON table_name TO user; | | TRUNCATE | Empty table | GRANT TRUNCATE ON table_name TO user; | | REFERENCES | Create foreign keys | GRANT REFERENCES ON table_name TO user; | | TRIGGER | Create triggers | GRANT TRIGGER ON table_name TO user; |

#### Granting Table Permissions

`sql -- Grant specific permissions GRANT SELECT, INSERT ON employees TO hr_user;

-- Grant all table permissions GRANT ALL PRIVILEGES ON employees TO admin_user;

-- Grant permissions on all tables in schema GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Grant permissions on future tables ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user; `

Column-Level Permissions

`sql -- Grant select on specific columns GRANT SELECT (employee_id, first_name, last_name) ON employees TO hr_user;

-- Grant update on specific columns GRANT UPDATE (salary, department) ON employees TO manager_user; `

Viewing Permissions

#### Table Permissions

`sql -- Using psql meta-command \dp table_name

-- Using SQL query SELECT grantee, privilege_type, is_grantable FROM information_schema.table_privileges WHERE table_name = 'employees'; `

#### Database Permissions

`sql SELECT datname as database, datacl as permissions FROM pg_database WHERE datname = 'mydb'; `

Role Management

Understanding Roles vs Users

In PostgreSQL, users and roles are essentially the same thing. A role becomes a user when it has the LOGIN attribute.

#### Creating Roles

`sql -- Create a role without login capability CREATE ROLE developers;

-- Create a role with specific permissions CREATE ROLE readonly_access; GRANT CONNECT ON DATABASE mydb TO readonly_access; GRANT USAGE ON SCHEMA public TO readonly_access; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_access; `

#### Role Inheritance

`sql -- Create parent role CREATE ROLE department_manager; GRANT CREATE ON SCHEMA public TO department_manager;

-- Create child role that inherits permissions CREATE ROLE hr_manager INHERIT; GRANT department_manager TO hr_manager;

-- Create user and assign role CREATE USER john_doe WITH LOGIN PASSWORD 'secure_pass'; GRANT hr_manager TO john_doe; `

Role Hierarchy Example

`sql -- Create organizational roles CREATE ROLE company_employee; CREATE ROLE department_head INHERIT; CREATE ROLE executive INHERIT;

-- Set up inheritance GRANT company_employee TO department_head; GRANT department_head TO executive;

-- Create specific users CREATE USER alice WITH LOGIN PASSWORD 'alice_pass'; CREATE USER bob WITH LOGIN PASSWORD 'bob_pass'; CREATE USER charlie WITH LOGIN PASSWORD 'charlie_pass';

-- Assign roles GRANT company_employee TO alice; GRANT department_head TO bob; GRANT executive TO charlie; `

Security Best Practices

Password Security

#### Strong Password Policies

`sql -- Create user with strong password CREATE USER secure_user WITH PASSWORD 'Str0ng_P@ssw0rd_2024!' VALID UNTIL '2024-12-31'; `

#### Password Encryption

PostgreSQL automatically encrypts passwords, but you can verify the method:

`sql -- Check password encryption method SHOW password_encryption;

-- Set encryption method (in postgresql.conf or session) SET password_encryption = 'scram-sha-256'; `

Connection Security

#### Host-Based Authentication (pg_hba.conf)

`bash

Example pg_hba.conf entries

TYPE DATABASE USER ADDRESS METHOD

Local connections

IPv4 local connections

SSL connections only

Principle of Least Privilege

#### Creating Limited-Access Users

`sql -- Create application user with minimal permissions CREATE USER app_user WITH LOGIN PASSWORD 'app_secure_password' CONNECTION LIMIT 10;

-- Grant only necessary permissions GRANT CONNECT ON DATABASE production_db TO app_user; GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE ON specific_table TO app_user; `

#### Read-Only User Creation

`sql -- Create read-only user CREATE USER readonly_user WITH LOGIN PASSWORD 'readonly_password' CONNECTION LIMIT 5;

-- Grant read-only permissions GRANT CONNECT ON DATABASE mydb TO readonly_user; GRANT USAGE ON SCHEMA public TO readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- Ensure future tables are also readable ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user; `

Command Reference

Essential psql Commands

| Command | Description | Example | |---------|-------------|---------| | \du | List users/roles | \du+ (detailed view) | | \l | List databases | \l+ (with sizes) | | \c | Connect to database | \c mydb username | | \dn | List schemas | \dn+ | | \dt | List tables | \dt public.* | | \dp | List table permissions | \dp tablename | | \z | Same as \dp | \z public.* | | \db | List tablespaces | \db+ |

Administrative Commands

#### User Management Commands

`sql -- Create user variations CREATE USER username; CREATE USER username WITH PASSWORD 'password'; CREATE USER username WITH ENCRYPTED PASSWORD 'password';

-- Modify user commands ALTER USER username WITH PASSWORD 'new_password'; ALTER USER username CREATEDB; ALTER USER username NOSUPERUSER; ALTER USER username RENAME TO new_name;

-- Delete user DROP USER username; DROP USER IF EXISTS username; `

#### Database Management Commands

`sql -- Create database variations CREATE DATABASE dbname; CREATE DATABASE dbname OWNER username; CREATE DATABASE dbname TEMPLATE template0;

-- Modify database commands ALTER DATABASE dbname RENAME TO new_name; ALTER DATABASE dbname OWNER TO new_owner; ALTER DATABASE dbname SET parameter = value;

-- Delete database DROP DATABASE dbname; DROP DATABASE IF EXISTS dbname; `

Backup and Restore Considerations

#### User and Role Backup

`bash

Dump global objects (users, roles, tablespaces)

Restore global objects

#### Database Backup with User Context

`bash

Backup database

Backup with specific user

Restore database

Monitoring and Maintenance

User Activity Monitoring

#### Current User Sessions

`sql SELECT usename as username, application_name, client_addr, client_port, backend_start, state, query FROM pg_stat_activity WHERE state = 'active' ORDER BY backend_start; `

#### User Connection Statistics

`sql SELECT usename, count(*) as connection_count, max(backend_start) as latest_connection FROM pg_stat_activity GROUP BY usename ORDER BY connection_count DESC; `

Database Usage Statistics

#### Database Size and Activity

`sql SELECT d.datname as database_name, pg_size_pretty(pg_database_size(d.datname)) as size, s.numbackends as active_connections, s.xact_commit as transactions_committed, s.xact_rollback as transactions_rolled_back, s.blks_read as blocks_read, s.blks_hit as blocks_hit_cache FROM pg_database d LEFT JOIN pg_stat_database s ON d.datname = s.datname WHERE d.datistemplate = false ORDER BY pg_database_size(d.datname) DESC; `

Maintenance Tasks

#### Regular Maintenance Queries

`sql -- Find unused roles (no login in last 30 days) SELECT r.rolname FROM pg_roles r LEFT JOIN pg_stat_activity a ON r.rolname = a.usename WHERE r.rolcanlogin = true AND a.usename IS NULL;

-- Find databases with no recent activity SELECT datname, stats_reset, numbackends FROM pg_stat_database WHERE stats_reset < NOW() - INTERVAL '30 days' AND datname NOT IN ('template0', 'template1', 'postgres'); `

This comprehensive guide provides the foundation for effective PostgreSQL user and database management. Regular practice with these commands and concepts will develop proficiency in PostgreSQL administration, ensuring secure and efficient database operations.