[SQL] Controlling access to Sequences
Hi All, I'm implementing a system where I need to be reasonably careful about security. One thing that worries me is SEQUENCES. My understanding is that I need to GRANT the UPDATE privilege on the SEQUENCE if I want a user to be able to to use nextval() on it. The trouble is, if they can do a nextval() they can also do a setval() which would allow them to set the sequence to any value they felt like and potentially cause problems for other user's use of the SEQUENCE. I've considered forcing the user to use the nextval() by using a RULE on a VIEW, but it appear as though the nextval() function still runs as the logged-in user not the owner of the VIEW. Is there any effective method for controlling access to a SEQUENCE? or should I do something like in the view: INSERT INTO x VALUES ( ( SELECT MAX( id ) + 1 FROM x ), some_field ); Is this safe to do? Or might this lead to undesirable problems if called concurrently? Any assistance would be appreciated. Matthew. ---(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: [SQL] Controlling access to Sequences
Hi Tom, Thanks for the response. On Saturday, February 1, 2003, at 03:09 PM, Tom Lane wrote: Matthew Horoschun <[EMAIL PROTECTED]> writes: My understanding is that I need to GRANT the UPDATE privilege on the SEQUENCE if I want a user to be able to to use nextval() on it. The trouble is, if they can do a nextval() they can also do a setval() So? With enough time on your hands, you can apply nextval() often enough to get from any point A to any point B. It seems illusory to think that forbidding setval() makes things more secure. Absolutely, You're right. nextval() is just as troublesome. I don't want to arbitrarily restrict access to setval(). I just want a safer way of handling automatic allocation of primary keys on certain tables. Should I just avoid SEQUENCES altogether and use the OIDs under normal circumstances and the MAX( id ) + 1 style thing when I need a human-usable number? Matthew. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Controlling access to Sequences
On Saturday, February 1, 2003, at 03:43 PM, Tom Lane wrote: Matthew Horoschun <[EMAIL PROTECTED]> writes: Should I just avoid SEQUENCES altogether and use the OIDs under normal circumstances and the MAX( id ) + 1 style thing when I need a human-usable number? I don't think so. MAX()+1 has more than enough problems of its own. The real bottom line here is that you should not allow untrustworthy users any sort of direct access to SQL commands of any kind. They should only be allowed access to an application that issues suitably restricted SQL commands on their behalf. While I agree that would solve the problem, that is not the kind of solution we're looking for. We're in the process of porting a custom application from MySQL to PostgreSQL. The main reason for moving is that we can push all the business logic into the database and out of the application layer. That includes security. From my little experience it seems PostgreSQL has a pretty powerful security system if you apply VIEWS, RULES, SCHEMAS, and GRANT carefully. Excluding SEQUENCES, I can see no reason why you cannot lock down PostgreSQL to the point where you could allow users virtually direct access to PostgreSQL. Of course, if anybody can suggest why this isn't the case, I'm more than willing to hear why! Also, can anybody suggest in exactly what kind of situation MAX() + 1 will fail or cause problems? Excluding the performance hit (which I'm probably willing to live with). Thanks Matthew. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Inserting a tab character
Hi Luke. On Wednesday, February 5, 2003, at 02:39 PM, Luke Pascoe wrote: I have a table which defines various possible file delimiters (CHAR(1) NOT NULL), for the moment it'll only contain comma and tab. Inserting a comma is easy, but inserting a tab is proving somewhat more difficult. How do I do it in 'psql'? insert into test values( '\t' ); appears to work... Matt. ---(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: [SQL] executing a function
Hi Pawan, CREATE FUNCTION "public"."gettesttab" () RETURNS SETOF public.testtab AS' select * from TestTab; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; You want LANGUAGE 'sql' Cheers Matthew. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Logging select statements
Hi Rudi, You can't trigger on a SELECT, but you could wrap your SQL in a set returning function... http://techdocs.postgresql.org/guides/SetReturningFunctions Here is a rough and ready solution: CREATE TABLE access_log ( id int not null ); CREATE TABLE datatable ( id int not null primary key, somedata varchar(255) not null ); INSERT INTO datatable VALUES( 1, 'apple' ); INSERT INTO datatable VALUES( 2, 'orange' ); INSERT INTO datatable VALUES( 3, 'banana' ); CREATE OR REPLACE FUNCTION get_rows_and_log( varchar ) RETURNS SETOF record AS ' DECLARE r record; BEGIN FOR r IN EXECUTE ''SELECT * FROM '' || $1 LOOP INSERT INTO access_log VALUES( r.id ); RETURN NEXT r; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; Now, as an example, do: SELECT * FROM get_rows_and_log( 'datatable' ) AS data( id int, somedata varchar); You'll get the data returned, and the log entries will be made. You can put your WHERE clause in the parameter: SELECT * FROM get_rows_and_log( 'datatable WHERE somedata LIKE ''%e''' ) AS data( id int, somedata varchar); Hope that is what you were after! Cheers Matthew. On Wednesday, July 9, 2003, at 04:55 PM, Rudi Starcevic wrote: Thanks Achilleus, I know there's a couple of ways I could do this. In my first email I can see a senario of 1 select plus 100 inserts. Another may be 1 select plus 1 insert. For example; In a table of 3000 rows a user submits a query which returns 100 rows. I could loop through the result set and build a string of id's ( 1,2,5,7,8,9,44,22 etc ) and make one insert into a logging table of the entire string. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SECURITY DEFINER changes CURRENT_USER?
Hi Chris, You want to use "session_user". I would expect this to change the password of the user currently logged in but instead it changes MY password. Evidently when a function is called which is set to SECURITY DEFINER, it changes the context of the current user. The CURRENT_USER then returns the name of the definer rather than the invoker of the function. So this being said-- are there any workarounds that don't allow anyone to change anyone else's password? Cheers ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
