Obtaining information on the schema of tables which I do not own

[Daniel Ariel]

Hi all,

We are currently developing an application which needs to know
information about table schema in order to display them correctly
(specificly foreign keys, default values and not null characteristics).

For reasons of security I would prefer for it not to run as superuser.

I may often wish to display tables which I do not own and potentially
not even have anything other than SELECT permissions to. I will need the
schema information for these tables.

It is desirable since the app needs to also work with other DB's
besides postgres, to implement the requirements using as standard a
technique as possible, and consequently the views inside
information_schema look to me to be the best option. The problem is
that as best as I can tell from experimentation and reading the
documentation, I am only given information on tables owned by the
current user.

Is there any way to achieve I want without being forced to use the
postgres-specific system tables ?

Your assistance is much appreciated,

Daniel

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Daniel Ariel]

Dear list,

I am currently developing an application which needs to know information

about table schema in order to display them correctly (specificly
foreign keys, default values and not null characteristics).

For security reasons I would prefer for it not to run as superuser.

I may often wish to display tables which I do not own and potentially
not even have anything other than SELECT permissions to. I will need the
schema information for these tables.

It is desirable since the app needs to also work with other DB's
besides postgres, to implement the requirements using as standard a
technique as possible, and consequently the views inside
information_schema look to me to be the best option. The problem is
that as best as I can tell from experimentation and reading the
documentation, I am only given information on tables owned by the
current user.

Is there any way to achieve I want without being forced to use the
postgres-specific system tables ?

Your assistance is much appreciated,

Daniel

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Bruno Wolff III]

On Mon, Dec 12, 2005 at 23:02:52 +0200,
Daniel Ariel <pgsqlin...@netzach.co.il> wrote:
>
> Dear list,
>
> I am currently developing an application which needs to know information
> about table schema in order to display them correctly (specificly
> foreign keys, default values and not null characteristics).
>
> For security reasons I would prefer for it not to run as superuser.

You don't have to be a superuser to see the catalog tables. In fact people
sometimes complain about that.

>
> I may often wish to display tables which I do not own and potentially
> not even have anything other than SELECT permissions to. I will need the
> schema information for these tables.

Probably you should start by seeing if the information schema provides what
you need:
http://developer.postgresql.org/docs/postgres/information-schema.html

If you need more than that, then you probably want to look at the documentation
for the system catalogs:
http://developer.postgresql.org/docs/postgres/catalogs.html

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Tom Lane]

Bruno Wolff III <br...@wolff.to> writes:

> Daniel Ariel <pgsqlin...@netzach.co.il> wrote:
>> I am currently developing an application which needs to know information
>> about table schema in order to display them correctly (specificly
>> foreign keys, default values and not null characteristics).

> Probably you should start by seeing if the information schema provides what
> you need:
> http://developer.postgresql.org/docs/postgres/information-schema.html

I think what he was complaining about was that information_schema
restricts access to many things unless you are the table owner.
This is per SQL99, but SQL2003 seems to have relaxed the rules to
allow you to see info about tables you can access (ie, have nonzero
rights for). Updating the information_schema to follow the SQL2003
rules is on the to-do list, but I dunno if anyone is actively working
on it. (Peter?)

regards, tom lane

[Daniel Ariel]

> I think what he was complaining about was that information_schema
> restricts access to many things unless you are the table owner.
> This is per SQL99, but SQL2003 seems to have relaxed the rules to
> allow you to see info about tables you can access (ie, have nonzero
> rights for). Updating the information_schema to follow the SQL2003
> rules is on the to-do list, but I dunno if anyone is actively working
> on it. (Peter?)

Tom's interpretation is correct.

Is the required work to update to SQL2003 merely an update of the SQL of
the views in information_schema ? If it would expedite matters I would
be willing to help modify the SQL accordingly.

Daniel

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Tom Lane]

Daniel Ariel <pgsqlin...@netzach.co.il> writes:
> Is the required work to update to SQL2003 merely an update of the SQL of
> the views in information_schema ? If it would expedite matters I would
> be willing to help modify the SQL accordingly.

Possibly that's all that's needed, or maybe we need some changes to the
C-code functions that provide the protection-checking infrastructure for
the information_schema views. The first step is to analyze exactly what
changed between the SQL99 and SQL2003 definitions of these views, and
then look at how this ought to fit in with Postgres' security features
(which are not 100% identical to what the spec thinks...). If you've
got some time to spend on this, by all means hop aboard.

regards, tom lane

---------------------------(end of broadcast)---------------------------

[Daniel Ariel]

> > Is the required work to update to SQL2003 merely an update of the SQL of
> > the views in information_schema ? If it would expedite matters I would
> > be willing to help modify the SQL accordingly.
> Possibly that's all that's needed, or maybe we need some changes to the
> C-code functions that provide the protection-checking infrastructure for
> the information_schema views. The first step is to analyze exactly what
> changed between the SQL99 and SQL2003 definitions of these views, and
> then look at how this ought to fit in with Postgres' security features
> (which are not 100% identical to what the spec thinks...). If you've
> got some time to spend on this, by all means hop aboard.

OK. How do I obtain the SQL99/2003 specification without paying money ?

Daniel

---------------------------(end of broadcast)---------------------------

[Daniel Ariel]

OK, if I understand things correctly, comparing the
CONSTRAINT_COLUMN_USAGE view:

SQL2003 late draft:
WHERE ( SCHEMA_OWNER = CURRENT_USER
OR
SCHEMA_OWNER IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) )

PG 7.4.7:
WHERE pg_class.relowner=pg_user.usesysid
AND pg_user.usename=current_user()

What PG should be, in order to meet the SQL2003 standard:
WHERE pg_class.relowner=pg_user.usesysid
AND (pg_user.usename=current_user() OR
pg_user IN (SELECT role_name FROM
information_schema.enabled_roles)
)

I cannot comment on other changes between SQL92 and 2003 until somebody
can point me in the direction of the SQL92 spec.

Please note that my quotes are based on the SQL as extracted from the
views in the database, not those in CVS.

Daniel

---------------------------(end of broadcast)---------------------------

[Tom Lane]

Daniel Ariel <pgsqlin...@netzach.co.il> writes:
> OK. How do I obtain the SQL99/2003 specification without paying money ?

Look in our developer's FAQ

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Daniel Ariel <pgsqlin...@netzach.co.il> writes:
> OK, if I understand things correctly, comparing the
> CONSTRAINT_COLUMN_USAGE view:

> PG 7.4.7:

7.4.7 is irrelevant to this discussion; you should be looking at PG 8.1
if not CVS tip. There were already extensive changes for ROLE support
in information_schema.

regards, tom lane

[Jim C. Nasby]

On Thu, Dec 15, 2005 at 09:28:15AM +0200, Daniel Ariel wrote:
> > I think what he was complaining about was that information_schema
> > restricts access to many things unless you are the table owner.
> > This is per SQL99, but SQL2003 seems to have relaxed the rules to
> > allow you to see info about tables you can access (ie, have nonzero
> > rights for). Updating the information_schema to follow the SQL2003
> > rules is on the to-do list, but I dunno if anyone is actively working
> > on it. (Peter?)
>
> Tom's interpretation is correct.
>
> Is the required work to update to SQL2003 merely an update of the SQL of
> the views in information_schema ? If it would expedite matters I would
> be willing to help modify the SQL accordingly.

Some of the code in http://pgfoundry.org/projects/newsysviews/ might be
of use to you...
--
Jim C. Nasby, Sr. Engineering Consultant jna...@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match