Install PostgreSQL
First, refresh your server's local package index before installing PostgreSQL
sudo apt update
Then, install the Postgres package as well as the -contrib package, which includes some extra utilities and functionality:
sudo apt install postgresql postgresql-contrib
When prompted to confirm installation, press Y. If any services are prompted to be restarted, press ENTER to accept the defaults and continue.
Roles and Databases
Postgres handles authentication and authorization by default using a concept known as "roles." In some ways, these are similar to regular Unix-style users and groups.
Postgres is configured to use ident authentication upon installation, which means it associates Postgres roles with a matching Unix/Linux system account. If a role exists in Postgres, a Unix/Linux username with the same name can sign in as that role
The installation procedure created a user account called postgres, which is associated with the default Postgres role. There are several ways to use this account to access Postgres. One method is to switch to the postgres account on your server by running the following command:
sudo -i -u postgres
Then, run: to get to the Postgres prompt
psql
This will launch the PostgreSQL prompt, from which you can immediately interact with the database management system.
Run the following commands to exit the PostgreSQL prompt:
\q
This returns you to the postgres Linux command prompt. Run the exit command to return to your regular system user
exit
Another way to connect to the Postgres prompt is to use sudo to run the psql command as the Postgres account
sudo -u postgres psql
This will log you into Postgres without the need for an intermediary bash shell.
You can exit the interactive Postgres session once more by typing:
\q
Create Role
If you are logged in as the postgres account, run the following command to create a new role:
createuser --interactive
If you prefer to use sudo for each command instead of switching to your normal account, execute:
sudo -u postgres createuser --interactive
In either case, the script will prompt you with some options before executing the appropriate Postgres commands to create a user based on your responses.
Enter name of role to add: kawigraphics_user
Shall the new role be a superuser? (y/n) yCreate Database
Another default assumption of the Postgres authentication system is that any role used to log in will have a database with the same name that it can access.
This means that if the user you created in the previous section is named Sammy, the role will attempt to connect to a database named Sammy by default. The createdb command can be used to create the necessary database.
If you're logged in as the postgres account, you'd enter something like this:
createdb kawigraphics_db;
If you prefer to use sudo for each command instead of switching to your normal account, run
sudo -u postgres createdb kawigraphics_db;
Opening a Postgres Prompt with the New Role
You'll need a Linux user with the same name as your Postgres role and database to log in with ident based authentication.
If you don't already have a matching Linux user, use the adduser command to create one. You must do the following from a non-root account with sudo privileges (not as the postgres user):
sudo adduser kawigraphics_user;
Once this new account is ready, you can switch over and connect to the database by running the following commands
sudo -i -u kawigraphics_user
psql
Or, you can do this inline:
If all of the components have been properly configured, this command will automatically log you in.
If you want your user to connect to a different database, specify the database name as follows
psql -d postgres
Once logged in, you can use the following command to check your connection information
\conninfo
You are connected to database "kawigraphics_db" as user "kawigraphics_user" via socket in "/var/run/postgresql" at port "5432".
Conclusion
PostgreSQL is now configured on your Ubuntu 22.04 server. Check out the following resources if you want to learn more about Postgres and how to utilize it
