pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRestrict permissions on schema to hide pl/pgsql code
8+ messages / 6 participants
[nested] [flat]
* Restrict permissions on schema to hide pl/pgsql code
@ 2019-07-24 13:03 Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Swanand Kshirsagar @ 2019-07-24 13:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
I have a situation where customer is using multi-tenant architecture in the
PostgreSQL database i.e. one schema is for one customer. Now, I have
revoked USAGE permissions from user 'B' on schema 'A' so that user B cannot
query on tables in schema A. This is working as expected. But, when my user
is connecting through pgAdmin (pgAdmin 3 in this case), User B can view and
copy the code for functions/procedures/triggers etc. This is critical,
other users should not have ANY access to schema when I revoke usage
permissions from it.
I tried revoking all permissions from pg_proc for user B and then when user
B clicks on any function in schema A an error is thrown (thus, working as
expected). But, I am afraid, revoking privileges on catalog tables is not a
best approach as it will impact using any other functions as well.
Isn't revoking permissions from a schema should take care of this
situation? Kindly guide.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restrict permissions on schema to hide pl/pgsql code
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
@ 2019-07-24 13:28 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 16:47 ` Re: Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2019-07-24 13:28 UTC (permalink / raw)
To: Swanand Kshirsagar <swanandon@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wednesday, July 24, 2019, Swanand Kshirsagar <swanandon@gmail.com> wrote:
>
> Isn't revoking permissions from a schema should take care of this
> situation?
>
The pl/pgsql function body is stored in pg_catalog which the user still has
permission to read. There isn’t a good/supported way to work around this
behavior.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restrict permissions on schema to hide pl/pgsql code
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
@ 2019-07-24 16:47 ` Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 17:08 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Swanand Kshirsagar @ 2019-07-24 16:47 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Yes, that's the reason why I tried revoking permissions from
pg_catalog.pg_proc table.
What's the right way to notify this OR check if there is any plan in
roadmap?
On Wed, Jul 24, 2019 at 6:58 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Wednesday, July 24, 2019, Swanand Kshirsagar <swanandon@gmail.com>
> wrote:
>>
>> Isn't revoking permissions from a schema should take care of this
>> situation?
>>
>
> The pl/pgsql function body is stored in pg_catalog which the user still
> has permission to read. There isn’t a good/supported way to work around
> this behavior.
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restrict permissions on schema to hide pl/pgsql code
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 16:47 ` Re: Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
@ 2019-07-24 17:08 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 17:15 ` Re: Restrict permissions on schema to hide pl/pgsql code Stephen Frost <sfrost@snowman.net>
2019-07-24 17:15 ` Re: Restrict permissions on schema to hide pl/pgsql code Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: David G. Johnston @ 2019-07-24 17:08 UTC (permalink / raw)
To: Swanand Kshirsagar <swanandon@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
You can consider this email to have accomplished both. Lacking someone
saying they they are working on it and pointing you to a patch you can
safely operate under the assumption that this behavior isn’t going to
change. I suppose its possible with the recent row-level security feature
that a fresh look leveraging that facility could be considered but AFAIK
that hasn’t and isn’t being done.
There is the commitfest website but otherwise plans and complaint raising
are pretty informal here. Though typically the -general list is a better
choice for inquiries of this nature.
David J.
On Wednesday, July 24, 2019, Swanand Kshirsagar <swanandon@gmail.com> wrote:
> Yes, that's the reason why I tried revoking permissions from
> pg_catalog.pg_proc table.
>
> What's the right way to notify this OR check if there is any plan in
> roadmap?
>
> On Wed, Jul 24, 2019 at 6:58 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Wednesday, July 24, 2019, Swanand Kshirsagar <swanandon@gmail.com>
>> wrote:
>>>
>>> Isn't revoking permissions from a schema should take care of this
>>> situation?
>>>
>>
>> The pl/pgsql function body is stored in pg_catalog which the user still
>> has permission to read. There isn’t a good/supported way to work around
>> this behavior.
>>
>> David J.
>>
>>
>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restrict permissions on schema to hide pl/pgsql code
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 16:47 ` Re: Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 17:08 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
@ 2019-07-24 17:15 ` Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 8+ messages in thread
From: Stephen Frost @ 2019-07-24 17:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Swanand Kshirsagar <swanandon@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Greetings,
* David G. Johnston (david.g.johnston@gmail.com) wrote:
> You can consider this email to have accomplished both. Lacking someone
> saying they they are working on it and pointing you to a patch you can
> safely operate under the assumption that this behavior isn’t going to
> change. I suppose its possible with the recent row-level security feature
> that a fresh look leveraging that facility could be considered but AFAIK
> that hasn’t and isn’t being done.
Actually, there was work specifically around that which it'd be nice if
someone had time to pick up and work on.
https://www.postgresql.org/message-id/CAJrrPGdCZEVxQTs49CqxjjyffHKPFtff+sa6c6f5Z5grXztodw@mail.gmail...
One of the big questions around that is how much good is it to limit
access when the catalog tables are queried directly when you can gather
information about what's in them in other ways that we wouldn't want to
be running through RLS (such as when we do direct catalog accesses as
part of some DDL or such). That's a similar trade-off that application
designers have to deal with when using RLS (eg: primary key violations
could 'leak' the knowledge that a particular value exists even if you
can't see it) and in many cases it's acceptable.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restrict permissions on schema to hide pl/pgsql code
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 16:47 ` Re: Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 17:08 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
@ 2019-07-24 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-07-24 17:44 ` Re: Restrict permissions on schema to hide pl/pgsql code richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2019-07-24 17:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Swanand Kshirsagar <swanandon@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> You can consider this email to have accomplished both. Lacking someone
> saying they they are working on it and pointing you to a patch you can
> safely operate under the assumption that this behavior isn’t going to
> change.
It isn't. We've considered complaints like this before and determined
that we're not going to do anything about it. For better or worse, the
PG catalogs are readable by any authorized user, with only narrow
exceptions (like password columns).
A sufficiently determined person could perhaps do something like creating
their own PL that stores encrypted function source text in pg_proc, and
just hands it off to an existing PL after decryption. I'm not exactly
sure where you'd keep the decryption key though.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restrict permissions on schema to hide pl/pgsql code
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 16:47 ` Re: Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 17:08 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 17:15 ` Re: Restrict permissions on schema to hide pl/pgsql code Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-07-24 17:44 ` richard coleman <rcoleman.ascentgl@gmail.com>
2019-07-24 18:59 ` Re: Restrict permissions on schema to hide pl/pgsql code Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: richard coleman @ 2019-07-24 17:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Swanand Kshirsagar <swanandon@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I guess that just means postgresql *probably* shouldn't be used in a
multi-tenancy situation if you need;
- complete isolation between tenants
- you still want to give tenants direct and otherwise unfettered access
to the database
just a thought,
rik.
On Wed, Jul 24, 2019 at 1:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > You can consider this email to have accomplished both. Lacking someone
> > saying they they are working on it and pointing you to a patch you can
> > safely operate under the assumption that this behavior isn’t going to
> > change.
>
> It isn't. We've considered complaints like this before and determined
> that we're not going to do anything about it. For better or worse, the
> PG catalogs are readable by any authorized user, with only narrow
> exceptions (like password columns).
>
> A sufficiently determined person could perhaps do something like creating
> their own PL that stores encrypted function source text in pg_proc, and
> just hands it off to an existing PL after decryption. I'm not exactly
> sure where you'd keep the decryption key though.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restrict permissions on schema to hide pl/pgsql code
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 16:47 ` Re: Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 17:08 ` Re: Restrict permissions on schema to hide pl/pgsql code David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 17:15 ` Re: Restrict permissions on schema to hide pl/pgsql code Tom Lane <tgl@sss.pgh.pa.us>
2019-07-24 17:44 ` Re: Restrict permissions on schema to hide pl/pgsql code richard coleman <rcoleman.ascentgl@gmail.com>
@ 2019-07-24 18:59 ` Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Thomas Kellerer @ 2019-07-24 18:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
richard coleman schrieb am 24.07.2019 um 19:44:
> I guess that just means postgresql /probably/ shouldn't be used in a multi-tenancy situation if you need;
>
> * complete isolation between tenants
> * you still want to give tenants direct and otherwise unfettered access to the database
You can use one database per tenant if you want that kind of isolation.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2019-07-24 18:59 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-24 13:03 Restrict permissions on schema to hide pl/pgsql code Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 13:28 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 16:47 ` Swanand Kshirsagar <swanandon@gmail.com>
2019-07-24 17:08 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-24 17:15 ` Stephen Frost <sfrost@snowman.net>
2019-07-24 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-07-24 17:44 ` richard coleman <rcoleman.ascentgl@gmail.com>
2019-07-24 18:59 ` Thomas Kellerer <spam_eater@gmx.net>
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