PostgreSQL databases¶
You can manage PostgreSQL with a Satan tool.
Naming convention
Every user has an unique prefix added to the name of the database: pg keyword, your UID and underscore _. Then comes any name you desire, e.g. pg1234_forumdb. Maximum length of the name with prefix is 14 characters. You can use letters and numbers.
Known flaws
User information such as grants and password is not shown correctly by the satan pgsql list command.
Syntax¶
The syntax is as follows:
satan pgsql adddb <database>
satan pgsql deldb <database>
satan pgsql adduser <username> <password>
satan pgsql adduser <username> stdin
satan pgsql deluser <username>
satan pgsql grant <username> <database>
satan pgsql revoke <username> <database>
satan pgsql list
You can display syntax anytime with satan pgsql help command.
| Command | Description |
|---|---|
| satan pgsql adddb <database> | Create a new database. Remember about database naming convention. |
| satan pgsql deldb <database> | Remove selected database. |
| satan pgsql adduser <username> <password> | Add a new database user. Password is specified in command line. |
| satan pgsql adduser <username> stdin | As above but you will be prompted for the password. |
| satan pgsql deluser <username> | Remove a database user. |
| satan pgsql grant <username> <database> | Grant ALL PRIVILEGES WITH GRANT OPTION to the specified user on chosen database. To use different privileges you need to create two users. First with all privileges and second with no privileges at all. Then from the frist user you can grant any privileges you want to the second user. |
| satan pgsql revoke <username> <database> | Revoke privileges from the user on the specified database. |
| satan pgsql list | List all databases and users. User listing doesn’t work completely. See known flaws. |
Examples¶
Creating database¶
In the first example we will create database called my1234_wiki. In the real world example change the number to your UID:
$ id -u
1234
$ satan pgsql adddb pg1234_wiki
$ satan pgsql list
Databases
pg1234_wiki
Users
No users.
Adding user¶
Now we will add new user of the same name as the database. Using stdin argument at the end we will be prompted for password:
$ satan pgsql adduser pg1234_wiki stdin
Enter your password (will not be shown):
$ satan pgsql list
Databases
pg1234_wiki
Users
Username Privileges Grant on Password
pg1234_wiki - - no
Privileges and grants will not be displayed correctly by satan. Please see known flaws for details.
Granting privileges¶
To grant all privileges to recently created user type:
$ satan pgsql grant pg1234_wiki pg1234_wiki
$ satan pgsql list
Databases
pg1234_wiki
Users
Username Privileges Grant on Password
pg1234_wiki - - no
Connecting database¶
Host of the database is pgsql.rootnode.net. Every Rootnode server can connect to PostgreSQL directly.
Local connection using pgsql¶
You can connect to PostgreSQL locally with the pgsql command:
$ psql -h pgsql.rootnode.net -U pg1234_wiki pg1234_wiki
Password for user pg1234_wiki:
psql (8.4.5, server 8.4.4)
Type "help" for help.
pg2961_wiki=>
With -U parameter you specify the username. Last argument is the database name. More information about this tool you can find at http://www.postgresql.org/docs/8.2/static/app-psql.html
From outside world via SSH tunnel¶
To connect from outside world with any database software you like, you need to create a secure tunnel. It is needed because we do not support SSL connections for PostgreSQL and traffic is limited only to Rootnode network.
The simplest tunnel you can create with SSH on your local machine:
$ ssh -C -L 1234:pgsql.rootnode.net:5432 yourlogin@s2.rootnode.net
Now you can connect to database over tunnel using host localhost and port 1234.
Backup¶
At the moment, we do automatic backup of all databases every day. You do not need to configure anything. In the future you will need to use Performing data backups in order to perform backup.