Adding a user means to allow a user, identified by a database user name and optionally by a password, to read and to modify a particular refdb database.
Note: Both the database user name and the database password may be different from the login name and login password used to get access to the operating system.
Some database engines like SQLite do not support access control. There is no need to add or remove users with these database engines.
refdb uses the access control facilities of the database server. Therefore, the details of adding a user differ slightly between MySQL and PostgreSQL. In any case, refdb is designed to simplify user management as far as possible. This is mainly achieved by combining the information necessary to authenticate a user and the information necessary to grant access to specific database objects into a single command, the adduser command of refdba. This command also silently takes care of the fact that users need access to an internal refdb database in addition to the specified reference database.
Database servers use the username, optionally a password, and the host a user tries to connect from to authenticate a user. From the database server's point of view, refdbd is the database client that actually connects to the server, not the refdb clients refdba, refdbc, and refdbib. Database servers usually distinguish between local connections (i.e. by users logged into the same computer) and remote connections (i.e. all connections via TCP/IP). If both refdbd and your database server run on the same computer, all connections will be local and the host-based access control is fairly simple, as both MySQL and PostgreSQL allow local connections by default. All you need to do is:
refdba: adduser -d foo jack -N newpass |
This will allow the new user "jack" to connect to the database server and to access the contents of the database "foo". "jack" has to identify himself by providing the password "newpass" when starting a refdb client.
Note: If a user is already known to the database server, e.g. if he already has access to a different database, you don't have to specify the password again. If you do provide a password, the password of that particular user will be changed to the new one.
Things get a tad more complicated if refdbd and your database server do not run on the same computer. As this is where MySQL and PostgreSQL differ, we'll look at these two cases separately.
On many operating system distributions, MySQL is configured to accept only local connections. Either the mysqld process is started with the --skip-networking option, or the my.conf configuration file contains the corresponding option skip-networking. In order to allow remote connections, please remove these options from your system.
MySQL allows to alter the host-based component of access control through the SQL interface. The adduser command has an additional option -H to specify the host or the network where refdbd runs:
refdba: adduser -d foo -H % jack -N newpass |
In this example, refdbd may run on any host. You can as well specify a subnet (-H 192.168.1.%) or one specific host (-H mono.mycomp.com).
By default, PostgreSQL will accept only local connections. To allow remote connections, the postmaster process must be started with the -i option. Change your start scripts accordingly.
The host-based component of access control is not available through the SQL interface in this database server. The -H option of the adduser command is therefore ignored. Instead the database administrator has to edit the configuration file /home/pgsql/data/pg_hba.conf. The following entries would:
Allow local access (i.e. refdbd and postmaster run on the same computer) to the databases refs and refdb. The latter is an internal refdb database that users must be able to access. Users must provide a password.
Allow access to the databases refs and refdb through refdbd instances that run somewhere in the network "192.168.1.0". Users must provide a password.
# host DBNAME IP_ADDRESS ADDRESS_MASK AUTH_TYPE [AUTH_ARGUMENT]
local refdb crypt
local refs crypt
host refdb 192.168.1.0 255.255.255.0 crypt
host refs 192.168.1.0 255.255.255.0 crypt
|
The default pg_hba.conf file allows local access to all databases without password protection. This may not be what you want. The file contains a bunch of helpful comments, though. You may also want to peruse the PostgreSQL documentation for more information about host-based access control.
This is again done with the adduser, using the -r switch. The access rights will be revoked for the specified username and database. No other access rights will be modified. The following command will revoke the access rights of user "jack" on the database "foo".
refdba: adduser -d foo -r jack |
Keep in mind that user "jack" still can access the refdb main database refdb and any other databases he was granted access to.
If you use MySQL as your database server and refdbd runs on a different box than mysqld, you also have to specify the host or network with the -H option, just like when you added the user in the first place. See the Add users section for the specifics.