Thread: creating variable views

Hi. New to the list.

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?

Thanks.

--
Dado Feigenblatt                                 Wild Brain, Inc.
Technical Director                               (415) 553-8000 x???
dado@wildbrain.com                               San Francisco, CA.

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:

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 nullnum       | 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 |   1andy     |   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

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

Dado,

    Glad to have you with us.   Incidentally, in answer to two of your
remarks:

1. "current_user", like "current_timestamp" is a built-in SQL92
function, as opposed to a PostgreSQL function.  As such, it does not
require () and takes no parameters.

2. You can compile any user-defined function into a view, wherever you
want.  Be warned, though, that views with lots of user-defined functions
suffer from a certain lack of optimization as compared with pure SQL
views.  As such, I try to stick with strictly formatting functions in
views.

> 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?

Actually, in every project I've undertaken, I avoid using the built-in
DB security and create my own security interfaces.  This is because, for
an end-user program, you are concered with the user's access to
*interfaces*, not their access to *tables*.  In only two occasions can I
imagine DB security making any sense for a user application:

1. The users are SQL experts and want to run their own queires, and have
to be kept away from specific sensitive data.

2. The users have access to certain 3rd-party tools that need to bypass
the regular interface (e.g. IQ Reports, FRx) and the database contains
specific tables of sensitive data.

Additionally, it must be noted that trying to combine a Web interface
with specfic user DB logins eliminates all ability to pool DB
connections and similar web efficiency, as well as forcing you to use
SSL for any extranet app.

Otherwise, one takes these steps:

1. Create a table of users, passwords, and access levels for your app.

2. Create a second table of interfaces and special functions and the
access levels required to reach them.

3. Build your interface so that it connects to the database using a
single super-user login which is kept encrypted and hidden from the
user.

4. In the interface, before letting the user open each screen or run
each function, check their user access against the tables in 1. and 2.

This works quite well for me.  It's a *lot* easier to adjust than DB
level security ("all of the accountants need access to the Void
function" can be fixed with a single UPDATE) and remains secure because
the *user* does not know the application password, and without it has no
access to the database at all.

-Josh Berkus

P.S. Keep up the fun cartoons!

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Josh Berkus wrote:

> Dado,
>
>         Glad to have you with us.   Incidentally, in answer to two of your
> remarks:
>
> 1. "current_user", like "current_timestamp" is a built-in SQL92
> function, as opposed to a PostgreSQL function.  As such, it does not
> require () and takes no parameters.

Hmm... I don't recall any reference to that. Thanks for the clarification.

> 2. You can compile any user-defined function into a view, wherever you
> want.  Be warned, though, that views with lots of user-defined functions
> suffer from a certain lack of optimization as compared with pure SQL
> views.  As such, I try to stick with strictly formatting functions in
> views.

Formatting functions? As in formatted output? Could you give an example?

> > 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?
>
> Actually, in every project I've undertaken, I avoid using the built-in
> DB security and create my own security interfaces.  This is because, for
> an end-user program, you are concered with the user's access to
> *interfaces*, not their access to *tables*.  In only two occasions can I
> imagine DB security making any sense for a user application:
>
> 1. The users are SQL experts and want to run their own queires, and have
> to be kept away from specific sensitive data.
>
> 2. The users have access to certain 3rd-party tools that need to bypass
> the regular interface (e.g. IQ Reports, FRx) and the database contains
> specific tables of sensitive data.
>
> Additionally, it must be noted that trying to combine a Web interface
> with specfic user DB logins eliminates all ability to pool DB
> connections and similar web efficiency, as well as forcing you to use
> SSL for any extranet app.

Ok. Looks like I agree with you in every aspect.
Better yet, it's good ammunition for me to explain here why I'm gonna do they
way you suggested.

> Otherwise, one takes these steps:
>
> 1. Create a table of users, passwords, and access levels for your app.
>
> 2. Create a second table of interfaces and special functions and the
> access levels required to reach them.
>
> 3. Build your interface so that it connects to the database using a
> single super-user login which is kept encrypted and hidden from the
> user.
>
> 4. In the interface, before letting the user open each screen or run
> each function, check their user access against the tables in 1. and 2.
>
> This works quite well for me.  It's a *lot* easier to adjust than DB
> level security ("all of the accountants need access to the Void
> function" can be fixed with a single UPDATE)

I'm sorry but I have no idea what you're talking about here.
What is this problem? What is the Void function?

> and remains secure because
> the *user* does not know the application password, and without it has no
> access to the database at all.

It definitely seems to be a much better approach to access level management.
One thing that still isn't clear for me is how to implement access level
control on a per row basis.
Perhaps by implementing a group permissions scheme, where I could combine
groups that describe a job title (and its granted permissions) with groups
that describe projects (and its required permissions).
But still, I'm not sure if I should implement that on the interface or use
views that select rows pertaining only to the user's projects.

Any pointer on that would be immensely appreciated.
But the info you already gave is invaluable.
Thanks a lot.

> -Josh Berkus
>
> P.S. Keep up the fun cartoons!

That's what I really do.
I got pulled out to create this DB just because I worked with DB's before.
10 years ago :(

--
Dado Feigenblatt                                 Wild Brain, Inc.
Technical Director                               (415) 553-8000 x???
dado@wildbrain.com                               San Francisco, CA.

Dado,
> Formatting functions? As in formatted output? Could you give an
> example?

Yes.  For example, I have a function called:

qf_format_contact_name (VARCHAR, VARCHAR, VARCHAR, VARCHAR) 

That produces (depending on data) formatted output like:

Julie Snodgrass
Ms. Keller, Law Clerk
Human Resources Director

... and I call in in views like:

CREATE VIEW lv_billing_contacts AS
SELECT client_usq, client_name, qf_format_contact_name(prefix,
last_name, first_name, contact_title) AS contact_name
FROM clients JOIN client_contacts ...

All this function does is format output, rather than perform any fancy
manipulation.  I find that the Postgres view optimizer has no trouble
with such functions.

More complex functions, like qf_calc_next_invoice_date(VARCHAR) which
calculates a client's next invoice date based on their invoice interval
plus certain system variables pretty much kills the view optimizer if I
do a WHERE on that column, since the optimizer doesn't know what to
expect from the function.



> > This works quite well for me.  It's a *lot* easier to adjust than
> DB
> > level security ("all of the accountants need access to the Void
> > function" can be fixed with a single UPDATE)
> 
> I'm sorry but I have no idea what you're talking about here.
> What is this problem? What is the Void function?

That was an example of the sort of sweeping user access change one might
be asked to implement.  For example, you might set up the system at the
start so that only the Sysadmin can "void" (cancel) financial
transactions for security purposes.  However, changes in your company's
business policies in 3 months may dictate that the whole accounting
dept.  needs to be able to void.  Using SQL DB security, this can be a
serious headache, as opposed to a single update with an interface-based
system.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Dado Feigenblatt <dado@wildbrain.com> writes:
> One thing that still isn't clear for me is how to implement access level
> control on a per row basis.

The SQL GRANT/REVOKE stuff doesn't deal with anything finer-grain than
tables.  The best way I know to cope with a need for row-level read
access control is to create a view that shows only the records you want
someone to be able to see, then grant them access to the view not the
original table.

For write access control, you have a choice of putting the controls into
the ON INSERT etc rules you make for the view, or attaching triggers to
the underlying table and checking access permissions in the triggers.
The trigger method is probably easier to deal with, but bear in mind
that such triggers will fire for everyone, including people who've been
granted direct access to the underlying table.  This might or might not
be just what you want...
        regards, tom lane

"Josh Berkus" <josh@agliodbs.com> writes:
> All this function does is format output, rather than perform any fancy
> manipulation.  I find that the Postgres view optimizer has no trouble
> with such functions.
> More complex functions, like qf_calc_next_invoice_date(VARCHAR) which
> calculates a client's next invoice date based on their invoice interval
> plus certain system variables pretty much kills the view optimizer if I
> do a WHERE on that column, since the optimizer doesn't know what to
> expect from the function.

I don't understand the distinction you're trying to make here.  In
general, a view column that is implemented as a function will give the
optimizer headaches if you refer to it in WHERE --- the simplicity or
complexity of the function has got nothing to do with that AFAICS.

> That was an example of the sort of sweeping user access change one might
> be asked to implement.  For example, you might set up the system at the
> start so that only the Sysadmin can "void" (cancel) financial
> transactions for security purposes.  However, changes in your company's
> business policies in 3 months may dictate that the whole accounting
> dept.  needs to be able to void.  Using SQL DB security, this can be a
> serious headache, as opposed to a single update with an interface-based
> system.

Seems to me that such a change could be trivial, or a serious headache,
with *either* SQL GRANT-based security or application-based security.
The critical factor is going to be whether you represented the "voiding"
access privilege separately from the other special privileges of the
sysadmin.  I don't see how one implementation is going to encourage you
to have that foresight better than the other one would.
        regards, tom lane

Tom,

> I don't understand the distinction you're trying to make here.  In
> general, a view column that is implemented as a function will give
> the
> optimizer headaches if you refer to it in WHERE --- the simplicity or
> complexity of the function has got nothing to do with that AFAICS.

OK.  I thought that I noticed a difference between simple SQL functions
and PL/pgSQL functions in this respect, but I could easily be mistaken.
> Seems to me that such a change could be trivial, or a serious
> headache,
> with *either* SQL GRANT-based security or application-based security.
> The critical factor is going to be whether you represented the
> "voiding"
> access privilege separately from the other special privileges of the
> sysadmin.  I don't see how one implementation is going to encourage
> you
> to have that foresight better than the other one would.

It's a little difficult to explain wihtout a demo.  It is possible,
using a database with many views and functions which are the primary
methods of DML and query access to develop analogous functionality using
GRANT and REVOKE on the database objects.  In fact, you *have* to do
this if your users will have command-line access to the database.
However, it's not easy.

The interface-based system I use, in its simplest incarnation, takes
into account only a 5-level user access system with no departmental user
groups.  Thus each user is: 0:No Access, 1:Read-only, 2:Data Entry,
3:Full Access, or 5:Admin.  I create a table that lists all of the
interfaces, and the required access level for each interface; if a user
fails the access test for an interface, they are denied access with a
firendly error message ("I'm sorry, you do not have sufficient access
...").

It's very simple to administrate because in order to change the access
to a particular feature on has only to change the access level number.
And the results of the access level test are much easier to trap in your
client-side code than the results of a DENY access error would be.  The
last thing I want for my users is to have to contend with "5301: No
rights on TABLE client_contacts."

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

"Josh Berkus" <josh@agliodbs.com> writes:
>> I don't understand the distinction you're trying to make here.  In
>> general, a view column that is implemented as a function will give
>> the
>> optimizer headaches if you refer to it in WHERE --- the simplicity or
>> complexity of the function has got nothing to do with that AFAICS.

> OK.  I thought that I noticed a difference between simple SQL functions
> and PL/pgSQL functions in this respect, but I could easily be mistaken.

AFAIR, the optimizer doesn't pay any attention at all to the
implementation language of a function.  The only thing that comes to
mind here is that some care has been taken to mark all the built-in
functions as "iscachable" (or not, as appropriate); but user-created
functions may not be so marked when they should be, leading to loss of
performance in some cases.
        regards, tom lane