One of the twitter friend asked me a question . It was related to switching production to standby database and standby database to new production under oracle environment. I came across this situation before 4 years. I provided this link and it’s nicely solved his problem.
Guys,
Open Source databases still needs solid presence in enterprise world. One of the myths about open source databases is that they aren’t secure. To eliminate this myth we have to share our experience related to security aspect of PostgreSQL database. At OmniTi , we help businesses to achieve PCI compliance or provide payment processor:PayPI . PayPI runs on PostgreSQL database.
“Use documentation as map,not as GPS.”Publishing my own quote…
Let's put hands together to test a new tool and help making PostgreSQL more advanced with adding these rich tools!!
Guys,
Last couple of months, I came across two incidents of SQL injections due to most common errors by IT professionals.
“SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.”
Most common SQL injections are:
2. Incorrect type handling
3. Vulnerabilities inside the database server
4. Blind SQL injection
You can avoid these catastrophic SQL injections by little care during the web application coding such as using bind variables, verify input values and take care of escaping characters. Database level security such as password and access control also help to avoid SQL injections.
I hope this will save you from future SQL injection 
I came across a nice post by Peter Eisentraut that shows how text based index can be used to do direct lookup on the text fields.
Quick test proves that..
postgres=# d employees
Table "public.employees"
Column | Type | Modifiers
--------+---------+-----------
emp_id | integer | not null
name | text |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
postgres=# create index idx_employees_name on employees(name);
CREATE INDEX
postgres=# explain select * from employees where name like 'Denish%';
QUERY PLAN
-----------------------------------------------------------
Seq Scan on employees (cost=0.00..25.38 rows=6 width=36)
Filter: (name ~~ 'Denish%'::text)
(2 rows)
postgres=# explain select * from employees where name='Denish';
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on employees (cost=4.30..13.76 rows=6 width=36)
Recheck Cond: (name = 'Denish'::text)
-> Bitmap Index Scan on idx_employees_name (cost=0.00..4.30 rows=6 width=0)
Index Cond: (name = 'Denish'::text)
(4 rows)
postgres=# create index idx_employees_name_like on employees (name text_pattern_ops);
CREATE INDEX
postgres=# explain select * from employees where name like 'Denish%';
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on employees (cost=4.31..13.78 rows=6 width=36)
Filter: (name ~~ 'Denish%'::text)
-> Bitmap Index Scan on idx_employees_name_like (cost=0.00..4.31 rows=6 width=0)
Index Cond: ((name ~>=~ 'Denish'::text) AND (name ~ Bitmap Index Scan on idx_employees_name_like (cost=0.00..4.30 rows=6 width=0)
Index Cond: (name = 'Denish'::text)
(4 rows)
Above example shows that we don’t need regular index on name field as text based index (idx_employees_name_like) fulfill the gap. However, if we do range query i.e select * from employees where name > ‘denish’ and name<'patel' then text based index will not be used. I don't think we ever run a range query on text based fields!.
