Contents

PostgreSQL permission reports and checks
========================================

This extension allows you to review object permissions on a PostgreSQL database.

Cookbook
--------

First, you have to install the extension in the database:

    CREATE EXTENSION pg_permissions SCHEMA public;

Then you need to add entries to `permission_target` that correspond to your
desired permissions.

Let's assume we have a schema `appschema`, and `appuser` should have
`SELECT`, `UPDATE`, `DELETE` and `INSERT` permissions on all tables and
views in that schema:

    INSERT INTO public.permission_target
       (id, role_name, permissions,
        object_type, schema_name)
    VALUES
       (1, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}',
        'TABLE', 'appschema');
    INSERT INTO public.permission_target
       (id, role_name, permissions,
        object_type, schema_name)
    VALUES
       (2, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}',
        'VIEW', 'appschema');

Of course, the user will need the `USAGE` privilege on the schema:

    INSERT INTO public.permission_target
       (id, role_name, permissions,i
        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');

Now we can review which permissions are missing and which additional
permissions are granted:

    SELECT * FROM public.permission_diffs();

     missing | role_name | object_type | schema_name | object_name | column_name | permission
    ---------+-----------+-------------+-------------+-------------+-------------+------------
     f       | laurenz   | VIEW        | appschema   | appview     |             | SELECT
     t       | appuser   | TABLE       | appschema   | apptable    |             | DELETE
    (2 rows)

That means that `appuser` is missing the `DELETE` privilege on
`appschema.apptable` which should be granted, while user `laurenz`
has the additional `SELECT` privilege on `appschema.appview` (`missing`
is `FALSE`).

To review the actual permissions on an object, we can use the `*_permissions`
views:

    SELECT * FROM schema_permissions
       WHERE role_name = 'appuser' AND schema_name = 'appschema';

     object_type | role_name | schema_name | object_name | column_name | permissions | granted
    -------------+-----------+-------------+-------------+-------------+-------------+---------
     SCHEMA      | appuser   | appschema   |             |             | USAGE       | t
     SCHEMA      | appuser   | appschema   |             |             | CREATE      | f
    (2 rows)

Usage
-----

### Views ###

The extension provides a number of views:

- `database_permissions`: permissions granted on the current database

- `schema_permissions`: permissions granted on schemas

- `table_permissions`: permissions granted on tables

- `view_permissions`: permissions granted on views

- `column_permissions`: permissions granted on table and view columns

- `function_permissions`: permissions granted on functions

- `sequence_permissions`: permissions granted on sequences

- `all_permissions`: permissions on all objects (`UNION` of the above)

All views have the same columns; a column is NULL if it has no meaning
for the current view.

These views can be used to examine the currently granted permissions on
database objects.

**Note:** Superusers are not show in the view, as they automatically have
all permissions.

### Tables ###

The extension provides a table `permission_target` with which you can describe
the permissions that *should* be granted on database objects.

If you set a relevant column in `permission_target` to NULL (e.g., the
`object_name` and `column_name` columns in a `TABLE` entry), the meaning is
that the entry refers to *all* possible objects (in the example above, all
tables in the schema).

### Functions ###

The table function `permission_diffs()` checks the desired permissions in
`permission_target` against the actually granted permissions in the views
of the extension and returns a table of differences.

If the first column `missing` is `TRUE`, the result is a permission that should
be there but isn't; if `missing` is `FALSE`, the result row is a permission that
is there even though it is not defined in `permission_target` (an extra
permission).

Installation
------------

Make sure the PostgreSQL extension building infrastructure is installed.
If you installed PostgreSQL with installation packages, you usually need to
install the "development"-Package.

Make sure that `pg_config` is on your `PATH`.  Then type

    make install

Then connect to the database where you want to run `pg_permissions` and use

    CREATE EXTENSION pg_permissions;

You need `CREATE` privileges on the schema where you install the extension.

### Installation without the extension building infrastructure ###

This is also what Windows users will have to dom because there is no extension
building infrastructure for Windows.

Find out where your PostgreSQL share directory is:

    pg_config --sharedir

Then copy `pg_permissions.control` and the SQL files to the `extension`
subdirectory of that directory, e.g.

    copy pg_permissions.control *.sql "C:\Program Files\PostgreSQL\10\share\extension"

You still have to run `CREATE EXTENSION` as described above.

Support
-------

Open an [issue][issue] on Gitgub if you have problems or questions.

For professional support, please contact [Cybertec][cybertec].


 [issue]: https://github.com/cybertec-postgresql/pg_permission/issues
 [cybertec]: https://www.cybertec-postgresql.com/