pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBaffled by basic permission issue.
7+ messages / 4 participants
[nested] [flat]
* Baffled by basic permission issue.
@ 2021-06-16 20:56 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Wells Oliver @ 2021-06-16 20:56 UTC (permalink / raw)
To: pgsql-admin
I have a view that does this:
create view stats.v
select * from stats.t
join alias.i ON t.id = i.id
join alias.i2 ON t.id2 = i2.id;
And when I do: SELECT * FROM stats.v I receive: *ERROR: permission denied
for table i*
However, if I paste the contents of the view as a query and run it
manually, it works fine.
I can also query alias.i as I please.
The role I am using to connect has USAGE on both stats and alias, it also
has SELECT on the table as well as the view.
The only difference is that the view is owned by a different user than the
tables, though the SELECT permissions are definitely granted.
psql 13.2
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Baffled by basic permission issue.
@ 2021-06-16 20:58 Holger Jakobs <holger@jakobs.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Holger Jakobs @ 2021-06-16 20:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
If you want to use a view, you need the permissions for it. Not for the underlying tables
Am 16. Juni 2021 22:56:17 MESZ schrieb Wells Oliver <wells.oliver@gmail.com>:
>I have a view that does this:
>
>create view stats.v
>select * from stats.t
>join alias.i ON t.id = i.id
>join alias.i2 ON t.id2 = i2.id;
>
>And when I do: SELECT * FROM stats.v I receive: *ERROR: permission
>denied
>for table i*
>
>However, if I paste the contents of the view as a query and run it
>manually, it works fine.
>
>I can also query alias.i as I please.
>
>The role I am using to connect has USAGE on both stats and alias, it
>also
>has SELECT on the table as well as the view.
>
>The only difference is that the view is owned by a different user than
>the
>tables, though the SELECT permissions are definitely granted.
>
>psql 13.2
>
>--
>Wells Oliver
>wells.oliver@gmail.com <wellsoliver@gmail.com>
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Baffled by basic permission issue.
@ 2021-06-16 21:01 Wells Oliver <wells.oliver@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Wells Oliver @ 2021-06-16 21:01 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
The role has SELECT perm on the view and on the underlying tables and I am
received a permission denied table on the table while trying to select from
the view. The view is owned by a different user but SELECT perms are very
clearly granted to my role.
On Wed, Jun 16, 2021 at 1:58 PM Holger Jakobs <holger@jakobs.com> wrote:
> If you want to use a view, you need the permissions for it. Not for the
> underlying tables
>
> Am 16. Juni 2021 22:56:17 MESZ schrieb Wells Oliver <
> wells.oliver@gmail.com>:
>>
>> I have a view that does this:
>>
>> create view stats.v
>> select * from stats.t
>> join alias.i ON t.id = i.id
>> join alias.i2 ON t.id2 = i2.id;
>>
>> And when I do: SELECT * FROM stats.v I receive: *ERROR: permission
>> denied for table i*
>>
>> However, if I paste the contents of the view as a query and run it
>> manually, it works fine.
>>
>> I can also query alias.i as I please.
>>
>> The role I am using to connect has USAGE on both stats and alias, it also
>> has SELECT on the table as well as the view.
>>
>> The only difference is that the view is owned by a different user than
>> the tables, though the SELECT permissions are definitely granted.
>>
>> psql 13.2
>>
>> --
>> Wells Oliver
>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>
>
> --
> Holger Jakobs, Bergisch Gladbach
> +49 178 9759012
> - sent from mobile, therefore short -
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Baffled by basic permission issue.
@ 2021-06-16 21:10 Wells Oliver <wells.oliver@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Wells Oliver @ 2021-06-16 21:10 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
To follow up a bit, it seems clear to me that after I restored to a new
server, there's some setting (maybe?) preventing folks from querying
objects owned by other users EVEN if they have SELECT perms on the object,
and USAGE on the schema. Is this a possibility?
On Wed, Jun 16, 2021 at 2:01 PM Wells Oliver <wells.oliver@gmail.com> wrote:
> The role has SELECT perm on the view and on the underlying tables and I am
> received a permission denied table on the table while trying to select from
> the view. The view is owned by a different user but SELECT perms are very
> clearly granted to my role.
>
> On Wed, Jun 16, 2021 at 1:58 PM Holger Jakobs <holger@jakobs.com> wrote:
>
>> If you want to use a view, you need the permissions for it. Not for the
>> underlying tables
>>
>> Am 16. Juni 2021 22:56:17 MESZ schrieb Wells Oliver <
>> wells.oliver@gmail.com>:
>>>
>>> I have a view that does this:
>>>
>>> create view stats.v
>>> select * from stats.t
>>> join alias.i ON t.id = i.id
>>> join alias.i2 ON t.id2 = i2.id;
>>>
>>> And when I do: SELECT * FROM stats.v I receive: *ERROR: permission
>>> denied for table i*
>>>
>>> However, if I paste the contents of the view as a query and run it
>>> manually, it works fine.
>>>
>>> I can also query alias.i as I please.
>>>
>>> The role I am using to connect has USAGE on both stats and alias, it
>>> also has SELECT on the table as well as the view.
>>>
>>> The only difference is that the view is owned by a different user than
>>> the tables, though the SELECT permissions are definitely granted.
>>>
>>> psql 13.2
>>>
>>> --
>>> Wells Oliver
>>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>>
>>
>> --
>> Holger Jakobs, Bergisch Gladbach
>> +49 178 9759012
>> - sent from mobile, therefore short -
>>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Baffled by basic permission issue.
@ 2021-06-16 21:19 Wells Oliver <wells.oliver@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Wells Oliver @ 2021-06-16 21:19 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
As one last follow up, and I apologize for the volume, but this might be
related to RDS: our DBs were in a 'Modifying...' state and being backed up,
and now something has changed, and we can query these views again. I don't
understand. Maybe the underlying tables were temporarily locked during some
RDS backup process.
Anyone run into that kind of thing?
On Wed, Jun 16, 2021 at 2:10 PM Wells Oliver <wells.oliver@gmail.com> wrote:
> To follow up a bit, it seems clear to me that after I restored to a new
> server, there's some setting (maybe?) preventing folks from querying
> objects owned by other users EVEN if they have SELECT perms on the object,
> and USAGE on the schema. Is this a possibility?
>
> On Wed, Jun 16, 2021 at 2:01 PM Wells Oliver <wells.oliver@gmail.com>
> wrote:
>
>> The role has SELECT perm on the view and on the underlying tables and I
>> am received a permission denied table on the table while trying to select
>> from the view. The view is owned by a different user but SELECT perms are
>> very clearly granted to my role.
>>
>> On Wed, Jun 16, 2021 at 1:58 PM Holger Jakobs <holger@jakobs.com> wrote:
>>
>>> If you want to use a view, you need the permissions for it. Not for the
>>> underlying tables
>>>
>>> Am 16. Juni 2021 22:56:17 MESZ schrieb Wells Oliver <
>>> wells.oliver@gmail.com>:
>>>>
>>>> I have a view that does this:
>>>>
>>>> create view stats.v
>>>> select * from stats.t
>>>> join alias.i ON t.id = i.id
>>>> join alias.i2 ON t.id2 = i2.id;
>>>>
>>>> And when I do: SELECT * FROM stats.v I receive: *ERROR: permission
>>>> denied for table i*
>>>>
>>>> However, if I paste the contents of the view as a query and run it
>>>> manually, it works fine.
>>>>
>>>> I can also query alias.i as I please.
>>>>
>>>> The role I am using to connect has USAGE on both stats and alias, it
>>>> also has SELECT on the table as well as the view.
>>>>
>>>> The only difference is that the view is owned by a different user than
>>>> the tables, though the SELECT permissions are definitely granted.
>>>>
>>>> psql 13.2
>>>>
>>>> --
>>>> Wells Oliver
>>>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>>>
>>>
>>> --
>>> Holger Jakobs, Bergisch Gladbach
>>> +49 178 9759012
>>> - sent from mobile, therefore short -
>>>
>>
>>
>> --
>> Wells Oliver
>> wells.oliver@gmail.com <wellsoliver@gmail.com>
>>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Baffled by basic permission issue.
@ 2021-06-16 21:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-06-16 21:21 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org
Wells Oliver <wells.oliver@gmail.com> writes:
> The role has SELECT perm on the view and on the underlying tables and I am
> received a permission denied table on the table while trying to select from
> the view. The view is owned by a different user but SELECT perms are very
> clearly granted to my role.
When you select from a view, you need permissions only on the view.
The view's owner needs permissions on the underlying table(s).
This is so that views can act as permission gateways.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Baffled by basic permission issue.
@ 2021-06-17 06:43 Wim Bertels <wim.bertels@ucll.be>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Wim Bertels @ 2021-06-17 06:43 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Tom Lane schreef op wo 16-06-2021 om 17:21 [-0400]:
>
> When you select from a view, you need permissions only on the view.
> The view's owner needs permissions on the underlying table(s).
> This is so that views can act as permission gateways.
additionally,
if you want to allow modifications through this view,
have a look at 'with check option' to avoid unwanted permission side-
effects
>
> regards, tom lane
>
>
--
mvg,
Wim Bertels
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-06-17 06:43 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-16 20:56 Baffled by basic permission issue. Wells Oliver <wells.oliver@gmail.com>
2021-06-16 20:58 ` Holger Jakobs <holger@jakobs.com>
2021-06-16 21:01 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-16 21:10 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-16 21:19 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-16 21:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-17 06:43 ` Wim Bertels <wim.bertels@ucll.be>
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