X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4252BD1B7BC for ; Tue, 15 Jun 2004 23:57:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08558-10 for ; Wed, 16 Jun 2004 02:57:49 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id DBE76D1B7C1 for ; Tue, 15 Jun 2004 23:57:44 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5G2vjeF016050; Tue, 15 Jun 2004 22:57:45 -0400 (EDT) To: Brijesh Shrivastav Cc: "Pglibpq (E-mail)" Subject: Re: Filtering tables based on user privileges In-reply-to: <491DC5F3D279CD4EB4B157DDD62237F401ADE49B@zipwire.esri.com> References: <491DC5F3D279CD4EB4B157DDD62237F401ADE49B@zipwire.esri.com> Comments: In-reply-to Brijesh Shrivastav message dated "Tue, 15 Jun 2004 15:51:26 -0700" Date: Tue, 15 Jun 2004 22:57:44 -0400 Message-ID: <16049.1087354664@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/41 X-Sequence-Number: 4969 Brijesh Shrivastav writes: > After researching again I have come with following syntax to select all > table with specific privilege (SELECT/INSERT/UPDATE..)to current user. > SELECT relname > FROM pg_class > WHERE pg_table_is_visible(oid) > AND relname !~ '^pg_' > AND has_table_privilege (oid,'INSERT'); > Is this the best way to do it? You likely also want to filter on relkind, but something about like this is probably a reasonable starting point. > Is there any way to execute such > a statement for any user? Sure, see the three-parameter variants of has_table_privilege. > In this case such queries will most > likely be executed by a super user. A superuser will always be considered to have all privileges, so the has_table_privilege filter will be a no-op for such a one. regards, tom lane