Getting Started with PostgreSQL on Mac Part 2

This time we will learn:

1. Changing role password
2. Creating a role using postgres utility and psql
3. Adding attributes to a role during creation
4. Connect to a cluster as a role
5. Deleting a role using postgress utility and psql
6. Connect to a cluster as a role

Please make sure the postgres server is running. Check this Getting Started with PostgreSQL on Mac Part 1.

Changing the postgres default password

It’s a best practice to change the postgres password for security reason.

1. Enter this to open postgres shell:

   BASH

   Collapse Copy

   1psql postgres

   Click to expand and view more

2. Enter this in postgres shell to change the default password (no passowrd), the it will prompt to enter a new password:

   BASH

   Collapse Copy

   1\password

   Click to expand and view more

Creating a role

By default after installation, a default postgres user or role with your Mac username will be created as a superuser. Creating a new role instead of using the default superuser role is always a best practice.

If you want to add more there are two ways to create a role (user):

1. Through the PostgreSQL utilities (installed with postgres installation) in the terminal.

   Using client utilities createuser .

   BASH

   Collapse Copy

   1createuser <username>

   Click to expand and view more

2. Directly in database or through psql shell of a superuser.

   BASH

   Collapse Copy

   1CREATE ROLE <username> WITH LOGIN PASSWORD 'your password';

   Click to expand and view more

1. Create a role using utilities

This newly created user will have a default attributes which is empty. Okay, now let’s create a role using the postgres utilities.

1. Open terminal and type this command:

   BASH

   Collapse Copy

   1createuser testuser

   Click to expand and view more

   This will create a new role with name testuser. This role will does not have a database

2. Now, let’s connect to db as this role. Run this on terminal to connect to the default db:

   BASH

   Collapse Copy

   1psql postgres -U testuser

   Click to expand and view more

   As you can see from the terminal log, we are logged in as a non superuser. (>).

   BASH

   Collapse Copy

   1# terminal log
   2psql (12.4)
   3Type "help" for help.
   4
   5postgres=>

   Click to expand and view more

3. Let’s check the all roles that exist in the database. Run this command on postgres shell:

   BASH

   Collapse Copy

   1    \du

   Click to expand and view more

   As you can see, there our newly created role exists already but with empty attributes (default).

   BASH

   Collapse Copy

   1Role name |                         Attributes                         | Member of
   2
   3-----------+------------------------------------------------------------+-----------
   4macuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
   5testuser |

   Click to expand and view more

4. We can add more attributes during role creation by providing the attributes or create the role interactively. For example, creating the role interactively:

   BASH

   Collapse Copy

   1createuser testuser1 --interactive
   2# prompt
   3Shall the new role be a superuser? (y/n) n
   4Shall the new role be allowed to create databases? (y/n) y
   5Shall the new role be allowed to create more new roles? (y/n) n

   Click to expand and view more

   Now if we go into psql shell again and check the roles, we will see testuser1 will have an attribute we have specified during interactive creation:

   BASH

   Collapse Copy

   1postgres=> \du
   2                                   List of roles
   3
   4Role name | Attributes | Member of
   5-----------+------------------------------------------------------------+-----------
   6macuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
   7testuser | | {}
   8testuser1 | Create DB

   Click to expand and view more

5. Let’s delete all the roles we have created. Run these commands on terminal (assuming they don’t have any databases yet):

   BASH

   Collapse Copy

   1dropuser testuser
   2dropuser testuser1

   Click to expand and view more

2. Creating a role using psql shell

1. Open terminal and enter this to open psql shell as a superuser:

   BASH

   Collapse Copy

   1psql postgres

   Click to expand and view more

2. Type and enter this sql query in psql shell to create a role. Password can be NULL, but it’s not recommended:

   BASH

   Collapse Copy

   1CREATE ROLE testuser WITH LOGIN PASSWORD 'yourpassword';

   Click to expand and view more

3. Check if the user is created by listing all the users in cluster:

   BASH

   Collapse Copy

    1\du
    2
    3# output
    4                               List of roles
    5Role name |                         Attributes                         | Member of
    6-----------+------------------------------------------------------------+-----------
    7macuser   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    8testuser  |                                                            | {}
    9
   10postgres=#

   Click to expand and view more

4. Now, let’s add some attributes. Type and enter this command:

   BASH

   Collapse Copy

   1ALTER ROLE testuser CREATEDB;

   Click to expand and view more

   Now if check the list of users again using \du:

   BASH

   Collapse Copy

   1  List of roles
   2
   3  Role name | Attributes | Member of
   4  -----------+------------------------------------------------------------+-----------
   5  swiftmage | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
   6  testuser | Create DB

   Click to expand and view more

5. Let’s delete this new role we have created:

   BASH

   Collapse Copy

   1DROP ROLE testuser;

   Click to expand and view more

   Check it’s gone from the list using \du.

6. Type and enter \q to quit psql shell.