Introduction to the PostgreSQL tutorial to create a user

In this article, we will show a PostgreSQL tutorial to create a user using PgAdmin and PL/PgSQL.

Requirements

1. PgAdmin installed. In this example, the PgAdmin 4 version 6.12 was used.
2. PostgreSQL installed.

Tutorial to create a user in PGAdmin

Open PGAdmin and connect to the server where you want to create the new user.

In the Object Browser, expand the Server and go to Login/Group Roles tree, and right-click on the folder. Select Create>Login/Group Role option from the context menu.

In the General tab, enter the new user’s name using the Name field. Optionally you can add comments to describe the user.

Next, in the Definition tab, enter the new user’s password in the Password field. Optionally, select the account expiration date to expire the user in a date and the connection limit (by default is -1 which means that there is no limit)

Also, in the Privileges tab, you can select the privileges you want to grant to the new user. For example, you can provide privileges to log in. To have superuser privileges. The superuser has all the privileges under the server. You can also grant permissions to the user to create roles and create databases. You can also inherit rights from the parent role. You can inherit privileges from the parent or not. Finally, you have the option to start the streaming replication and set the system in the backup mode with the Can initiate streaming replication and backups option.

The Membership tab allows you to specify which roles a user belongs to when creating or editing a user.

In the Membership tab, you will see a list of all available roles in the PostgreSQL server, and you can select one or more roles that the user should be a member of. The roles that you select will determine the permissions that the user has on the database.

With the + icons you can add members of or members.

The Members is used to view and manage the users that are members of a specific role. This displays a list of all users that have been assigned to a role, and it allows you to add or remove users from the role. The members of a role have the same permissions as the role has, which means that if you have a role with a specific set of permissions, all the members of that role will have the same permissions.

Members Of is used to view and manage the roles that a specific user belongs to. it displays a list of all roles that a user has been assigned to, and it allows you to add or remove roles from the user. The roles that a user belongs to determine the permissions that the user has on the database.

The Parameter tab is used to specify additional options when creating or editing a role.

In addition, we have the Security tab which is used to create security labels for the role. Using the + icon, you can add new labels of security.

Finally, we have the SQL tab. You can generate the script here. Note that in PostgreSQL a role and a user are the same. You can use both, the create role and the create user options.

Once you have set the options you want, click the Save button to create a new user.

PostgreSQL tutorial to create a user – Roles

In PostgreSQL, roles are used to manage access control and permissions on the database server. PostgreSQL comes with a set of predefined roles, also known as system roles with specific permissions and responsibilities. Here you have some examples:

The pg_database_owner role is a system role that is created automatically when a new database is created. This role can perform all actions in the database, like creating, modifying, and deleting tables, indexes, sequences, and other objects.

Also, it can create new roles in the specific database. And also, has the ability to grant and revoke permissions on the objects to other roles.

The pg_execute_server_program role is a system role that allows a role to execute server programs and some other administrative functions. This role is for an operating system’s superuser or an administrator.

This role is used to run a specific program or script that needs to be executed on the server, for example, for backups, to schedule tasks, or manage other administrative functions.

The pg_monitor is used to execute the pg_stat related functions and view statistics about the current state of the server.

We also have the pg_read_all_settings which can read all server setting to schedule tasks or manage other administrative functions., but cannot change them. In addition, this role allows reading using the all-server settings. For example, you can view all the configuration parameters of the PostgreSQL server, such as the max_connections, shared_buffers, and other settings. Note that it does not allow the user to change the configuration values.

Also, the pg_read_all_stats can read statistics from all available statistics views, but cannot change them.

The pg_read_server_files is a role that allows a role to read server files. This role allows a user to read files on the server file system using the PostgreSQL server process. This includes files such as the PostgreSQL configuration file, log files, and other files that are used by the server.

This role is used for monitoring, troubleshooting, or to modify files. In addition, you can use it for backup purposes. For example, you could read the log files to diagnose bugs or read the configuration file to check the current settings and detect problems.

Note that granting the pg_read_server_files role does not allow to modify the files. It’s just for reading the files.

The pg_signal_backend allows a role to send signals to backend processes. This role allows a user to send signals to the individual processes that make up a PostgreSQL server. The signals are a way to communicate with the operating system and can be used to perform actions, such as stopping or restarting a process.

The role can be useful for troubleshooting or performing specific actions on the backend processes. For example, sending the “SIGTERM” signal to a specific process to terminate it, or sending the “SIGUSR1” signal to rotate the log file. The SIGTERM is sent to disallow new connections and keep existing connections. After all the sessions are ended, it is shut down.

Note that granting the pg_signal_backend role to a user does not allow them to perform any action on the backend processes, it just allows them to send signals to those processes.

The pg_stat_scan_tables role allows handling locks or deadlocks on tables. The role can be useful to monitor and troubleshoot the performance of the server, as it allows you to see how often tables are being scanned, which tables are being scanned most frequently, and how long those scans are taking. Note that granting the pg_stat_scan_tables role to a user does not grant permissio to perform any action on the server, it just allows them to view the statistics.

In addition, we have the pg_write_all_data which is used to write data in tables, views, and sequences. You have the privileges to INSERT, UPDATE andn DELETE data and also USAGE rights on the schemas.

The pg_write_server_files allows a role to write to server files. This role allows a user to write to files on the server file system that are accessible by the PostgreSQL server process. This includes files like the PostgreSQL configuration file, log files, and other files used by the server. The role can be used for some administrative tasks, such as modifying the configuration file, working with log files, or doing backup tasks.

As a good practice, create your roles, with the minimum permissions required, to handle the access control of your application, and avoid using these predefined system roles.

PostgreSQL tutorial to create a user with the command line

PgAdmin is the easiest way to create users. However, sometimes we need to automate the process. In that case, we can use PL/pgSQL to create a user.

For newbies, we strongly recommend using the SQL tab shown before to learn the syntax. So, you can use the tabs visually and then check the code.

The syntax is simple:

You can use the CREATE ROLE and assign a name. In the previous example, the user’s name was sqlshackuser.

We granted login permissions and it is not a superuser (nosuperuser). We also granted the inherit from parent role permissions and granted the create database privileges (created). We did not grant permissions to create roles (nocreaterole). Also, we did not grant permission to replicate replication and backup streaming.

Also, we have the encrypted password and grant the pg_monitor permissions to the user. Finally, we used the comment on the role to add a description to the role.

Conclusion

In this article, we learned how to create a user using PgAdmin. We explained the different options available. Also, we learned the system roles by default and finally, we learned how to create the user using PL\PgSQL.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023