Re: creating variable views - Mailing list pgsql-sql

From: "Richard Huxton" <dev@archonet.com>

> From: "Dado Feigenblatt" <dado@wildbrain.com>
>
> > 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:

Thanks :)

> 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"());

CURRENT_USER !  That's how it's called, uh?

> 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

I remember reading somewhere that these queries would be compiled,
improving performance on views a little bit.

Funny that current_user is a function, but one should not append the ()'s to
it.
And how did you know to type cast username::name ?  Tricks of the trade?
It says in the documentation that

"The name type exists only for storage of internal catalog names and is not
intended for use by the general user. "

Well, it looks like I can replace 'current_user' by any function and not
have to worry about creating views every time I get a new user, group, or
project. That's very good! Unless... is there any current_group() ?
How do I find which group a user belongs to? Hmmm... maybe I don't need
that/

Lastly, any pointer on how people go about managing that kind of access? I
mean, gazzillions of tables with related data spread all over, and many
different groups of people with different access levels to certain rows on
certain tables?

That's it for now.
Thanks a lot.

Dado Feigenblatt.
dado@wildbrain.com