PostgreSQL Administration

Data

We’ll use a database from https://postgrespro.com/education/demodb distributed under the PostgreSQL licence. It stores a month of data about airline flights in Russia and is organized according to the following schema:

From the Cloud IDE

  • Create PostgreSQL db
  • Pass: BZ76DSs9Sm0JhXZ9M1LtOJoY
  • Open a new terminal

Download db Dump

wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/example-guided-project/flights_RUSSIA_small.sql

The file you downloaded is a full database backup of a month of flight data in Russia. Now, you can perform a full restoration of the data set by first opening the PostgreSQL CLI.

Open PostgreSQL CLI

  • Click on PostgreSQL CLI button
  • Prompt will change to postgres=#

Create new db sakila

  • In the PostgreSQL CLI, enter the command \i <file_name>. In your case, the file name will be the name of the file you downloaded, flights_RUSSIA_small.sql. This will restore the data into a new database called demo.
postgres=# \i flights_RUSSIA_small.sql

# Verify the db was properly created - you'll notice the prompt has changed to db name
demo=# \dt

               List of relations
  Schema  |      Name       | Type  |  Owner   
----------+-----------------+-------+----------
 bookings | aircrafts_data  | table | postgres
 bookings | airports_data   | table | postgres
 bookings | boarding_passes | table | postgres
 bookings | bookings        | table | postgres
 bookings | flights         | table | postgres
 bookings | seats           | table | postgres
 bookings | ticket_flights  | table | postgres
 bookings | tickets         | table | postgres
(8 rows)

Now that we’ve created a db and populated with data, let’s create a server instance

Configure Server Instance

A PostgreSQL server instance has a corresponding file named postgresql.conf that contains the configuration parameters for the server. By modifying this file, you can enable, disable, or otherwise customize the settings of your PostgreSQL server instance to best suit your needs as a database administrator.

While you can manually modify this postgresql.conf file and restart the server for the changes to take effect, you can also edit some configuration parameters directly from the command line interface (CLI).

Wal_level

Now we will customize the configuration settings for the PostgreSQL instance using the CLI.

  • First, let’s take a look at the current setting of the wal_level parameter. You can do so by entering the following command into the CLI:
demo=# SHOW wal_level;
 wal_level 
-----------
 replica
(1 row)
  • Without going into too much detail, the wal_level parameter dictates how much information is written to the write-ahead log (WAL), which can be used for continuous archiving. If you’re interested, you can find further information in the PostgreSQL official documentation

Alter System

The ALTER SYSTEM command is a way to modify the global defaults of a PostgreSQL instance without having to manually edit the configuration file.

  • Let’s give it a try and change the wal_level parameter to logical.
  • To change the parameter, enter the following command into the CLI:
demo=# ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM
  • Let’s check the wal_level again now that we changed the level to logical

Nothing changed as you see below because this type of change requires a system restart

demo=# SHOW wal_level;
 wal_level 
-----------
 replica
(1 row)
  • Go back to the Instance manager menu above and click on the STOP blue button to stop it.
  • When it gives you the option to START click on it to start up again

Postgresql.conf

When you executed the ALTER SYSTEM command in Step 2 of this exercise, a new file named postgres.auto.conf was created. You can open the file by first opening the file explorer on Cloud IDE then clicking postgres > data > pgdata> postgresql.conf.

Check wal_level now

demo=# SHOW wal_level;
 wal_level 
-----------
 logical
(1 row)

For more advanced instance configuration where many parameter changes are required, using a series of ALTER SYSTEM commands may be cumbersome. Instead, you can edit the postgresql.conf file directly. You can once again use the Cloud IDE file explorer to open postgres > data > pgdata> postgresql.conf.

System Catalog Tables

The system catalog stores schema metadata, such as information about tables and columns and internal bookkeeping information. In PostgreSQL, the system catalogs are regular tables in which you can add columns and insert and update values. In directly modifying the system catalogs, you can cause severe problems in your system, so it is generally recommended to avoid doing so. Instead, the system catalogs are updated automatically when performing other SQL commands. For example, if you run a CREATE DATABASE command, a new database is created on the disk and a new row is automatically inserted into the pg_database system catalog table, storing metadata about that database.

Connect to db

Since we are already connected to the demo db no need to run the following command:

\connect demo

pg_tables

Start with a simple query of pg_tables, which is a system catalog containing metadata about each table in the database.

Let’s query it to display metadata about all the tables belonging to the bookings schema in the demo database by entering the following command into the CLI:

SELECT * FROM pg_tables WHERE schemaname = 'bookings';

Alter Security of Table

Suppose as the database administrator, you would like to enable row-level security for the boarding_passes table in the demo database.

When row security is enabled on a table, all normal access to the table for selecting or modifying rows must be specified by a row security policy. Since row security policies are not the focus of this lab, we will not go in depth about specifying a policy but will simply enable it for demonstration purposes. However, if you wish to learn more about this topic, you can check out the PostgreSQL documentation. To enable row security on the boarding_passes table, enter the following command in the CLI:

ALTER TABLE boarding_passes ENABLE ROW LEVEL SECURITY;

#Display the metadata of the table to see if it was altered
SELECT * FROM pg_tables WHERE schemaname = 'bookings';

 schemaname |    tablename    | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------------+------------+------------+------------+----------+-------------+-------------
 bookings   | ticket_flights  | postgres   |            | t          | f        | t           | f
 bookings   | aircrafts_data  | postgres   |            | t          | f        | t           | f
 bookings   | flights         | postgres   |            | t          | f        | t           | f
 bookings   | airports_data   | postgres   |            | t          | f        | t           | f
 bookings   | seats           | postgres   |            | t          | f        | t           | f
 bookings   | tickets         | postgres   |            | t          | f        | t           | f
 bookings   | bookings        | postgres   |            | t          | f        | t           | f
 bookings   | boarding_passes | postgres   |            | t          | f        | t           | t

pg_settings

Let’s connect your work in the previous section about PostgreSQL instance configuration to the system catalogs. Earlier, you used SHOW statements to display configuration parameters. There’s also a system catalog called pg_settings that stores data about configuration parameters of the PostgreSQL server. Let’s query with the following command

SELECT name, setting, short_desc FROM pg_settings WHERE name = 'wal_level';
demo=# SELECT name, setting, short_desc FROM pg_settings WHERE name = 'wal_level';
   name    | setting |                    short_desc                    
-----------+---------+--------------------------------------------------
 wal_level | logical | Set the level of information written to the WAL.
(1 row)

From the query, you see the same results from the SHOW statement in Exercise 1 and more. In fact, pg_tables contains much more data about a given parameter than is available from the SHOW statement (a full list can be found in the documentation) so, the somewhat more complicated SQL query has its benefits.

Change Table Name

Suppose you wanted to change the name of the aircrafts_data to aircraft_fleet.

demo=# UPDATE pg_tables SET tablename = 'aircraft_fleet' WHERE tablename = 'aircrafts_data';
ERROR:  cannot update view "pg_tables"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

To properly change the name of the aircrafts_data, enter the following command in the CLI:

demo=# ALTER TABLE aircrafts_data RENAME TO aircraft_fleet;
ALTER TABLE

# Review the table to see if it was altered
demo=# SELECT tablename FROM pg_tables WHERE schemaname = 'bookings';
    tablename    
-----------------
 aircraft_fleet
 ticket_flights
 flights
 airports_data
 seats
 tickets
 bookings
 boarding_passes
(8 rows)

User Management

For much of the routine tasks involved with interacting with a database, such as reading the content of a table or adding new entries, the postgres superuser may not be appropriate as it bypasses all permission checks, which carries inherent risk. Furthermore, as a database administrator, you will almost certainly not be the only one who will need to access the database in some capacity. For this reason, you will need a way to add new users to the database and give them the proper privileges that is appropriate for their use cases.

We’ll go through how to

  • Create roles in a database and grant them select permissions
  • Create new users in the database and assign them the appropriate role
  • Revoke and deny access to the database from a user

Data

Download the data:

$ wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/example-guided-project/flights_RUSSIA_small.sql

Create & Restore Data to db

  • From the PostgreSQL CLI > Load data from file > List Tables
postgres=# \i flights_RUSSIA_small.sql

demo=# \dt
               List of relations
  Schema  |      Name       | Type  |  Owner   
----------+-----------------+-------+----------
 bookings | aircrafts_data  | table | postgres
 bookings | airports_data   | table | postgres
 bookings | boarding_passes | table | postgres
 bookings | bookings        | table | postgres
 bookings | flights         | table | postgres
 bookings | seats           | table | postgres
 bookings | ticket_flights  | table | postgres
 bookings | tickets         | table | postgres
(8 rows)

Create Roles & Privileges

In PostgreSQL, users, groups, and roles are all the same entity, with the difference being that users can log in by default. Here we will create two new roles:

  • read_only
  • read_write
  • then grant them the relevant privileges
  • Since we are already connected to the demo db we can get started

New Role: read_only

demo=# CREATE ROLE read_only;
CREATE ROLE

Grant Privileges

demo=# GRANT CONNECT ON DATABASE demo TO read_only;
GRANT

Access Schema in Table

  • The role needs to be able to use the schema in use in this database. In our example, this is the bookings schema
  • Grant the privilege for the read_only role to use the schema by entering the following
demo=# GRANT USAGE ON SCHEMA bookings TO read_only;
GRANT

SELECT Only Privilege

  • To access the information in tables in a database, the SELECT command is used.
  • For the read_only role, we want it to be able to access the contents of the database but not to edit or alter it.
  • So for this role, only the SELECT privilege is needed.
  • To grant this privilege, enter the following command:
demo=# GRANT SELECT ON ALL TABLES IN SCHEMA bookings TO read_only;
GRANT

New Role: read_write

Privilege to Connect

Use Bookings Schema

demo=# CREATE ROLE read_write;
CREATE ROLE

# Grant privilege to connect to db
demo=# GRANT CONNECT ON DATABASE demo TO read_write;
GRANT

# Privilege to use the bookings schema
demo=# GRANT USAGE ON SCHEMA bookings TO read_write;
GRANT

Permission to Create

  • So far the commands for the read_write role have been essentially the same as for the read_only role.
  • However, the read_write role should have the privileges to not only access the contents of the database, but also to
    • create
    • delete
    • modify entries
  • The corresponding commands for these actions are SELECT, INSERT, DELETE, and UPDATE, respectively.
demo=# GRANT SELECT, INSERT, DELETE, UPDATE ON ALL TABLES IN SCHEMA bookings TO read_write;
GRANT

Add User & Assign a Role

This method streamlines the process of adding new users to the database since you don’t have to go through the process of granting custom privileges to each one. Instead, you can assign them a role and the user inherits the privileges of that role.

Suppose you wish to add a new user, user_a, for use by an information and help desk at an airport. In this case, assume that there is no need for this user to modify the contents of the database. As you may have guessed, the appropriate role to assign is the read_only role.

Create New User

demo=# CREATE USER user_a WITH PASSWORD 'user_a_password';
CREATE ROLE

Assign User to Role

demo=# GRANT read_only TO user_a;
GRANT ROLE

List All Roles

demo=# \du
                                     List of roles
 Role name  |                         Attributes                         |  Member of  
------------+------------------------------------------------------------+-------------
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 read_only  | Cannot login                                               | {}
 read_write | Cannot login                                               | {}
 user_a     |                                                            | {read_only}

Revoke & Deny

Suppose there is no need for the information and help desk at the airport to access information stored in the aircrafts_data table. We will revoke the SELECT privilege on the aircrafts_data table in the demo database from user_a.

Revoke SELECT Privilege from User

demo=# REVOKE SELECT ON aircrafts_data FROM user_a;
REVOKE

Revoke Role from User

Now suppose user_a is transferred departments within the airport and no longer needs to be able to access the demo database at all. You can remove all their SELECT privileges by simply revoking the read_only role you assigned to them earlier. 

demo=# REVOKE read_only FROM user_a;
REVOKE

List Updated Roles

demo=# \du

                                    List of roles
 Role name  |                         Attributes                         | Member of 
------------+------------------------------------------------------------+-----------
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 read_only  | Cannot login                                               | {}
 read_write | Cannot login                                               | {}
 user_a     |                                                            | {}