GoAdmio

postgre

Create, change, or delete a PostgreSQL database and its login user for a hosting account, and track it in GoAdmio's database list

On this page: Overview · Quick start · Syntax · Options · Output · Examples · Troubleshooting · Details · See also


Overview

/go/postgre manages PostgreSQL databases for GoAdmio hosting accounts. It is the PostgreSQL counterpart of mysql. It can:

PostgreSQL must already be installed and running (see postgresql).

[!WARNING] Read Known limits before you rely on this command. Some privileges are granted in the wrong database, success messages are printed even when a step failed, and an unknown action word creates a database.


Quick start

# Create the database "shop" with the user "shopuser" for example.com
/go/postgre example.com shop shopuser 'S3cureP@ss'

Syntax

# Create
/go/postgre <domain> <database> <user> <password>

# Change the user's password, or rename the user
/go/postgre <domain> <database> <user> <password> edit

# Delete
/go/postgre <domain> <database> <user> <password> delete
Form Behavior
/go/postgre <domain> <database> <user> <password> Creates the database and user.
... edit If <user> is the database's current user, changes its password. Otherwise, renames the user (see Edit).
... delete Deletes the database and its user. Only <domain> and <database> are used, but <user> and <password> must still be given as placeholders, so that delete is the fifth argument.

Options

This command has no options. It takes five positional arguments.

Argument Required Notes
<domain> Yes The hosting account. When creating, the account must exist in GoAdmio and not be in the recycle bin.
<database> Yes The database name. Use lowercase letters, digits, and underscores, and start with a letter (see Names).
<user> Yes The PostgreSQL user. For delete, it is ignored (the stored user of the database is used).
<password> Yes The user's password. For delete, it is ignored. See the placeholders below.
<action> No edit or delete. Any other value, or none, creates a database. The words are case-sensitive.

Four placeholders in the password are replaced by the characters that are awkward to type in a shell:

Placeholder Becomes
[DollarSign] $
[AndSign] &
[EqualSign] =
[HashSign] #

A password that contains a single quote (') does not work, because the password is placed inside SQL quotes without escaping.

Security

[!WARNING]

  • The password is logged. The command writes all of its arguments to /var/log/go/postgre.log, so the password is stored there in plain text (with the placeholders still in place).
  • The password is also stored in plain text in GoAdmio's PostgreSQL list (/var/lib/sqlite/postgre.db), and it is visible in the process list while the command runs.
  • Remove old passwords from the log, and use a strong, unique password for each database user.

Output

Message Meaning
Database '<name>' created successfully. The create steps ran. This message is printed even if one of the PostgreSQL steps failed.
Database '<name>' was successfully modified. The password of the existing user was changed (edit, same user).
Database <name> deleted successfully... The delete steps ran. It is printed even if a step failed.
Account <domain> not exists The domain is not a hosting account in GoAdmio, or it is in the recycle bin. Create only.
Database '<name>' already exists. Another database with this name is already in GoAdmio's list, for any account. Create only.
Username '<user>' already exists. The user name is already taken.

psql also prints its own result lines, such as CREATE ROLE, GRANT, ALTER DEFAULT PRIVILEGES, and DROP DATABASE, and any error. Read them: they are the real result of each step.

A rename (edit with a different user) and a delete for a database that isn't in the list print no message of their own.

Every run is also recorded in /var/log/go/postgre.log.


Examples

# Create a database and user
/go/postgre example.com shop shopuser 'S3cureP@ss'
# Password with special characters, using placeholders
/go/postgre example.com shop shopuser 'Pa[DollarSign]w0rd[HashSign]1'
# Change the password of the existing user
/go/postgre example.com shop shopuser 'NewP@ss1' edit
# Rename the user of a database
/go/postgre example.com shop newuser 'NewP@ss1' edit
# Delete a database (user and password are placeholders)
/go/postgre example.com shop - - delete
# List the databases and users that PostgreSQL has
sudo -i -u postgres psql -c "\l"
sudo -i -u postgres psql -c "\du"

Troubleshooting

Symptom Likely cause Fix
Account <domain> not exists Misspelled domain, or the account is in the recycle bin. Check the domain, or create the account first with create.
Database ... already exists. or Username ... already exists. The name is in GoAdmio's list already. Names are checked across all accounts. Choose another name.
A database was created when you meant to delete or edit The action word was misspelled or capitalized (Delete, remove), so it counted as a create. Use exactly edit or delete, in lowercase, then clean up the extra database.
The success message appears but the database is missing The message is printed regardless, and a psql step failed (for example PostgreSQL isn't installed, or the database already exists in PostgreSQL). Read the psql output above the message. Check with sudo -i -u postgres psql -c "\l".
The database is in PostgreSQL but not in GoAdmio's list, or the other way round The list is updated first, and a later step failed. Remove the leftover on the side that has it.
permission denied for table ... when the user opens tables, for example after you imported a dump as postgres The table privileges were granted in the wrong database (see Known limits). Grant them in the right database: sudo -i -u postgres psql -d <database> -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <user>;", and the same for ALL SEQUENCES.
permission denied for schema public when the user creates tables, on PostgreSQL 15 and newer Newer versions don't let ordinary users create objects in public, and this command does not grant it. sudo -i -u postgres psql -d <database> -c "GRANT ALL ON SCHEMA public TO <user>;"
delete prints nothing No database with that name and domain is in GoAdmio's list. Check the name and domain.
After delete, the user still exists: role ... cannot be dropped because some objects depend on it Privileges that the command granted in the postgres database still refer to the user. Remove them, or drop the user by hand.
database ... is being accessed by other users An application or session is still connected. Disconnect it, then delete again.
The user was renamed, but the old user is still there The old user still owns tables, so it can't be dropped. Reassign or drop its objects, then drop the old user.
The password in GoAdmio is different from the real one edit changes the password in PostgreSQL only, and a rename does not store the new password in GoAdmio either. Treat PostgreSQL as the truth, and update GoAdmio's record if needed.
Errors with names that have capitals or hyphens Names are used unquoted, so PostgreSQL lowercases them and rejects special characters. See Names.

Details

Create

  1. Checks that the account exists and is not in the recycle bin.
  2. Checks that neither the database name nor the user name is already in GoAdmio's list.
  3. Adds a row (database, user, password, domain, the account's system user name, and the time) to /var/lib/sqlite/postgre.db.
  4. Creates the database as the postgres user.
  5. Creates the user with the password.
  6. Grants the user all privileges on the database.
  7. Grants all privileges on the tables of the public schema, and sets default privileges for future tables.

Steps 4 to 7 run as the postgres database user.

Edit

Situation What happens
<user> is the database's current user Changes the user's password in PostgreSQL. Prints ... was successfully modified.
<user> is a different name Checks that the name is not already used in the same domain, updates the user in GoAdmio's list, creates the new user, makes it the owner of the database, drops the old user, and grants privileges. Prints no message.

The duplicate check for a rename only compares against the user of the domain's databases and only works reliably when the domain has a single database. It does not check other accounts.

Delete

  1. Looks up the database and its user in GoAdmio's list by name and domain.
  2. If found, deletes the row, then runs DROP DATABASE and DROP USER, and prints Database <name> deleted successfully....
  3. If not found, does nothing and prints nothing.

Known limits

Names

PostgreSQL folds unquoted names to lowercase and accepts only letters, digits, underscores, and (inside the name) dollar signs. Names are used unquoted here, so use lowercase letters, digits, and underscores, start with a letter, and avoid words that PostgreSQL reserves (for example user).


See also

Command Description
postgresql Install and start PostgreSQL
mysql Create, edit, or delete a MySQL/MariaDB database and user for a hosting account
mariadb2postgre Convert a SQL file from MariaDB to PostgreSQL
pgloader Install PGLoader
postgresync Copy PostgreSQL data to a remote server
create Create a GoAdmio account from the command line