Read-only mode

[hiyelbaz]

Is it possible to connect sqlite in read-only mode?

[hrkuma]

Hi,

It is possible to connect sqlite using read-only database file. I could execute following sequence. Does this answer your question?

$ sqlite3 /tmp/test.db
sqlite> CREATE TABLE t1(a int primary key, b text);
sqlite> INSERT INTO t1 VALUES (0, 'test');
sqlite> SELECT * FROM t1;
0|test
sqlite>.q
$ chmod a-w /tmp/test.db
$ psql
# CREATE EXTENSION sqlite_fdw;
# CREATE SERVER sqlite_testdb FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/test.db');
# IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_testdb INTO public;
# SELECT * FROM t1;
SELECT * FROM t1;
 a |  b
---+------
 0 | test
(1 row)
# INSERT INTO t1 VALUES (1, 'new');
INSERT INTO t1 VALUES (1, 'new');
ERROR:  failed to execute remote SQL: rc=8 attempt to write a readonly database
   sql=INSERT INTO main."t1"(`a`, `b`) VALUES (?, ?)

[hiyelbaz]

Thanks for the answer. there is a second process writing to sqlite db. As long as they can work together by just setting up file permissions this the solution. I think I need to remove write permission only from postgres user.

[mkgrgis]

@hiyelbaz , Your issue gives great idea for my PR https://github.com/pgspider/sqlite_fdw/pull/59, thanks!

[mkgrgis]

@hiyelbaz , full read-only mode implemented in https://github.com/pgspider/sqlite_fdw/pull/78. Thanks for idea.