Hot to restrict access to subset of data

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Hot to restrict access to subset of data

andrus-5
I have table of documents

CREATE TABLE document ( DocumentType CHARACTER(1), .... )

DocumentType field determines the document type stored in record.

I want to restrict access to this table based on the user name, document
type and access level. I have 3 levels: no access, view only, modify access.

Example:

User A can only view documents of type X and modify documents of type Y
User B can only view documents of type Z

I created application which implements those restictions.

Unfortunately, users can run pgAdmin and bypass the access restrictions.

I'm thinking about two solutions:

1. Postgres should automatically modify WHERE clauses to apply restrictions
based on user name.

For example, if user A runs query from pgAdmin

SELECT * FROM document

Postgres should actually run the query

SELECT * FROM document WHERE DocumentType IN ('X','Y')

2. Postgres should allow access from my application only. Is it possible to
use authentication method which allows access from my application only ?

Users connect to 5432 port from internet.
I'm using Postgres 8 in Windows from Windows ODBC clients.

Any idea how to implement this ?

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Michael Fuhr
On Fri, Jul 01, 2005 at 01:56:41PM +0300, Andrus wrote:
>
> I want to restrict access to this table based on the user name, document
> type and access level. I have 3 levels: no access, view only, modify access.
>
> Example:
>
> User A can only view documents of type X and modify documents of type Y
> User B can only view documents of type Z

You could use a view: revoke all privileges from the table and grant
privileges to a view that selects from the table and restricts the
output based on CURRENT_USER or SESSION_USER (e.g., via a join with
a permissions table).  For updates you could create a rule on the
view; see "The Rule System" in the documentation for more information.

> 2. Postgres should allow access from my application only. Is it possible to
> use authentication method which allows access from my application only ?

You could have the application connect to the database as a particular
user and grant permissions on the table only to that user.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Gregory Youngblood-2
In reply to this post by andrus-5
I believe you can probably use views to accomplish this.

You create a view that is populated based on their username. Then you  
remove access to the actual table, and grant access to the view.

When people look at the table, they will only see the data in the  
view and will not have access to the other.

Of course, this assumes they do not need to update the data. I've not  
played around with rules to make a view allow updates. I believe it  
is possible, I've just not done it yet. This also assumes you have  
data somewhere that maps user names to document types.

The postgresql docs should provide the syntax and additional details  
if you want to try this. I have also found pgAdmin very useful to  
create views and other schema related activities as well.

Hope this helps,
Greg



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Bruno Wolff III
In reply to this post by Michael Fuhr
On Fri, Jul 01, 2005 at 08:46:04 -0600,
  Michael Fuhr <[hidden email]> wrote:
> On Fri, Jul 01, 2005 at 01:56:41PM +0300, Andrus wrote:
>
> > 2. Postgres should allow access from my application only. Is it possible to
> > use authentication method which allows access from my application only ?
>
> You could have the application connect to the database as a particular
> user and grant permissions on the table only to that user.

Note, that will not put much of a hurdle in front of people who are running
the application on a machine they have physical access to. So that may
not be an acceptible solution for you.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

andrus-5
In reply to this post by Michael Fuhr

"Michael Fuhr" <[hidden email]> wrote in message
news:[hidden email]...

> On Fri, Jul 01, 2005 at 01:56:41PM +0300, Andrus wrote:
>>
>> I want to restrict access to this table based on the user name, document
>> type and access level. I have 3 levels: no access, view only, modify
>> access.
>>
>> Example:
>>
>> User A can only view documents of type X and modify documents of type Y
>> User B can only view documents of type Z
>
> You could use a view: revoke all privileges from the table and grant
> privileges to a view that selects from the table and restricts the
> output based on CURRENT_USER or SESSION_USER (e.g., via a join with
> a permissions table).  For updates you could create a rule on the
> view; see "The Rule System" in the documentation for more information.
>
>> 2. Postgres should allow access from my application only. Is it possible
>> to
>> use authentication method which allows access from my application only ?
>
> You could have the application connect to the database as a particular
> user and grant permissions on the table only to that user.

Thank you. I'm thinking about following approach:

My application connects to Postgres always as superuser, using user name
postgres.
Postgres server as only one user.
Actual users names of users who can access data are stored in special table.
Since only my application knows the super-user password, the users can only
access data
throught my application. My application implements desired level of security
by allowing only pre-defined queries to be run by particular user.

Is this approach secure and better ?

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Michael Fuhr
On Fri, Jul 01, 2005 at 09:43:34PM +0300, Andrus wrote:
>
> My application connects to Postgres always as superuser, using user name
> postgres.
> Postgres server as only one user.

Does the application really need superuser privileges or is that
just a convenience?  It's usually a good idea to follow the "Principle
of Least Privilege" -- do some searches on that phrase to learn
more about it and the rationale for following it.

> Actual users names of users who can access data are stored in special table.
> Since only my application knows the super-user password, the users can only
> access data
> throught my application. My application implements desired level of security
> by allowing only pre-defined queries to be run by particular user.
>
> Is this approach secure and better ?

Whether this approach is "secure and better" depends on the application
requirements, the threat model, how well the application is written,
etc.  As Bruno pointed out, if users have enough access to the
system that they could discover the account name and password, then
they could easily bypass the application's security.  Another
potential problem is SQL injection: if the application isn't careful
with how it handles user input, then specially-crafted data could
result in the pre-defined queries doing more than intended.  You'll
have to evaluate the risks and benefits of the various approaches
in the context of your own environment; there's no universal "this
way is better" answer.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Andrus Moor-2
> Does the application really need superuser privileges or is that
> just a convenience?  It's usually a good idea to follow the "Principle
> of Least Privilege" -- do some searches on that phrase to learn
> more about it and the rationale for following it.

> Whether this approach is "secure and better" depends on the application
> requirements, the threat model, how well the application is written,
> etc.  As Bruno pointed out, if users have enough access to the
> system that they could discover the account name and password, then
> they could easily bypass the application's security.  Another
> potential problem is SQL injection: if the application isn't careful
> with how it handles user input, then specially-crafted data could
> result in the pre-defined queries doing more than intended.  You'll
> have to evaluate the risks and benefits of the various approaches
> in the context of your own environment; there's no universal "this
> way is better" answer.

My application is general purpose accounting and sales application. If
database does not exists, it prompts user and creates new database containig
some hundreds of tables and
upsizes local data to database.
Each database can have a lot of schemas. Each schema represents a single
company. All those schemas have exactly the same tables, each schema
contains 80 tables.
In public schema I store tables common for all companies (60 tables).

So I seems that my application needs to be run with super-user privileges in
Postgres.

Andrus



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Andrus Moor-2
In reply to this post by Gregory Youngblood-2
Greg,

using views would be nice.

I have also a add privilege which allows to add only new documents. I think
that this requires writing triggers in Postgres.

This seems to be a lot of work.
I do'nt have enough knowledge to implement this in Postgres.

So it seems to more reasonable to run my application as Postgres superuser
and implement security in application.

Andrus.

"Gregory Youngblood" <[hidden email]> wrote in message
news:[hidden email]...

>I believe you can probably use views to accomplish this.
>
> You create a view that is populated based on their username. Then you
> remove access to the actual table, and grant access to the view.
>
> When people look at the table, they will only see the data in the  view
> and will not have access to the other.
>
> Of course, this assumes they do not need to update the data. I've not
> played around with rules to make a view allow updates. I believe it  is
> possible, I've just not done it yet. This also assumes you have  data
> somewhere that maps user names to document types.
>
> The postgresql docs should provide the syntax and additional details  if
> you want to try this. I have also found pgAdmin very useful to  create
> views and other schema related activities as well.
>
> Hope this helps,
> Greg
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Gregory Youngblood
I would strongly suggest that you create a database specific user,  
one that has read/write access within this database, and that your  
application use that user instead of the pg super user.

In general, the "super user" should never be used, except for  
specific administrative tasks. This holds true for Windows  
Administrator, Unix root, and postgresql's postgres users. If your  
application runs under a single user to the database, then that  
single user should be one that you create specifically for that  
purpose, and not the postgres user.

Greg

On Jul 3, 2005, at 1:19 PM, Andrus Moor wrote:

> Greg,
>
> using views would be nice.
>
> I have also a add privilege which allows to add only new documents.  
> I think
> that this requires writing triggers in Postgres.
>
> This seems to be a lot of work.
> I do'nt have enough knowledge to implement this in Postgres.
>
> So it seems to more reasonable to run my application as Postgres  
> superuser
> and implement security in application.
>
> Andrus.
>
> "Gregory Youngblood" <[hidden email]> wrote in message
> news:[hidden email]...
>
>> I believe you can probably use views to accomplish this.
>>
>> You create a view that is populated based on their username. Then you
>> remove access to the actual table, and grant access to the view.
>>
>> When people look at the table, they will only see the data in the  
>> view
>> and will not have access to the other.
>>
>> Of course, this assumes they do not need to update the data. I've not
>> played around with rules to make a view allow updates. I believe  
>> it  is
>> possible, I've just not done it yet. This also assumes you have  data
>> somewhere that maps user names to document types.
>>
>> The postgresql docs should provide the syntax and additional  
>> details  if
>> you want to try this. I have also found pgAdmin very useful to  
>> create
>> views and other schema related activities as well.
>>
>> Hope this helps,
>> Greg
>>
>>
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>>
>
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Dawid Kuroczko
On 7/4/05, Gregory Youngblood <[hidden email]> wrote:

> I would strongly suggest that you create a database specific user,
> one that has read/write access within this database, and that your
> application use that user instead of the pg super user.
>
> In general, the "super user" should never be used, except for
> specific administrative tasks. This holds true for Windows
> Administrator, Unix root, and postgresql's postgres users. If your
> application runs under a single user to the database, then that
> single user should be one that you create specifically for that
> purpose, and not the postgres user.

Exactly.  And the reasons are quite important also.  PostgreSQL
superuser has right to run unsecure scripts.  Let's assume the
unlikely situation that someone finds a hole in your page which
will allow her to do some SQL injections/etc.The "normal" user
is limited to what that user can do.  In your case, probably wipe
out much of data.  But superuser has right to make scripts
which are unsafe.  In other words -- has right to execute almost
any command in name of UNIX postgres user. If abuser is skillful,
she can run some local root exploit and gain root priveleges,
assuming there is some local hole open.  When using "normal"
user (who owns all the tables and so on; so is not limited from
point of view of application), it would be (much) harder for her
to gain such an access.

   Regards,
      Dawid

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Guy Fraser
In reply to this post by Andrus Moor-2
On Sun, 2005-03-07 at 23:14 +0300, Andrus Moor wrote:

> > Does the application really need superuser privileges or is that
> > just a convenience?  It's usually a good idea to follow the "Principle
> > of Least Privilege" -- do some searches on that phrase to learn
> > more about it and the rationale for following it.
>
> > Whether this approach is "secure and better" depends on the application
> > requirements, the threat model, how well the application is written,
> > etc.  As Bruno pointed out, if users have enough access to the
> > system that they could discover the account name and password, then
> > they could easily bypass the application's security.  Another
> > potential problem is SQL injection: if the application isn't careful
> > with how it handles user input, then specially-crafted data could
> > result in the pre-defined queries doing more than intended.  You'll
> > have to evaluate the risks and benefits of the various approaches
> > in the context of your own environment; there's no universal "this
> > way is better" answer.
>
> My application is general purpose accounting and sales application. If
> database does not exists, it prompts user and creates new database containig
> some hundreds of tables and
> upsizes local data to database.
> Each database can have a lot of schemas. Each schema represents a single
> company. All those schemas have exactly the same tables, each schema
> contains 80 tables.
> In public schema I store tables common for all companies (60 tables).
>
> So I seems that my application needs to be run with super-user privileges in
> Postgres.
>
> Andrus
I am quite sure that you can use a non super-user account and still
work with different schemas.

First thing I would do in your case is determine who should have
access to PgAdmin, and create restricted-users for each of them.

Next I would remove all privileges, then specifically grant access
to the action required on any specific table to your application.

If your application needs more privileges under special
circumstances then have a higher privileged user defined to
allow those changes. I have some applications that have
a couple of user levels defined. I have also built a php
interface for one customer that used postgresql to store
user accounts and session information. It should be possible
to extend that type of system to use the authenticated user
as the application user, but depending on how many users
simultaneously connect, you may run into a problem due to
too many open connections. If you don't use a separate PG
user for each user, you can use views as stipulated by others.
The program I wrote used a hierarchal access system and each
record had a userid and privilege level associated with it.
In that system users were stored in a hierarchal lookup table
using id's and the specific information for the user was held
in a contact table, so that a real person could belong to more
than one organization without having to be redefined. The
privilege was basically ; private, supervisor, peer, subordinates
and public. The permission levels a user was allowed to
access and assign were defined in the hierarchal lookup table.

I hope that helps. I have another similar but much larger
project I have been mulling over, that will require this
same kind of granularity, and due to privacy concerns, I will
need to use all the tricks I have used before and maybe even
some new ones.

Good Luck


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Samuel Thoraval
In reply to this post by Andrus Moor-2

I have been trying to use views to restrict access to a subset of data as stated :

Using Andrus's example for user B with document in public schema :

REVOKE ALL FROM public.document;

CREATE SCHEMA b AUTHORIZATION b;
CREATE VIEW b.document AS SELECT * FROM public.document WHERE DocumentType = 'Z';
GRANT SELECT ON b.document TO b;

This way when user B connects, with its search_path variable properly set, he will see datas from view b.document instead of from table public.document.

But let's say we also want user B being able to update VIEW b.document ? Then we'd have to grant UPDATE privilege and define a RULE :

-- GRANT UPDATE ON b.document TO b;  let's try without it
CREATE RULE document_b AS ON UPDATE TO b.document DO INSTEAD
UPDATE public.document set bla bla bla where bla bla bla...

I have been trying this example not executing the GRANT UPDATE statement at first to check that user b doesn't have the right to update. The problem is that even though B was not granted the update privilege, it worked anyway. In other words, simply executing " GRANT SELECT ON b.document TO b;" is sufficient for user b to be able to update the view, and thus the public.document table for DocumentType = Z.

Anybody has an explanation to this ?

Sam

Andrus Moor a écrit :
Greg,

using views would be nice.

I have also a add privilege which allows to add only new documents. I think 
that this requires writing triggers in Postgres.

This seems to be a lot of work.
I do'nt have enough knowledge to implement this in Postgres.

So it seems to more reasonable to run my application as Postgres superuser 
and implement security in application.

Andrus.

"Gregory Youngblood" [hidden email] wrote in message 
<a class="moz-txt-link-freetext" href="news:CB2AF562-2A4D-4A9C-BC2A-E55C9029FB56@mac.com">news:CB2AF562-2A4D-4A9C-BC2A-E55C9029FB56@......
  
I believe you can probably use views to accomplish this.

You create a view that is populated based on their username. Then you 
remove access to the actual table, and grant access to the view.

When people look at the table, they will only see the data in the  view 
and will not have access to the other.

Of course, this assumes they do not need to update the data. I've not 
played around with rules to make a view allow updates. I believe it  is 
possible, I've just not done it yet. This also assumes you have  data 
somewhere that maps user names to document types.

The postgresql docs should provide the syntax and additional details  if 
you want to try this. I have also found pgAdmin very useful to  create 
views and other schema related activities as well.

Hope this helps,
Greg



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

    



  

Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Tom Lane-2
Samuel Thoraval <[hidden email]> writes:
> I have been trying this example not executing the GRANT UPDATE statement
> at first to check that user b doesn't have the right to update. The
> problem is that even though B was not granted the update privilege, it
> worked anyway. In other words, simply executing " GRANT SELECT ON
> b.document TO b;" is sufficient for user b to be able to update the
> view, and thus the public.document table for DocumentType = Z.

> Anybody has an explanation to this ?

What PG version are you running?  This item from the 7.3.6 release notes
seems relevant:

     Revert erroneous changes in rule permissions checking

     A patch applied in 7.3.3 to fix a corner case in rule permissions
     checks turns out to have disabled rule-related permissions checks
     in many not-so-corner cases. This would for example allow users to
     insert into views they weren't supposed to have permission to
     insert into. We have therefore reverted the 7.3.3 patch. The
     original bug will be fixed in 8.0.

The first couple of 7.4.x releases had the bug too.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Hot to restrict access to subset of data

Samuel Thoraval



Tom Lane a écrit :
Samuel Thoraval [hidden email] writes:
  
I have been trying this example not executing the GRANT UPDATE statement 
at first to check that user b doesn't have the right to update. The 
problem is that even though B was not granted the update privilege, it 
worked anyway. In other words, simply executing " GRANT SELECT ON 
b.document TO b;" is sufficient for user b to be able to update the 
view, and thus the public.document table for DocumentType = Z.
    

  
Anybody has an explanation to this ?
    

What PG version are you running?  This item from the 7.3.6 release notes
seems relevant:

     Revert erroneous changes in rule permissions checking

     A patch applied in 7.3.3 to fix a corner case in rule permissions
     checks turns out to have disabled rule-related permissions checks
     in many not-so-corner cases. This would for example allow users to
     insert into views they weren't supposed to have permission to
     insert into. We have therefore reverted the 7.3.3 patch. The
     original bug will be fixed in 8.0.

The first couple of 7.4.x releases had the bug too.

			regards, tom lane

  
I am running verison 7.4.1 . Thanks for the answer. I will update (and read the release notes ;-) ).

Cheers,

Sam