Add /dp command to information_schema reader

[henlue]

Adds the /dp backslash command. It lists privileges that have been granted on tables, views and sequences.

It is implemented in the information schema metadata reader and was tested against postgres, sqlserver and mariadb. I could not test it against snowflake and netezza, since I don't have access to a development instance.

Partly implements #172

[henlue]

Some examples:

Sqlserver

                                      Access privileges
 Schema |         Name          |    Type    |  Access privileges   |    Column privileges    
--------+-----------------------+------------+----------------------+-------------------------
 dbo    | MSreplication_options | BASE TABLE |                      |  
 dbo    | spt_fallback_db       | BASE TABLE | public=SELECT/dbo    |  
 dbo    | spt_fallback_dev      | BASE TABLE | public=SELECT/dbo    |  
 dbo    | spt_fallback_usg      | BASE TABLE | public=SELECT/dbo    |  
 dbo    | spt_monitor           | BASE TABLE | public=SELECT/dbo    |  
 dbo    | spt_values            | VIEW       | public=SELECT/dbo    |  
 dbo    | test_table            | BASE TABLE | test_user=SELECT/dbo | id:                    +
        |                       |            |                      |   test_user=UPDATE*/dbo

Maria DB

                                       Access privileges
    Schema     |    Name    |    Type    |    Access privileges    |     Column privileges     
---------------+------------+------------+-------------------------+---------------------------
 test_database | test_table | BASE TABLE | 'test_user'@'%'=SELECT* | id:                      +
               |            |            |                         |   'test_user'@'%'=INSERT* 
(1 row)

Postgres

                                                                               Access privileges
 Schema |    Name    |    Type    |                                Access privileges                                 |                    Column privileges                    
--------+------------+------------+----------------------------------------------------------------------------------+---------------------------------------------------------
 public | test_table | BASE TABLE | postgres=DELETE*,INSERT*,REFERENCES*,SELECT*,TRIGGER*,TRUNCATE*,UPDATE*/postgres+| id:                                                    +
        |            |            | test_user=SELECT/postgres                                                        |   postgres=INSERT*,REFERENCES*,SELECT*,UPDATE*/postgres+
        |            |            |                                                                                  |   test_user=INSERT*,SELECT/postgres                    +
        |            |            |                                                                                  | name:                                                  +
        |            |            |                                                                                  |   postgres=INSERT*,REFERENCES*,SELECT*,UPDATE*/postgres+
        |            |            |                                                                                  |   test_user=SELECT/postgres 
(1 row)

The output for postgres is quite verbose. The problem is that the owner by default has all privileges and all those privileges will be printed for each table and for each column separately. In psql the default privileges for the owner are hidden. But from the information_schema it is not possible to infer the owner of a table.

Maria DB and Sqlserver don't have this problem because they don't write the default privileges to the information_schema in the first place.

In psql the privileges are abreviated (e.g. postgres=arwdDxt/postgres). I left them in a written out form. Otherwise we would need to come up with abreviations for all privileges that are used in all supported databases. But this also makes the output longer.

I guess a workaround to help keep the overview for bigger schemas might be to only show the "Column priviliges" column with \dp+. A better solution would be a postgres specific implentation that has access to the table owner and can filter the output based on that.

[nineinchnick]

Thanks, these examples look neat!

I guess a workaround to help keep the overview for bigger schemas might be to only show the "Column priviliges" column with \dp+. A better solution would be a postgres specific implentation that has access to the table owner and can filter the output based on that.

When I was implementing other metadata items earlier I ended up having to use PostgreSQL's system catalogs anyway, instead of the information schema.

Does it make sense to cram both table and column privileges into a single table? Maybe it'll be easier to see if the PrivilegeSummary abstraction is right if there's more than one implementation, so that's another reason to try to use PostgreSQL's specific catalogs.

[henlue]

Good question. I didn't really think about it. Just copied what psql does. I guess it can be nice to have all the privileges for one db object in a single place.

So we leave it as it is for now, and implement the postgres reader in a seperate PR?

[nineinchnick]

Good question. I didn't really think about it. Just copied what psql does. I guess it can be nice to have all the privileges for one db object in a single place.

So we leave it as it is for now, and implement the postgres reader in a seperate PR?

Yes, sounds like a good plan!

[nineinchnick]

@henlue is it ready to be merged?

[henlue]

Yes :-)