Grant documentation about "all tables"

Greetings Lætitia!

* Lætitia Avrot (laetitia.avrot@gmail.com) wrote:

When you look at Postgres' SQL reference documentation for `GRANT`, the
`ALL TABLES` clause is explained as :

ALL TABLES also affects views and foreign tables, just like

the specific-object GRANT command.

A colleague of mine was asking himself if it included materialized views or
not (well, yes it does).

I made that tiny patch to add materialized views to the list. It builds on
my laptop.

Then another question crossed my mind... What about partitioned tables ?
I'm pretty sure it works for them too (because they're basically tables)
but should we add them too ? I couldn't decide whether to add them too or
not so I refrain from doing it and am asking you the question.

The question here, at least in my mind, is if we feel it necessary to
list out all of the specific kinds of "views" (as in, regular views and
materialized views), and the same question applies to tables- do we list
out all the specific kinds of "tables" (to include partitioned tables),
or not?

To figure that out, I'd suggest looking at existing documentation where
we have similar lists and see what we've done in the past. If those
other cases list everything explicitly, then the answer is clear, and if
they don't, then we can either leave the documentation as-is, or come up
with a complete list of changes that need to be made.

If there aren't any other cases then I'd probably fall-back on looking
at how we document things in the system catalogs area of the docs and
see how much we get into the individual specific kinds of tables/views
and perhaps that would help us figure out what makes sense to do here.

Thanks!

Stephen

Hi Stephen,

Le sam. 5 janv. 2019 à 20:41, Stephen Frost <sfrost@snowman.net> a écrit :

Greetings Lætitia!

* Lætitia Avrot (laetitia.avrot@gmail.com) wrote:

When you look at Postgres' SQL reference documentation for `GRANT`, the
`ALL TABLES` clause is explained as :

ALL TABLES also affects views and foreign tables, just like

the specific-object GRANT command.

A colleague of mine was asking himself if it included materialized views

or

not (well, yes it does).

I made that tiny patch to add materialized views to the list. It builds

on

my laptop.

Then another question crossed my mind... What about partitioned tables ?
I'm pretty sure it works for them too (because they're basically tables)
but should we add them too ? I couldn't decide whether to add them too or
not so I refrain from doing it and am asking you the question.

The question here, at least in my mind, is if we feel it necessary to
list out all of the specific kinds of "views" (as in, regular views and
materialized views), and the same question applies to tables- do we list
out all the specific kinds of "tables" (to include partitioned tables),
or not?

To figure that out, I'd suggest looking at existing documentation where
we have similar lists and see what we've done in the past. If those
other cases list everything explicitly, then the answer is clear, and if
they don't, then we can either leave the documentation as-is, or come up
with a complete list of changes that need to be made.

Excellent advice. I looked at several doc pages that could be appropriate
on the subject and I came up with this list :

- [ALTER DEFAULT PRIVILEDGES](
https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html):
This documentation page indicates that :

Currently, only the privileges for schemas, tables (including views and

foreign tables), sequences, functions, and types (including domains) can be
altered.
-> foreign tables are mentioned but nothing about materialized views or
temporary or partitioned tables...

- [COPY](https://www.postgresql.org/docs/current/sql-copy.html) :

COPY FROM can be used with plain, foreign, or partitioned tables or with

views that have INSTEAD OF INSERT triggers.
-> different tables are listed, still nothing about materialized views and
temporary tables

- [CREATE PUBLICATION](
https://www.postgresql.org/docs/current/sql-createpublication.html)

FOR ALL TABLES : Marks the publication as one that replicates changes for

all tables in the database, including tables created in the future.
-> I included it here because this documentation page because it uses a
`FOR ALL TABLES` clause, but this time only base tables are included. (As
written in [that other page](
https://www.postgresql.org/docs/current/logical-replication-restrictions.html)
"Replication is only possible from base tables to base tables. That is, the
tables on the publication and on the subscription side must be normal
tables, not views, materialized views, partition root tables, or foreign
tables.")

- [sepgsql documentation page](
https://www.postgresql.org/docs/11/sepgsql.html)

Currently, `sepgsql` allows security labels to be assigned to schemas,

tables, columns, sequences, views, and functions.
-> in this documentation, I suppose (from reading source code) "tables"
means all kind of tables and "views" means all kind of views, just as in
the `GRANT` documentation page.

So we have this:

| Meaning
| Number of documentation pages |
|---------------------------------------------------------------------------|-------------------------------|
| "tables" meaning all kind of tables and "views" meaning all kind of views
| 2 |
| ALL TABLES meaning only tables
| 1 |
| "tables" meaning all tables and views
| 1 |
| "tables" meaning only tables
| 1 |

Maybe we need to standardize this. I think it's better to add explicitly
each type of table (plain, partitioned, foreign, temporary) or view (plain,
materialized) when needed, so there is no doubt for everyone. What do you
think ?

Cheers,

Lætitia
--
*Think! Do you really need to print this email ? *
*There is no Planet B.*