Firebird 3.0 on Xubuntu - CONNECT to security database

[Nick Vaughan]

I'm trying to create databases and users in Firebird 3.0 on Xubuntu without having to go through the root account. I proposed a way of doing this (See "On Xubuntu Fireball 3.0 only accessible through root account" entry timed 26 Mar 2024, 15:05:49 - you can find below the proposed steps.)

Given that the employees database isn't installed a key step is to be able to connect to the Security database (even though Firebird3.0 doesn't use it). I thought that I would isolate and try this step.

The contents of /etc/firebird/3.0/databases.conf is

 ------------------------------
# List of known databases
# ------------------------------

#
# Makes it possible to specify per-database configuration parameters.
# See the list of them and description on file firebird.conf.
# To place that parameters in this file add them in curly braces
# after "alias = /path/to/database.fdb" line. Example:
#    big = /databases/bigdb.fdb
#    {
#        LockMemSize = 32M        # We know that bigdb needs a lot of locks
#        LockHashSlots = 19927    #    and big enough hash table for them
#    }
#

#

#
# Master security database specific setup.
# Do not remove it until you understand well what are you doing!
#
security.db = $(dir_secDb)/security3.fdb
{
    RemoteAccess = false
    DefaultDbCachePages = 50
}

#
# Live Databases:
#

When I go into isql-fb under the SYSDBA user I get

nick@Aspire-one-1-131:~$ isql-fb -u SYSDBA -p PPPPPPP
Use CONNECT or CREATE DATABASE to specify a database
SQL> CONNECT security.fb;
Statement failed, SQLSTATE = HY000
operating system directive access failed
-Not a directory
SQL>

Using catfish to search for "security3" failed to find any files with that name.

Can anyone please say how I might find the security database or another way of connecting to a database without having to go through the Xubuntu root account?

Thanks

Nick

> 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 ....

[Mark Rotteveel]

On 02/04/2024 15:56, Nick Vaughan wrote:
> I'm trying to create databases and users in Firebird 3.0 on Xubuntu
> without having to go through the root account. I proposed a way of doing
> this (See "On Xubuntu Fireball 3.0 only accessible through root account"
> entry timed 26 Mar 2024, 15:05:49 - you can find below the proposed steps.)
>
> Given that the employees database isn't installed a key step is to be
> able to connect to the Security database (even though Firebird3.0
> doesn't use it). I thought that I would isolate and try this step.

Firebird 3.0 does use the security database. Only, when you use
embedded, it does not perform authentication, so you don't have to
provide a password and you can fundamentally connect with any *Firebird*
user to a database.

Connecting to the security database (or any other database) and creating
a user has nothing to do with avoiding or not avoiding root. It is about
creating user credentials for connecting through the Firebird server,
and for being able to give the create database privilege to a *Firebird*
user (unrelated to the *OS* user).

[..]
> When I go into isql-fbunder the SYSDBA user I get

>
> nick@Aspire-one-1-131:~$ isql-fb -u SYSDBA -p PPPPPPP
> Use CONNECT or CREATE DATABASE to specify a database
> SQL> CONNECT security.fb;
> Statement failed, SQLSTATE = HY000
> operating system directive access failed
> -Not a directory
> SQL>

Did you add yourself to the firebird group?

That said, doing that will not help much for connecting to the security
database. If the firebird service is running, then the security database
is opened exclusively by the Firebird process. You would need to stop
the Firebird service first.

I just checked on my Ubuntu VM, and connecting to security.db will work
(if the server isn't running and if you are a member of the firebird
group). On Ubuntu, the security database is located in
/var/lib/firebird/3.0/system/security3.fdb, and as XUbuntu seems to use
the same package, I expect it to exist there as well

However, instead, should create a database through the Firebird service,
and use that to create additional Firebird users.

> Using catfish to search for "security3" failed to find any files with
> that name.
>
> Can anyone please say how I might find the security database or another
> way of connecting to a database without having to go through the Xubuntu
> root account?

Adding yourself to the firebird group (and reloading with newgrp
firebird) should do it.

In any case, I'll try to find time to setup a new VM and walk through
installing Firebird again to see if I missed a step here.

Mark
--
Mark Rotteveel

[Nick Vaughan]

Mark,

Thanks for your response.

Just to let you know these are the firebird processes that I can find

    536 firebird  20   0    7832   1064   1024 S   0.0   0.1   0:00.00 fbguard
    537 firebird  20   0  211032   1936   1740 S   0.0   0.1   0:00.13 firebird

From your earlier comments I'm guessing that I can ignore fbguard although I plan to kill both processes.

Regards

Nick

[Mark Rotteveel]

On 03/04/2024 11:04, Nick Vaughan wrote:
> Just to let you know these are the firebird processes that I can find
>     536 firebird  20   0    7832   1064   1024 S   0.0   0.1   0:00.00
> fbguard
>     537 firebird  20   0  211032   1936   1740 S   0.0   0.1   0:00.13
> firebird
>
> From your earlier comments I'm guessing that I can ignore fbguard
> although I plan to kill both processes.

You should *stop* the service using `/etc/init.d/firebird3.0 stop`, not
kill the process.

Mark
--
Mark Rotteveel

[Nick Vaughan]

Mark,

Thanks. I've now stopped the service

nick@Aspire-one-1-131:~$ /etc/init.d/firebird3.0 stop
Stopping firebird3.0 (via systemctl): firebird3.0.service.
nick@Aspire-one-1-131:~$ top -b -n1 | grep fireb
nick@Aspire-one-1-131:~$

Also I have now joined the firebird group by running

$ sudo usermod -aG firebird nick
$ newgrp firebird

The next steps seem to go into isql-fb and create the user and then the database.

Regards

Nick

[Nick Vaughan]

Mark,

I thought that I have cracked it but am still getting an error message. You can see below what I ran and the outcome.

nick@Aspire-one-1-131:~$ cd /var
nick@Aspire-one-1-131:/var$ sudo mkdir -m770 firebirddb
[sudo] password for nick:
nick@Aspire-one-1-131:/var$ sudo chown -R firebird:firebird /var/firebirddb

nick@Aspire-one-1-131:~$ isql-fb -u nick -p ******

Use CONNECT or CREATE DATABASE to specify a database

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

Use CONNECT or CREATE DATABASE to specify a database

SQL> exit;

(When I look in Thunar then /var/firebirddb exists but I don't have permission to view the contents (it's owned by firebird?))

nick@Aspire-one-1-131:~$ sudo nano /etc/firebird/3.0/databases.conf
(adds the line fbtest.db = /var/firebirddb/fbtest.fbd)

nick@Aspire-one-1-131:~$ isql-fb -u nick -p *******

Use CONNECT or CREATE DATABASE to specify a database

SQL> CONNECT fbtest
CON> ;

Statement failed, SQLSTATE = HY000
operating system directive access failed
-Not a directory
SQL>

I'll keep exploring.

Regards

[Vlad Khorsun]

I thought that I have cracked it but am still getting an error message. You can see below what I ran and the outcome.

nick@Aspire-one-1-131:~$ cd /var
nick@Aspire-one-1-131:/var$ sudo mkdir -m770 firebirddb
[sudo] password for nick:
nick@Aspire-one-1-131:/var$ sudo chown -R firebird:firebird /var/firebirddb

nick@Aspire-one-1-131:~$ isql-fb -u nick -p ******

Use CONNECT or CREATE DATABASE to specify a database

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

Use CONNECT or CREATE DATABASE to specify a database

SQL> exit;

(When I look in Thunar then /var/firebirddb exists but I don't have permission to view the contents (it's owned by firebird?))

You set owner to firebird:firebird few lines above...

 

nick@Aspire-one-1-131:~$ sudo nano /etc/firebird/3.0/databases.conf
(adds the line fbtest.db = /var/firebirddb/fbtest.fbd)

Above you was able to connect to: /var/firebirddb/fbtest.fdb

But alias you added is different:     /var/firebirddb/fbtest.fbd

look at last 3 chars: fdb vs fbd

 

nick@Aspire-one-1-131:~$ isql-fb -u nick -p *******

Use CONNECT or CREATE DATABASE to specify a database

SQL> CONNECT fbtest

You added alias fbtest.db while connecting to the fbtest.

You need to be more attentive.

Regards,

Vlad

[Nick Vaughan]

Vlad,

Thanks for picking up that difference in the file extension. The text you refer to (in the brackets) was an explanation and the actual text in the databases.conf file was correct.

Since trying this installation process I've tried another, using the Ubuntu installation guide and that is now the subject of another conversation.

Regards

Nick

[Mark Rotteveel]

On 02/04/2024 15:56, Nick Vaughan wrote:

> I'm trying to create databases and users in Firebird 3.0 on Xubuntu
> without having to go through the root account. I proposed a way of doing
> this (See "On Xubuntu Fireball 3.0 only accessible through root account"
> entry timed 26 Mar 2024, 15:05:49 - you can find below the proposed steps.)

I did a quick walkthrough of installing Firebird 3.0 on Xubuntu, and
wrote a blog on it. I did not run into any of the problems you
mentioned. You can find the write up at
https://www.lawinegevaar.nl/firebird/installing-firebird-3-on-xubuntu.html

Mark
--
Mark Rotteveel

[Mark Rotteveel]

Something I forgot to mention: If you open a new terminal without
logging out from Xubuntu, then you will have to run newgrp firebird
again, otherwise the newly added firebird group is gone again from your
groups.

Mark
--
Mark Rotteveel

[Nick Vaughan]

Mark,

Thanks very much for your response. I'll have a look at your blog before trying anything else. 

Regards. 

Nick

--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/DSpBHWPFFGg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/0026e0d9-d74c-4c3f-b6aa-076f7acfc11e%40lawinegevaar.nl.