PostgreSQL Security | Learning PostgreSQL 10

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents
Feedback & Rating

Learning PostgreSQL 10 - Second Edition

By : Juba, Volkov

2.3 (6)

Learning PostgreSQL 10

2.3 (6)

By: Juba, Volkov

Overview of this book

PostgreSQL is one of the most popular open source databases in the world, supporting the most advanced features included in SQL standards. This book will familiarize you with the latest features released in PostgreSQL 10. We’ll start with a thorough introduction to PostgreSQL and the new features introduced in PostgreSQL 10. We’ll cover the Data Definition Language (DDL) with an emphasis on PostgreSQL, and the common DDL commands supported by ANSI SQL. You’ll learn to create tables, define integrity constraints, build indexes, and set up views and other schema objects. Moving on, we’ll cover the concepts of Data Manipulation Language (DML) and PostgreSQL server-side programming capabilities using PL/pgSQL. We’ll also explore the NoSQL capabilities of PostgreSQL and connect to your PostgreSQL database to manipulate data objects. By the end of this book, you’ll have a thorough understanding of the basics of PostgreSQL 10 and will have the necessary skills to build efficient database solutions.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

Relational Databases

Relational Databases

Database management systems

Relational and object relational databases

Relational algebra

Data modeling

Summary

PostgreSQL in Action

PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL capabilities

Installing PostgreSQL

Summary

PostgreSQL Basic Building Blocks

PostgreSQL Basic Building Blocks

Database coding

PostgreSQL objects hierarchy

PostgreSQL database components

The car web portal database

Summary

PostgreSQL Advanced Building Blocks

PostgreSQL Advanced Building Blocks

Views

View synopsis

View categories

Updatable views

Indexes

Functions

User-defined data types

Triggers and rule systems

Summary

SQL Language

SQL Language

SQL fundamentals

Querying data with SELECT statements

Changing the data in the database

Summary

Advanced Query Writing

Advanced Query Writing

Common table expressions

Window functions

Advanced SQL techniques

Summary

Server-Side Programming with PL/pgSQL

Server-Side Programming with PL/pgSQL

SQL language and PL/pgSQL – a comparison

PostgreSQL function parameters

The PostgreSQL PL/pgSQL control statements

Function predefined variables

Exception handling

Dynamic SQL

Summary

OLAP and Data Warehousing

OLAP and Data Warehousing

Online analytical processing

Partitioning

Parallel query

Index-only scans

Summary

Beyond Conventional Data Types

Beyond Conventional Data Types

Arrays

The hash store data structure

The JSON data structure

A PostgreSQL full text search

Summary

Transactions and Concurrency Control

Transactions and Concurrency Control

Transactions

Explicit locking

Summary

PostgreSQL Security

PostgreSQL Security

Authentication in PostgreSQL

Authentication best practices

PostgreSQL default access privileges

Role system and proxy authentication

PostgreSQL security levels

Encrypting data

Summary

The PostgreSQL Catalog

The PostgreSQL Catalog

The system catalog

System catalog for administrators

Cleaning up the database

Tuning for performance

Selective dump

Summary

Optimizing Database Performance

Optimizing Database Performance

PostgreSQL configuration tuning

Tuning performance for write

Tuning performance for read

Detecting problems in query plans

Common mistakes in writing queries

Table partitioning

Query rewriting

Summary

Testing

Testing

Unit testing

Database abstraction interfaces

Summary

Using PostgreSQL in Python Applications

Using PostgreSQL in Python Applications

Python DB API 2.0

Low-level database access with psycopg2

Alternative drivers for PostgreSQL

Object relational mapping with SQLAlchemy

Summary

Scalability

Scalability

The problem of scalability and the CAP theorem

Data replication in PostgreSQL

Using replication to scale PostgreSQL

Summary

Customer Reviews

2.3 (6)

5 star

33.3%

4 star

0%

3 star

0%

2 star

0%

1 star

66.7%

Encrypting data

By default, PostgreSQL internally encrypts sensitive data, such as roles' passwords. However, database users can also encrypt and decrypt sensitive data using the pgcrypto extension.

PostgreSQL role password encryption

When creating a role with password and login options, one can see the role's details in the pg_shadow catalog relation. Note that it is not recommended to use the following format to create the password:

CREATE ROLE <role_name> <with options> PASSWORD 'some_password';

The CREATE ROLE statement can appear in pg_stat_activity as well as the server logs, as follows:

postgres=# SELECT query FROM pg_stat_activity;
                query 
--------------------------------------


 SELECT query FROM pg_stat_activity;
 create role c password 'c';

All passwords stored in pg_shadow are encrypted with salt; finally, renaming an account will rest the password as follows:

postgres=# ALTER ROLE a RENAME TO b;
NOTICE: MD5 password cleared because of role rename

When creating...

Visually different images

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.

Learning PostgreSQL 10

Search

Your notes and bookmarks