MacLochlainns Weblog

The directory for the data dictionary changes with PostgreSQL has changed. You can find it with the following command:

postgres=# show data_directory;

This will return the following:

     data_directory     
------------------------
 /var/lib/pgsql/11/data
(1 row)

You need to create a physical videoDB subdirectory in the /var/lib/pgsql/11 directory. You can use the following syntax from the /var/lib/pgsql/11 directory to create the videoDB subdirectory:

mkdir videoDB

You can create a video_db tablespace with the following syntax:

CREATE TABLESPACE video_db
  OWNER postgres
  LOCATION '/var/lib/pgsql/11/videoDB';

This will return the following:

CREATE TABLESPACE

You can query whether you successfully create the video_db tablespace with the following:

SELECT * FROM pg_tablespace;

It should return the following:

  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 video_db   |       10 |        | 
(3 rows)

You can create a videodb database with the following syntax:

CREATE DATABASE videodb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = video_db
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

You can verify the creation of the videodb with the following command:

postgres# \l

It should show you a display like the following:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 videodb   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | dba=CTc/postgres
(4 rows)

Then, you can assign comment to the database with the following syntax:

COMMENT ON DATABASE videodb IS 'Video Database';

In this section you create a dba role, grant privileges on a videodb database to a role, and create a user with the role that you created previously with the following three statements. There are three steps in this sections.

• The first step creates a dba role:

  CREATE ROLE dba WITH SUPERUSER;

  CREATE ROLE dba WITH SUPERUSER;

• The second step grants all privileges on a videodb database to a dba role:

  GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;

  GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;

• The third step creates a student user with the dba role:

  CREATE USER student WITH ROLE dba ENCRYPTED PASSWORD 'student';

  CREATE USER student WITH ROLE dba ENCRYPTED PASSWORD 'student';

• It is possible that you may (and should if this is a new instance you are building) encounter an error when you try to connect as a sandboxed user. The syntax to connect as the student user is:

  psql -d videodb -U student -W

  psql -d videodb -U student -W

  You may encounter this error:

  psql: FATAL: Peer authentication failed for user "student"

  psql: FATAL: Peer authentication failed for user "student"

  You can fix this in PostgreSQL 11 by changing the user access parameters in the pg_hba.conf configuration file. The file is found in the /var/lib/pgsql/11/data directory and you need to edit it as the root or postgres user.

  The default entry is:

  # TYPE DATABASE USER ADDRESS METHOD   # "local" is for Unix domain socket connections only local all all peer

  # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer

  You should replace it with the following:

  # TYPE DATABASE USER ADDRESS METHOD   # "local" is for Unix domain socket connections only local postgres all peer local videodb student md5

  # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local postgres all peer local videodb student md5

  After you save those changes in the pg_hba.conf file, you need to restart the PostgreSQL (postgresql-11) service. You can do that with the following command as the root user:

  service postgresql-11 restart

  service postgresql-11 restart

Once the postgresql-11 service is restarted, you can connect with the sandboxed student user with this syntax:

psql -d videodb -U student -W

If you did everything correctly, you should see the following after correctly providing the student password for the student user:

psql (11.4, server 11.5)
Type "help" for help.
 
videodb=>

After connecting to the videodb database, you can query the current database, like

SELECT current_database();

It should return the following:

 current_database 
------------------
 videodb
(1 row)