8. PostgreSQL Security | Learning PostgreSQL

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Learning PostgreSQL

By : Salahaldin Juba, Achim Vannahme, Andrey Volkov

4.3 (6)

Learning PostgreSQL

4.3 (6)

By: Salahaldin Juba, Achim Vannahme, Andrey Volkov

Overview of this book

PostgreSQL is one of the most powerful and easy to use database management systems. It supports the most advanced features included in SQL standards. The book starts with the introduction of relational databases with PostegreSQL. It then moves on to covering data definition language (DDL) with emphasis on PostgreSQL and common DDL commands supported by ANSI SQL. You will then learn the data manipulation language (DML), and advanced topics like locking and multi version concurrency control (MVCC). This will give you a very robust background to tune and troubleshoot your application. The book then covers the implementation of data models in the database such as creating tables, setting up integrity constraints, building indexes, defining views and other schema objects. Next, it will give you an overview about the NoSQL capabilities of PostgreSQL along with Hstore, XML, Json and arrays. Finally by the end of the book, you'll learn to use the JDBC driver and manipulate data objects in the Hibernate framework.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Relational Databases

1. Relational Databases

Database management systems

Relational algebra

Data modeling

Summary

2. PostgreSQL in Action

2. PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL capabilities

Installing PostgreSQL

Summary

3. PostgreSQL Basic Building Blocks

3. PostgreSQL Basic Building Blocks

Database coding

PostgreSQL objects hierarchy

PostgreSQL high-level object interaction

PostgreSQL database components

Summary

4. PostgreSQL Advanced Building Blocks

4. PostgreSQL Advanced Building Blocks

Views

Indexes

Functions

PostgreSQL user-defined data types

Triggers and rule systems

Summary

5. SQL Language

5. SQL Language

SQL fundamentals

Querying the data with the SELECT statement

Changing the data in the database

Summary

6. Advanced Query Writing

6. Advanced Query Writing

Common table expressions

Window functions

Advanced SQL

Transaction isolation and multiversion concurrency control

Summary

7. Server-Side Programming with PL/pgSQL

7. Server-Side Programming with PL/pgSQL

Introduction

PostgreSQL function parameters

The PostgreSQL PL/pgSQL control statements

Exception handling

Dynamic SQL

Summary

8. PostgreSQL Security

8. PostgreSQL Security

Authentication in PostgreSQL

PostgreSQL default access privileges

PostgreSQL security levels

Encrypting data

Summary

9. The PostgreSQL System Catalog and System Administration Functions

9. The PostgreSQL System Catalog and System Administration Functions

The system catalog

Getting the database cluster and client tools version

Terminating and canceling user sessions

Setting and getting database cluster settings

Getting the database and database object size

Cleaning up the database

Cleaning up data in the database

Managing database locks

Adding missing indexes on foreign keys and altering the default statistic

Getting the views dependency tree

Summary

10. Optimizing Database Performance

10. Optimizing Database Performance

PostgreSQL configuration tuning

Tuning PostgreSQL queries

Cross column correlation

Table partitioning

Summary

11. Beyond Conventional Data types

11. Beyond Conventional Data types

PostgreSQL arrays

Hash store

The PostgreSQL JSON data type

A PostgreSQL full text search

Summary

12. Testing

12. Testing

Unit testing

Schema difference

The interfaces test

PostgreSQL benchmarks

Summary

13. PostgreSQL JDBC

13. PostgreSQL JDBC

Introduction to JDBC

Connecting to a PostgreSQL database

Issuing a query and processing the results

Function handling

Design considerations

Summary

14. PostgreSQL and Hibernate

14. PostgreSQL and Hibernate

Introduction to ORM and Hibernate

Installation and configuration

Working with entities

Summary

Index

Index

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 because the statement can appear in pg_stat_activity or the server logs:

CREATE ROLE <role_name> WITH LOGIN PASWWORD 'role_password';

The passwords in pg_catalog are encrypted with a slat by default, as shown in the following example. Note how passwd for the a and b roles are different even though they have the same password:

CREATE ROLE a WITH LOGIN PASWWORD 'a';
CREATE ROLE b WITH LOGIN PASWWORD 'a';
SELECT usename, passwd FROM pg_shadow WHERE usename IN ('a','b');
 usename |               passwd
---------+-------------------------------------
 b     ...

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

Search

Your notes and bookmarks