Re: [GENERAL] User privileges in web database applications
Antonis Christofides wrote: But I think that checking user privileges at the database level is better. I think it's simpler and more secure, and if later you also want to create nonweb apps, you won't have any more authentication/privilege headaches. Couldn't agree more. But consider this reasoning as perhaps more fundamental. For a database app all security resolves to the basic permissions of a single user being allowed to insert, update, delete or select any particular row from any particular table. Every security system that is implemented on some other basis will have to be resolved down to this. So why not just implement this in the first place? Our own approach was to build security directly into the specification. Our table definitions include security definitions, which groups can do what do the table. The generator builds the security commands the same way it builds the CREATE TABLE commands. For this reason, in a web app I've made, the app connects to the database as user postgres, and after authenticating (receives user's password, checks with pg_shadow, and uses session cookie) uses set session authorization in order to lower its privileges. I've considered this. How is it working out in real life? We connect using real user credentials, and where necessary elevate to super-user, which I think is probably marginally safer but more expensive. I've even written triggers to implement row-level permissions checking. Yeah, this is cool. What kind of features have you implemented here? We've just done the very basics, not much to brag about. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] User privileges in web database applications
On Jun 29, 2006, at 5:58 PM, Tim Hart wrote: I'm coming in a bit late to this conversation, so forgive me if I've missed something. Isn't this problem the reason that connection pools were created? In a connection pool, connections are only associated with a particular user for the duration of a transaction. Once the transaction is complete, the connection goes back to the pool. Right, this is standard operating procedure. But the original poster mentioned tying the connection to a particular web user/session. In other words, one connection per user. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] User privileges in web database applications
Right, this is standard operating procedure. But the original poster mentioned tying the connection to a particular web user/session. In other words, one connection per user. Maybe I didn't phrase the question correctly, but I think that the answer to my question is, indeed, connection pooling, probably pgpool. Thanks all. -- Antonis Christofides +30-2107722840 (work) +30-2106521785 (home) +30-6979924665 (mobile) ---(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
Re: [GENERAL] User privileges in web database applications
Both connection pooling and using the superuser with SET SESSION AUTHORIZATION both have their uses. You might have an application that processes some type of transaction and inserts data into a users schema or table, but where there are no user credentials available. Then you might have a web interface for users to access that data where user credentials are available. We have this type of setup and we use a superuser with SET SESSION AUTHORIZATION for the incoming transactions.But we also have extensive security requirements that demand we do things most people don't do. Full security/code audits every quarter, peer review and full testing for any new code, hardware encryption for sensitive data and keys stored on tokens, client certificate authentication for all web access, restrictive firewall, etc.. Bottom line is that I'm paranoid about using SET SESSION AUTHORIZATION, but it does have it's uses and can be used safely. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] User privileges in web database applications
Hi, Most web database applications I've seen use a system separate from the rdbms's user database for managing user privileges. For example, there may be a users table, or there may be external authentication with, say, LDAP. Or, for example, in MoinMoin (an extensible wiki, where apps can be written as MoinMoin plugins), you can use MoinMoin's authentication system. The application does the authentication, and it also implements permissions checking. But I think that checking user privileges at the database level is better. I think it's simpler and more secure, and if later you also want to create nonweb apps, you won't have any more authentication/privilege headaches. For this reason, in a web app I've made, the app connects to the database as user postgres, and after authenticating (receives user's password, checks with pg_shadow, and uses session cookie) uses set session authorization in order to lower its privileges. I've even written triggers to implement row-level permissions checking. The benefit of this solution is that I avoid reconnecting to the database on each request, having instead a persistent connection as user postgres. One disadvantage, however, is that, since I use identd to allow the web server user (www-data) to connect as postgres, a web server compromise shall mean a compromise of the database as user postgres. I'm considering developing applications with MoinMoin, and I'd like to have a unified user database used both by the wiki and by the database. I can probably hack MoinMoin to use postgresql for authenticating. But how may I have persistent database connections without the security risk I described? Another issue is that I may have thousands of users, as is common in open web-accessible databases; could this be a problem for PostgreSQL? I'd also like your general opinion or pointer on the issue of authentication and privilege checking of web db apps; all I can find on Google is tutorials that tell you how to create a users table and do all checking at the application level. Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] User privileges in web database applications
Antonis Christofides wrote: But I think that checking user privileges at the database level is better. I think it's simpler and more secure, and if later you also want to create nonweb apps, you won't have any more authentication/privilege headaches. For this reason, in a web app I've made, the app connects to the database as user postgres, and after authenticating (receives user's password, checks with pg_shadow, and uses session cookie) uses set session authorization in order to lower its privileges. What stops the user code from issuing a RESET SESSION AUTHORIZATION command, say from a SQL injection, thus regaining superuser privileges? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] User privileges in web database applications
On Jun 29, 2006, at 3:37 AM, Antonis Christofides wrote: The benefit of this solution is that I avoid reconnecting to the database on each request, having instead a persistent connection as user postgres. But it is also a resource liability. How do you know if the user will make another request or when they are finished using your site? You can certainly time out the connection but for a busy site you will potentially be holding open a large number of connections that will never be used again. I agree with your reasons for wanting to do this, but it may not be feasible for a busy web site. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] User privileges in web database applications
I'm coming in a bit late to this conversation, so forgive me if I've missed something. Isn't this problem the reason that connection pools were created? In a connection pool, connections are only associated with a particular user for the duration of a transaction. Once the transaction is complete, the connection goes back to the pool. Pool sizes are usually configurable, and the number of actual connections in the pool can be less than the configured amount. The pool is responsible for allocating blocks of connection - up to MAX - as necessary. The pool is also responsible for guaranteeing that a connection is valid when it's requested from the pool. This may involve periodic 'pinging' of unused connections, or simply testing, and recreating if nessecary, a connection before it's reserved for use. I'm pretty sure there is a pgpool project somewhere to provide exactly this service. Tim ___ Sent with SnapperMail www.snappermail.com .. Original Message ... On Thu, 29 Jun 2006 10:19:34 -0400 John DeSoi [EMAIL PROTECTED] wrote: On Jun 29, 2006, at 3:37 AM, Antonis Christofides wrote: The benefit of this solution is that I avoid reconnecting to the database on each request, having instead a persistent connection as user postgres. But it is also a resource liability. How do you know if the user will make another request or when they are finished using your site? You can certainly time out the connection but for a busy site you will potentially be holding open a large number of connections that will never be used again. I agree with your reasons for wanting to do this, but it may not be feasible for a busy web site. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] User Privileges using dblink
On Wed, Jun 23, 2004 at 09:03:37AM +0200, Kreißl, Karsten wrote: But, under this conditions we must specify username and password (without encryption!) in the view definition. Every user can read this information using pgadmin or other tools. It's very simple ! In our environment the remote DB knows the same users as our local DB. So we are always searching for a solution, without publishing username and password. Our background is a migration from INFORMIX DB to PostgreSQL. Using INFORMIX there is a rather simple solution for this problem, called Synonyms. Personally, I think it's a very bad idea to use a different database for this kind of data exchange. I'd go with using different schemas. There's a lot more fine grained control on access privileges, and users won't be able to see any password; also, the performance should be much better. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El número de instalaciones de UNIX se ha elevado a 10, y se espera que este número aumente (UPM, 1972) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] User Privileges using dblink
I've never used dblink, but why don't you store your connection strings
in a table.
CREATE connections (id SERIAL PRIMARY KEY, conn_str text NOT NULL);
The create a function as SECURITY DEFINER that takes id as a parameter
and returns the conn_str
CREATE OR REPLACE FUNCTION get_connection(INT) RETURNS TEXT STABLE
RETURNS NULL ON NULL INPUT SECURITY DEFINER AS 'SELECT conn_str FROM
connections WHERE id = $1';
You can revoke read from that table by everyone besides the user
defining the function, then create the view as: SELECT * FROM
dblink(get_connection(5) ...);
Haven't tried it, but I hope it leads you down the right path.
Kreißl, Karsten wrote:
Hello Tom,
Ok, we have changed our authentication to password. Sorry, my mistake.
But, under this conditions we must specify username and password (without encryption!)
in the view definition.
Every user can read this information using pgadmin or other tools. It's very simple !
In our environment the remote DB knows the same users as our local DB. So we are
always searching for a solution, without publishing username and password.
Our background is a migration from INFORMIX DB to PostgreSQL. Using INFORMIX there is
a rather simple solution for this problem, called Synonyms.
Regards
Karsten
-Ursprüngliche Nachricht-
Von: Tom Lane [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 22. Juni 2004 16:05
An: Kreißl, Karsten
Cc: [EMAIL PROTECTED]
Betreff: Re: [GENERAL] User Privileges using dblink
=?iso-8859-1?Q?=22Krei=DFl=2C_Karsten=22?= [EMAIL PROTECTED] writes:
The second problem with dblink is a security hole.
create view myinst as select * from dblink('dbname=sva4_int1','select from inst') as (...);
This is not a security hole in dblink, it is a security hole in your
pg_hba.conf setup. Don't use trust authentication.
This problem could also be resolved, if dblink uses the current login
information.
That seems completely impractical. In the first place, it's not a
reasonable default (there's no good reason to assume that the remote
DB has the same users as the local), and in the second place dblink
cannot get at the user's password. (We *would* have a security hole
if it could.)
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] User privileges on sequences
Mihai Gheorghiu writes: Therefore, I have to give users permissions to sequences, just to the extent that they could add records. What is the minimum set of privileges for this? You need UPDATE privileges to be able to execute nextval() and currval(). I assume this would include setval() as well, though I don't see it in the docs offhand. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] User privileges on sequences
I have a db in which users can view and update data only via views. It is my understanding that the privileges of the view/rule creator do not extend to sequences. Therefore, I have to give users permissions to sequences, just to the extent that they could add records. What is the minimum set of privileges for this? Are SELECT privileges on sequences enough? What happens actually when a user issues a statement: SELECT nextval(sequence)? What about setval()? Do these involve only a SELECT to the sequence, or is it an UPDATE, or is it a DELETE and INSERT, or just an INSERT? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] User privileges
It is possible that a user is a member of more than one group. Do this user's privileges AND or OR the privileges of each of the two groups? Thank you all. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] User Privileges
Hello, the information can be found in the system-table-column PG_CLASS.RELACL: SELECT RELACL FROM PG_CLASS WHERE RELNAME = 't_dummy'; The result seems to be an array of granted permissions, so you should treat it is an array and look for the appropriate user- or group-name. Hope this helps ... Regards, Jens "W. van den Akker" schrieb: How do I retrieve this privilages? I want to disable menu-options within a program. For that I have to retrieve the privilages for some tables. gr, Willem - Original Message - From: "Dan Wilson" [EMAIL PROTECTED] To: "Niral Trivedi" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 26, 2000 7:25 PM Subject: Re: [GENERAL] User Privileges For example I have 5 tables in database A. And now I want to give SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according to documentation, I have to execute 'GRANT' query 3 times(for select/update/insert) per table. meaning total of 15 times That's incorrect... you can do it all in one statement: GRANT select,update,insert TO "username" ON table_1,table_2,table_3,etc http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm -Dan = Jens Hartwig - debis Systemhaus GEI mbH 10875 Berlin Tel. : +49 (0)30 2554-3282 Fax : +49 (0)30 2554-3187 Mobil: +49 (0)170 167-2648 E-Mail : [EMAIL PROTECTED] =
Re: [GENERAL] User Privileges
How do I retrieve this privilages? I want to disable menu-options within a program. For that I have to retrieve the privilages for some tables. gr, Willem - Original Message - From: "Dan Wilson" [EMAIL PROTECTED] To: "Niral Trivedi" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 26, 2000 7:25 PM Subject: Re: [GENERAL] User Privileges For example I have 5 tables in database A. And now I want to give SELECT/UPDATE/INSERT privileges to a user to all 5 tables. But according to documentation, I have to execute 'GRANT' query 3 times(for select/update/insert) per table. meaning total of 15 times That's incorrect... you can do it all in one statement: GRANT select,update,insert TO "username" ON table_1,table_2,table_3,etc http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm -Dan
