MacLochlainns Weblog

How time flies, last March I explained how to install and configure PostgreSQL on Windows. It was my intent to start posting more content on PostgreSQL but I was distracted by another writing commitment on Oracle Database 12c PL/SQL Advanced Programming Techniques, which should be available in November. It tempted me away from PostgreSQL because I got to write about how to use Java inside Oracle Database 12c, which was fun. Having completed that, I’m back on task. Here’s the second entry on PostgreSQL. It shows you howto create your own database, database administrator role, user, and how to connect with psql CLI (Command Line Interface) as the new user.

1. Create a user-defined video_db tablespace for your database. This requires that you know where the physical files where created when you installed PostgreSQL. You can discover the directory with the following query:

SELECT   setting AS "Data Location"
FROM     pg_settings
WHERE    name = 'data_directory';

            Data Location
--------------------------------------
 C:/Program Files/PostgreSQL/9.3/data
(1 row)

CREATE TABLESPACE video_db
  OWNER postgres
  LOCATION 'C:\Program Files\PostgreSQL\9.3\data';

SELECT * FROM pg_tablespace;

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

2. Create a database that uses your user-defined video_db tablespace with the following two commands:

CREATE DATABASE videodb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = video_db
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252'
       CONNECTION LIMIT = -1;
 
COMMENT ON DATABASE videodb
  IS 'VideoDB';

3. Create a database role, grant the super user privileges to the role, and create a user with the role. You can do that with the following three commands:

CREATE ROLE dba
  WITH SUPERUSER;
 
GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;
 
CREATE USER video
  WITH ROLE dba
       PASSWORD 'video';

4. Connect to the new videodb database with the psql CLI as the video user. You can do that with the following OS command:

psql -d videodb -U video

5. Once connected as the new video user, you can use a system information function to determine the current database:

SELECT current_database();

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

There are many privilege options, and you should choose wisely which ones you use. As always, I hope this answers questions for other users.