Thread: Security Issue..
I'm running into a minor issue with security in regards to users being able to see constructs that they have no access too. The solution? Information_Schema coupled with no direct access to pg_catalog. Internals can use pg_catalog, possibly super users, but regular users shouldn't be able to do any reads / writes to it directly -- as per spec with definition_schema. Anyway, I'd like to start working on the information_schema and converting psql, pg_dump and other tools over to use it. After a couple of releases I'd like to block pg_catalog usage -- perhaps a GUC option? Any thoughts or objections? Obviously the information schema needs (aside from the spec) enough information to allow pg_dump to run. My thought is that if I start now when a large rewrite of clientside applications is required for schema support that there won't be nearly as much backlash later. A number of pg_dump items will be moved into base functions. Trigger statement, type formatting (various view fields). Whats the radix of the numeric, int, etc. types anyway? As a bonus, this adds a layer between the actual system tables and the clients. Might allow changes to be done easier. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise.
Rod Taylor wrote: > I'm running into a minor issue with security in regards to users being > able to see constructs that they have no access too. > > The solution? Information_Schema coupled with no direct access to > pg_catalog. Internals can use pg_catalog, possibly super users, but > regular users shouldn't be able to do any reads / writes to it > directly -- as per spec with definition_schema. Is the problem that people can see system catalog columns that should be more secure? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Rod Taylor writes: > The solution? Information_Schema coupled with no direct access to > pg_catalog. Internals can use pg_catalog, possibly super users, but > regular users shouldn't be able to do any reads / writes to it > directly -- as per spec with definition_schema. The catch on this is that privileges on views don't work quite perfectly yet. For instance, if you create a view CREATE VIEW bar AS SELECT * FROM foo; then the statement SELECT * FROM bar; needs privileges to read "foo". The privileges would need to be changed to be checked at view creation time. -- Peter Eisentraut peter_e@gmx.net
Yes. A number of people in the company have mentioned that our customers can see tables and structures which they shouldn't know exist. Not a severe issue, but it's a checkmark for those wanting to switch to Oracle. Revoking read access to system catalogs causes interesting things to occur :) -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "Rod Taylor" <rbt@zort.ca> Cc: "Hackers List" <pgsql-hackers@postgresql.org> Sent: Sunday, April 14, 2002 9:33 PM Subject: Re: [HACKERS] Security Issue.. > Rod Taylor wrote: > > I'm running into a minor issue with security in regards to users being > > able to see constructs that they have no access too. > > > > The solution? Information_Schema coupled with no direct access to > > pg_catalog. Internals can use pg_catalog, possibly super users, but > > regular users shouldn't be able to do any reads / writes to it > > directly -- as per spec with definition_schema. > > Is the problem that people can see system catalog columns that should be > more secure? > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
Yeah, I was planning on blocking queries to pg_catalog for all cases. Make it so that it can never be done by any user directly. It would have to be done in the parser before the view was evaluated, and no user created views would be allowed to access pg_catalog. The spec describes the definition schema as accessable only from the information schema. Long term goal of course. It would take a few releases to ensure that everything was setup to be done like that. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ----- Original Message ----- From: "Peter Eisentraut" <peter_e@gmx.net> To: "Rod Taylor" <rbt@zort.ca> Cc: "Hackers List" <pgsql-hackers@postgresql.org> Sent: Sunday, April 14, 2002 9:45 PM Subject: Re: [HACKERS] Security Issue.. > Rod Taylor writes: > > > The solution? Information_Schema coupled with no direct access to > > pg_catalog. Internals can use pg_catalog, possibly super users, but > > regular users shouldn't be able to do any reads / writes to it > > directly -- as per spec with definition_schema. > > The catch on this is that privileges on views don't work quite perfectly > yet. For instance, if you create a view > > CREATE VIEW bar AS SELECT * FROM foo; > > then the statement > > SELECT * FROM bar; > > needs privileges to read "foo". The privileges would need to be changed > to be checked at view creation time. > > -- > Peter Eisentraut peter_e@gmx.net >
Peter Eisentraut <peter_e@gmx.net> writes:
> For instance, if you create a view
> CREATE VIEW bar AS SELECT * FROM foo;
> then the statement
> SELECT * FROM bar;
> needs privileges to read "foo".
This works just fine, thank you: the privileges are checked against the
owner of the view.
> The privileges would need to be changed
> to be checked at view creation time.
That would be broken; privileges are and must be checked at query
time not view creation time.
But having said that, I do not foresee being able to replace direct
pg_catalog access with INFORMATION_SCHEMA views anytime soon. There
are too many clients out there that are used to doing it that way.
Moreover, pg_dump will never be able to work off INFORMATION_SCHEMA,
because it needs to get at Postgres-specific information that will
not be available from a spec-compliant set of views. I'm fairly
dubious about converting psql, even.
Rod's welcome to work on developing a set of spec-compliant
INFORMATION_SCHEMA views ... and maybe he can even turn off public
read access to pg_catalog in his own installation ... but he should
not expect us to accept a patch that makes that the default anytime
in the foreseeable future.
regards, tom lane
Tom Lane wrote: > But having said that, I do not foresee being able to replace direct > pg_catalog access with INFORMATION_SCHEMA views anytime soon. There > are too many clients out there that are used to doing it that way. > > Moreover, pg_dump will never be able to work off INFORMATION_SCHEMA, > because it needs to get at Postgres-specific information that will > not be available from a spec-compliant set of views. I'm fairly > dubious about converting psql, even. > > Rod's welcome to work on developing a set of spec-compliant > INFORMATION_SCHEMA views ... and maybe he can even turn off public > read access to pg_catalog in his own installation ... but he should > not expect us to accept a patch that makes that the default anytime > in the foreseeable future. Yes, it would be nice to have spec-compliant stuff. However, things like psql really get into those catalogs and grab detailed information that is probably not covered the the spec. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
For the non-spec compliant stuff, I was going to add various pg_ views to accomodate it, but with the spirit of the spec. That is, users can only see catalog entries which they have access to, and can only view definitions of entries that they have ownership of. Anyway, I got the feedback I wanted so I'll start puttering away at it. Theres a number of minor things missing or slightly out of whack which I hope to add as well. Timestamps on trigger creation, access levels on data types, etc. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: "Peter Eisentraut" <peter_e@gmx.net>; "Rod Taylor" <rbt@zort.ca>; "Hackers List" <pgsql-hackers@postgresql.org> Sent: Sunday, April 14, 2002 10:38 PM Subject: Re: [HACKERS] Security Issue.. > Tom Lane wrote: > > But having said that, I do not foresee being able to replace direct > > pg_catalog access with INFORMATION_SCHEMA views anytime soon. There > > are too many clients out there that are used to doing it that way. > > > > Moreover, pg_dump will never be able to work off INFORMATION_SCHEMA, > > because it needs to get at Postgres-specific information that will > > not be available from a spec-compliant set of views. I'm fairly > > dubious about converting psql, even. > > > > Rod's welcome to work on developing a set of spec-compliant > > INFORMATION_SCHEMA views ... and maybe he can even turn off public > > read access to pg_catalog in his own installation ... but he should > > not expect us to accept a patch that makes that the default anytime > > in the foreseeable future. > > Yes, it would be nice to have spec-compliant stuff. However, things > like psql really get into those catalogs and grab detailed information > that is probably not covered the the spec. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Tom Lane writes: > Peter Eisentraut <peter_e@gmx.net> writes: > > For instance, if you create a view > > CREATE VIEW bar AS SELECT * FROM foo; > > then the statement > > SELECT * FROM bar; > > needs privileges to read "foo". > > This works just fine, thank you: the privileges are checked against the > owner of the view. OK, nevermind. The case I was referring to was that the CREATE VIEW statement succeeds and the privileges are checked when the view is queried. This is not in compliance with SQL, but it doesn't seem to matter that much. -- Peter Eisentraut peter_e@gmx.net