Re: [sqlite] Permissions

[Steinar Midtskogen]

[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

Oh, great!  I'm just at 3.7.9, the latest on cpan.  This was the
fastest response to a feature request I've ever experienced.  Fixed a
few weeks before I asked the question!

-- 
Steinar
___
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 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] 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] Permissions issue with SQLite

[Dan Kennedy]

On 09/23/2011 03:09 AM, Magnus Thor Torfason wrote:

On 9/22/2011 10:25, Dan Kennedy wrote:

For new versions, new db files are created with the permissions
specified by compilation option SQLITE_DEFAULT_FILE_PERMISSIONS.
Subject to umask of course.

http://www.sqlite.org/compile.html#default_file_permissions



Thanks for your help.

But I take it that the "subject to umask" means that for each permission
bit, the file will have the stricter permission of default and umask, so
the only way to get looser permissions would be to compile a new version
after changing the compilation option. No way to do that by setting an
environmental variable or anything like that?


That's correct. Really, you need to upgrade anyhow. 3.3.6 came out
in 2006.


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

Re: [sqlite] Permissions issue with SQLite

[Magnus Thor Torfason]

On 9/22/2011 10:25, Dan Kennedy wrote:

For new versions, new db files are created with the permissions
specified by compilation option SQLITE_DEFAULT_FILE_PERMISSIONS.
Subject to umask of course.

http://www.sqlite.org/compile.html#default_file_permissions



Thanks for your help.

But I take it that the "subject to umask" means that for each permission 
bit, the file will have the stricter permission of default and umask, so 
the only way to get looser permissions would be to compile a new version 
after changing the compilation option. No way to do that by setting an 
environmental variable or anything like that?


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

Re: [sqlite] Permissions issue with SQLite

[Dan Kennedy]

On 09/22/2011 09:20 PM, Stephan Beal wrote:

On Thu, Sep 22, 2011 at 4:16 PM, Magnus Thor Torfason<
zulutime@gmail.com>  wrote:


SQLite version 3.3.6



Just to preempt the inevitable request to try it on a current version: this
is reproducible on 3.7.2 (Ubuntu 10.10).



For new versions, new db files are created with the permissions
specified by compilation option SQLITE_DEFAULT_FILE_PERMISSIONS.
Subject to umask of course.

  http://www.sqlite.org/compile.html#default_file_permissions
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Permissions issue with SQLite

[Stephan Beal]

On Thu, Sep 22, 2011 at 4:16 PM, Magnus Thor Torfason <
zulutime@gmail.com> wrote:

> SQLite version 3.3.6
>

Just to preempt the inevitable request to try it on a current version: this
is reproducible on 3.7.2 (Ubuntu 10.10).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Permissions issue with SQLite

[Magnus Thor Torfason]

Hi,

I'm having a permission issue with SQLite on my institution's computing 
grid/cluster.


When I create a file using touch (or any other program for that matter, 
it correctly receives '-rw-rw' as the permission. However, when I 
create it from SQLite, it gets created with '-rw-r-' (group does not 
get write permissions, even if it should get them).


I've confirmed this both from the command line client (demonstration 
script below) and from RSQLite (the SQLite library for R, demonstration 
script is longer but could attach if anyone thinks it helps).


I've checked with my admins, and they tell me that there was nothing 
unusual about how SQLite was installed. Any ideas for what could be the 
cause of this? It seems that to override the umask setting, SQLite must 
be doing something explicitly, right?



## Reproduction script below

HOSTNAME [TestPermissions]$ umask
0007
HOSTNAME [TestPermissions]$ ll
total 0
HOSTNAME [TestPermissions]$ touch TouchedFile.txt
HOSTNAME [TestPermissions]$ sqlite3 DatabaseFile.sqlite
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> .tables
sqlite> .quit
HOSTNAME [TestPermissions]$ ll
total 0
-rw-r- 1 mtorfason faculty 0 Sep 22 10:07 DatabaseFile.sqlite
-rw-rw 1 mtorfason faculty 0 Sep 22 10:07 TouchedFile.txt

## End reproduction script


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

[sqlite] permissions for created database files are too restrictive and do not obey umask setting

[Anders Lennartsson]

File permissions on database files created by the library libsqlite3
on Unix/Linux are created with a mask setting that restricts
permissions beyond what the user may expect from the current umask
setting.

In the file src/os_unix.c a mask is defined as:

 ** Default permissions when creating a new file
 */
 #ifndef SQLITE_DEFAULT_FILE_PERMISSIONS
 # define SQLITE_DEFAULT_FILE_PERMISSIONS 0644
 #endif

which is applied when the open function is called to open a database
and create it if necessary. In many cases the user has umask settings
022 and new files are thus created with 644 permissions which are what
the user expects.

However, if the user has an umask setting of 002, to allow write
permissions for the group by giving newly created files permissions as
664, the mask defined in the sqlite source code restricts this to 644
nevertheless, as the default file permission defined above is a value
for masking the permissions.

IMHO this is an error. A valid example of that are on server machines
where web services such as Trac are provided. If several
administrators work together such machines are often configured so
that the SGD bit is set on the directories where Trac-webs are stored,
and the group for them are set to the user name that runs the
web-server. On Debian/Ubuntu systems that is www-data. The intention
of this is to allow the web-server to write in the database file, but
also to allow an administrator to do administration such as deleting a
file created by another administrator.

With Subversion 1.6 there is an essentially identical problem arising
as sqlite3 is used within Subversion repositories in the form of the
file db/rep-cache.db. This file is created when something is put in
the repository and not when the repository is created (with svnadmin
create). Thus everything looks fine and files and directories have the
correct permissions in a newly created repository. But, the database
file that is created when something is put into the repository may not
be writable by the web-server process, as it will have 644
permissions, and the web-server process will then report an error.

A work-around for this is to create an empty database file (e.g. by
touch) and give it correct permissions (664). But it is clearly
tedious and error prone to depend on this type of extra tasks for
something that should be done correctly in the first place.

This problem is reported as Debian bug #608604, but it has come to my
attention that other distributions are also bitten by this and it is
probably best fixed upstream.

I have searched the sqlite archives and this topic sometimes surfaces,
the most recent mentioning seems to be on Jan 10, 2011:
http://comments.gmane.org/gmane.comp.db.sqlite.general/62194
Another thread is started by
http://article.gmane.org/gmane.comp.db.sqlite.general/26395
where some posts indicate a full understanding of the problem.

In summary, I'd like to report this as a bug. A trivial fix exists
which does not change the behaviour for most users but does help in a
common situation where many people cooperate on administrating
important production servers on which sqlite databases are used.

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

[sqlite] PHP created sqlite permissions

[Ben Marchbanks]

I use PDF-PDO to create and populate sqlite tables as a backup  - each 
time the backup process is run
I get a table is locked error. Deleting the sqlite db allows the process 
to run without error.

After the db is created I chmod the file to "0775";

I notice the result is ...

-rwxrwxr-x 1 apache apache 9411584 2008-11-21 09:23 mzdb.sqlite

Could it be that the reason for the "table lock" is due to file permissions?

If not what could cause the table to remain locked after being populated ?

-- 
*Ben Marchbanks*

www.magazooms.com 
Signature
Email: [EMAIL PROTECTED] 
Phone: (864) 284.9918
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users