pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange inconsistency using psql
4+ messages / 3 participants
[nested] [flat]
* Strange inconsistency using psql
@ 2019-12-18 17:28 John Scalia <jayknowsunix@gmail.com>
2019-12-18 17:41 ` Re: Strange inconsistency using psql Keith <keith@keithf4.com>
0 siblings, 1 reply; 4+ messages in thread
From: John Scalia @ 2019-12-18 17:28 UTC (permalink / raw)
To: pgsql-admin
I had to do some privilege assignments this morning on a bunch of tables, sequences, functions, and views. On all of these object, I generally try to use a command like:
GRANT all ON TABLE x TO new_user;
Where TABLE is either that object or a SEQUENCE or FUNCTION. These all worked perfectly for me. What did not work was specifying that the object was a VIEW. The system would spit out a syntax error at the object’s name being specified, however, if I omitted the word VIEW, and not specify the type of object, then the GRANT succeeded.
Was this intentional behavior, or is the grammar slightly amiss? All the other types of objects worked perfectly with this style of command.
—
Jay
Sent from my iPad
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange inconsistency using psql
2019-12-18 17:28 Strange inconsistency using psql John Scalia <jayknowsunix@gmail.com>
@ 2019-12-18 17:41 ` Keith <keith@keithf4.com>
2019-12-18 18:35 ` Re: Strange inconsistency using psql John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Keith @ 2019-12-18 17:41 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
On Wed, Dec 18, 2019 at 12:29 PM John Scalia <jayknowsunix@gmail.com> wrote:
> I had to do some privilege assignments this morning on a bunch of tables,
> sequences, functions, and views. On all of these object, I generally try to
> use a command like:
>
> GRANT all ON TABLE x TO new_user;
>
> Where TABLE is either that object or a SEQUENCE or FUNCTION. These all
> worked perfectly for me. What did not work was specifying that the object
> was a VIEW. The system would spit out a syntax error at the object’s name
> being specified, however, if I omitted the word VIEW, and not specify the
> type of object, then the GRANT succeeded.
>
> Was this intentional behavior, or is the grammar slightly amiss? All the
> other types of objects worked perfectly with this style of command.
> —
> Jay
>
> Sent from my iPad
>
>
There is no VIEW clause to the GRANT command. The TABLE clause is actually
optional in the command when setting privileges on tables or views. And
generally you can use the privileges used on tables when setting privileges
on views.
https://www.postgresql.org/docs/current/sql-grant.html
Keith
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange inconsistency using psql
2019-12-18 17:28 Strange inconsistency using psql John Scalia <jayknowsunix@gmail.com>
2019-12-18 17:41 ` Re: Strange inconsistency using psql Keith <keith@keithf4.com>
@ 2019-12-18 18:35 ` John Scalia <jayknowsunix@gmail.com>
2019-12-18 19:01 ` Re: Strange inconsistency using psql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: John Scalia @ 2019-12-18 18:35 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: pgsql-admin
Correct, but my question really is, why is VIEW different from all the other types of objects? Shouldn’t the word VIEW also be an optional qualifier for the name of the object? Inconsistency strikes me as an oversight.
Sent from my iPad
> On Dec 18, 2019, at 12:41 PM, Keith <keith@keithf4.com> wrote:
>
>
>
>
>> On Wed, Dec 18, 2019 at 12:29 PM John Scalia <jayknowsunix@gmail.com> wrote:
>> I had to do some privilege assignments this morning on a bunch of tables, sequences, functions, and views. On all of these object, I generally try to use a command like:
>>
>> GRANT all ON TABLE x TO new_user;
>>
>> Where TABLE is either that object or a SEQUENCE or FUNCTION. These all worked perfectly for me. What did not work was specifying that the object was a VIEW. The system would spit out a syntax error at the object’s name being specified, however, if I omitted the word VIEW, and not specify the type of object, then the GRANT succeeded.
>>
>> Was this intentional behavior, or is the grammar slightly amiss? All the other types of objects worked perfectly with this style of command.
>> —
>> Jay
>>
>> Sent from my iPad
>>
>
> There is no VIEW clause to the GRANT command. The TABLE clause is actually optional in the command when setting privileges on tables or views. And generally you can use the privileges used on tables when setting privileges on views.
>
> https://www.postgresql.org/docs/current/sql-grant.html
>
> Keith
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange inconsistency using psql
2019-12-18 17:28 Strange inconsistency using psql John Scalia <jayknowsunix@gmail.com>
2019-12-18 17:41 ` Re: Strange inconsistency using psql Keith <keith@keithf4.com>
2019-12-18 18:35 ` Re: Strange inconsistency using psql John Scalia <jayknowsunix@gmail.com>
@ 2019-12-18 19:01 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2019-12-18 19:01 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Keith <keith@keithf4.com>; pgsql-admin
John Scalia <jayknowsunix@gmail.com> writes:
> Correct, but my question really is, why is VIEW different from all the other types of objects?
It's not really -- GRANT considers all kinds of privilege-grantable
relations to be "tables", with the exception of sequences which are
different because they have a different set of applicable privileges.
If we treated views specially here then we'd also have to treat
materialized views and foreign tables as distinct things-to-grant-on,
which seems like mostly clutter.
The history of ALTER TABLE is an analogy, which I guess you could
read as support for either side. Originally PG just had ALTER TABLE
and it worked on all relation kinds (for which the ALTER was sensible).
We've since grown ALTER VIEW etc, but I don't think their coverage of
the ALTER TABLE options is quite complete --- and anyway we still let
you say ALTER TABLE, for backwards-compatibility reasons.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-12-18 19:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-18 17:28 Strange inconsistency using psql John Scalia <jayknowsunix@gmail.com>
2019-12-18 17:41 ` Keith <keith@keithf4.com>
2019-12-18 18:35 ` John Scalia <jayknowsunix@gmail.com>
2019-12-18 19:01 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox