Powerful and flexible open source relational database management system. Get access to a community-backed database through Linode’s PostgreSQL One-Click App.
As the world’s most advanced open source database, PostreSQL (also known as Postgres) is the DB of choice for developers who need advanced features and increased security. The Linux-based database supports many data types and sophisticated locking for advanced out-of-the-box security compared to other databases. Postgres is the most scalable solution for developers and organizations with evolving data structures and volume.
Getting Started After Deployment
After the PostgreSQL One-Click App server has finished installing, you will be able to access PostgreSQL from the console via ssh with your Linode’s IPv4 address:
- SSH into your Linode and create a limited user account.
- Log out and log back in as your limited user account.
- Update your server:
sudo apt-get update && apt-get upgrade
By default, PostgreSQL will create a Linux user named
postgres to access the database software.
postgres user should not be used for other purposes (e.g. connecting to other networks). Doing so presents a serious risk to the security of your databases.
- Change the
postgresuser’s Linux passowrd:
sudo passwd postgres
- Issue the following commands to set a password for the
postgresdatabase user. Be sure to replace
newpasswordwith a strong password and keep it in a secure place.
su - postgres psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"
This user is distinct from the
postgres Linux user. The Linux user is used to access the database, and the PostgreSQL user is used to perform administrative tasks on the databases.
The password set in this step will be used to connect to the database via the network. Peer authentication will be used by default for local connections. See the Secure Local PostgreSQL Access section for information about changing this setting.
Create a Database
- Create a sample database called
- Connect to the test database:
- You will see the following output:
psql (12.2 (Debian 12.2-2.pgdg90+1)) Type "help" for help. mytestdb=#
This is the PostgreSQL client shell, in which you can issue SQL commands. To see a list of available commands, use the
\hcommand. You may find more information on a specific command by adding it after
This section contains examples which create a test database with an employee’s first and last name, assigning each a unique key. When creating your own tables, you may specify as many parameters (columns) as you need and name them appropriately. Run the commands in this section from the PostgreSQL client shell that you opened to create
- Create a table called “employees” in your test database:
CREATE TABLE employees (employee_id int PRIMARY KEY, first_name varchar, last_name varchar);
- Insert a record into the table:
INSERT INTO employees VALUES (1, 'John', 'Doe');
- View the contents of the “employees” table:
SELECT * FROM employees;
This produces the following output:
employee_id | first_name | last_name
-------------+------------+----------- 1 | John | Doe
- Exit the PostgreSQL shell by entering the
Create PostgreSQL Roles
PostgreSQL grants database access through roles which are used to specify privileges. Roles can be understood as having a similar function to Linux “users.” In addition, roles may also be created as a set of other roles, similar to a Linux “group.” PostgreSQL roles apply globally, so you will not need to create the same role twice if you’d like to grant it access to more than one database on the same server.
The example commands in this section should be run as the
postgres Linux user.
- Add a new user role, then a password at the prompt:
createuser examplerole --pwprompt
If you need to delete a role, you can use the
dropusercommand in place of
- Connect to the database:
You’ll be connected as the
postgresdatabase user by default.
- From the PostgreSQL shell, enter the following to grant all privileges on the table
employeesto the user
GRANT ALL ON employees TO examplerole;
- Exit the PostgreSQL shell by entering
The PostgreSQL One-Click App was built by Linode. For support regarding app deployment, contact Linode Support via the information listed in the sidebar. For support regarding the tool or software itself, visit PostgreSQL Support.