Securing PostgreSQL databases in a multi-user environment
Today, Elliot Blackburn and I attempted to tackle the problem of effectively securing our Postgres databases to support many developers working concurrently with varying permissions and accesses. At the start of this work ,we sat down and thought through our core objectives.
- Give all users read access to the tables
- Enable elevation for certain users to have write permissions (select, insert, and update)
- Enable elevation of permissions for a restricted set of users to have admin permissions (delete and drop)
From this, we mapped 3 roles that we wanted for our databases
gradient_read
create role gradient_read with nocreatedb nocreaterole nologin noinheritgradient_write
create role gurn_write with nocreatedb nocreaterole noinherit nologin;gradient_admin
create role gradient_admin with createdb createrole noinherit nologin;As you can see each role has a number of flags set against it. Documentation for the different flags that can be set on role creation can be found here. The main flag of interest to us isinherit / noinherit. The inherit flag determines how a roles are assumed by other roles they have been granted to. For example, ifinherit is set on a role mytestuser, it means that the permissions of any role granted to the role mytestuserare available to it without having to do anything. If noinherit is set on the role mytestuser, it means the permissions are available to the role, but they have to explicitly “set” the role to assume them.
A concrete example of this is as follows
create role mytestuser with noinherit login password 'mysecret';-- define the role
create role my_awesome_role;-- grant permissions to the role
grant insert on my_table to my_awesome_role;-- give the role to user mytestuser
grant my_awesome_role to mytestuser;-- logged in as user mytestuser-- this fails (it would work if mytestuser was set to inherit
-- let's try that next)
insert into my_table (id) values (1);-- assume my_awesome_role
set role my_awesome_role;-- this now works as I am acting as my_awesome_role
insert into my_table (id) values (1);
As you can see with noinherit I have to be explicit about assuming my role. If my role was set to inherit, my first insert would have worked.
The next challenge we faced was how we make our gradient_write and gradient_admin roles act in a way that is consistent with the noinherit flag when someone assumes them. At first, we granted gradient_write directly to mytestuser and they could perform all the operations gradient_write supported without explicitly having to set role gradient_write.
This happened as mytestuser is set to inherit. We know from our earlier example that this means mytestuser will assume all roles granted to them. By setting noinherit on gradient_write we are saying that any roles that are granted to gradient_write are not inherited by gradient_write. This setting controls what happens up the inheritance chain but has no control over what happens down the chain.
Going back to our issue, how do we make it so mytestuser has to actively assume their write and admin permissions but not their read permissions? The solution is to introduce two new roles to sit in between the user and gradient_write/admin. These roles have the inherit flag set to noinherit
elevate_write
create role elevate_write nologin noinherit;elevate_admin
create role elevate_admin nologin noinherit;Rather than explicitly granting gradient_write or gradient_admin to the user, we grant them elevate_write and elevate_admin respectively. We then grant elevate_write and elevate_admin to the user.
grant gradient_write to elevate_write;
grant gradient_write, gradient_admin to elevate_admin;
The outcome of this approach is that mytestuser inherits all the permissions from gradient_read, elevate_write and elevate_admin. The roles elevate_write and elevate_admin have the noinherit flag, meaning they have access to gradient_write and gradient_admin but they do not inherit their permissions. They have to set role to assume that access e.g. set role gradient_write;
Now that we have a strategy for escalating permission sets, lets look at the definitions for each role.
gradient_read
/*
Configure gradient_read
Read-only user that can select from database tables.
Can run all procedures (they will break if they have insert, update or delete permission)
*/-- grant usage on the db
grant usage on schema public to gradient_read; -- allow select on all tables
grant select on all tables in schema public to gradient_read; -- Grant default select on all tables created in the future
alter default privileges in schema public
grant select on tables to gradient_read;
gradient_write
/*
Configure gradient_write
Can read, write, delete from tables
Can run all sequences in db
Can run all procedures
*/-- grant usage on the db
grant usage on schema public to gradient_write;-- allow all operations on all tables
grant select, insert, update, delete on all tables in schema public to gradient_write;-- allow usage of sequences, need for serial keys etc
grant usage, select on all sequences in schema public to gradient_write;-- grant default operations on all tables created in the future
alter default privileges in schema public
grant select, insert, update on tables to gradient_write;-- grant default operations on all sequences created in the future
alter default privileges in schema public
grant usage, select on sequences to gradient_write;
gradient_admin
/*
Configure gradient_admin
Can read, write, delete from tables
Can run all sequences in db
Can run all procedures
Can drop tables
Can drop sequences
Can drop function
*/-- grant usage on the db
grant usage on schema public to gradient_admin; -- allow all operations on all tables
grant select, insert, update, delete on all tables in schema public to gradient_admin;-- allow usage of sequences, need for serial keys etc
grant usage, select on all sequences in schema public to gradient_admin;-- grant default operations on all tables created in the future
alter default privileges in schema public
grant select, insert, update, delete on tables to gradient_admin;-- grant default operations on all tables created in the future
alter default privileges in schema public
grant usage, select on sequences to gradient_admin;
You’ll notice that gradient_write and gradient_admin have identical permissions, the difference between them is in how we use them. We set the ownership of all tables, functions and sequences to gradient_admin. This restricts the ability to perform drop operations against these objects to only users who have this role.
In order to set the owner of tables to a different user at creation, we used an approach outlined by Magnus Hagander https://blog.hagander.net/setting-owner-at-create-table-237/
/*
Trigger to change ownership of table and functions when created.
*/
CREATE OR REPLACE FUNCTION trg_create_set_owner()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
obj record;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag in ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE FUNCTION', 'CREATE TRIGGER', 'CREATE EVENT TRIGGER') LOOP
CASE obj.command_tag
WHEN 'CREATE FUNCTION' THEN
EXECUTE format('ALTER FUNCTION %s OWNER TO gurn_admin', obj.object_identity);
WHEN 'CREATE TRIGGER' THEN
EXECUTE format('ALTER TRIGGER %s OWNER TO gurn_admin', obj.object_identity);
WHEN 'CREATE EVENT TRIGGER' THEN
EXECUTE format('ALTER EVENT TRIGGER %s OWNER TO gurn_admin', obj.object_identity);
WHEN 'CREATE TABLE' THEN
EXECUTE format('ALTER TABLE %s OWNER TO gurn_admin', obj.object_identity);
WHEN 'CREATE TABLE AS' THEN
EXECUTE format('ALTER TABLE %s OWNER TO gurn_admin', obj.object_identity);
END CASE;
END LOOP;
END;
$$;
CREATE EVENT TRIGGER trg_create_set_owner
ON ddl_command_end
WHEN tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'CREATE FUNCTION', 'CREATE TRIGGER')
EXECUTE PROCEDURE trg_create_set_owner();Note: you need to superuser to create an event trigger in Postgres.
This trigger is invoked whenever a create table or create function event occurs and automatically assigns ownership of the object that triggered the event to gradient_admin. In order to run properly, the user creating the table has to have the gradient_admin role. This prevents any users who do not have the role of admin from creating tables. Another added protection for our databases.
With this approach in place, we can look back at our 3 objectives and how we have addressed them.
Give all users read access to the tables
This is done through gradient_read, granted to our devs.
Enable elevation for certain users to have write permissions (select, insert, update, delete)
This is done through granting elevate_write, allowing users to set role gradient_write; and perform the required operations on the database.
Enable elevation of permissions for a restricted set of users to have admin permissions (drop)
This is done by granting elevate_admin, allowing users to set role gradient_admin; and perform the drop or create operations in addition to write ops.
We have now protected our databases in a way that meets our objectives. By deploying our database changes through a Continuous Integration user that has the gurn_admin role we are able to control access permissions of our databases in a tight a secure way.
We spent a short amount of time developing this strategy. It may have holes in and there may be much better ways of approaching the problem. We would love to hear about alternative approaches and we would really appreciate any feedback and suggestions you may have on our approach so far.
N.B.
One modification to this approach could be to make gradient_admin a “lightweight” role and grant elevate_admin the gradient_write role. This would look something like this
/*
Configure gradient_admin
*/-- grant usage on the db
grant usage on schema public to gradient_admin;
Benefits of this approach
You do not duplicate a role definition gradient_write’s permissions being redefined in gradient_admin.
Cons with this approach
You would need toset role gradient_write; to perform insert, update, delete operations and separately set role gradient_admin to create and drop tables.
