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! This blog post describes the extension pg_permissions, which helps you to get an overview of the permissions in your PostgreSQL database.
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_permissions (https://github.com/cybertec-postgresql/pg_permissions). There are a couple of things which can be achieved with pg_permissions:
- Gain a faster overview and list all permissions
- Compare your “desired state” to what you presently have
- Instantly fix errors
In short: pg_permissions can do more than just listing what there is.
However, let us get started with the simple case – listing all permissions. pg_permissions provides a couple of views, which can be accessed directly once the extension has been deployed. Here is an example:
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:
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 with pg_permissions
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:
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:
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_permissions 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_permissions even better
We want to make pg_permissions 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.
Further reading
For more info about security and permissions in PostgreSQL, read our other blog posts.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.