Thread: Obtaining information on the schema of tables which I do not own
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
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
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
> 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
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
> > 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
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
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
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
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