6. Security | PostgreSQL 9 Administration Cookbook - Second Edition

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

PostgreSQL 9 Administration Cookbook - Second Edition

By : Simon Riggs, GIANNI CIOLLI, Hannu Krosing, Gabriele Bartolini

5 (3)

PostgreSQL 9 Administration Cookbook - Second Edition

5 (3)

By: Simon Riggs, GIANNI CIOLLI, Hannu Krosing, Gabriele Bartolini

Overview of this book

Through example-driven recipes, with plenty of code, focused on the most vital features of the latest PostgreSQL version (9.4), both administrators and developers will follow short, specific guides to understand and leverage useful Postgre functionalities to create better and more efficient databases.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Sections

Conventions

Reader feedback

Customer support

Free Chapter

1. First Steps

1. First Steps

Introduction

Getting PostgreSQL

Connecting to the PostgreSQL server

Enabling access for network/remote users

Using graphical administration tools

Using the psql query and scripting tool

Changing your password securely

Avoiding hardcoding your password

Using a connection service file

Troubleshooting a failed connection

2. Exploring the Database

2. Exploring the Database

Introduction

What version is the server?

What is the server uptime?

Locating the database server files

Locating the database server's message log

Locating the database's system identifier

Listing databases on this database server

How many tables in a database?

How much disk space does a database use?

How much disk space does a table use?

Which are my biggest tables?

How many rows in a table?

Quickly estimating the number of rows in a table

Listing extensions in this database

Understanding object dependencies

3. Configuration

3. Configuration

Introduction

Reading The Fine Manual (RTFM)

Planning a new database

Changing parameters in your programs

Finding the current configuration settings

Which parameters are at nondefault settings?

Updating the parameter file

Setting parameters for particular groups of users

The basic server configuration checklist

Adding an external module to PostgreSQL

Using an installed module

Managing installed extensions

4. Server Control

4. Server Control

Introduction

Starting the database server manually

Stopping the server safely and quickly

Stopping the server in an emergency

Reloading the server configuration files

Restarting the server quickly

Preventing new connections

Restricting users to only one session each

Pushing users off the system

Deciding on a design for multitenancy

Using multiple schemas

Giving users their own private database

Running multiple servers on one system

Setting up a connection pool

Accessing multiple servers using the same host and port

5. Tables and Data

5. Tables and Data

Introduction

Choosing good names for database objects

Handling objects with quoted names

Enforcing the same name and definition for columns

Identifying and removing duplicates

Preventing duplicate rows

Finding a unique key for a set of data

Generating test data

Randomly sampling data

Loading data from a spreadsheet

Loading data from flat files

6. Security

6. Security

Introduction

The PostgreSQL superuser

Revoking user access to a table

Granting user access to a table

Creating a new user

Temporarily preventing a user from connecting

Removing a user without dropping their data

Checking whether all users have a secure password

Giving limited superuser powers to specific users

Auditing DDL changes

Auditing data changes

Always knowing which user is logged in

Integrating with LDAP

Connecting using SSL

Using SSL certificates to authenticate the client

Mapping external usernames to database roles

Encrypting sensitive data

7. Database Administration

7. Database Administration

Introduction

Writing a script that either succeeds entirely or fails entirely

Writing a psql script that exits on the first error

Performing actions on many tables

Adding/removing columns on a table

Changing the data type of a column

Changing the definition of a data type

Adding/removing schemas

Moving objects between schemas

Adding/removing tablespaces

Moving objects between tablespaces

Accessing objects in other PostgreSQL databases

Accessing objects in other foreign databases

Updatable views

Using materialized views

8. Monitoring and Diagnosis

8. Monitoring and Diagnosis

Introduction

Checking whether a user is connected

Checking which queries are running

Checking which queries are active or blocked

Knowing who is blocking a query

Killing a specific session

Detecting an in-doubt prepared transaction

Knowing whether anybody is using a specific table

Knowing when a table was last used

Usage of disk space by temporary data

Understanding why queries slow down

Investigating and reporting a bug

Producing a daily summary of log file errors

Analyzing the real-time performance of your queries

9. Regular Maintenance

9. Regular Maintenance

Introduction

Controlling automatic database maintenance

Avoiding auto-freezing and page corruptions

Avoiding transaction wraparound

Removing old prepared transactions

Actions for heavy users of temporary tables

Identifying and fixing bloated tables and indexes

Maintaining indexes

Adding a constraint without checking existing rows

Finding unused indexes

Carefully removing unwanted indexes

Planning maintenance

10. Performance and Concurrency

10. Performance and Concurrency

Introduction

Finding slow SQL statements

Collecting regular statistics from pg_stat* views

Finding out what makes SQL slow

Reducing the number of rows returned

Simplifying complex SQL queries

Speeding up queries without rewriting them

Why a query is not using an index

Forcing a query to use an index

Using optimistic locking

Reporting performance problems

11. Backup and Recovery

11. Backup and Recovery

Introduction

Understanding and controlling crash recovery

Planning backups

Hot logical backup of one database

Hot logical backup of all databases

Hot logical backup of all tables in a tablespace

Backup of database object definitions

Standalone hot physical database backup

Hot physical backup and continuous archiving

Recovery of all databases

Recovery to a point in time

Recovery of a dropped/damaged table

Recovery of a dropped/damaged tablespace

Recovery of a dropped/damaged database

Improving performance of backup/recovery

Incremental/differential backup and restore

Hot physical backups with Barman

Recovery with Barman

12. Replication and Upgrades

12. Replication and Upgrades

Introduction

Replication best practices

Setting up file-based replication – deprecated

Setting up streaming replication

Setting up streaming replication security

Hot Standby and read scalability

Managing streaming replication

Using repmgr

Using Replication Slots

Monitoring replication

Performance and Synchronous Replication

Delaying, pausing, and synchronizing replication

Logical Replication

Bi-Directional Replication

Archiving transaction log data

Upgrading – minor releases

Major upgrades in-place

Major upgrades online

Index

Index

Giving limited superuser powers to specific users

First, the superuser role has some privileges, which can also be granted to non-superuser roles separately.

To give the bob role the ability to create new databases, run this:

ALTER ROLE BOB WITH CREATEDB;

To give the bob role the ability to create new users, run the following:

ALTER ROLE BOB WITH CREATEUSER;

However, it is also possible to give ordinary users more fine-grained and controlled access to some action reserved for superusers, using SECURITY DEFINER functions. The same trick can also be used to pass partial privileges between different users.

Getting ready

First, you must have access to the database as a superuser in order to delegate some powers. Here, we assume the use of the default superuser named postgres.

We will demonstrate two cases of making some superuser-only functionality available to select an ordinary user.

The database must have support for the PL/pgSQL embedded language installed. Starting from PostgreSQL 9.0, the...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PostgreSQL 9 Administration Cookbook - Second Edition

Search

Your notes and bookmarks