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.



Sun, 14 Apr 2002 21:26:16 -0400
"Rod Taylor" <rbt@zort.ca>
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

Sun, 14 Apr 2002 21:33:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Security Issue..
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
>


Sun, 14 Apr 2002 21:42:19 -0400
"Rod Taylor" <rbt@zort.ca>
Re: 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


Sun, 14 Apr 2002 21:45:13 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: Security Issue..
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
>


Sun, 14 Apr 2002 22:12:29 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Security Issue..
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

Sun, 14 Apr 2002 22:33:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 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

Sun, 14 Apr 2002 22:38:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Security Issue..
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
>


Sun, 14 Apr 2002 22:52:53 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Security Issue..
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


Mon, 15 Apr 2002 00:15:47 -0400 (EDT)
Peter Eisentraut <peter_e@gmx.net>
Re: Security Issue.. 