PostgreSQL Position Membership – Penetration Testing Instruments, ML and Linux Tutorials


PostgreSQL

Abstract: on this tutorial, you’ll find out about PostgreSQL group roles and learn how to use them to handle privileges extra successfully.


Introduction to PostgreSQL group roles

It’s simpler to handle roles as a bunch as a way to grant or revoke privileges from a bunch as a complete as an alternative of doing it on a person position.

Usually, you create a task that represents a bunch after which grants membership within the group position to particular person roles.

By conference, a bunch position doesn’t have the LOGIN privilege. It signifies that you will be unable to make use of the group position to log in to PostgreSQL.

To create a bunch position, you utilize the CREATE ROLE assertion as follows:

CREATE ROLE group_role_name;

For instance, the next assertion creates a bunch position gross sales:

CREATE ROLE gross sales;

Once you use the du command within the psql software, you will notice that the group roles are listed along with consumer roles:

Checklist of roles
  Position title |                         Attributes                         | Member of
 -----------+------------------------------------------------------------+-----------
  alice     |                                                            | 
  api       | 1000 connections                                           | 
  bob       | Can't login                                               | 
  dba       | Create DB                                                  | 
  john      | Superuser                                                  | 
  postgres  | Superuser, Create position, Create DB, Replication, Bypass RLS | 
  gross sales     | Can't login                                               | 

So as to add a task to a bunch position, you utilize the next type of the GRANT assertion:

GRANT group_role to user_role;

For instance, the next assertion provides the position alice to the group position gross sales:

GRANT gross sales TO alice;

If you happen to run the du command once more, you will notice that alice now’s a member of gross sales:

du
Checklist of roles
  Position title |                         Attributes                         | Member of
 -----------+------------------------------------------------------------+-----------
  alice     |                                                            | gross sales
  api       | 1000 connections                                           | 
  bob       | Can't login                                               | 
  dba       | Create DB                                                  | 
  john      | Superuser                                                  | 
  postgres  | Superuser, Create position, Create DB, Replication, Bypass RLS | 
  gross sales     | Can't login                                               | 

To take away a consumer position from a bunch position, you utilize REVOKE assertion:

REVOKE group_role FROM user_role;

For instance, the next assertion makes use of the REVOKE assertion to take away the position alice from the group position gross sales:

REVOKE gross sales FROM alice;

Discover that PostgreSQL doesn’t assist you to have round membership loops, wherein a task is the member of one other position and vice versa.

PostgreSQL Position Membership instance

A task can use privileges of the group position within the following methods:

  • First, a task with the INHERIT attribute will robotically have privileges of the group roles of which it’s the member, together with any privileges inherited by that position.
  • Second, a task can use the SET ROLE assertion to quickly turn into the group position. The position may have privileges of the group position moderately than its authentic login position. Additionally, the objects are created by the position are owned by the group position, not the login position.

Step 1. Organising a pattern database and tables

1. Login to the PostgreSQL utilizing the postgres database.

2. Create a brand new database known as corp:

create database corp;

4. Create the contacts desk:

create desk contacts(
    id int generated at all times as identification main key,
    title varchar(255) not null,
    cellphone varchar(255) not null
 );

5. Create the forecasts desk:

create desk forecasts(
     yr int, 
     month int, 
     quantity numeric
 );

Step 2. Setting roles and group roles

1. Create a task jane that may log in with a password and inherit all privileges of group roles of which it’s a member:

create position jane inherit login password 'securePass1';

2. Grant the choose on the forecasts desk to jane:

grant choose on forecasts to jane;

3. Use the z command to examine the grant desk:

z

4. Create the advertising and marketing group position:

create position advertising and marketing noinherit;

5. Create the planning group position:

create position planning noinherit;

6. Grant all privileges on contacts desk to advertising and marketing:

grant all on contacts to advertising and marketing;

7. Grant all privileges on forecasts desk to planning:

grant all on forecasts to planning;

8. Add jane as a member of advertising and marketing:

grant advertising and marketing to jane;

9. Add planning as a member of advertising and marketing:

grant advertising and marketing to planning;

Step 3. Utilizing the roles

1. If you happen to hook up with PostgreSQL utilizing the position jane , you should have privileges instantly granted to jane plus any privileges granted to advertising and marketing as a result of jane inherits advertising and marketing’s privileges:

psql -U jane -d corp

2. It’ll immediate you for the jane’s password.

3. The position jane can choose information from the forecasts desk:

dcorp=> choose * from forecasts;

4. And insert a row into the contacts desk:

corp=> insert into contacts(title, cellphone) values('Jone Doe','408-102-3459');

5. Nonetheless, jane can not insert a row into the forecasts desk:

corp=> insert into forecasts(yr, month, quantity) values(2020,1,1000);
ERROR:  permission denied for desk forecasts

6. After executing the next SET ROLE assertion:

corp=> set position planning;

7. The position jane may have privileges granted to planning, not those that granted on to jane or not directly to advertising and marketing.

8. Now, jane can insert a row into the forecasts desk:

corp=> insert into forecasts(yr, month, quantity) values(2020,1,1000);

9. If jane makes an attempt to pick information from the contacts desk, it’s going to fail as a result of the position planning has no privilege on the contacts desk:

corp=> choose * from contacts;
ERROR:  permission denied for desk contacts

To revive the unique privileges of jane, you utilize the RESET ROLE assertion:

RESET ROLE;

Abstract

  • Handle privileges on group roles as an alternative of particular person roles.
  • Roles with INHERIT attribute takes all privileges of the group roles of which they’re the members.



Source link