Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5UC3Ca06329 for ; Sat, 30 Jun 2001 08:03:12 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 2.12 #1) id 15GJTq-000MkA-0V; Sat, 30 Jun 2001 13:04:06 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 9938E12B3B; Sat, 30 Jun 2001 12:49:21 +0100 (BST) Message-ID: <00b701c1015a$b330f8a0$1101a8c0@archonet.com> From: "Richard Huxton" To: "Dado Feigenblatt" , References: <200106291855.f5TItja06398@postgresql.org> <3B3CD71D.D35F928@wildbrain.com> Subject: Re: creating variable views Date: Sat, 30 Jun 2001 12:49:16 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/476 X-Sequence-Number: 3779 From: "Dado Feigenblatt" > Hi. New to the list. Welcome aboard :-) > I'm building a database that will hold data for many different projects. > Some people, or groups of people, will have access to just the rows of data of their > projects. > Some are very granular. Let's use for this example the data about the people itself. > Other than the administrators, I want people to see only their own data. > Instead of creating a view for each person, is it possible to create a single view with > variable data? > > CREATE VIEW user_info AS > SELECT * FROM users > WHERE user_name = pg_user > > where pg_user is the user name that person used to log into the database. > Is there a way to get the user name in Postgresql? > Even if the variable pg_user is not available, > is it possible to create views using variables like that? Nice idea, and seems to work: richardh=> \c richardh richardh You are now connected to database richardh as user richardh. richardh=> \d usertest Table "usertest" Attribute | Type | Modifier -----------+-----------------------+---------- username | character varying(64) | not null num | integer | Index: usertest_name_idx richardh=> \d utview View "utview" Attribute | Type | Modifier -----------+-----------------------+---------- username | character varying(64) | num | integer | View definition: SELECT usertest.username, usertest.num FROM usertest WHERE (name(usertest.username) = "current_user"()); richardh=> select * from usertest; username | num ----------+----- richardh | 1 andy | 2 (2 rows) richardh=> select * from utview; username | num ----------+----- richardh | 1 (1 row) richardh=> \c richardh andy You are now connected to database richardh as user andy. richardh=> select * from usertest; ERROR: usertest: Permission denied. richardh=> select * from utview; username | num ----------+----- andy | 2 (1 row) richardh=> select version(); version ------------------------------------------------------------- PostgreSQL 7.1.1 on i586-pc-linux-gnu, compiled by GCC 2.96 Interesting (to me anyway) to note that the view definition is based on a compiled query, not what I typed since I used "username::name" in the cast and CURRENT_USER rather than current_user() HTH - Richard Huxton