Thread: Hot to restrict access to subset of data
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.
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/
On Fri, Jul 01, 2005 at 08:46:04 -0600, Michael Fuhr <mike@fuhr.org> 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.
"Michael Fuhr" <mike@fuhr.org> wrote in message news:20050701144604.GA14542@winnie.fuhr.org... > 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.
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
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/
> 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
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" <gsyoungblood@mac.com> wrote in message news:CB2AF562-2A4D-4A9C-BC2A-E55C9029FB56@mac.com... >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 >
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" <gsyoungblood@mac.com> wrote in message > news:CB2AF562-2A4D-4A9C-BC2A-E55C9029FB56@mac.com... > >> 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 >
On 7/4/05, Gregory Youngblood <pgcluster@netio.org> 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
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
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" <gsyoungblood@mac.com> wrote in message news:CB2AF562-2A4D-4A9C-BC2A-E55C9029FB56@mac.com...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
Samuel Thoraval <samuel.thoraval@librophyt.com> 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
Tom Lane a écrit :
I am running verison 7.4.1 . Thanks for the answer. I will update (and read the release notes ;-) ).Samuel Thoraval <samuel.thoraval@librophyt.com> 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
Cheers,
Sam