PG_PERMISSION: INSPECTING YOUR POSTGRESQL SECURITY SYSTEM

Security is an important topic. This is not only true in the PostgreSQL world – it holds truth for pretty much any modern IT system. Databases, however, have special security requirements. More often than not confidential data is stored and therefore it makes sense to ensure that data is protected properly. Security first!

PostgreSQL: Listing all permissions

Gaining an overview of all permissions granted to users in PostgreSQL can be quite difficult. However, if you want to secure your system, gaining an overview is really everything – it can be quite easy to forget a permission here and there and fixing things can be a painful task. To make life easier, Cybertec has implemented pg_permission (https://github.com/cybertec-postgresql/pg_permission). There are a couple of things, which can be achieved with pg_permission:

• Gain a faster overview and list all permissions
• Compare your “desired state” to what you got
• Instantly fix errors

In short: pg_permission can do more than just listing what there is. However, let us get started with the simple case – listing all permissions. pg_permission provides a couple of views, which can be accessed directly once the extension has been deployed. Here is an example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

test=# \x Expanded display is on. test=# SELECT *        FROM     all_permissions        WHERE    role_name = 'workspace_owner'; -[ RECORD 1 ]------------------------------------------------------- object_type | TABLE role_name   | workspace_owner schema_name | public object_name | b column_name | permission  | SELECT granted     | t -[ RECORD 2 ]------------------------------------------------------- object_type | TABLE role_name   | workspace_owner schema_name | public object_name | b column_name | permission  | INSERT granted     | t -[ RECORD 3 ]------------------------------------------------------- object_type | TABLE role_name   | workspace_owner schema_name | public object_name | b column_name | permission  | UPDATE granted     | f

The easiest way is to use the “all_permissions” view to gain an overview of EVERYTHING. However, if you are only interested in function, tables, columns, schemas and so on there are more views, which you can use. “all_permissions” will simply show you all there is:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE VIEW all_permissions AS SELECT * FROM table_permissions UNION ALL SELECT * FROM view_permissions UNION ALL SELECT * FROM column_permissions UNION ALL SELECT * FROM sequence_permissions UNION ALL SELECT * FROM function_permissions UNION ALL SELECT * FROM schema_permissions UNION ALL SELECT * FROM database_permissions;

 

PostgreSQL: Detecting security issues

Securing your application is not too hard when your application is small – however, if your data model is changing small errors and deficiencies might sneak in, which can cause severe security problems in the long run. pg_permissions has a solution to that problem: You can declare, how the world is supposed to be. What does that mean? Here is an example: “All bookkeepers should be allowed to read data in the bookkeeping schema.” or “Everybody should have USAGE permissions on all schemas”. What you can do now is to compare the world as it is with the way you want it to be. Here is how it works:

1 2 3 4

INSERT INTO public.permission_target    (id, role_name, permissions, object_type, schema_name) VALUES    (3, 'appuser', '{USAGE}', 'SCHEMA', 'appschema');

The user also needs USAGE privileges on the appseq sequence in that schema:

1 2 3 4 5 6 7 8 9 10 11 12

INSERT INTO public.permission_target    (id, role_name, permissions,     object_type, schema_name, object_name) VALUES    (4, 'appuser', '{USAGE}', 'SEQUENCE', 'appschema', 'appseq');   SELECT * FROM public.permission_diffs();  missing | role_name | object_type | schema_name | object_name | column_name | permission ---------+-----------+-------------+-------------+-------------+-------------+------------        f |      hans |        VIEW |   appschema |     appview |             |     SELECT        t |   appuser |       TABLE |   appschema |    apptable |             |     DELETE (2 rows)

You will instantly get an overview and see, which differences between your desired state and your current state exist. By checking the differences directly during your deployment process, our extension will allow you to react and fix problems quickly.

Changing permissions as fast as possible

Once you have figured out, which permissions there are, which ones might be missing or which ones are wrong, you might want to fix things. Basically, there are two choices: You can fix stuff by hand and assign permissions one by one. That can be quite a pain and result in a lot of work. So why not just update your “all_permissions” view directly? pg_permissions allows you to do exactly that … You can simply update your views and pg_permission will execute the desired changes for you (fire GRANT and REVOKE statements behind the scene). This way you can change hundreds or even thousands of permission using a simple UPDATE statement. Securing your database has never been easier.

Many people are struggling with GRANT and REVOKE statements. Therefore, being able to use UPDATE might make life easier for many PostgreSQL users out there.

Making pg_permission even better

We want to make pg_permission even better: if there are any cool ideas out there, don’t hesitate to contact us anytime. We are eagerly looking for new ideas and even better concepts.