permissions in PostgreSQL

2 years ago

15 minutes

In this tutorial, you will learn permissions in PostgreSQL the correct procedure for effectively handling privileges and granting user permissions. By doing so, you will be able to assign the necessary privileges to your applications without impacting other databases.

The introduction is simply an opening section.

PostgreSQL or Postgres is a relational database management system that is open-source. It stores data in tables consisting of rows and columns, similar to other relational databases. Users can efficiently handle, control, and retrieve data using Structured Query Language (SQL). PostgreSQL is a robust tool suitable for managing application and web data on a Virtual Private Server.

Requirements necessary prior to taking a certain action or course.

In order to keep pace with this tutorial, you will require:

One Ubuntu 22.04 server that has been configured by following our Initial Server Setup for Ubuntu 22.04 guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.
To complete Step 1 of our How To Install and Use PostgreSQL on Ubuntu 22.04 tutorial to have Postgres installed on your server.

Once you have set up your environment and successfully started Postgres on your server, you can start exploring Postgres’ authorization system and how it manages permissions.

Looking at the roles and permissions in PostgreSQL.

Postgres controls access rights by using the concept of roles. Unlike traditional Unix-style permissions where there are separate users and groups, Postgres roles can function as both. These roles can be modified to mimic these conventions, while also offering more adaptable options. During the installation process, Postgres defaults to peer authentication, which associates Postgres roles with corresponding Unix/Linux system accounts. In this system, if a role exists within Postgres, a Unix/Linux username with an identical name can log in as that role.

After completing the installation process, a user account named “postgres” is generated and linked to the default Postgres role. To utilize Postgres, simply log in to this account.

To begin with, ensure that your server is operational by employing the systemctl start command.

sudo systemctl start postgresql.service

Next, you have the option to switch to the postgres account by typing:

sudo -i -u postgres

By typing, you can immediately access the PostgreSQL prompt now.

psql

In order to view the roles in your Postgres instance, enter the given command:

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Right now, there is only a single default role that holds numerous influential privileges.

Establishing roles in PostgreSQL

There are several ways to generate roles for Postgres, either within the Postgres interface or via the command line.

Creating roles within PostgreSQL

Within the Postgres prompt interface, a new role can be created using the following syntax.

CREATE ROLE new_role_name;

To exemplify this, construct a fresh role referred to as demo_role.

CREATE ROLE demo_role;

Recheck the designated users once more.

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– demo_role | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

You will see two users in your output.

Generating roles using the command line

A different approach to generating roles involves utilizing the createuser command directly from the command line.

To momentarily exit the PostgreSQL command prompt, type the following:

Next, access the postgres account by logging in.

sudo -i -u postgres

To initiate fresh roles via the command line, make use of the createuser command. Enabling the –interactive flag will prompt you to input the name of the fresh role and inquire whether it should possess superuser privileges.

If you are currently logged in as the postgres account, you have the ability to create a new user by simply typing:

createuser –interactive

The script will present you with various options and, depending on your answers, it will carry out the appropriate Postgres commands according to your requirements.

Output

Enter name of role to add: test_user Shall the new role be a superuser?

(y/n) n Shall the new role be allowed to create databases?

(y/n) n Shall the new role be allowed to create more new roles? (y/n) n

If you answer “no” to all of these prompts, you will generate a user who is similar to the user before.

Re-enter your psql Postgres prompt.

psql

To uncover the divergences between the two newly created roles, run the “du” command. This command begins with “\” in order to distinguish it as a psql specific meta-command that is executed directly by psql and not by PostgreSQL.

Output

Please observe that the user generated through the command line does not possess the attribute Cannot login.

Removing Roles in PostgreSQL

To remove a role, you can utilize the subsequent syntax.

DROP ROLE role_name;

To show, you can get rid of the demo_role role by simply typing:

DROP ROLE demo_role;

If you try to execute the command on a user that doesn’t exist, you will get an error message.

Output

ERROR: role “demo_role” does not exist

To prevent this scenario and ensure that the drop command removes a user if they exist, while remaining silent if the user is not found, utilize the following syntax:

DROP ROLE IF EXISTS role_name;

If you specify this option, the command will be executed successfully regardless of whether the role is valid or not. Removing the demo_role using the mentioned commands will lead to this outcome.

DROP ROLE IF EXISTS demo_role;

Output

NOTICE: role “demo_role” does not exist, skipping DROP ROLE

The position has been removed.

Determining Rights During Role Establishment

Now, you can proceed to modify the permissions of the demo_role and recreate it. You can accomplish this by specifying the desired permissions after the create clause.

CREATE ROLE role_name WITH assigned_permissions;

If you want to view the complete range of options, then type:

\h CREATE ROLE

Output

To enable the demo_role user to sign in, simply enter:

CREATE ROLE demo_role WITH LOGIN;

By inspecting the attributes using the \du command, it can be observed that both users currently possess the same privileges.

Output

List of roles Role name | Attributes | Member of ———–+————————————————————+———– demo_role | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test_user | | {}

Instead of specifying the LOGIN attribute every time you create a role, you can attain this state by using the CREATE USER command, which automatically grants login privileges to the role.

CREATE USER role_name;

The position is established with automatic privilege bestowed.

Altering the privileges of roles in PostgreSQL.

If you want to modify the characteristics of a role that has already been created, you can utilize the ALTER ROLE command. The format for this command is as follows:

ALTER ROLE role_name WITH attribute_options;

Instead of deleting and recreating users, you can utilize this command to make changes to privileges easily. For example, you can revert demo_role to its previous state of being unable to login by executing this command.

ALTER ROLE demo_role WITH NOLOGIN;

To verify the alteration, employ the \du command.

Output

Use the given command to revert it to a role with login access.

ALTER ROLE demo_role WITH LOGIN;

Now the role has been switched back.

To log in to PostgreSQL as a different user, the process involves accessing the system with alternative user credentials.

By default, users can only log in locally if their system username matches their PostgreSQL username. However, you have the ability to modify this by adjusting the login type or instructing PostgreSQL to utilize the loopback network interface. This modification alters the connection type to remote even though it is actually a local connection.

Initially, establish a password for the user to establish a connection and allow for authentication. To experiment, you may assign a password to the previously generated test_user.

\password test_user

After entering and confirming a password, exit the PostgreSQL interface and return to your normal user using this command.

When logging into PostgreSQL, it is expected that your username will correspond to your operating system username, and the database you connect to will have the same name as well.

To specify the desired options explicitly, use this syntax along with your parameters:

psql -U user_name -d database_name -h 127.0.0.1 -W

Here is a concise explanation of each element in the command:

The user_name should be replaced with the username you want to connect with.
The database_name should be the name of an existing database that you have access to.
The -h 127.0.0.1 section is the part that specifies that you will be connecting to the local machine, but through a network interface, which allows you to authenticate even though your system username does not match.
The -W flag tells PostgreSQL that you will be entering a password.

To access your test_user, simply enter the following command to log in.

sudo psql -U test_user -d postgres -h 127.0.0.1 -W

After executing this command, you will be required to input a password.

In this scenario, the database postgres is utilized. This particular database is automatically configured upon installation. If you try to execute certain actions in this session, you will realize that there are limitations placed on your abilities. This limitation arises from the fact that administrative permissions have not been granted to test_user.

End the current session.

Afterwards, re-enter the administrative postgres session.

sudo u – postgres psql

Next, you will proceed to authorize permissions.

Giving authorizations in PostgreSQL

Typically, only the role responsible for creating a database or table, excluding superuser roles, is granted the authority to make changes to it. However, this can be modified by assigning permissions to other roles.

You have the ability to provide permissions by utilizing the GRANT command with this general structure:

GRANT permission_type ON table_name TO role_name;

To practice these concepts, you have the option to create a table using the following commands.

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

Enter this command to access the table you have created.

Output

Take note that there is a particular type of table and a particular type of sequence. The sequence is automatically created when you employ the id serial command during the creation of your table. This results in an integer that increments automatically.

To grant privileges to the demo_role for the new demo table, use the given command to assign UPDATE privileges to the demo_role user.

GRANT UPDATE ON demo TO demo_role;

To assign all permissions to a user, replace the permission type with the term ALL. Use the following command to grant this permission to the test_user.

GRANT ALL ON demo TO test_user;

Instead of specifying permissions individually for each user, you can use the term “PUBLIC” to assign permissions for all users on the system.

GRANT INSERT ON demo TO PUBLIC;

To access the grant table, utilize the given command.

Output

This discloses the complete list of granted permissions.

Revoking privileges in PostgreSQL

To eliminate permissions, employ the REVOKE command which shares a nearly identical syntax with the grant command.

REVOKE permission_type ON table_name FROM user_name;

In the command, you also have the option to utilize the same abbreviated terms, namely ALL and PUBLIC.

REVOKE INSERT ON demo FROM PUBLIC;

The permissions that you previously granted have now been withdrawn.

Utilization of Group Roles in PostgreSQL

Roles can be adjusted to group other roles, providing extensive permission control. For example, you have the option to form a group called “temporary_users” and include both “demo_role” and “test_user” in it.

To begin with, establish the new position that will serve as a collective entity.

CREATE ROLE temporary_users;

After that, allocate the users to the recently formed group named temporary_users.

GRANT temporary_users TO demo_role;

GRANT temporary_users TO test_user;

These two users can now have their permissions controlled by managing the temporary_users group role instead of individually managing each member.

By typing, you have the ability to access and view the membership information regarding roles.

Output

List of roles Role name | Attributes | Member of —————–+————————————————————+——————- demo_role | | {temporary_users} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} temporary_users | Cannot login | {} test_user | | {temporary_users}

You can use the SET ROLE command to act as any role within a group, regardless of whether you are a member of that group or not. As the postgres user you are currently logged in as has superuser privileges, you can still use the SET ROLE command even if it is not a part of the temporary_users group.

SET ROLE temporary_users;

Currently, the temporary_users role is the rightful owner of all newly created tables.

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

Now, verify the ownership of the table by executing this command:

Output

The temporary_users role is the owner of the new table and the sequence connected to the serial data type.

To revert back to the original role permissions, input the subsequent command:

RESET ROLE;

If the INHERIT property is assigned to a user using the ALTER ROLE command, they will automatically possess all the privileges granted by the roles they are a part of, eliminating the need for the SET ROLE command.

ALTER ROLE test_user INHERIT;

The test_user will possess all the permissions of the roles it belongs to. You can eliminate a group role, or any role, by using the DROP ROLE command. To verify this, you can enter the following command for the temporary_users group:

DROP ROLE temporary_users;

Output

ERROR: role “temporary_users” cannot be dropped because some objects depend on it DETAIL: owner of sequence hello_id_seq owner of table hello

An error occurs because the hello table is owned by temporary_users. You can resolve this issue by transferring ownership to another role.

ALTER TABLE hello OWNER TO demo_role;

To verify whether temporary_users has relinquished ownership of any tables, you can employ the subsequent method.

Output

By executing this command, you will be able to successfully remove the temporary_users role.

DROP ROLE temporary_users;

The temporary_users role will be eliminated, but its previous members will not be removed.

One possible option for paraphrasing “Conclusion” natively could be “In summary” or “To sum up”.

Now that you possess the fundamental abilities required to manage permissions for your PostgreSQL database, it is crucial to be aware of how to handle permissions in order to ensure that your applications can access the necessary databases without interfering with data utilized by other applications.

If you want to expand your knowledge on Postgres and its utilization, we highly recommend exploring the provided guides.

SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems
Practice running queries with SQL