Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Wednesday, 04 September 2019 06:21

Postgres 11 Video DB

Written by  http://blog.mclaughlinsoftware.com/2019/09/05/postgres-11-video-db/
Rate this item
(0 votes)

Installing PostgreSQL 11 on Fedora, Version 30, requires an update to my previous instructions to create a sandboxed user. A sandboxed user can only access a non-data dictionary database with a

password. In the real world, rather than a personal test instance you would configure users to include aspects of networking. However, this post is only showing you how to connect from the local server.

This post builds on my PostgreSQL Installation blog post and shows you how to create a tablespace, database, role, and user. It also shows you how to change the default configuration for how users connect to the database.

The following steps create a tablespace, database, role, and user:

  1. Create tablespace

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

postgres=# show data_directory;

postgres=# show data_directory;

This will return the following:

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

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

mkdir videoDB

  1. Create Tablespace

You can create a video_db tablespace with the following syntax:

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

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

This will return the following:

CREATE TABLESPACE

CREATE TABLESPACE

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

SELECT * FROM pg_tablespace;

SELECT * FROM pg_tablespace;

It should return the following:

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

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

  1. Create a Database

You can create a videodb database with the following syntax:

CREATEDATABASE videodb WITHOWNER= postgres ENCODING='UTF8'TABLESPACE= video_db LC_COLLATE='en_US.UTF-8'LC_CTYPE='en_US.UTF-8'CONNECTIONLIMIT= -1;

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

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)

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:

COMMENTONDATABASE videodb IS'Video Database';

COMMENT ON DATABASE videodb IS 'Video Database';

  1. Create a Role, Grant, and User

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:

    CREATEROLE dba WITHSUPERUSER;

    CREATE ROLE dba WITH SUPERUSER;

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

    GRANTALLPRIVILEGESONDATABASE videodb TO dba;

    GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;

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

    CREATEUSER student WITHROLE dba ENCRYPTEDPASSWORD'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 postgres -U student -W

    psql -d videodb -U postgres -U student -W

    You may encounter this error:

    psql: FATAL: Peer authentication failed foruser"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 onlylocal 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 onlylocal postgres all peerlocal 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

  1. Connect to the videodb as the student user

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

psql -d videodb -U postgres -U student -W

psql -d videodb -U postgres -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, server11.5)Type"help"for help. videodb=>

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();

SELECT current_database();

It should return the following:

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

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

This has shown you how to create a videodb database, dba role, and student user.

Read 35 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.