Restrict permissions on schema to hide pl/pgsql code

From: Swanand Kshirsagar <swanandon(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Restrict permissions on schema to hide pl/pgsql code
Date: 2019-07-24 13:03:46
Message-ID: CAMn6ZARbP+r=AdDpFG1xC217=+mMVOsqnVBLL4Pf+ktDJt33gA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2019-07-24 13:28:05 Re: Restrict permissions on schema to hide pl/pgsql code
Previous Message Mariel Cherkassky 2019-07-24 09:15:05 pg_locks relation cant be found in pg_class