What’s new in PostgreSQL 10? Part 5 – Authentication and Row Level Security

Introduction

Historically, PostgreSQL used a single encryption method. It’s not-so-safe md5 algorithm. In 10 version provided support for new authentication method SCRAM-SHA-256 for password negotiation and storage.

Also in 9.6 or earlier version of PostgreSQL when multiple policies were set for a table, policies were determined by OR condition. Now can using the AND condition.

Will try to check this in PostgreSQL 10 (Beta 3 release) server.

SCRAM-SHA-256 authentication

This is an implementation of SCRAM-SHA-256 as specified in RFC 5802 and 7677. First of all, the type of parameter “password_encryption” in postgresql.conf has changed. Previously, the parameter was of type “boolean”. Now it has the type “enum”.

postgres=# SELECT * FROM pg_settings WHERE name = 'password_encryption';
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
name | password_encryption
setting | md5
unit |
category | Connections and Authentication / Security and Authentication
short_desc | Encrypt passwords.
extra_desc | When a password is specified in CREATE USER or ALTER USER without writing either ENCRYPTED or UNENCRYPTED, this parameter determines whether the password is to be encrypted.
context | user
vartype | enum
source | default
min_val |
max_val |
enumvals | {md5,scram-sha-256}
boot_val | md5
reset_val | md5
sourcefile |
sourceline |
pending_restart | f

Also, a new method “scram-sha-256” was added to the pg_hba.conf file. For example:

host    all    all     10.0.0.0/8      scram-sha-256

Enable scram passwords in postgresql.conf:

password_encryption = scram-sha-256

Create a new user:

postgres=# CREATE USER my_user WITH PASSWORD 'qwerty';
CREATE ROLE

And let’s check what is stored in the pg_shadow table:

postgres=# SELECT * FROM pg_shadow WHERE usename = 'my_user';
-[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------------------
usename | my_user
usesysid | 16749
usecreatedb | f
usesuper | f
userepl | f
usebypassrls | f
passwd | SCRAM-SHA-256$4096:0BQGCLi0kjSubLqX$CksjSyf4t+HLAelY7eavaIn+/7hi+nSZHSsG9A22lUg=:xGnrGXyIrgwIqZmKHVT7UBvvM0NOKlyZIVB4Qb795YE=
valuntil |
useconfig |

Now delete the user and create it with the same password:

postgres=# DROP ROLE my_user;
DROP ROLE

postgres=# CREATE USER my_user WITH PASSWORD 'qwerty';
CREATE ROLE

Let’s see if the password hash changed in pg_shadow:

postgres=# SELECT * FROM pg_shadow WHERE usename = 'my_user';
-[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------------------
usename | my_user
usesysid | 16750
usecreatedb | f
usesuper | f
userepl | f
usebypassrls | f
passwd | SCRAM-SHA-256$4096:vMeotjmcmsUq3+NH$WrO5M8rOFT5A6b7YIMOzrChfRu4lnTqSXqFfUBrZBk4=:SZwYMno7SALBi9lVuhCSTvM2BgWFObxjs0brRECYoaE=
valuntil |
useconfig |

Great! The password hash is different.

Row Level Security

CREATE POLICY statement:

CREATE POLICY policy_name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]

The AS PERMISSIVE / RESTRICTIVE clause can now be specified in the CREATE POLICY statement. When specifying the AS PERMISSIVE clause, the restriction becomes loose (OR), and if AS RESTRICTIVE is specified, the limit becomes strict (AND).

Create a test table:

postgres=# CREATE TABLE test_policy(id integer, user_name text, check_policy text);
CREATE TABLE

Create a RESTRICTIVE policy:

postgres=# CREATE POLICY select_policy ON test_policy AS RESTRICTIVE FOR SELECT USING (check_policy = 'read_only');
CREATE POLICY

Create a PERMISSIVE policy:

postgres=# CREATE POLICY user_policy ON test_policy FOR SELECT USING (user_name = 'my_user');
CREATE POLICY

Enable row level security and insert two rows:

postgres=# ALTER TABLE test_policy ENABLE ROW LEVEL SECURITY;
ALTER TABLE
INSERT INTO public.test_policy VALUES (1,'my_user', 'read_only');

Connecting to the user my_user:

#psql -U my_user -d postgres

And execute EXPLAIN:

postgres=> EXPLAIN SELECT * FROM test_policy;
 QUERY PLAN
----------------------------------------------------------------------------------
 Seq Scan on test_policy (cost=0.00..22.75 rows=1 width=68)
 Filter: ((check_policy = 'read_only'::text) AND (user_name = 'my_user'::text))
(2 rows)

postgres=> SELECT * FROM test_policy ;
 id | user_name | check_policy
----+-----------+--------------
 1 | my_user | read_only
(1 row)

Both security policies are involved in data filtering.

Summary

In version 10, a new authentication method “SCRAM-SHA-256” appeared, which is more secure. There is also an opportunity to combine security policies with PERMISSIVE / RESTRICTIVE clause at the row level.