Determining who has control over what data and who can view, edit and change aspects of your business data is a serious decision. With growing cybersecurity threats, you must know who accesses every part of your system so that if there is a problem, you can isolate the incident, and everyone involved quickly and efficiently.
Employees require access to data to be productive. But how do you manage access, especially if you have many different employees who need access to different information across your databases? Using a simple process to grant access and, almost more importantly, revoke access are necessary to ensure that your organization remains productive as well as secure and compliant.
That’s where PostgreSQL Access Control comes in!
This article will cover the following topics in depth to provide more information on using a PostgreSQL access control system.
What is Access Control?
Access control is a security method that determines which individuals have access privileges to use a system’s assets in a computer system. It is a key premise in security that seeks to minimize the exposed risks of a company or organization.
The purpose of access control is to reduce damage through illegal entry into both physical and logical facilities. Access control is an essential part of security policies and standards, which help ensure appropriate security equipment and user access rules are implemented to safeguard confidential material. It is also necessary to ensure that security and compliance regulations are met.
There are several types of access controls, RBAC, ABAC, Policy-based and JIT. The purpose of each of these types of access control is to ensure that data is limited to only those authorized. However, granting and revoking access can be a time and resource consuming progress.
Read more about how Access Control is the Dementor of Data Engineering.
What is PostgreSQL?
PostgreSQL is a robust entity database management system available as open-source and free software. It uses and develops the SQL language, combining this with several capabilities that allow it to meet even the most complex data demands securely.
No matter the dataset size, Postgres’s many built-in features help its effective management. These features are designed to help developers and database administrators maintain data integrity and security as well as create environments that are resilient to errors.
Postgres security is based on:
- Network-level: Unix Domain sockets, TCP/IP sockets, and firewalls.
- Transport-level: SSL/TLS to ensure secure communication.
- Database-level: roles and permissions, row level security (RLS), auditing & monitoring.
While securing access in all three of these areas is important in this guide we focus on securing database-level security which relies on the granting of roles and permissions, row-level security, and authentication.
PostgreSQL Roles
Postgres utilizes the idea of roles (RBAC) to manage the various permissions associated with database access. In Postgres, a role is defined as a user. This is important because each database account name is treated as a role, and comes with a LOGIN attribute that enables the role to connect to this database.
There are a variety of roles within Postgres each with corresponding privileges.
- SUPERUSER: bypasses permission checks
- CREATEDB: creates databases
- CREATEROLE: allows the user to create other roles.
Depending on the configuration, it is possible to think of a role as either a single user account or a collection of database users. Database objects (tables, functions, etc.) are owned by roles. Roles can update or delete authority privileges on resources, in addition to establishing and revising other roles that govern who has authority over different entities.
Further, it is possible to offer participation as a role, making it possible for the individual role to use the privileges associated with the other position.
Role Membership
Granting privileges and roles can be time consuming particularly for data engineering teams. Therefore, it is convenient to provide privileges to a group of users. From the Postgres documentation, we can see how to create and revoke role membership.
First, create a role that represents the group
Then grant membership in the group role to individual user roles
It is just as easy to REVOKE membership to the group as well as dissolve the group completely by using DROP ROLE.
Group members can then SET ROLE to become the group role. In this case, the privileges are granted based on the group role rather than the original login role, so any objects created are owned by the group role, not the login role.
To remove a role, any objects must first be dropped or reassigned to other owners and any permissions granted to the role revoked using ALTER.
Otherwise using the REASSIGN OWNED changes the ownership of database objects and must be run before the DROP OWNED command. Also, since REASSIGN OWNED is database specific it must be run on each database where the object is located, further, the ordering of the commands is important to remove the dependencies of a role that is to be dropped.
Privileges
When an object (table, function, etc.) is created, an owner is immediately assigned, typically the role that executed the creation declaration is considered the owner. The default state of a newly created object is that only the owner can interact with it. Therefore to share or allow other roles access to the object it is necessary to grant privileges.
The permissions relevant to a particular object change based on the type of item, and the power to alter default privileges or destroy an entity is intrinsic to being the possessor of the object. This right cannot be given or withdrawn in and of itself. On the other hand, just like any other privilege, you can pass down the right to own anything through the role members.
There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, SET and ALTER SYSTEM.
Privileges can be inherited by roles using INHERIT to attribute automatically gain privileges of roles of which they are members.
Authentication
If the user discovers any information in the documentation that is not correct or faulty, requires further clarification, or does not match the user’s experience with the particular feature, the user can submit a correction immediately and report a documentation issue.
Access Control with Satori
PostgreSQL must check that users possess adequate permissions on every level before it lets them read valuable data. The full chain of permits must be in proper working order.
Access control is essential because it is a beneficial security strategy that you can employ to control who or what can access or use any particular resource. This option makes access control an extremely vital aspect of security. Access control’s ultimate purpose is to deliver a security level that reduces the risks for a company or other organization. This function protects people, information, and data locations.
Satori simplifies access control by helping organizations move from persistent permission to JIT and need-to-know access, increasing productivity and security.
To learn more:
- Book a demo with one of our experts
- JIT Access Control Capabilities
- Review our guide on Access control
- Blog: Access Control is the Dementor of Data Engineering
