Install PostgreSQL server on Ubuntu (22.04)
To install install the packages with the following commands
$ sudo apt install postgresql postgresql-contrib
Roles and Databases
Default postgres uses roles to handle authentication and authorization similar to users and groups.
The installation created a user account called postgres as default. To use this account to access postgres you can run the following command to switch over
$ sudo -i -u postgres
After switching over to the account you can access the postgres prompt with the following command
$ psql
To quit the prompt use the command
\q
Creating a New Role
Using the default postgres account, you can create new roles. Log into the postgres account.
$ sudo -i -u postgres
After switching over run the following command and follow the prompts to create a new role
$ createuser --interactive
output:
Enter name of role to add: rolename
Shall the new role be a superuser? (y/n) y
To login with ident based authentication a linux user with the same name as the created role must exist. To create a new user in linux you can use the following command:
$ sudo adduser username
Create a New Database
By default postgres authentication system assumes any role used to log in has a database with the same name that it is able to access.
This means by default if you create a role by the name of “erick” then it would expect a database called “erick” to log into by default.
To create a database first login to the default postgres account:
$ sudo -i -u postgres
After the switch over run the create database command and name it the same as the previously created role:
$ createdb rolename
Access Postgres Prompt with the New Role
Switch over to the user that was created earlier that has a matching role.
$ sudo -u username
then
$ psql
Note: If you would like to connect to a different database than the default user database, you can use the following command to specify the database:
$ psql -d databaseName
Confirm Current Connection Information
Run the following command to see the current database connection information
rolename=# \conninfo