Re: [sqlite] Permissions

[Simon Slavin]

On 22 Apr 2012, at 6:02pm, Richard Hipp  wrote:

> There was a change in version 3.7.11 to do exactly that.
> http://www.sqlite.org/src/info/84b324606a

Woo hoo.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Richard Hipp]

On Sun, Apr 22, 2012 at 12:40 PM, Steinar Midtskogen
wrote:

>
> Any reason why sqlite doesn't use the same file permissions as the
> database file when creating these extra files?
>
>
There was a change in version 3.7.11 to do exactly that.
http://www.sqlite.org/src/info/84b324606a

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Steinar Midtskogen]

[Simon Slavin]

> The solution I came up with is that the database file owner also
> uses Apache to look at it: I use web-facing database administration
> software rather than opening the database in another application.
> (I wrote a simple one myself in PHP and JavaScript.)  However this
> is unacceptable for some users.

That gave me an idea, which should solve the problem for me.  Only two
applications access the database: apache or the sqlite3 commandline
tool.  So I simply chowned the sqlite3 application and made it setuid
apache.

It doesn't solve the general case, though, where any application owned
by any user in a certain group should be able to access the database.

> You're using WAL mode.  DELETE mode is the default behaviour: when
> the last connection to the database is closed, the journal is
> deleted.  But you can change this to TRUNCATE or some other value
> that suits you.  That way, the files will not have to be remade.  So
> then you would …

I chose WAL since I'd like to have as much concurrency as possible.

If TRUNCATE means that the files will always be present, never
deleted, then I suppose that also could solve my problem, since the
file then could be made group writeable.


Any reason why sqlite doesn't use the same file permissions as the
database file when creating these extra files? 
-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Steinar Midtskogen]

Stephan Beal  writes:

> Try the sticky bit:
>
> chown user:apache theDir
> chmod 4775 theDir

I think the effect of that only is to restrict anyone but root or the
owner of a file from deleting or renaming an otherwise writeable file
in that directory.

-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ??: Error: database is locked on redhat6

[Simon Slavin]

On 22 Apr 2012, at 1:55pm, 田晶  wrote:

> Is there any temp solutions for this case? As we need some time to change our 
> plan on this,thanks!

Don't do multi-user, multi-process, or multi-thread access for a file accessed 
using NFS.

It is not possible for the SQLite team to fix this: the thing that is broken is 
NFS, not SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 答复: ??: Error: database is locked on redhat6

[田晶]

Is there any temp solutions for this case? As we need some time to change our 
plan on this,thanks!

Tianjing


-邮件原件-
发件人: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
代表 Kees Nuyt
发送时间: 2012年4月22日 15:57
收件人: sqlite-users@sqlite.org
主题: Re: [sqlite] ??: Error: database is locked on redhat6

On Sun, 22 Apr 2012 02:36:24 +, ??  wrote:

> I using the -o nolock option when I mount nfs storage, and it works, 
> but I am not sure what will cause by this option in future...

In that case database corruption will occur if you happen to update (insert, 
update, delete rows) the database from two different connections at the same 
time.

http://www.sqlite.org/draft/howtocorrupt.html


--
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Simon Slavin]

On 22 Apr 2012, at 9:31am, Steinar Midtskogen  wrote:

> This might be slightly off topic, but perhaps a common problem for
> many sqlite users.

Common problem, and it would be nice to have a solution.

> I have a database (wal mode) that apache (the web server) needs to
> access, readonly.  Since it needs to be able to lock it for reading,
> apache needs write access.  So the database has these permissions:
> 
> -rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db
> 
> The directory is also group writeable.
> 
> The trouble is that when apache is accessing the database, the
> database file owner can't access it, not even for reading.

The solution I came up with is that the database file owner also uses Apache to 
look at it: I use web-facing database administration software rather than 
opening the database in another application.  (I wrote a simple one myself in 
PHP and JavaScript.)  However this is unacceptable for some users.

> The result
> is "unable to open database file".  I believe that the cause is that
> apache creates these files:
> 
> -rw-r--r--  1 apache  apache   32768 2012-04-22 10:15 database.db-shm
> -rw-r--r--  1 apache  apache   0 2012-04-22 09:58 database.db-wal

Take a look at "PRAGMA journal_mode":



You're using WAL mode.  DELETE mode is the default behaviour: when the last 
connection to the database is closed, the journal is deleted.  But you can 
change this to TRUNCATE or some other value that suits you.  That way, the 
files will not have to be remade.  So then you would …

1) Use any app/interface to open the SQLite database.
2) Set the journal mode to, for example, TRUNCATE.
3) Have the app/interface close the database.
4) Using your operating system, set the protection on the journal files so that 
they can be accessed by whatever apps and users you want.
5) From then on, every app using the database must always remember to set that 
journal mode every time it opens the database.  Otherwise it'll revert to 
DELETE.

However, you're using WAL mode, and a useful change to SQLite might be a WAL 
PERSISTENT mode, perhaps by creating another option for the existing PRAGMA or 
by creating one PRAGMA for journal format and another for journal clearup.  An 
alternative -- probably better -- solution would be for SQLite to check the 
permissions on the database file, and when it creates a journal file, set the 
same permissions for the new file.  I am not a SQLite programmer and I don't 
know how difficult either of these solutions would be to implement.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Patrik Nilsson]

Google gives:

http://docs.oseems.com/application/apache/change-user-and-group


If for some reason you need to run Apache as different user and group,
the trick is to just change the User and Group directive in Apache
configuration file. The configuration file is normally in
/etc/apache2/httpd.conf, though that depends on the system you're using.
The following example will make Apache run as the user nobody and the
group nobody;

User nobody
Group nobody




On 04/22/2012 01:19 PM, Steinar Midtskogen wrote:
> Patrik Nilsson  writes:
> 
>> You can try setting your user as member of group apache.
> 
> That's already done, but the trouble is that when the shm and wal
> files are created by and therefore owned by "apache", then "user"
> can't change that file unless it's group writeable.  Having apache run
> with umask 002 should fix this, but I wonder if there is another
> workaround (and I haven't figured yet out how to configure apache to
> do this, anyway).
> 
> -Steinar
> 
>>
>> On 04/22/2012 10:31 AM, Steinar Midtskogen wrote:
>>> This might be slightly off topic, but perhaps a common problem for
>>> many sqlite users.
>>>
>>> I have a database (wal mode) that apache (the web server) needs to
>>> access, readonly.  Since it needs to be able to lock it for reading,
>>> apache needs write access.  So the database has these permissions:
>>>
>>> -rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db
>>>
>>> The directory is also group writeable.
>>>
>>> The trouble is that when apache is accessing the database, the
>>> database file owner can't access it, not even for reading.  The result
>>> is "unable to open database file".  I believe that the cause is that
>>> apache creates these files:
>>>
>>> -rw-r--r--  1 apache  apache   32768 2012-04-22 10:15 database.db-shm
>>> -rw-r--r--  1 apache  apache   0 2012-04-22 09:58 database.db-wal
>>>
>>> which other users have no write access to.  So access to the database
>>> is locked until sqlite remove these files.
>>>
>>> Is there a way to work around this, other than to set umask 002 for
>>> apache?
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Stephan Beal]

Try the sticky bit:

chown user:apache theDir
chmod 4775 theDir

:-?

- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
On Apr 22, 2012 1:19 PM, "Steinar Midtskogen"  wrote:

> Patrik Nilsson  writes:
>
> > You can try setting your user as member of group apache.
>
> That's already done, but the trouble is that when the shm and wal
> files are created by and therefore owned by "apache", then "user"
> can't change that file unless it's group writeable.  Having apache run
> with umask 002 should fix this, but I wonder if there is another
> workaround (and I haven't figured yet out how to configure apache to
> do this, anyway).
>
> -Steinar
>
> >
> > On 04/22/2012 10:31 AM, Steinar Midtskogen wrote:
> >> This might be slightly off topic, but perhaps a common problem for
> >> many sqlite users.
> >>
> >> I have a database (wal mode) that apache (the web server) needs to
> >> access, readonly.  Since it needs to be able to lock it for reading,
> >> apache needs write access.  So the database has these permissions:
> >>
> >> -rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db
> >>
> >> The directory is also group writeable.
> >>
> >> The trouble is that when apache is accessing the database, the
> >> database file owner can't access it, not even for reading.  The result
> >> is "unable to open database file".  I believe that the cause is that
> >> apache creates these files:
> >>
> >> -rw-r--r--  1 apache  apache   32768 2012-04-22 10:15
> database.db-shm
> >> -rw-r--r--  1 apache  apache   0 2012-04-22 09:58
> database.db-wal
> >>
> >> which other users have no write access to.  So access to the database
> >> is locked until sqlite remove these files.
> >>
> >> Is there a way to work around this, other than to set umask 002 for
> >> apache?
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Steinar Midtskogen]

Patrik Nilsson  writes:

> You can try setting your user as member of group apache.

That's already done, but the trouble is that when the shm and wal
files are created by and therefore owned by "apache", then "user"
can't change that file unless it's group writeable.  Having apache run
with umask 002 should fix this, but I wonder if there is another
workaround (and I haven't figured yet out how to configure apache to
do this, anyway).

-Steinar

>
> On 04/22/2012 10:31 AM, Steinar Midtskogen wrote:
>> This might be slightly off topic, but perhaps a common problem for
>> many sqlite users.
>> 
>> I have a database (wal mode) that apache (the web server) needs to
>> access, readonly.  Since it needs to be able to lock it for reading,
>> apache needs write access.  So the database has these permissions:
>> 
>> -rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db
>> 
>> The directory is also group writeable.
>> 
>> The trouble is that when apache is accessing the database, the
>> database file owner can't access it, not even for reading.  The result
>> is "unable to open database file".  I believe that the cause is that
>> apache creates these files:
>> 
>> -rw-r--r--  1 apache  apache   32768 2012-04-22 10:15 database.db-shm
>> -rw-r--r--  1 apache  apache   0 2012-04-22 09:58 database.db-wal
>> 
>> which other users have no write access to.  So access to the database
>> is locked until sqlite remove these files.
>> 
>> Is there a way to work around this, other than to set umask 002 for
>> apache?
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions

[Patrik Nilsson]

You can try setting your user as member of group apache.

On 04/22/2012 10:31 AM, Steinar Midtskogen wrote:
> This might be slightly off topic, but perhaps a common problem for
> many sqlite users.
> 
> I have a database (wal mode) that apache (the web server) needs to
> access, readonly.  Since it needs to be able to lock it for reading,
> apache needs write access.  So the database has these permissions:
> 
> -rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db
> 
> The directory is also group writeable.
> 
> The trouble is that when apache is accessing the database, the
> database file owner can't access it, not even for reading.  The result
> is "unable to open database file".  I believe that the cause is that
> apache creates these files:
> 
> -rw-r--r--  1 apache  apache   32768 2012-04-22 10:15 database.db-shm
> -rw-r--r--  1 apache  apache   0 2012-04-22 09:58 database.db-wal
> 
> which other users have no write access to.  So access to the database
> is locked until sqlite remove these files.
> 
> Is there a way to work around this, other than to set umask 002 for
> apache?
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Permissions

[Steinar Midtskogen]

This might be slightly off topic, but perhaps a common problem for
many sqlite users.

I have a database (wal mode) that apache (the web server) needs to
access, readonly.  Since it needs to be able to lock it for reading,
apache needs write access.  So the database has these permissions:

-rw-rw-r--  1 userapache  1837704192 2012-04-22 09:58 database.db

The directory is also group writeable.

The trouble is that when apache is accessing the database, the
database file owner can't access it, not even for reading.  The result
is "unable to open database file".  I believe that the cause is that
apache creates these files:

-rw-r--r--  1 apache  apache   32768 2012-04-22 10:15 database.db-shm
-rw-r--r--  1 apache  apache   0 2012-04-22 09:58 database.db-wal

which other users have no write access to.  So access to the database
is locked until sqlite remove these files.

Is there a way to work around this, other than to set umask 002 for
apache?

-- 
Steinar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ??: Error: database is locked on redhat6

[Kees Nuyt]

On Sun, 22 Apr 2012 02:36:24 +, ??  wrote:

> I using the -o nolock option when I mount nfs storage,
> and it works, but I am not sure what will cause by this option in future...

In that case database corruption will occur if you happen to update
(insert, update, delete rows) the database from two different
connections at the same time.

http://www.sqlite.org/draft/howtocorrupt.html


-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users