PostgreSQL Privileges & User Management – What You Should Know

User management within PostgreSQL can be tricky. Typically new users are managed, in concert, within a couple of key areas in the environment. Oftentimes, privileges are perfect on one front, yet configured incorrectly on the other. This blog post will provide practical ‘Tips and Tricks’ for a user or role, as we will come to know it, setup within PostgreSQL.

The subject areas we will focus on are:

• PostgreSQL’s Take on Roles

You will learn about roles, role attributes, best practices for naming your roles, and common role setups.

• The pg_hba.conf file

In this section we will look at one of the key files and its settings, for client-side connections and communication with the server.

• Database, Table, and Column level privileges and restrictions.

Looking to configure roles for optimal performance and usage? Do your tables contain sensitive data, only accessible to privileged roles? Yet with the need to allow different roles to perform limited work? These questions and more will be exposed in this section.

PostgreSQL’s Take on Roles – What is a ‘Role’ and how to Create one?

Permissions for database access within PostgreSQL are handled with the concept of a role, which is akin to a user. Roles can represent groups of users in the PostgreSQL ecosystem as well.

PostgreSQL establishes the capacity for roles to assign privileges to database objects they own, enabling access and actions to those objects. Roles have the ability to grant membership to another role. Attributes provide customization options, for permitted client authentication.

Attributes for roles through the CREATE ROLE command, are available in the official PostgreSQL documentation.

Below, are those attributes you will commonly assign when setting up a new role. Most of these are self-explanatory. However, a brief description is provided to clear up any confusion along with example uses.

SUPERUSER – A database SUPERUSER deserves a word of caution. Bottom line, roles with this attribute can create another SUPERUSER. Matter of fact, this attribute is required to create another SUPERUSER role. Since roles with this attribute bypass all permission checks, grant this privilege judiciously.

CREATEDB – Allows the role to create databases.

CREATEROLE – With this attribute, a role can issue the CREATE ROLE command. Hence, create other roles.

LOGIN – Enables the ability to login. A role name with this attribute can be used in the client connection command. More details on this attribute with forthcoming examples.

Certain attributes have an explicit polar opposite named command and typically is the default when left unspecified.

e.g.
SUPERUSER | NOSUPERUSER
CREATEROLE |NOCREATEROLE
LOGIN |NOLOGIN

Let’s look at some of these attributes in action for various configurations you can set up to get going.

Creating And Dropping Roles

Creating a role is relatively straightforward. Here’s a quick example:

postgres=# CREATE ROLE $money_man;
ERROR: syntax error at or near "$"
LINE 1: CREATE ROLE $money_man;

What went wrong there? Turns out, role names cannot start with anything other than a letter.

“What about wrapping the name in double quotes?” Let’s see:

postgres=# CREATE ROLE "$money_man";
CREATE ROLE

That worked, though probably not a good idea. How about a special character in the middle of the name?

postgres=# CREATE ROLE money$_man;
CREATE ROLE

No problem there. Even without double quotes, no error was returned.

I’m just not fond of the name structure of $money_man for a user. I’m dropping you $money_man and starting afresh. The DROP ROLE command takes care of removing a role. Here it is in use.

postgres=# DROP ROLE $money_man;
ERROR: syntax error at or near "$"
LINE 1: DROP ROLE $money_man;

And another error with the $money_man role. Again, resorting to the double quotes it is.

postgres=# DROP ROLE "$money_man";
DROP ROLE

The LOGIN privilege

Let’s look at two different users, one with the LOGIN privilege and one without. I’ll assign them passwords as well.

postgres=# CREATE ROLE nolog_user WITH PASSWORD 'pass1';
CREATE ROLE

postgres=# CREATE ROLE log_user WITH LOGIN PASSWORD 'pass2';
CREATE ROLE

Note: The passwords provided to the above fictional roles are for demonstration purposes only. You should always strive to provide unique and hardened passwords when implementing roles. While a password is better than no password, a hardened password is even better than a trivial one.

Let’s assign log_user the CREATEDB and CREATEROLE attributes with the ALTER ROLE command.

postgres=# ALTER ROLE log_user CREATEROLE CREATEDB;
ALTER ROLE

You can verify these set attributes, by checking the pg_role catalog. Two columns of interest are rolcreaterole and rolcreatedb. Both are of the Boolean data type so they should be set to t for true for these attributes.

Confirm with a similar SELECT query.

postgres=# SELECT rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = 'log_user';
rolcreaterole | rolcreatedb 
---------------+-------------
t | t
(1 row)

How can you determine the existing roles present in the database?

Two available methods are the psql du command or selecting from the pg_roles catalog.

Here they both are in use.

postgres=> du
List of roles
Role name | Attributes | Member of 
------------+------------------------------------------------------------+-----------
log_user | Create role, Create DB | {}
nolog_user | Cannot login | {}

postgres=> SELECT rolname FROM pg_roles;
rolname 
----------------------
nolog_user
log_user
(2 rows)

Logging in

Let’s give both roles, an opportunity to login to the server.

psql -U nolog_user -W postgres
Password for user nolog_user: 
psql: FATAL: no pg_hba.conf entry for host "[local]", user "nolog_user", database "postgres", SSL off
psql -U log_user -W postgres
Password for user log_user: 
psql: FATAL: no pg_hba.conf entry for host "[local]", user "log_user", database "postgres", SSL off

To resolve this issue, we have to dig into that pg_hba.conf file. The solution is discussed as we continue in this post, to that specific section.

Actionable Takeaways

• CREATE ROLE and its counterpart, DROP ROLE, are your go-to commands for implementing and removing roles.
• ALTER ROLE handles changing the attributes of a role.
• Roles are valid within all databases due to definition at the database cluster level.
• Keep in mind, creating a role name beginning with a special character, requires you to ‘address’ it with double quotes.
• Roles and their privileges are established using attributes.
• To establish roles needing the LOGIN attribute by default, CREATE USER is an optional command at your disposal. Used in lieu of CREATE ROLE role_name LOGIN, they are essentially equal.

The pg_hba.conf file – Establishing Common Ground Between the Server and the Client

Covering all aspects and settings for the pg_hba.conf file in one blog post would be daunting at best. Instead, this section will present common pitfalls you may encounter and solutions to remedy them.

Successful connections require a conjunctive effort from both parts as a whole. Roles connecting to the server, must still meet access restrictions set at the database level, after passing the settings in the pg_hba.conf file.

Relevant examples of this relationship are included as this section progresses.

To locate your pg_hba.conf file, issue a similar SELECT query, on the pg_settings VIEW. You must be logged in as a SUPERUSER to query this VIEW.

postgres=# SELECT name, setting
FROM pg_settings WHERE name LIKE '%hba%';
name | setting 
----------+-------------------------------------
hba_file | /etc/postgresql/10/main/pg_hba.conf
(1 row)

The pg_hba.conf file contains records specifying one of seven available formats for a given connection request. See the full spectrum here .

For the purpose of this blog post, we will look at settings you can use for a local environment.

Perhaps this server is for your continued learning and study (as mine is).

I must make special note that these settings are not the optimal settings for a hardened system containing multiple users.

The fields for this type of connection are:

local database user auth-method [auth-options]

Where they mean:

local – connections are attempted with Unix-domain sockets.

database – Specifies the database(s) named for this record match.

user – The database user name matched for this record. A comma-separated list of multiple users or all is allowed for this field as well.

auth-method – Is used when a connection matches this unique record. The possible choices for this field is:

• trust
• reject
• scram-sha-256
• md5
• password
• gss
• sspi
• ident
• peer
• ldap
• radius
• cert
• pam
• bsd

The lines set in pg_hba.conf file for roles nolog_user and log_user look like this:

local all nolog_user password
local all log_user password

Note: Since password is sent in clear text, this should not be used in untrusted environments with untrusted networks.

Let’s look at three interesting columns from the pg_hba_file_rules VIEW with the below query. Again your role needs the SUPERUSER attribute to query this VIEW.

postgres=# SELECT database, user_name, auth_method
postgres-# FROM pg_hba_file_rules
postgres-# WHERE CAST(user_name AS TEXT) LIKE '%log_user%';
database | user_name | auth_method 
----------+--------------+-------------
{all} | {nolog_user} | password
{all} | {log_user} | password
(2 rows)

We can see identical information from the lines provided above found in the pg_hba.conf file as we can from the accompanying query. At first glance, it looks as if both roles can log in.

We will test and confirm.

psql -U nolog_user -W postgres
Password for user nolog_user: 
psql: FATAL: role "nolog_user" is not permitted to log in
psql -U log_user -W postgres
Password for user log_user: 
psql (10.1)
Type "help" for help.
postgres=>

The key point here is, although nolog_user and log_user are both able to login according to the pg_hba.conf file, only log_user is allowed to actually login.

Where log_user passed the database level access restrictions (By having the LOGIN attribute), nolog_user did not.

Let’s edit log_user’s line in the pg_hba.conf file and change the database name this role is allowed to access. Here is the change, indicating log_user can now login to the trial database only.

local trial log_user password

First let’s try to login to the postgres database, which log_user previously had access to due to the all flag.

$ psql -U log_user -W postgres
Password for user log_user: 
psql: FATAL: no pg_hba.conf entry for host "[local]", user "log_user", database "postgres", SSL off

Now with the trial database log_user does have privilege to

$ psql -U log_user -W trial
Password for user log_user: 
psql (10.1)
Type "help" for help.
trial=>

No error there and the trial=> prompt shows the currently connected database.

These settings apply within the server environment as well, once a connection is established.

Let’s attempt a connection to that postgres database again:

trial=> c postgres;
Password for user log_user: 
FATAL: no pg_hba.conf entry for host "[local]", user "log_user", database "postgres", SSL off
Previous connection kept

Through the examples presented here, you should be aware of the customization options for the roles in your cluster.

Note: Oftentimes, reloading the pg_hba.conf file is required for changes to take effect.

Use the pg_ctl utility to reload your server.

The syntax would be:

pg_ctl reload [-D datadir] [-s]

To know where your datadir is, you can query the pg_settings system VIEW, if logged in as a SUPERUSER with a similar SELECT query as below.

postgres=# SELECT setting FROM pg_settings WHERE name = 'data_directory';
           setting           
-----------------------------
 /var/lib/postgresql/10/main
(1 row)

Then, give your shell to the postgres user (or other SUPERUSER) with:

$ sudo -u postgres bash

Unless you have added the pg_ctl utility to your $PATH, you must fully qualify it for use, then pass the command to execute, along with the datadir location.

Here is an example:

$ /usr/lib/postgresql/10/bin/pg_ctl reload -D /var/lib/postgresql/10/main
server signaled

Let’s check the server’s status with:

$ /usr/lib/postgresql/10/bin/pg_ctl status -D /var/lib/postgresql/10/main
pg_ctl: server is running (PID: 1415)
/usr/lib/postgresql/10/bin/postgres "-D" "/var/lib/postgresql/10/main" "-c" "config_file=/etc/postgresql/10/main/postgresql.conf"

Actionable Takeaways

• Roles must pass requirements from both the pg_hba.conf file and database level access privileges.
• pg_hba.conf file is checked from the top down, for each connection request. Order in the file is significant.

Database, Table, and Column Privileges and Restrictions – Tailor fit Roles for Tasks and Responsibilities

In order for roles to use database objects (tables, views, columns, functions, etc…), they must be granted access privileges to them.

The GRANT command defines these essential privileges.

We’ll go over a few examples to get the essence of its use.

Creating databases

Since log_user was granted the CREATEDB and CREATEROLE attributes, we can use this role to create a test database named trial.

postgres=> CREATE DATABASE trial:
CREATE DATABASE

In addition to creating a new ROLE:

postgres=> CREATE ROLE db_user WITH LOGIN PASSWORD 'scooby';
CREATE ROLE

Finally, log_user will connect to the new trial database:

postgres=> c trial;
Password for user log_user: 
You are now connected to database "trial" as user "log_user".
trial=>

Notice the prompt changed to the name ‘trial’ indicating that we are connected to that database.

Let’s utilize log_user to CREATE a mock table.

trial=> CREATE TABLE another_workload(
trial(> id INTEGER,
trial(> first_name VARCHAR(20),
trial(> last_name VARCHAR(20),
trial(> sensitive_info TEXT);
CREATE TABLE

Role log_user recently created a helper role, db_user. We require db_user to have limited privileges for table another_workload.

Undoubtedly, the sensitive_info column should not be accessed by this role. INSERT, UPDATE, and DELETE commands should not be granted at this time either, until db_user meets certain expectations.

However, db_user is required to issue SELECT queries. How can we limit this roles abilities within the another_workload table?

First let’s examine the exact syntax found in the PostgreSQL GRANT command docs, at the table level.

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

Next, we implement the requirements set forth for role db_user, applying specific syntax.

trial=> GRANT SELECT (id, first_name, last_name) ON TABLE another_workload TO db_user;
GRANT

Notice just after the SELECT keyword, we listed the columns that db_user can access. Until changed, should db_user attempt SELECT queries on the sensitive_info column, or any other command for that matter, those queries will not be executed.

With db_user logged in, we’ll put this into practice, attempting a SELECT query to return all columns and records from the table.

trial=> SELECT * FROM another_workload;
ERROR: permission denied for relation another_workload

Column sensitive_info is included in this query. Therefore, no records are returned to db_user.

But db_user can SELECT the allowable columns

trial=> SELECT id, first_name, last_name
trial-> FROM another_workload;
id | first_name | last_name 
-----+------------+-----------
10 | John | Morris
191 | Jannis | Harper
2 | Remmy | Rosebuilt
(3 rows)

That works just fine.

We will test INSERT, UPDATE, and DELETE commands as well.

trial=> INSERT INTO another_workload(id,first_name,last_name,sensitive_info)
VALUES(17,'Jeremy','Stillman','key code:400Z');
ERROR: permission denied for relation another_workload

trial=> UPDATE another_workload
trial-> SET id = 101
trial-> WHERE id = 10;
ERROR: permission denied for relation another_workload

trial=> DELETE FROM another_workload
trial-> WHERE id = 2;;
ERROR: permission denied for relation another_workload

By not assigning INSERT, UPDATE, or DELETE commands to db_user, the role is denied access to using them.

With the plethora of available options, configuring your role is virtually limitless. You can make them fully functional, able to execute any command, or as constrained as your requirements dictate.

Actionable Takeaways

• Roles are provided access privileges to database objects via the GRANT command.
• Database objects and commands against those objects, is highly configurable within the PostgreSQL environment.

Closing

Through this blog post’s provided examples, you should have a better understanding of:

1. Creating a role with specific attributes.
2. Setting a workable connection between the client and server, allowing roles login access to databases.
3. Highly customizing your roles to meet individual requirements for database, table, and column level access by implementing necessary attributes.