Обсуждение: Restrict permissions on schema to hide pl/pgsql code
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.
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.
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.
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:
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.
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.com 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
Вложения
"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
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
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.