pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedObtaining information on the schema of tables which I do not own
11+ messages / 4 participants
[nested] [flat]
* Obtaining information on the schema of tables which I do not own
@ 2005-12-12 21:02 Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Ariel @ 2005-12-12 21:02 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which I do not own
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
@ 2005-12-14 17:39 ` Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Bruno Wolff III @ 2005-12-14 17:39 UTC (permalink / raw)
To: Daniel Ariel <pgsqlinterfaces@netzach.co.il>; +Cc: pgsql-interfaces
On Mon, Dec 12, 2005 at 23:02:52 +0200,
Daniel Ariel <pgsqlinterfaces@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which I do not own
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
@ 2005-12-14 18:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-12-14 18:08 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Daniel Ariel <pgsqlinterfaces@netzach.co.il>; pgsql-interfaces@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Bruno Wolff III <bruno@wolff.to> writes:
> Daniel Ariel <pgsqlinterfaces@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-15 07:28 ` Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 07:13 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 23:09 ` Re: Obtaining information on the schema of tables which Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 11+ messages in thread
From: Daniel Ariel @ 2005-12-15 07:28 UTC (permalink / raw)
To: pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
@ 2005-12-15 07:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 10:50 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 13:27 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
1 sibling, 2 replies; 11+ messages in thread
From: Tom Lane @ 2005-12-15 07:13 UTC (permalink / raw)
To: Daniel Ariel <pgsqlinterfaces@netzach.co.il>; +Cc: pgsql-interfaces
Daniel Ariel <pgsqlinterfaces@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 07:13 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-15 10:50 ` Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 14:58 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Daniel Ariel @ 2005-12-15 10:50 UTC (permalink / raw)
To: pgsql-interfaces
> > 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 07:13 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 10:50 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
@ 2005-12-15 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2005-12-15 14:58 UTC (permalink / raw)
To: Daniel Ariel <pgsqlinterfaces@netzach.co.il>; +Cc: pgsql-interfaces
Daniel Ariel <pgsqlinterfaces@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 07:13 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-15 13:27 ` Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 15:09 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Daniel Ariel @ 2005-12-15 13:27 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 07:13 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 13:27 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
@ 2005-12-15 15:09 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2005-12-15 15:09 UTC (permalink / raw)
To: Daniel Ariel <pgsqlinterfaces@netzach.co.il>; +Cc: pgsql-interfaces
Daniel Ariel <pgsqlinterfaces@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Obtaining information on the schema of tables which
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Re: Obtaining information on the schema of tables which I do not own Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Re: Obtaining information on the schema of tables which I do not own Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
@ 2005-12-15 23:09 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jim C. Nasby @ 2005-12-15 23:09 UTC (permalink / raw)
To: Daniel Ariel <pgsqlinterfaces@netzach.co.il>; +Cc: pgsql-interfaces
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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 11+ messages in thread
* Obtaining information on the schema of tables which I do not own
@ 2005-12-13 15:31 Daniel Ariel <pgsqlinterfaces@netzach.co.il>
0 siblings, 0 replies; 11+ messages in thread
From: Daniel Ariel @ 2005-12-13 15:31 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-12-15 23:09 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-12 21:02 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-14 17:39 ` Bruno Wolff III <bruno@wolff.to>
2005-12-14 18:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 07:28 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 07:13 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 10:50 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 14:58 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 13:27 ` Re: Obtaining information on the schema of tables which Daniel Ariel <pgsqlinterfaces@netzach.co.il>
2005-12-15 15:09 ` Re: Obtaining information on the schema of tables which Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 23:09 ` Re: Obtaining information on the schema of tables which Jim C. Nasby <jnasby@pervasive.com>
2005-12-13 15:31 Obtaining information on the schema of tables which I do not own Daniel Ariel <pgsqlinterfaces@netzach.co.il>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox