blog
Self-Provisioning of User Accounts in PostgreSQL via Unprivileged Anonymous Access
Note from Severalnines: This blog is being published posthumously as Berend Tober passed away on July 16, 2018. We honor his contributions to the PostgreSQL community and wish peace for our friend and guest writer.
In previous article we introduced the basics of PostgreSQL triggers and stored functions and provided six example use cases including data validation, change logging, deriving values from inserted data, data hiding with simple updatable views, maintaining summary data in separate tables, and safe invocation of code at elevated privilege. This article builds further on that foundation and presents a technique utilizing a trigger and stored function to facilitate delegating login credential provisioning to limited-privilege (i.e., non-superuser) roles. This feature might be used to reduce administrative workload for high-value systems-administration personnel. Taken to the extreme, we demonstrate anonymous end-user self-provisioning of login credentials, i.e., letting prospective database users provision login credentials on their own by implementing “dynamic SQL” inside a stored function executed at appropriately-scoped privilege level.Introduction
Helpful Background Reading
The recent article by Sebastian Insausti on How to Secure your PostgreSQL Database includes some highly relevant tips you should be familiar with, namely, Tips #1 – #5 on Client Authentication Control, Server Configuration, User and Role Management, Super User Management, and Data Encryption. We’ll use parts of each tip in this article.
Another recent article by Joshua Otwell on PostgreSQL Privileges & User Management also has a good treatment of host configuration and user privileges that goes into a little more detail on those two topics.
Protecting Network Traffic
The proposed feature involves allowing users to provision database login credentials and while doing so, they will specify their new login name and password over the network. Protection of this network communication is essential and can be achieved by configuring the PostgreSQL server to support and require encrypted connections. Transport layer security is enabled in the postgresql.conf file by the “ssl” setting:
ssl = onHost-Based Access Control
For the present case, we will add a host-based access configuration line in the pg_hba.conf file that allows anonymous, i.e., trusted, login to the database from some appropriate sub-network for the population of prospective database users literally using the username “anonymous”, and a second configuration line requiring password login for any other login name. Remember that host configurations invoke the first match, so the first line will apply whenever the “anonymous” username is specified, permitting trusted (i.e., no password required) connection, and then subsequently whenever any other username is specified a password will be required. For example, if the sample database “sampledb” is to be used, say, by employees only and internally to corporate facilities, then we may configure trusted access for some non-routable internal subnet with:
# TYPE DATABASE USER ADDRESS METHOD
hostssl sampledb anonymous 192.168.1.0/24 trust
hostssl sampledb all 192.168.1.0/24 md5If the database is to be made available generally to the public, then we may configure “any address” access:
# TYPE DATABASE USER ADDRESS METHOD
hostssl sampledb anonymous all trust
hostssl sampledb all all md5Note the above is potentially dangerous without additional precautions, possibly in the application design or at a firewall device, to rate-limit use of this feature, because you know some script kiddie will automate endless account creation just for the lulz.
Note also we have specified the connection type as “hostssl” which means connections made using TCP/IP succeed only when the connection is made with SSL encryption so as to protect the network traffic from eavesdropping.
Locking Down the Public Schema
Since we are allowing possibly unknown (i.e., untrusted) persons to access the database, we will want to be sure that default accesses are capability limited. One important measure is to revoke the default public schema object creation privilege so as to mitigate a recently-published PostgreSQL vulnerability related to default schema privileges (cf. Locking Down the Public Schema by yours truly).
A Sample Database
We’ll start with an empty sample database for illustration purposes:
create database sampledb;
connect sampledb
revoke create on schema public from public;
alter default privileges revoke all privileges on tables from public;We also create the anonymous login role corresponding to the earlier pg_hba.conf setting.
create role anonymous login
nosuperuser
noinherit
nocreatedb
nocreaterole
Noreplication;And then we do something novel by defining an unconventional view:
create or replace view person as
select
null::name as login_name,
null::name as login_pass;This view references no table and so a select query always returns an empty row:
select * from person;
login_name | login_pass
------------+-------------
|
(1 row)One thing this does for us is in a sense to provide documentation or a hint to end users as to what data is required to establish an account. That is, by querying the table, even though the result is an empty row, the result reveals the names of the two data elements.
But even better, the existence of this view allows determination of the datatypes required:
d person
View "public.person"
Column | Type | Modifiers
--------------+------+-----------
login_name | name |
login_pass | name | We will be implementing the credential provisioning functionality with a stored function and trigger, so let’s declare an empty function template and the associated trigger:
create or replace function person_iit()
returns trigger
set schema 'public'
language plpgsql
security definer
as '
begin
end;
';
create trigger person_iit
instead of insert
on person
for each row execute procedure person_iit();Note that we are following the proposed naming convention from the previous article, using the associated table name suffixed with a short-hand abbreviation denoting attributes of the trigger relationship between the table and the stored function for an INSTEAD OF INSERT trigger (i.e., suffix “iit”). We have also added to the stored function the SCHEMA and SECURITY DEFINER attributes: the former because it is good practice to set the search path that applies for duration of function execution, and the latter to facilitate role creation, which is normally a database superuser authority only but in this case will be delegated to anonymous users.
And lastly we add minimally-sufficient permissions on the view to query and insert:
grant select, insert on table person to anonymous;Let’s Review
Before implementing the stored function code, let’s review what we have. First there’s the sample database owned by the postgres user:
l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
sampledb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
And there’s the user roles, including the database superuser and the newly-created anonymous login roles:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
anonymous | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}And there’s the view we created and a listing of create and read access privileges granted to the anonymous user by the postgres user:
d
List of relations
Schema | Name | Type | Owner
--------+--------+------+----------
public | person | view | postgres
(1 row)
dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------+------+---------------------------+-------------------+----------
public | person | view | postgres=arwdDxt/postgres+| |
| | | anonymous=ar/postgres | |
(1 row)Lastly, the table detail shows the column names and datatypes as well as the associated trigger:
d person
View "public.person"
Column | Type | Modifiers
--------------+------+-----------
login_name | name |
login_pass | name |
Triggers:
person_iit INSTEAD OF INSERT ON person FOR EACH ROW EXECUTE PROCEDURE person_iit()Dynamic SQL
We are going to employ dynamic SQL, i.e., constructing the final form of a DDL statement at run-time partially from user-entered data, to fill in the trigger function body. Specifically we hard code the outline of the statement to create a new login role and fill in the specific parameters as variables.
The general form of this command is
create role name [ [ with ] option [ ... ] ]where option can be any of sixteen specific properties. Generally the defaults are appropriate but we’re going to be explicit about several limiting options and use the form
create role name
with
login
inherit
nosuperuser
nocreatedb
nocreaterole
password ‘password’;where we will insert the user-specified the role name and password at run time.
Dynamically constructed statements are invoked with the execute command:
execute command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];which for our specific needs would look like
execute 'create role '
|| new.login_name
|| ' with login inherit nosuperuser nocreatedb nocreaterole password '
|| quote_literal(new.login_pass);where the quote_literal function returns the string argument suitably quoted for use as a string literal so as to comply with the syntactical requirement that the password in fact be quoted..
Once we have the command string built, we supply it as the argument to the pl/pgsql execute command within the trigger function.
Putting this all together looks like:
create or replace function person_iit()
returns trigger
set schema 'public'
language plpgsql
security definer
as $$
begin
-- note this is for demonstration only. it is vulnerable to sql injection.
execute 'create role '
|| new.login_name
|| ' with login inherit nosuperuser nocreatedb nocreaterole password '
|| quote_literal(new.login_pass);
return new;
end;
$$;Let’s Try It!
Everything is in place, so let’s give it whirl! First we switch session authorization to the anonymous user and then do an insert against the person view:
set session authorization anonymous;
insert into person values ('alice', '1234');The result is that new user alice has been added to the system table:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alice | | {}
anonymous | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}It even works directly from the operating system command line by piping a SQL command string to the psql client utility to add user bob:
$ psql sampledb anonymous <<< "insert into person values ('bob', '4321');"
INSERT 0 1
$ psql sampledb anonymous <<< "du"
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alice | | {}
anonymous | No inheritance | {}
bob | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}Apply Some Armor
The initial example of the trigger function is vulnerable to SQL injection attack, i.e. a malicious threat actor could craft input that results in unauthorized access. For example, while connected as the anonymous user role, an attempt to do something out of scope fails appropriately:
set session authorization anonymous;
drop user alice;
ERROR: permission denied to drop roleBut the following malicious input creates a superuser role named ‘eve’ (as well as a decoy account named ‘cathy’):
insert into person
values ('eve with superuser login password ''666''; create role cathy', '777');
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alice | | {}
anonymous | No inheritance | {}
cathy | | {}
eve | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}Then the surreptitious superuser role can be used to wreak havoc in the database, for example deleting user accounts (or worse!):
c - eve
drop user alice;
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
anonymous | No inheritance | {}
cathy | | {}
eve | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}To mitigate this vulnerability, we must take steps to sanitize the input. For example, applying the quote_ident function, which returns a string suitably quoted for use as an identifier in an SQL statement with quotes added when necessary, such as if the string contains non-identifier characters or would be case-folded, and properly doubling embedded quotes:
create or replace function person_iit()
returns trigger
set schema 'public'
language plpgsql
security definer
as $$
begin
execute 'create role '
|| quote_ident(new.login_name)
|| ' with login inherit nosuperuser nocreatedb nocreaterole password '
|| quote_literal(new.login_pass);
return new;
end;
$$;Now if the same SQL injection exploit is attempted to create another superuser named ‘frank’, it fails, and the result is a very unorthodox username:
set session authorization anonymous;
insert into person
values ('frank with superuser login password ''666''; create role dave', '777');
du
List of roles
Role name | Attributes | Member of
-----------------------+------------------------------------------------------------+----------
anonymous | No inheritance | {}
eve | Superuser | {}
frank with superuser | |
login password '666';| |
create role dave | |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}We can apply further sensible data validation within the trigger function such as requiring only alpha-numeric usernames and rejecting white space and other characters:
create or replace function person_iit()
returns trigger
set schema 'public'
language plpgsql
security definer
as $$
begin
-- Basic input sanitization
if new.login_name is null then
raise exception 'null login_name disallowed';
elsif position(' ' in new.login_name) > 0 then
raise exception 'login_name whitespace disallowed';
elsif length(new.login_name) = 0 then
raise exception 'login_name must be non-empty';
elsif not (select new.login_name similar to '[A-Za-z]%') then
raise exception 'login_name must begin with a letter.';
end if;
if new.login_pass is null then
raise exception 'null login_pass disallowed';
elsif position(' ' in new.login_pass) > 0 then
raise exception 'login_pass whitespace disallowed';
elsif length(new.login_pass) = 0 then
raise exception 'login_pass must be non-empty';
end if;
-- Provision login credentials
execute 'create role '
|| quote_ident(new.login_name)
|| ' with login inherit nosuperuser nocreatedb nocreaterole password '
|| quote_literal(new.login_pass);
return new;
end;
$$;and then confirm that the various sanitization checks work:
set session authorization anonymous;
insert into person values (NULL, NULL);
ERROR: null login_name disallowed
insert into person values ('gina', NULL);
ERROR: null login_pass disallowed
insert into person values ('gina', '');
ERROR: login_pass must be non-empty
insert into person values ('', '1234');
ERROR: login_name must be non-empty
insert into person values ('gi na', '1234');
ERROR: login_name whitespace disallowed
insert into person values ('1gina', '1234');
ERROR: login_name must begin with a letter.Let’s Step it Up a Notch
Suppose we want to store additional metadata or application data related to the created user role, e.g., maybe a time stamp and source IP address associated with role creation. The view of course cannot satisfy this new requirement since there is no underlying storage, so an actual table is required. Also, let’s further assume we want to restrict visibility of that table from users logging in with the anonymous login role. We can hide the table in a separate namespace (i.e., a PostgreSQL schema) which remains inaccessible to anonymous users. Let’s call this namespace the “private” namespace and create the table in the namespace:
create schema private;
create table private.person (
login_name name not null primary key,
inet_client_addr inet default inet_client_addr(),
create_time timestamptz default now()
);A simple additional insert command inside the trigger function records this associated metadata:
create or replace function person_iit()
returns trigger
set schema 'public'
language plpgsql
security definer
as $$
begin
-- Basic input sanitization
if new.login_name is null then
raise exception 'null login_name disallowed';
elsif position(' ' in new.login_name) > 0 then
raise exception 'login_name whitespace disallowed';
elsif length(new.login_name) = 0 then
raise exception 'login_name must be non-empty';
elsif not (select new.login_name similar to '[A-Za-z]%') then
raise exception 'login_name must begin with a letter.';
end if;
if new.login_pass is null then
raise exception 'null login_pass disallowed';
elsif length(new.login_pass) = 0 then
raise exception 'login_pass must be non-empty';
end if;
-- Record associated metadata
insert into private.person values (new.login_name);
-- Provision login credentials
execute 'create role '
|| quote_ident(new.login_name)
|| ' with login inherit nosuperuser nocreatedb nocreaterole password '
|| quote_literal(new.login_pass);
return new;
end;
$$;And we can give it an easy test. First we confirm that while connected as the anonymous role only the public.person view is visible and not the private.person table:
set session authorization anonymous;
d
List of relations
Schema | Name | Type | Owner
--------+--------+------+----------
public | person | view | postgres
(1 row)
select * from private.person;
ERROR: permission denied for schema privateAnd then after a new role insert:
insert into person values ('gina', '1234');
reset session authorization;
select * from private.person;
login_name | inet_client_addr | create_time
------------+------------------+-------------------------------
gina | 192.168.2.106 | 2018-06-24 07:56:13.838679-07
(1 row)the private.person table shows the metadata capture for IP address and the row insert time.
Conclusion
In this article, we have demonstrated a technique to delegate PostgreSQL role credential provisioning to non-superuser roles. While the example fully delegated the credentialing functionality to anonymous users, a similar approach could be used to partially delegate the functionality to only trusted personnel while still retaining the benefit of off-loading this work from high-value database or systems administrator personnel. We also demonstrated a technique of layered data access utilizing PostgreSQL schemas, selectively exposing or hiding database objects. In the next article in this series we will expand on the layered data access technique to propose a novel database architecture design for application implementations.
