Create, Drop, Alter Roles in PostgreSQL

PostgreSQL represents accounts as roles. PostgreSQL uses roles to represent user accounts. It doesn’t use the user concept like other database systems Oracle & MySql. Roles that can log in are called login roles. When roles contain other roles, they are called group roles. When you create a role, it is valid in all databases in the database server.

PostgreSQL Role is required to login into PostgreSQL database server. The concept of Role in PostgreSql is the same as Login in MS-SQL Server.

We will use sample DB dvdrental for this demo click here to know more about How To Load PostgreSQL Sample Database.

Step 1. Login to PostgreSql database server: Switch to OS user Postgres and use psql command-line utility to connect to PostgreSQL Server. Use command \l to list the existing database. We have already imported a sample DB dvdrental.

[root@test-machine02 ~]# su - postgres
Last login: Tue May 18 12:20:59 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (13.2)
Type "help" for help.

postgres=#
postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 dvdrental  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample-db1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(5 rows)

postgres=#

Create Role/User

Step 2. Create Role: Use the command create role to create a new role. If you create a role without any attributes like the below command, you will not able to login to PostgreSql Server. Use command \du+ to list existing roles. You will notice here its clearing saying “Cannot login“. If you try to login with role/user test1_role you will receive the below error.

postgres=#
postgres=# create role test1_role ;
CREATE ROLE
postgres=# \du+
                                           List of roles
 Role name  |                         Attributes                         | Member of | Description
------------+------------------------------------------------------------+-----------+-------------
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 test1_role | Cannot login                                               | {}        |

postgres=#\quit
-bash-4.2$ exit
logout
[root@test-machine02 data]# psql -htest-machine02 -Utest1_role -W dvdrental
Password:
psql: error: FATAL:  password authentication failed for user "test1_role"
[root@test-machine02 data]#

Step 3. Create a role with LOGIN Attribute: Now we will create a role/user with LOGIN and PASSWORD attributes. Once the role is created you can see this time we are able to login into database dvdrental successfully.

[root@test-machine02 ~]# su - postgres
Last login: Tue May 18 12:20:59 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=# create role test2_role with LOGIN PASSWORD 'Root@1234';
CREATE ROLE
postgres=#

postgres=#  \du+
                                           List of roles
 Role name  |                         Attributes                         | Member of | Description
------------+------------------------------------------------------------+-----------+-------------
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 test1_role | Cannot login                                               | {}        |
 test2_role |                                                            | {}        |
postgres=#\quit
-bash-4.2$ exit
logout

[root@test-machine02 data]#  psql -htest-machine02 -Utest2_role -W dvdrental
Password:
psql (13.2)
Type "help" for help.

dvdrental=>
dvdrental=>

dvdrental=>
dvdrental=> SELECT current_schema();
 public

dvdrental=> select current_user;
 test2_role

dvdrental=> select current_database();
 dvdrental

Step 4. Access DB dvdrental objects: We will create 2 dummy tables and then use command \dt+ to list available tables in database dvdrental. (Please note that, by default, every role/user has the only CREATE and USAGE privileges on the public schema.) Please note in the output of command \dt+ newly created tables are owned by role/user test2_role. Other tables are owned by role/user Postgres, So we will not be able to access other objects if you try to access you will receive the error “permission denied”.

dvdrental=> create table product (
dvdrental(>     id int generated always as identity,
dvdrental(>     first_name varchar(100) not null,
dvdrental(>     last_name varchar(100) not null,
dvdrental(>     email varchar(255) not null unique,
dvdrental(>     phone varchar(25) not null,
dvdrental(>     primary key(id)
dvdrental(> );
CREATE TABLE

dvdrental=> create table candidates (
dvdrental(>     candidate_id int generated always as identity,
dvdrental(>     first_name varchar(100) not null,
dvdrental(>     last_name varchar(100) not null,
dvdrental(>     email varchar(255) not null unique,
dvdrental(>     phone varchar(25) not null,
dvdrental(>     primary key(candidate_id)
dvdrental(> );
CREATE TABLE

dvdrental=> \dt+
                                  List of relations
 Schema |     Name      | Type  |   Owner    | Persistence |    Size    | Description
--------+---------------+-------+------------+-------------+------------+-------------
 public | actor         | table | postgres   | permanent   | 40 kB      |
 public | address       | table | postgres   | permanent   | 88 kB      |
 public | candidates    | table | test2_role | permanent   | 0 bytes    |
 public | category      | table | postgres   | permanent   | 8192 bytes |
 public | city          | table | postgres   | permanent   | 64 kB      |
 public | country       | table | postgres   | permanent   | 8192 bytes |
 public | customer      | table | postgres   | permanent   | 96 kB      |
 public | film          | table | postgres   | permanent   | 464 kB     |
 public | film_actor    | table | postgres   | permanent   | 272 kB     |
 public | film_category | table | postgres   | permanent   | 72 kB      |
 public | inventory     | table | postgres   | permanent   | 232 kB     |
 public | language      | table | postgres   | permanent   | 8192 bytes |
 public | payment       | table | postgres   | permanent   | 896 kB     |
 public | product       | table | test2_role | permanent   | 8192 bytes |
 public | rental        | table | postgres   | permanent   | 1232 kB    |
 public | staff         | table | postgres   | permanent   | 16 kB      |
 public | store         | table | postgres   | permanent   | 8192 bytes |
(17 rows)

dvdrental=>

dvdrental=> select count(*) from product;
 count
-------
     0
(1 row)

dvdrental=> select count(*) from candidates;
 count
-------
     0
(1 row)


dvdrental=> select count(*) from actor;
ERROR:  permission denied for table actor
dvdrental=>

Grant Privileges

Step 5. Grant Privileges to Role test2_role: Connect to database dvdrental using Postgres role and grant privileges to newly created role test2_role to access objects owned by Postgres role in the public schema. You can grant INSERT, UPDATE, DELETE, or ALL privileges using GRANT Command. Use \z command to obtain information about existing privileges.

In \z output arwdDxt = All privileges granted to Role & /postgres = granted by postgres role/user (r — SELECT (“read”) w — UPDATE (“write”) a — INSERT (“append”) d — DELETE).

After privileges are granted role test2_role, we will be able to access all objects in sample DB dvdrental including owned by Postgres role/user.

12[root@test-machine02 data]#  psql -htest-machine02 -Upostgres -W dvdrental
Password:
psql (13.2)
Type "help" for help.
dvdrental=#
dvdrental=# GRANT ALL ON ALL TABLES IN SCHEMA "public" TO test2_role;
GRANT
dvdrental=#  \z
 Schema |            Name             |   Type   |      Access privileges      | Column privileges | Policies
--------+-----------------------------+----------+-----------------------------+-------------------+----------
 public | actor                       | table    | postgres=arwdDxt/postgres  +|                   |
        |                             |          | test2_role=arwdDxt/postgres |                   |
 public | actor_actor_id_seq          | sequence |                             |                   |
 public | actor_info                  | view     | postgres=arwdDxt/postgres  +|                   |
        |                             |          | test2_role=arwdDxt/postgres |                   |
 public | address                     | table    | postgres=arwdDxt/postgres  +|                   |
        |                             |          | test2_role=arwdDxt/postgres |                   |
 public | address_address_id_seq      | sequence |                             |                   |

[root@test-machine02 data]#
[root@test-machine02 data]# psql -htest-machine02 -Utest2_role -W dvdrental
Password:
psql (13.2)
Type "help" for help.

dvdrental=>
dvdrental=>

dvdrental=>
dvdrental=> select count(*) from actor;
 count
-------
   200
(1 row)

dvdrental=>

Alter Role

Step 6. Alter role to add more attributes: Use command alter role to add more attributes to an existing role.

SUPERUSER | NOSUPERUSER – determine if the role is a superuser or not.
CREATEDB | NOCREATEDB – allows the role to create new databases.
CONNECTION LIMIT limit – specify the number of concurrent connections a role can make, -1 means unlimited.
VALID UNTIL ‘timestamp’ – set the date and time after which the role’s password is no longer valid.

[root@test-machine02 ~]# su - postgres
Last login: Tue May 18 12:20:59 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=#
postgres=# alter role test2_role CONNECTION LIMIT 1000;
ALTER ROLE
postgres=#
postgres=# alter role test2_role VALID UNTIL '2030-01-01';
ALTER ROLE
postgres=# alter role test2_role CREATEDB ;
ALTER ROLE
postgres=#  alter role test2_role superuser;
ALTER ROLE

postgres=#  \du+
                                           List of roles
 Role name  |                         Attributes                         | Member of | Description
------------+------------------------------------------------------------+-----------+-------------
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 test1_role | Cannot login                                               | {}        |
 test2_role | Superuser, Create DB                                      +| {}        |
            | 1000 connections                                          +|           |
            | Password valid until 2030-01-01 00:00:00+03                |           |

postgres=#

Drop Role

Step 7. Drop Role: If we try to drop role/user test2_role directly we will receive the below error, Remember we have created two tables which is owned by test2_role and also granted ALL privileges to test2_role. In order to drop we need to do the below steps:

1. First, either remove the database objects owned by the role using the DROP OWNED the statement or reassign the ownership of the database objects to another role REASSIGN OWNED.

2. Second, revoke any permissions granted to the role.

Connect to dvdrental sample DB with super role/user Postgres.

postgres=#
postgres=# drop role test2_role;
ERROR:  role "test2_role" cannot be dropped because some objects depend on it
DETAIL:  22 objects in database dvdrental
postgres=#

[root@test-machine02 ~]# su - postgres
-bash-4.2$ psql -Upostgres -W dvdrental
Password:
psql (13.2)
Type "help" for help.

dvdrental=#
dvdrental=# reassign owned by test2_role to postgres;
REASSIGN OWNED
dvdrental=#


dvdrental=# revoke ALL ON ALL TABLES IN SCHEMA "public" from  test2_role;
REVOKE
dvdrental=#

dvdrental=# drop role test2_role;
DROP ROLE
dvdrental=#

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

PGADMIN, POSTGRESQL, ROLE

DBsGuru