Developer's Guide to PostgreSQL on Linux: Creating Accounts and Making Connections

By Adam McQuistan in PostgreSQL  10/05/2019 Comment

Introduction

This is the forth article of a series featuring a collection of useful tasks for administering PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I cover how to create user accounts and establish connections with these accounts.

Series Contents

• Installation
• psql interactive shell
• Creating Databases
• Creating Accounts and Making Connections (you're here)
• Changing the Default Data Directory
• Using Explain and Analyze (coming soon)
• Utilitizing Indexes (coming soon)
• Creating Backups (coming soon)
• Restoring From Backups (coming soon)

Adding User Accounts (aka User Roles)

User accounts in PostgreSQL are synonymous with the PostgreSQL concept of roles. Think of roles as a set of privileges that are granted to perform various actions.

To create a new user, or user role, you can either use a SQL statement or the createuser utility program. I preferred to use the createuser program, in interactive mode, and with the echo flag set which displays the corresponding SQL statement being sent to the PostgreSQL server.

For example, to create a user account named test using the createuser command interactively in echo mode I use the following.

$ whoami
postgres
$ createuser --interactive --echo
Enter name of role to add: test
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE test NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Note that this command is ran from the regular Linux shell, not the psql interactive shell.

To specify a password to allow for a user to later connect via password authentication you should issue the createuser command with the --pwprompt flag as shown below to create a user role named test2.

$ createuser --pwprompt --interactive --echo
Enter name of role to add: test2
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE test2 PASSWORD 'md528d910d391470ad5ae52a54e9f1a1840' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

For completeness I also show how to alter the test user role by adding a password in SQL.

$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# ALTER ROLE test WITH PASSWORD 'testuser';
ALTER ROLE

As I described my earlier article on using the psql interactive shell you can list all available roles by entering the psql shell and issuing the \du command as follows.

$ psql
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}
 test2     |                                                            | {}

postgres=# 

You can see that the output from \du shows no privileges have been granted to the test and test2 user roles which matches what I interactively entered in the createuser commands.

Peer Authentication Based Connections

If I try to connect to the database cluster with the psql program specifying the test user role it will fail because by default PostgreSQL uses Peer Authentication. Peer Authentication requires connecting from a Linux system account which has a name that matches the PostgreSQL user role and database name. Thus, if I try to connect using the user role named test specifying the -U flag I get the following peer authentication error because their does not yet exist a Linux system account named test.

$ psql -U test
psql: FATAL:  Peer authentication failed for user "test"

To get Peer Authentication to work I create a Linux system account named test which of course needs to be done with either the root Linux system account or another with sudo privileges.

Adding Linux System User on Ubuntu

Adding a Linux system user and specifying its password is accomplished in one interactive adduser command on Ubuntu

# adduser test
Adding user `test' ...
Adding new group `test' (1000) ...
Adding new user `test' (1000) with group `test' ...
Creating home directory `/home/test' ...
Copying files from `/etc/skel' ...
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
Changing the user information for test
Enter the new value, or press ENTER for the default
	Full Name []: 
	Room Number []: 
	Work Phone []: 
	Home Phone []: 
	Other []: 
Is the information correct? [Y/n] Y

Adding Linux System User on CentOS

To add a user on CentOS again requires the adduser command but also needs to be followed with the passwd command to specify its password like so.

# adduser test

Now I can switch to the new Linux user account named test and connect using psql as follows.

# su - test
$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

test=> 

Recall from the last article on creating PostgreSQL databases I had already created a database named test as well as one named test2. If I had not already created the test datadase I would get the following error.

$ psql
psql: FATAL:  database "test" does not exist

If you are just joining in the tutorial series you will need to create a database named test by issuing the following command as the postgres Linux account user.

$ createdb test

Peer Authentication When User Roles Differ from Database Names

Often times it is desirable to connect to a database whose name is different from that of the user role that you are connecting from. Lets say I want to be able to connect to the test2 database I created in the last article as the user role named test. I can make this possible by editing the pg_hba.conf file specifying that the user test can make local peer based connections to the test2 database as shown below.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   test            test2                                   peer

If you are unsure of where the pg_hba.conf file is on either the Ubuntu or CentOS operating systems consult my earlier article on PostgreSQL Installation and Configuration files for help.

In order for the changes in the pg_hba.conf config file to take effect the PostgreSQL service must be reloaded (the below command is for the Ubuntu system, to reload on CentOS use postgresql-10 instead of postgresql in this example).

# systemctl reload postgresql

I can now connect to the test2 database using the test user role by specifying the test2 database using the -d flag as follows.

$ whoami
test
test@ubuntu-s-1vcpu-1gb-nyc3-01:~$ psql -d test2
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

test2=> 

Password Based Connections

Another common way to allow access to a PostgreSQL database is via password authentication. To do this you must have a user role that has been created with a password or altered to contain a password. Earlier, I created a role named test2 which I will use to demonstrate this. Its important to understand that by password authentication I am referring to the PostgreSQL role password not the Linux system user account password.

There are two types of password authentication in PostgreSQL: (i) clear, unencrypted, text passwords which should only be used for local connections and (ii) md5 hashed passwords which are preferred especially when connecting from an external client program.

To enable hashed password authentication you specify md5 in the authentication METHOD column of pg_hba.conf as shown below.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   test            test                                    peer
local   test2           test2                                   md5

Then after reloading the PostgreSQL service I can connect using the psql client specifying the test2 user and entering the password (note, I can type in the regular plain text password and the psql client will take care of the hashing for me).

# systemctl reload postgresql
# psql -U test2 -d test2
Password for user test2: 
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

test2=> 

Mapping Multiple Users for Peer Authentication to a Database

Sometimes you will want to enable multiple Linux user accounts to connect to a specific database as one common PostgreSQL user role. To accomplish this you will want to create a set of map entries in the pg_ident.conf configuration file that associate the Linux user accounts to one common PostgreSQL user role then specify the name of this map set in the pg_hba.conf file for the main PostgreSQL role and database.

For example, in the creating databases article I created a database named movies. Now I will create a role named movies which will serve as a common user role for a the users joe and jane.

Creating movies role.

$ whoami
postgres
$ createuser --interactive --echo 
Enter name of role to add: movies
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
SELECT pg_catalog.set_config('search_path', '', false)
CREATE ROLE movies NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Creating joe and jane Linux accounts.

# whoami
root
# adduser joe
# adduser jane

Now over in pg_ident.conf (again check installation article for the location of the PostgreSQL config files for Ubuntu and CentOS) I can create a map set named movieusers mapping joe and jane to the movies user.

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
movieusers      joe                     movies
movieusers      jane                    movies

Then I place an entry in pg_hba.conf to associate the movieusers map set to the movies role and database like so.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   movies          movies                                 peer map=movieusers

Now if I reload the postgresql service, change users to joe (or jane) and connect to PostgreSQL via psql specifying the movies role as follows.

# whoami
root
# systemctl reload postgresql
# su - joe
$ psql -U movies
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

movies=> \q

Resources for Learning More About PostgreSQL

• A great, example driven, reference book on robust management of PostgreSQL is PostgreSQL High Availability Cookbook - Second Edition
• A good companion book that is again example driven but focusing on performance is PostgreSQL High Performance Cookbook

thecodinginterface.com earns commision from sales of linked products such as the books above. This enables providing continued free tutorials and content so, thank you for supporting the authors of these resources as well as thecodinginterface.com

Conclusion

In this article I've covered how to create and configure PostgreSQL user roles which can then be used to connect to databases in the PostgreSQL cluster.