Re: Restricting user to see schema structure

On Tue, May 17, 2022 at 6:47 PM Bryn Llewellyn wrote: > *adrian.kla...@aklaver.com wrote:* > > b...@yugabyte.com wrote: > > The paragraph describes very surprising behavior in the present era of > "secure by default". The sentence "For maximum security..." at the end > emphasizes this and has yo

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >> The paragraph describes very surprising behavior in the present era of >> "secure by default". The sentence "For maximum security..." at the end >> emphasizes this and has you go to some effort (CREATE and REVOKE in the same

Re: Restricting user to see schema structure

On 5/17/22 13:21, Bryn Llewellyn wrote: The paragraph describes very surprising behavior in the present era of "secure by default". The sentence "For maximum security..." at the end emphasizes this and has you go to some effort (CREATE and REVOKE in the same txn) to undo the "insecurity by d

Re: Restricting user to see schema structure

On Tuesday, May 17, 2022, David G. Johnston wrote: > On Tuesday, May 17, 2022, Bryn Llewellyn wrote: >> >> Might it be possible to give the paragraph more prominence (like make it >> a note and start it with "WARNING" in large letters). And to x-ref it from >> the CREATE DATABASE section? And fr

Re: Restricting user to see schema structure

On Tuesday, May 17, 2022, Bryn Llewellyn wrote: > > Might it be possible to give the paragraph more prominence (like make it a > note and start it with "WARNING" in large letters). And to x-ref it from > the CREATE DATABASE section? And from the "pg_hba.conf" section? And > correspondingly from th

Re: Restricting user to see schema structure

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> Right, I see the importance of this now. I now realize that when a database >> is created, CONNECT on it is automatically granted to PUBLIC. But there's no >> mention of this (or what to read to learn that this is the case)

Re: Restricting user to see schema structure

On 5/16/22 22:21, Bryn Llewellyn wrote: Because as mentioned previously you did not "revoke connect on database postgres from public". Right, I see the importance of this now. I now realize that when a database is created, CONNECT on it is automatically granted to PUBLIC. But there's no ment

Re: Restricting user to see schema structure

On Mon, May 16, 2022 at 10:21 PM Bryn Llewellyn wrote: > > Right, I see the importance of this now. I now realize that when a > database is created, CONNECT on it is automatically granted to PUBLIC. But > there's no mention of this (or what to read to learn that this is the case) > in the "pg_hba

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> adrian.kla...@aklaver.com wrote: >>> b...@yugabyte.com wrote: > neerajmr12...@gmail.com wrote: > > ... What exactly do you mean b

Re: Restricting user to see schema structure

On Mon, May 16, 2022 at 2:04 PM Bryn Llewellyn wrote: > « > To connect to a particular database, a user must not only pass > the pg_hba.conf checks, but must have the CONNECT privilege for the > database. If you wish to restrict which users can connect to which > databases, it's usually easier to

Re: Restricting user to see schema structure

On 5/16/22 2:04 PM, Bryn Llewellyn wrote: /adrian.kla...@aklaver.com wrote:/ /b...@yugabyte.com wrote:/ /neerajmr12...@gmail.com wrote:/ ... What exactly do you mean by "have created a new user a

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> b...@yugabyte.com wrote: >> >>> neerajmr12...@gmail.com wrote: >>> >>> ... >> >> What exactly do you mean by "have created a new user and granted connection >> access to database"? As I understand it, there's no such thing. I mentioned >> a simple test

Re: Restricting user to see schema structure

On 5/12/22 22:13, Bryn Llewellyn wrote: /neerajmr12...@gmail.com wrote:/ What exactly do you mean by "have created a new user and granted connection access to database"? As I understand it, there's no such thing. I mentioned a simple test in my earlier email

Re: Restricting user to see schema structure

On 5/12/22 22:03, Neeraj M R wrote: Hi all, Thanks for your suggestions, I would like to define my problem a little more.  I am using pgAdmin . I have a database 'db' and it has got 2 schemas 'schema1' and 'schema2', I have created some views in schema2  from tables of schema1. I have crea

Re: Restricting user to see schema structure

On Thu, May 12, 2022 at 11:37 PM Bryn Llewellyn wrote: > *neerajmr12...@gmail.com wrote:* > > *b...@yugabyte.com wrote:* > > What exactly do you mean by "have created a new user and granted > connection access to database"? As I understand it, there's no such thing. > I mentioned a simple test

Re: Restricting user to see schema structure

Hi Bryn, What I meant by 'created a new user' is that I have used the following commands. CREATE USER WITH ENCRYPTED PASSWORD ''; GRANT CONNECT ON DATABASE TO ; GRANT USAGE ON SCHEMA TO ; Thanks & Regards Neeraj On Fri, May 13, 2022, 10:43 Bryn Llewellyn wrote: > *neerajmr12...@gmail.com

Re: Restricting user to see schema structure

Hi all, Thanks for your suggestions, I would like to define my problem a little more. I am using pgAdmin . I have a database 'db' and it has got 2 schemas 'schema1' and 'schema2', I have created some views in schema2 from tables of schema1. I have created a new user and granted connection acces

Re: Restricting user to see schema structure

> neerajmr12...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> What exactly do you mean by "have created a new user and granted connection >> access to database"? As I understand it, there's no such thing. I mentioned >> a simple test in my earlier email that showed that any user (with no

Re: Restricting user to see schema structure

> neerajmr12...@gmail.com wrote: > > I am using pgAdmin . I have a database 'db' and it has got 2 schemas > 'schema1' and 'schema2', I have created some views in schema2 from tables of > schema1. I have created a new user and granted connection access to database > and granted usage on tables

Re: Restricting user to see schema structure

On Thursday, May 12, 2022, Neeraj M R wrote: > > Thanks for your suggestions, I would like to define my problem a little > more. > > I am using pgAdmin > > Is there any way I can completely hide schema1 from the new user. > pgAdmin is a separate project. You may get a response here but they do

Re: Restricting user to see schema structure

> t...@sss.pgh.pa.us wrote: > >> b...@yugabyte.com writes: >> >> Maybe this entire discussion is moot when hackers can read the C code of >> PG's implementation… > > We have pretty much no interest in revisiting that design choice, even if > doing so wouldn't likely break a couple decades' wor

Re: Restricting user to see schema structure

pá 13. 5. 2022 v 5:42 odesílatel Bryn Llewellyn napsal: > *david.g.johns...@gmail.com wrote:* > > *b...@yugabyte.com wrote:* > > However, the design decision that, way back when, leads to this outcome > does surprise me. The principle of least privilege insists that (in the > database regime) y

Re: Restricting user to see schema structure

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> However, the design decision that, way back when, leads to this outcome does >> surprise me. The principle of least privilege insists that (in the database >> regime) you can create users that can do exactly and only what t

Re: Restricting user to see schema structure

Bryn Llewellyn writes: > Maybe this entire discussion is moot when hackers can read the C code of PG's > implementation… Hmm ... in one way that's unrelated, but in another way perhaps it is. Postgres' system catalogs have always been user-readable as much as possible, excepting only cases that

Re: Restricting user to see schema structure

On Thu, May 12, 2022 at 7:35 PM Bryn Llewellyn wrote: > > However, the design decision that, way back when, leads to this outcome > does surprise me. The principle of least privilege insists that (in the > database regime) you can create users that can do exactly and only what > they need to do.

Re: Restricting user to see schema structure

> david.g.johns...@gmail.com wrote: > >> adrian.kla...@aklaver.com wrote: >> >>> b...@yugabyte.com wrote: >>> >>> I've prototyped this scheme. It seems to work as designed. A client that >>> connects with psql (or any other tool) can list the API functions and >>> whatever \df and \sf show. (n

Re: Restricting user to see schema structure

On Thu, May 12, 2022 at 11:44 AM Adrian Klaver wrote: > On 5/12/22 11:29, Bryn Llewellyn wrote: > > > I've prototyped this scheme. It seems to work as designed. A client that > connects with psql (or any other tool) can list the API functions and > whatever \df and \sf show. (notice that \d will

Re: Restricting user to see schema structure

On 5/12/22 11:29, Bryn Llewellyn wrote: I've prototyped this scheme. It seems to work as designed. A client that connects with psql (or any other tool) can list the API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only the names of the f

Re: Restricting user to see schema structure

> adrian.kla...@aklaver.com wrote: > >> neerajmr12...@gmail.com wrote: >> >> Is there anyway that we can restrict a user from seeing the schema >> structure. I can restrict the user from accessing the data in the schema but >> the user is still able to see the table names and what all columns a

Re: Restricting user to see schema structure

On 5/12/22 03:04, Neeraj M R wrote: Hi, Is there anyway that we can restrict a user from seeing the schema structure. I can restrict the user from accessing the data in the schema but the user is still able to see the table names and what all columns are present in them. No. Thanks & Rega

Restricting user to see schema structure

Hi, Is there anyway that we can restrict a user from seeing the schema structure. I can restrict the user from accessing the data in the schema but the user is still able to see the table names and what all columns are present in them. Thanks & Regards Neeraj