36.12. Managing PostgreSQL Users
As you would expect, the PostgreSQL database server does not simply allow anyone to connect and start manipulating data. Instead, it verifies clients by requiring them to send a username and password, which it checks against its own internal user list. This list of database users is totally separate from the UNIX user list in the /etc/passwd file.
By default, only the user postgres will exist, and he will have full access to all databases and tables. If you are writing an application that uses a database, a new user should be created for that application to log in as. If multiple people will be accessing your database using the psql command or other client programs, each should have his own login and password.
To add a new user, follow these steps:
1. | On the module's main page, click on the PostgreSQL Users icon. This will take you to a list of existing users and their abilities, as shown in Figure 36.4. Figure 36.4. Existing PostgreSQL users.
|
2. | Click on the Create a new user link above or below the list, which will bring up the user creation form. |
3. | Enter a unique name for the user, made up of only letters, numbers, and the _ character, into the Username field. |
4. | To set a password for this user, select the second radio button in the Password field and enter a password into the text box next to it. If None is chosen, the user will not be able to log in unless the server has been configured to allow connections without a password (as explained in Section 36.14 “Restricting Client Access”). |
5. | If you want this user to be able to create his own databases, change the Can create databases? field to No. Only the master administration user (postgres) really needs to be able to do this. |
6. | To give this user the rights to edit and create PostgreSQL users, change the Can create users? field to Yes. Again, this should normally be left as No. |
7. | The Valid until field controls for how long this user can be used. If Forever is selected, it will have no expiry date—but if the second option is chosen and a date in YYYY-MM-DD format is entered into the text field, the account will not be usable after that date. |
8. | Click the Create button to have the new account added to PostgreSQL's user list. People or programs will be able to log in as this user immediately. Often the database server is set up by default to allow any local user to log in without needing to supply a password at all. To change this, see Section 36.14 “Restricting Client Access”. |
9. | To configure exactly which tables and views this new user can access, follow the instructions in Section 36.15 “Editing Object Privileges”. |
10. | Once a user has been created, it can be edited by clicking on its name in the user list shown in Figure 36.4. This takes you to the editing form that is almost identical to the user creation form, except that the user's name cannot be changed. Once you have finished modifying the password, expiry date, and other fields, hit the Save button to make the changes active. |
A user can be deleted as well by clicking the Delete button on its editing page. Be careful not to remove the postgres user, as it is normally used by this Webmin module to log in to PostgreSQL. In fact, even editing this user can cause problems if you set an expiry date or take away the user's ability to create databases or other users.
Like many other modules, this module can be configured to automatically create, update, or delete a PostgreSQL user when a UNIX user is added, modified, or removed, respectively. This can be useful if you allow some of the UNIX users on your system to access databases and want to keep their passwords in sync.
To set up synchronization, follow these steps:
1. | On the module's main page, click on the PostgreSQL Users icon and scroll down to the form below the list of existing accounts. |
2. | If you want a new PostgreSQL user to be created for each new UNIX user, check the Add a new PostgreSQL user when a UNIX user is added box. Automatically created users will not, however, have any specific object permissions. |
3. | If you want PostgreSQL users to have their passwords changed when the same thing happens to matching UNIX users, check the Update a PostgreSQL user when the matching UNIX user is modified box. |
4. | To have a PostgreSQL user deleted at the same time as the UNIX user of the same name, check the Delete a PostgreSQL user when the matching UNIX user is deleted box. |
5. | Hit the Save button to make the new synchronization settings active. |