On Xubuntu Fireball 3.0 only accessible through root account

[Nick Vaughan]

This Group has helped me before on installing Firebird 5.0 on Linux Mint Xfce. As a result of that information I'm now trying to install Firebird 3.0 on Xubunbtu 22.04. All the files are on the same laptop

Using the files in the Synaptic Package Manager the installation has succeeded and I can run isql-fb.

The issue I have is that I can only create anything from the xubuntu root account rather than from my own account. These means that, for example, if I create a database it ends up with the owner root:root. Looking at other support questions, for example on Stack Overflow I can see that one solution is to use chown to change the owner to fifrebird:firebird. Of course when i try to do this I can a permissions failure.

Again looking at Stack Overflow I can see that it's the fbserver that requires the relevant access and it makes a difference if the database is being run as a service or an application. It looks as though Linux only ever runs firebird as a service.

From the Firebird 3.0 Quick-Start Guide some checks that I can do are

1, Using ping 127.0.0.1

gets

PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.055 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.071 ms
64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.073 ms
64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.063 ms
64 bytes from 127.0.0.1: icmp_seq=5 ttl=64 time=0.065 ms
64 bytes from 127.0.0.1: icmp_seq=6 ttl=64 time=0.071 ms
64 bytes from 127.0.0.1: icmp_seq=7 ttl=64 time=0.103 ms
^C

2, Using top -b -n1 | grep fb check that a fb server is running.

nick@Aspire-one-1-131:~/Documents/Databases$ top -b -n1 | grep fb
    489 firebird  20   0    7832   1160   1152 S   0.0   0.1   0:00.00 fbguard

There is no fbserver process running

2.1 Trying to start a service using fbguard

nick@Aspire-one-1-131:~/Documents/Databases$ fbguard
Could not open /run/firebird3.0/fb_guard for write

2.2 Trying to start a service using systemctl

nick@Aspire-one-1-131:~/Documents/Databases$ systemctl start firebird
Failed to start firebird.service: Unit firebird.service not found.

2.3 nick@Aspire-one-1-131:~/Documents/Databases$ /etc/init.d/firebird start
bash: /etc/init.d/firebird: No such file or directory

3, Even so I can start up isql-fb

nick@Aspire-one-1-131:~/Documents/Databases$ isql-fb
Use CONNECT or CREATE DATABASE to specify a database
SQL> CREATE DATABASE '/home/nick/Documents/Databases/fbtestA.fdb' PAGE_SIZE = 8192  DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;
Statement failed, SQLSTATE = HY000
operating system directive access failed
-Not a directory
SQL>

If I log in through the Xubuntu root account then I can create a database, although it then has the owner root:root.

Searching on this support site I found this "Firebird DBs only accessible as root user" https://groups.google.com/g/firebird-support/c/vIX0-gXZA6E/m/qvEa_XinBQAJ

The suggestion was "Change /home/firebird/data/db.gdb to localhost:/home/firebird/data/db.gdb and it should work fine (it will
actually connect to the FB server process on the local machine, and the latter, from what you wrote, has permissions to /home/firebird)." Trying to follow this advice I get

nick@Aspire-one-1-131:~/Documents/Databases$ isql-fb -u SYSDBA -p ppppppp
Use CONNECT or CREATE DATABASE to specify a database
SQL>
SQL> CREATE DATABASE 'localhost:/home/nick/Documents/Databases/fbtestA.fdb' PAGE_SIZE = 8192  DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;
Statement failed, SQLSTATE = 08001
I/O error during "open O_CREAT" operation for file "/home/nick/Documents/Databases/fbtestA.fdb"
-Error while trying to create file
-Permission denied

Can anyone please say how I can check that the fbserver has required access?

Thanks

[Dimitry Sibiryakov]

Nick Vaughan wrote 25.03.2024 19:03:
> The issue I have is that I can only create anything from the xubuntu root
> account rather than from my own account. These means that, for example, if I
> create a database it ends up with the owner root:root.

IIRC, you was told in previous thread: DO NOT use embedded access on Linux
EVER, use only TCP connection strings.

--
WBR, SD.

[Nick Vaughan]

@SD, You are right in that the issue of embedded and server mode was raised but nothing was said about having to use server/TCP mode. I’ve copied out the text from those earlier Conversations and, as I’m using localhost, I could already been using server/TCP now (unless I’ve missed something. You can see below the relevant text).

If server/TCP mode is best then I’m very happy to use it. In this case I installed everything using default values and got to where I am. If I need to change something in the installation to create it as server/TCP then I’m happy to do so and would be happy to get guidance on how to do this.

Similarly I would be happy to get guidance on how to change the result of what I have now to make it work.

If it helps then I’ve found this https://firebirdsql.org/manual/qsg25-config.html and in particular the Section “Disable Classic local mode on Linux” which says “the so-called local or embedded access mode of the Firebird Classic and SuperClassic servers on Linux. Here too, users must have proper access rights to the database file itself. They also need read access to the security database security2.fdb. If this worries you (and it probably should), reserve filesystem access to the security database (and other databases, while you're at it) to the server process only. Users are then obliged to connect via the network layer. ” (my underlining.) It’s on version 2.5 but is this relevant?

Thanks

Nick

Comments from earlier Conversations

If you use embedded mode, then the access rights of *your* user applies. If you connect through the server, the access rights of the *service* user (e.g. the user firebird) applies.

Embedded means that application loads the Firebird database engine and
uses that to read the database in the same process. Server mode is
application connects (using TCP/IP) to the server running on localhost
(or on a remote server), and the database is accessed by the Firebird
server.

Since Firebird 3, it is basically, if you only provide a filename, then
you're probably using Firebird embedded (this is not 100% guaranteed,
for example if the client library can't load the database engine, it
will try to connect to the server on localhost).

So if you connect using '/some/path/to/database.fdb', you're probably
using embedded. If you're connecting using an URL like one of the
following, you're connecting through the server:

- localhost:/some/path/to/database.fdb
- localhost/3050:/some/path/to/database.fdb
- inet://localhost//some/path/to/database.fdb
- inet://localhost:3050//some/path/to/database.fdb
- inet:///some/path/to/database.fdb
- (and variants with inet4:// or inet6://, or a host name other than
localhost)

[Mark Rotteveel]

On 25/03/2024 19:03, Nick Vaughan wrote:
> This Group has helped me before on installing Firebird 5.0 on Linux Mint
> Xfce. As a result of that information I'm now trying to install Firebird
> 3.0 on Xubunbtu 22.04. All the files are on the same laptop
>
> Using the files in the Synaptic Package Manager the installation has
> succeeded and I can run isql-fb.
>
> The issue I have is that I can only create anything from the xubuntu
> root account rather than from my own account. These means that, for
> example, if I create a database it ends up with the owner root:root.
> Looking at other support questions, for example on Stack Overflow I can
> see that one solution is to use chown to change the owner to
> fifrebird:firebird. Of course when i try to do this I can a permissions
> failure.

Did you add your own user to the group called firebird?

> Again looking at Stack Overflow I can see that it's the fbserver that
> requires the relevant access and it makes a difference if the database
> is being run as a service or an application. It looks as though Linux
> only ever runs firebird as a service.
>
> From the Firebird 3.0 Quick-Start Guide some checks that I can do are
>

> 1, Using *ping 127.0.0.1*

> gets
> PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
> 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.055 ms
> 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.071 ms
> 64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.073 ms
> 64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.063 ms
> 64 bytes from 127.0.0.1: icmp_seq=5 ttl=64 time=0.065 ms
> 64 bytes from 127.0.0.1: icmp_seq=6 ttl=64 time=0.071 ms
> 64 bytes from 127.0.0.1: icmp_seq=7 ttl=64 time=0.103 ms
> ^C
> 2, Using top -b -n1 | grep fb check that a fb server is running.
> nick@Aspire-one-1-131:~/Documents/Databases$ top -b -n1 | grep fb
>     489 firebird  20   0    7832   1160   1152 S   0.0   0.1   0:00.00
> fbguard
>
> There is no fbserver process running
>
> 2.1 Trying to start a service using fbguard
>
> nick@Aspire-one-1-131:~/Documents/Databases$ fbguard
> Could not open /run/firebird3.0/fb_guard for write

In this day and age there is rarely a good reason to use fbguard.

> 2.2 Trying to start a service using systemctl
> nick@Aspire-one-1-131:~/Documents/Databases$ systemctl start firebird
> Failed to start firebird.service: Unit firebird.service not found.

Are you sure that XUbuntu calls the service firebird, and not for
example firebird3.0?

I think systemctl --type=service should list the available services.

(That said, your attempt below shows the Firebird server is running.)

Checking an old Linux VM I have with Ubuntu, the firebird3.0 package of
Ubuntu/Debian launches itself through /etc/init.d, not systemctl, so I
guess XUbuntu does as well.

> 2.3 nick@Aspire-one-1-131:~/Documents/Databases$ /etc/init.d/firebird start
> bash: /etc/init.d/firebird: No such file or directory

Are you sure that Ubuntu/XUbuntu calls the service firebird, and not for
example firebird3.0?

List the contents of /etc/init.d to see which services exist.

Checking the VM I mentioned earlier, Ubuntu uses /etc/init.d/firebird3.0.

(That said, your attempt below shows the Firebird server is running.)

> 3, Even so I can start up isql-fb
>
> nick@Aspire-one-1-131:~/Documents/Databases$ isql-fb
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> CREATE DATABASE '/home/nick/Documents/Databases/fbtestA.fdb'
> PAGE_SIZE = 8192  DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;
> Statement failed, SQLSTATE = HY000
> operating system directive access failed
> -Not a directory
> SQL>

IIRC, this means your user doesn't have sufficient access to the lock
directory. Add yourself to the firebird group with `sudo adduser
<yourusername> firebird`, and then reload the group with `newgrp firebird`.

> If I log in through the Xubuntu root account then I can create a
> database, although it then has the owner root:root.
>
> Searching on this support site I found this "Firebird DBs only
> accessible as root user"

> https://groups.google.com/g/firebird-support/c/vIX0-gXZA6E/m/qvEa_XinBQAJ <https://groups.google.com/g/firebird-support/c/vIX0-gXZA6E/m/qvEa_XinBQAJ>

>
> The suggestion was "Change /home/firebird/data/db.gdb to
> localhost:/home/firebird/data/db.gdb and it should work fine (it will
> actually connect to the FB server process on the local machine, and the
> latter, from what you wrote, has permissions to /home/firebird)." Trying
> to follow this advice I get
> nick@Aspire-one-1-131:~/Documents/Databases$ isql-fb -u SYSDBA -p ppppppp
> Use CONNECT or CREATE DATABASE to specify a database
> SQL>
> SQL> CREATE DATABASE
> 'localhost:/home/nick/Documents/Databases/fbtestA.fdb' PAGE_SIZE = 8192
> DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;
> Statement failed, SQLSTATE = 08001
> I/O error during "open O_CREAT" operation for file
> "/home/nick/Documents/Databases/fbtestA.fdb"
> -Error while trying to create file
> -Permission denied

As an aside, this error clearly means your Firebird server is running.

> Can anyone please say how I can check that the fbserver has required access?

The advice in the post you linked wasn't entirely correct, as it didn't
take into account access rights to the user directory. It won't work if
the Firebird server (either through user firebird or group firebird)
doesn't have access to /home/nick/Documents/Databases/ and intermediate
directories, and in general that is not something you should do: don't
give access to your user directory to other users or groups.

Your choice is basically between:

1) If you want to use a Firebird database in your home directory, you
should use Firebird embedded, and that means your user must be added to
the firebird group because otherwise it can't access the lock directory
(or you need to specify an alternative lock directory, owned by you,
through the FIREBIRD_LOCK environment variable for your session only).

2) Or, you need to put the database file elsewhere, in a directory owned
by the user firebird, and connect through the server instead of using
embedded.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 25/03/2024 21:49, Nick Vaughan wrote:
> If it helps then I’ve found this
> https://firebirdsql.org/manual/qsg25-config.html
> <https://firebirdsql.org/manual/qsg25-config.html> and in particular the
> Section “Disable Classic local mode on Linux” which says “the so-called
> local or embedded access mode of the Firebird Classic and SuperClassic

> servers on Linux. Here too, users /must/ have proper access rights to

> the database file itself. They also need read access to the security
> database security2.fdb. If this worries you (and it probably should),

> _reserve filesystem access to the security database (and other
> databases, while you're at it) to the server process only_. _Users are
> then obliged to connect via the network layer_. ” (my underlining.) It’s

> on version 2.5 but is this relevant?

Access to the security database is no longer needed, as since Firebird
3.0, embedded doesn't perform authentication: filesystem permission is
considered sufficient authorization to give you access, and the user
name is accepted as is (and used to apply privileges and determine
available roles).

Mark
--
Mark Rotteveel

[Nick Vaughan]

@Mark,

Thank you for your comments on the two posts (25/03/2024 19:03 and 25/03/2024 21:49).

In summary I think that the actions are

List the contents of /etc/init.d (You have already advised that Ubuntu uses "/etc/init.d/firebird3.0"). You can find the files in /etc/init.d below.

If I wish to use firebird embedded then add my user name to the firebird group with `sudo adduser <,myusername> firebird` and then reload the group with `newgrp firebird`.

Of the two options you provide I prefer Option 2 as it seems to be more technically sound (picking up sd's earlier comment). Unfortunately this creates some more questions

1, Finding a directory owned by firebird - I'm thinking of using /usr/share/doc/firebird3.0-examples.

2, How do I connect through a server? Is this in isql-fb by using the format "CREATE Database localhost:/usr/share/doc/firebird3.0-examples/fbtest.fbd"? My plan would be to create an alias in the /etc/firebird/3.0/databases.conf file.

If I can I would prefer to create any new databases under my own username rather than under SYSDBA. As the employees database hasn't been created (although there is a script to create something) I'm guessing that I would need to create a database in order to have something to work with and then use that to add another admin user and then use that to create the databases I actually want. Does that make sense?

Thanks

Nick

nick@Aspire-one-1-131:/etc/init.d$ ls
acpid             firebird3.0        pulseaudio-enable-autospawn
alsa-utils        grub-common        rsync
anacron           hwclock.sh         saned
apparmor          irqbalance         speech-dispatcher
apport            kerneloops         spice-vdagent
avahi-daemon      keyboard-setup.sh  udev
bluetooth         kmod               ufw
console-setup.sh  lightdm            unattended-upgrades
cron              lm-sensors         uuidd
cups              plymouth           whoopsie
cups-browsed      plymouth-log       x11-common
dbus              procps

[Mark Rotteveel]

On 26/03/2024 14:53, Nick Vaughan wrote:
> @Mark,
>
> Thank you for your comments on the two posts (25/03/2024 19:03 and
> 25/03/2024 21:49).
>
> In summary I think that the actions are

> List the contents of /etc/init.d(You have already advised that Ubuntu

> uses "/etc/init.d/firebird3.0"). You can find the files in /etc/init.d
> below.
> If I wish to use firebird embedded then add my user name to the

> firebirdgroup with `sudo adduser <,myusername> firebird` and then reload

> the group with `newgrp firebird`.
>
> Of the two options you provide I prefer Option 2 as it seems to be more
> technically sound (picking up sd's earlier comment). Unfortunately this
> creates some more questions
> 1, Finding a directory owned by firebird - I'm thinking of using
> /usr/share/doc/firebird3.0-examples.

Why not just create a directory (e.g. in `/var` and assign ownership to
firebird)? Doing it in `/usr/share/doc/firebird3.0-examples` makes no
sense, as that is specifically for the firebird3.0-examples package.

> 2, How do I connect through a server? Is this in isql-fb by using the
> format "CREATE Database
> localhost:/usr/share/doc/firebird3.0-examples/fbtest.fbd"? My plan would
> be to create an alias in the /etc/firebird/3.0/databases.conf file.
>
> If I can I would prefer to create any new databases under my own
> username rather than under SYSDBA. As the employees database hasn't been
> created (although there is a script to create something) I'm guessing
> that I would need to create a database in order to have something to
> work with and then use that to add another admin user and then use that
> to create the databases I actually want. Does that make sense?

You would need to create a user (e.g. by connecting to the security
database, or any other database), and then grant that user the create
database privilege.

Mark
--
Mark Rotteveel

[Nick Vaughan]

Mark,

Just to check ....

> Why not just create a directory (e.g. in `/var` and assign ownership to firebird)? Doing it in `/usr/share/doc/firebird3.0-examples` makes no sense, as that is specifically for the firebird3.0-examples package.

So from the Terminal

$ cd /var

$ sudo mkdir -m777 firebirddb

$ sudo chown -R firebird:firebird /var/firebirddb

> You would need to create a user (e.g. by connecting to the security database, or any other database), and then grant that user the create database privilege.

Again from the Terminal

$ isql-fd -u SYSDBA -p sdpwd

SQL > connect security.db;

SQL> CREATE USER UUUU PASSWORD 'upwd' FIRSTNAME 'First' LASTNAME 'Last' ACTIVE TAGS ('UserType' = "ADMIN")  grant admin role;

SQL>exit;

$ isql-fb -u UUUU -p upwd

SQL> CREATE DATABASE 'localhost:/var/firebirddb/fbtest.fdb' PAGE_SIZE = 8192  DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;

SQL>exit;

$ sudo nano /etc/firebird/3.0/databases.conf to add alias fbtest = /var/firebirddb/fbtest.fdb

$ isql-fb -u UUUU -p upwd

SQL> connect fbtest;

SQL> CREATE TABLE ....

Thanks

Nick

[Nick Vaughan]

Understand that everyone has day jobs and so may not be able to comment on this and also that I may not have made it clear that I was seeking some feedback on whether the proposed commands made sense.

If anyone does have any thoughts on the validity of those Linux and isql commands to create a new user and database I would be grateful.

Thanks

Nick

[Christian Diercks]

Well try them out and see if they work... you can find lots of sql info on google.com 

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/b5ed9d8b-834f-4fbf-88b6-29404363f331n%40googlegroups.com.

[Nick Vaughan]

Christian,

That was my original plan and it then occurred to me that they might corrupt the current installation whereas someone more knowledgeable might quickly identify possible flaws.

It's not so  much the actual SQL that I'm concerned about, more about creating the right user-permissions environment.

If I haven't heard anything after a couple of days I'll probably try them out anyway.

Regards

Nick

[Christian Diercks]

Here you can find pretty much all the information: https://www.firebirdsql.org/pdfmanual/html/isql.html

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/d836a53a-d60b-4bd9-a59e-8f7ab3a6dce8n%40googlegroups.com.

[Nick Vaughan]

Christian,

Thanks for that. I've also been looking at the 3.0 Quick-Start Guide supplemented by information from https://firebirdsql.org/manual/ubusetup.html, https://www.firebirdsql.org/en/documentation/ and https://help.ubuntu.com/community/Firebird3.0.

I'll look at that link you sent, although the text does seem familiar.

Regards

Nicl

[Mark Rotteveel]

On 27/03/2024 16:54, Christian Diercks wrote:
> Here you can find pretty much all the information:
> https://www.firebirdsql.org/pdfmanual/html/isql.html

> <https://www.firebirdsql.org/pdfmanual/html/isql.html>

That is actually on old link, I'll make sure it gets a redirect. The
latest version is at
https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/isql/firebird-isql.html

Mark
--
Mark Rotteveel