# PostgreSQL tutorial

## #1. Connecting to an AWS DB Instance Running the PostgreSQL Database Engine using psql:

You can use a local instance of the psql command line utility to connect to a PostgreSQL DB instance. You need either PostgreSQL or the psql client installed on your client computer.

Use one of the following formats to connect to a PostgreSQL DB instance on Amazon RDS. When you connect, you’re prompted for a password. For batch jobs or scripts, use the –no-password option:

```yaml
psql \\\\
   --host=<DB instance endpoint> \\\\
   --port=<port> \\\\
   --username=<master user name> \\\\
   --password \\\\
   --dbname=<database name>
```

For example, the following command connects to a database called mypgdb on a PostgreSQL DB instance called mypostgresql using fictitious credentials:

```yaml
psql --host=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=5432 --username=awsuser --password --dbname=mypgdb
```

**Notes:**

* In the case that you do not use RDS, and want to login to psql console: The PostgreSQL installation creates a “UNIX USER” called `postgres`, who is ALSO the “Default PostgreSQL’s SUPERUSER”. To run psql using “UNIX USER” postgres, you need to invoke:
    

```yaml
sudo -u postgres psql
```

* The PostgreSQL’s prompt is in the format of databaseName=# for superuser or databaseName=&gt; for regular user. In this case, the current database is also called postgres, which is the same as the username.
    
* The prompt changes to -# (superuser) or -&gt; (regular user) for command continuation.
    
* Type `help` to see the help menu.
    
* Type `\\\\q`to quit.
    
* Type `\\\\?` to see all the psql commands.
    
* You need to end a SQL statement with a semicolon (;) or \\g (for GO) to execute the statement. For example, in “SELECT version();”.
    
* Comments: In a SQL script or command, an end-of-line comment begins with –; a block comment is enclosed by /\* and \*/
    

## #2. Working with database:

### 2.1. Basic commands:

A running PostgreSQL server can manage many databases. Typically, a separate database is used for each project or for each user.

* List all databases: List all databases via \\l (or \\list), or \\l+ for more details:
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105835238/94561da4-ca50-4239-b147-b6590ec84bca.png align="center")

* Help on SQL command syntax:
    

```yaml
\\\\h <command>
```

For example:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105853984/bd393054-ff41-41bc-89e0-8282f3055346.png align="center")

* Create a new database called mytest:
    

```yaml
CREATE DATABASE mytest;
```

By default, the owner of the database is the current login user

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105870085/7f82ec4a-c044-418f-8a1f-db81addb64f1.png align="center")

* Connect to mytest database via \\c (or \\connect):
    

```yaml
\\\\c mytest
```

You can display all tables (aka relations) via \\dt or \\dt+ for more details.

* We will be using an enumeration (TYPE) in our table, let’s define it:
    

```yaml
CREATE TYPE cat_enum AS ENUM ('coffee', 'tea');
```

Display all types via /dT or /dT+ for more details:

```yaml
/dT+
```

* Create a new table:
    

```yaml
CREATE TABLE IF NOT EXISTS cafe (
  id SERIAL PRIMARY KEY,        -- AUTO_INCREMENT integer, as primary key
  category cat_enum NOT NULL,   -- Use the enum type defined earlier
  name VARCHAR(50) NOT NULL,    -- Variable-length string of up to 50 characters
  price NUMERIC(5,2) NOT NULL,  -- 5 digits total, with 2 decimal places
  last_update DATE              -- 'YYYY-MM-DD'
);
```

* Display all tables in the current database, via \\dt or \\dt+ for more details:
    

```yaml
\\\\dt+
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105893399/774ba965-2573-4230-a1ae-f88bf5b34f3f.png align="center")

* Display details of a particular table:
    

```yaml
\\\\d+ cafe
```

* Insert rows:
    

```yaml
INSERT INTO cafe (category, name, price) VALUES
  ('coffee', 'Espresso', 3.19),
  ('coffee', 'Cappuccino', 3.29),
  ('coffee', 'Caffe Latte', 3.39),
  ('coffee', 'Caffe Mocha', 3.49),
  ('coffee', 'Brewed Coffee', 3.59),
  ('tea', 'Green Tea', 2.99),
  ('tea', 'Wulong Tea', 2.89);
```

The output shows the OID (Object Identification Number) and the rows affected. — Query (SELECT):

```yaml
SELECT * FROM cafe;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105918306/475050d4-5445-473c-bd3e-9b82e2e94857.png align="center")

* Update:
    

```yaml
UPDATE cafe SET price = price * 1.1 WHERE category = 'tea';
```

* Delete:
    

```yaml
DELETE FROM cafe WHERE id = 6;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105932764/0e3b79c7-5bd3-4f71-90ba-4202e1594259.png align="center")

**Notes:**

+) Take note that there are two sets of commands:

* psql commands: such as `\\\\c`, `\\\\l`, `\\\\dt`.
    
* SQL commands: such as `CREATE DATABASE`, `CREATE TABLE` and `SELECT`. SQL command KEYWORDS are not case sensitive. I show them in uppercase for clarity. PostgreSQL automatically converts all names (identifiers) to lowercase (even you type in uppercase or mixed case). To use uppercase or mixed case, you need to double-quote the names.
    

+) You need to end your SQL commands with a semi-colon (;) or \\g. If you forget to enter a semi-colon, the command-prompt changes to “dbname-#” to indicate continuation (A SQL command can span many lines). You can enter the semi-colon on the new line.

**More on psql commands:**

+) `\\\\?`: show all psql commands.

+) `\\\\h sql-command`: show syntax on SQL command.

+) `\\\\c dbname [username]`: Connect to database, with an optional username (or \\connect).

+) Display Commands: You can append + to show more details.

* `\\\\l`: List all database (or \\list).
    
* `\\\\d`: Display all tables, indexes, views, and sequences.
    
* `\\\\dt`: Display all tables.
    
* `\\\\di`: Display all indexes.
    
* `\\\\dv`: Display all views.
    
* `\\\\ds`: Display all sequences.
    
* `\\\\dT`: Display all types.
    
* `\\\\dS`: Display all system tables.
    
* `\\\\du`: Display all users.
    
* `\\\\x auto|on|off`: Toggle|On|Off expanded output mode.
    

### 2.2. Using Utility “createuser” and “createdb”:

As an example, let’s create a PostgreSQL user called `testuser`, and a new database called `testdb` owned by `testuser`.

* Create a new PostgreSQL user called testuser, allow user to login, but NOT creating databases:
    

```yaml
sudo -u postgres createuser --login --pwprompt testuser
```

* Create a new database called testdb, owned by testuser:
    

```yaml
sudo -u postgres createdb --owner=testuser testdb
```

* Tailor the PostgreSQL configuration file /var/lib/pgsql/data/pg\_hba.conf to allow non-default user testuser to login to PostgreSQL server, by adding the following entry:
    

```yaml
# TYPE  DATABASE    USER        ADDRESS          METHOD
local   testdb      testuser                     md5
```

* Restart PostgreSQL server:
    

```yaml
sudo service postgresql restart
```

* Login to PostgreSQL server:
    

```yaml
psql -U testuser testdb
```

***Note:*** If you encounter the error: `psql: FATAL: Peer authentication failed for user "testuser"` You can fix by:

Locate to your pg\_hba.conf file, and find this line:

```yaml
local   all             postgres                                peer
```

change it to:

```yaml
local   all             postgres                                md5
```

and then restart PostgreSQL server.

* List all databases (\\list or \\l):
    

```yaml
\\\\list
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105948804/c4dab0a0-bf26-472c-a8d2-de03c99e4e24.png align="center")

## #3. PostgreSQL administration:

### 3.1. Default Superuser “postgres”:

During installation, a “UNIX USER” (who cannot login to the system interactively) called postgres is created. You can verify by checking the entry in `/etc/passwd` and `/etc/shadow`:

```yaml
sudo less /etc/passwd | grep postgres
```

and:

```yaml
$ sudo less /etc/shadow | grep postgres
```

The user postgres belongs to the following groups:

```yaml
groups postgres
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105961644/ccc3097b-7f88-4bbe-9088-d236d033791e.png align="center")

An entry is also defined in PostgreSQL’s HBA (Host-Based Authentication) configuration file for this user:

```yaml
sudo less /var/lib/pgsql/data/pg_hba.conf
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105971890/8b320fb3-51a4-4a6e-ac18-6c7abb98729e.png align="center")

### 3.2. Authentication Methods:

PostgreSQL supports a number of authentication methods. The commonly-used are:

* `ident, peer`: identical (or fully match) to the OS account, with an optional name mapping defined in pg\_ident.conf file. ident is applicable to TCP/IP; while peer for “local” connection.
    
* `md5`: require md5-hashed password (most common).
    
* `password`: require clear-text password.
    
* `trust`: no password, as long as meeting the IP, user, and database criteria defined in the HBA.
    
* `reject`: reject login immediately.
    
* others, such as `GSSAPI, SSPI, Kerberos, LDAP, RADIUS, Certificate, PAM`.
    

There are two ways to login PostgreSQL:

1/ By running the “psql” command as a UNIX user which is also configured as PostgreSQL user using so-called IDENT/PEER authentication, e.g., “sudo -u postgres psql”.

2/ Via TCP/IP connection using PostgreSQL’s own managed username/password (using so-called MD5 authentication).

### 3.3. Set a Password for User postgres:

To set a password, login to PostgreSQL server using postgres via psql and issue command “\\password username”, as follows:

* Login in to server via “psql” with user “postgres”:
    

```yaml
sudo -u postgres psql
```

* Change password for current user “postgres”:
    

```yaml
\\\\password postgres
```

* Display the user table:
    

```yaml
SELECT * FROM pg_user;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713105984772/748b13ed-b26b-4c28-afb5-31e7504d8acd.png align="center")

To test the password login, you need to change the the authentication method from “peer” to “md5” in `pg_hba.conf`. Restart the server, and login via `sudo -u postgres sql`. The system will prompt you for the password.

### 3.4. Add your UNIX user as PostgreSQL user:

Using user “postgres” (which is a UNIX user as well as PostgreSQL user) to run `psql` involves switching user (via `sudo -u <username>` or `su - <username>`). You can simply the process by configuring your current UNIX userID as PostgreSQL user, as follows:

* Switch to default superuser “postgres”, — run utility “createuser” to create a superuser same name as current login. — “$USER” is an environment variable denoting the current login user.
    

Command:

```yaml
sudo -u postgres createuser --superuser $USER
```

* Create the default database which shall be the same as the username:
    

```yaml
sudo -u postgres createdb $USER
```

* Now, you can invoke “psql” from your user account
    

```yaml
psql
```

![https://live.staticflickr.com/65535/49969433281_6f8dddddd1_z.jpg](https://live.staticflickr.com/65535/49969433281_6f8dddddd1_z.jpg align="left")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713106005637/1de1f989-1136-43b4-963a-6174af3565a3.png align="center")

### 3.5. Group and User:

### 3.5.1. Create role:

The latest PostgreSQL treats both group and user as “role”. Some roles can login (i.e. user), some roles have member of other roles (i.e., group).

Roles are different from traditional Unix-style permissions in that there is no distinction between users and groups. Roles can be manipulated to resemble both of these conventions, but they are also more flexible.

For instance, roles can be members of other roles, allowing them to take on the permission characteristics of previously defined roles. Roles can also own objects and control access to those object for other roles.

You should use CREATE ROLE to create both users and groups (CREATE USER and CREATE GROUP are meant for compatibility).

* Create a login user role:
    

```yaml
CREATE ROLE user1 LOGIN PASSWORD 'xxxx' CREATEDB VALID UNTIL 'infinity';
```

* Create a login superuser role:
    

```yaml
CREATE ROLE user2 LOGIN PASSWORD 'xxxx' SUPERUSER VALID UNTIL 'YYYY-MM-DD';
```

* Create a group role:
    

```yaml
CREATE ROLE group1 INHERIT;
```

* Add a user (or group) to this group:
    

```yaml
GRANT group1 TO user1;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1713106028704/d406dc07-80aa-432c-a50a-d0753bf9b9f0.jpeg align="center")

### 3.5.2. Define Privileges Upon Role Creation:

We can do this by specifying the permissions we want after the main create clause:

```yaml
CREATE ROLE <role_name> WITH <optional_permissions>;
```

![https://live.staticflickr.com/65535/49969758762_ae46670e49_z.jpg](https://live.staticflickr.com/65535/49969758762_ae46670e49_z.jpg align="left")

If we want to get to this state without specifying the “login” attribute with every role creation, we can actually use the following command instead of the “CREATE ROLE” command:

```yaml
CREATE USER <role_name>;
```

The only difference between the two commands is that “CREATE USER” automatically gives the role login privileges.

### 3.5.3. Use Group Roles & alter ownership in PostgreSQL:

### 3.5.3.1. Use group roles:

Any member of a group role can act as the group role they are a member of by using the “set role” command.

Since the “postgres” user we are logged into currently has superuser privileges, we can use “set role” even though we are not a member of that group:

```yaml
SET ROLE group1;
```

Now, any tables that are created are owned by the group1 role:

```yaml
CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);
```

We can check the table ownership by issuing this command:

```yaml
\\\\d
```

![https://live.staticflickr.com/65535/49969567886_09db6ca7e1_z.jpg](https://live.staticflickr.com/65535/49969567886_09db6ca7e1_z.jpg align="left")

As you can see, the new table (and the sequence associated with the serial data type) is owned by the “group1” role.

We can get back to our original role permissions with the following command:

```yaml
RESET ROLE;
```

If we give a user the “inherit” property with the “alter role” command, that user will automatically have all of the privileges of the roles they belong to without using the “set role” command:

```yaml
ALTER ROLE dev INHERIT;
```

Now `dev` will have every permission of the roles it is a member of.

### 3.5.3.2. Alter the ownership:

You can delete a role using the following syntax:

```yaml
DROP ROLE <role_name>;
```

or to avoid not found error:

```yaml
DROP ROLE IF EXISTS <role_name>;
```

Now we try to remove a group role (or any role) with the “drop role” command:

```yaml
DROP ROLE group1;
```

![https://live.staticflickr.com/65535/49969843177_66cf00cf83_z.jpg](https://live.staticflickr.com/65535/49969843177_66cf00cf83_z.jpg align="left")

This will give you an error, because we created a table that is owned by “group1”. We can solve this problem by transferring ownership to a different role, the syntax is:

```yaml
ALTER TABLE <table_name> OWNER TO <role>;
```

and in this case:

```yaml
ALTER TABLE hello OWNER TO demo_role;
```

If we check, we can see that “group1” no longer owns any of the tables:

```yaml
\\\\d
```

We can now drop the “group1” role successfully by issuing the command again:

```yaml
DROP ROLE group1;
```

This will destroy the group1 role. The former members of “group1” (`dev` role) is not removed.

![https://live.staticflickr.com/65535/49969078913_0b7f3beb84_z.jpg](https://live.staticflickr.com/65535/49969078913_0b7f3beb84_z.jpg align="left")

### Note:

***+) By default, the owner of the database is the current login user. If you want to alter the owner of a database:***

```yaml
ALTER DATABASE <database_name> OWNER TO <role>;
```

***+) Alter schema’s owner:***

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and [operators.To](http://operators.to/) access an object of a schema, you qualify its name with the schema name as a prefix:

```yaml
schema_name.object_name
```

For example, you may have sales schema that has staff table and the public schema which also has the staff table. When you refer to the staff table you must qualify it as follows:

```yaml
public.staff
```

If you want to alter the ownership of a schema, use the command:

```yaml
ALTER SCHEMA <schema_name> OWNER TO <role>;
```

![https://live.staticflickr.com/65535/49970012532_6fa41a51a7_z.jpg](https://live.staticflickr.com/65535/49970012532_6fa41a51a7_z.jpg align="left")

***Important note:***

> You must own the schema to use ALTER SCHEMA. To alter the owner, you must also be a direct or indirect member of the new owning role, and you must have the CREATE privilege for the database. (Note that superusers have all these privileges automatically.)

### 3.5.4. Change Privileges of Roles in PostgreSQL:

To change the attributes of an already created role, we use the “ALTER ROLE” command.

This command allows us to define privilege changes without having to delete and recreate users.

```yaml
ALTER ROLE <role_name> WITH <attribute_options>;
```

For example:

![https://live.staticflickr.com/65535/49969768172_c091f7e255_z.jpg](https://live.staticflickr.com/65535/49969768172_c091f7e255_z.jpg align="left")

### 3.5.5. Log In as a Different User in PostgreSQL:

```yaml
psql -U <user_name> -d <database_name> -h 127.0.0.1 -W
```

and then enter the password for the user\_name that you have created.

### 3.5.6. Grant Permissions in PostgreSQL:

When a database or table is created, usually only the role that created it (not including roles with superuser status) has permission to modify it. We can alter this behavior by granting permissions to other roles.

We can grant permissions using the “GRANT” command. The general syntax:

```yaml
GRANT <permission_type> ON <table_name> TO <role_name>;
```

For example:

Create a simple table

```yaml
CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);
```

We can see the result with:

```yaml
\\\\d
```

![https://live.staticflickr.com/65535/49969530511_c9494b3bd6_z.jpg](https://live.staticflickr.com/65535/49969530511_c9494b3bd6_z.jpg align="left")

We can now grant some privileges to the new “demo” table to “testuser”. Give the user “UPDATE” privileges with the following command:

```yaml
GRANT UPDATE ON demo TO testuser;
```

We can grant full permissions to a user by substituting the permission type with the word “all”:

```yaml
GRANT ALL ON demo TO testuser;
```

If we want to specify permissions for every user on the system, we can use the word “public” instead of a specific user:

```yaml
GRANT INSERT ON demo TO PUBLIC;
```

To view the grant table, use the following command:

```yaml
\\\\z
```

![https://live.staticflickr.com/65535/49969542746_66ede1efd4_z.jpg](https://live.staticflickr.com/65535/49969542746_66ede1efd4_z.jpg align="left")

### 3.5.7 Remove Permissions in PostgreSQL:

You can remove permissions using the “REVOKE” command. The revoke command uses almost the same syntax as grant:

```yaml
REVOKE <permission_type> ON <table_name> FROM <user_name>;
```

Again, we can use the same shorthand words (all and public) to make the command easier:

```yaml
REVOKE INSERT ON demo FROM PUBLIC;
```

![https://live.staticflickr.com/65535/49969813772_a782815034_z.jpg](https://live.staticflickr.com/65535/49969813772_a782815034_z.jpg align="left")