Linux Guides, Tips and Tutorials | LinuxScrew

Home » Programming » Databases » How to Set Up Users and Roles in PostgreSQL

PostgreSQL Users and Roles

How to Set Up Users and Roles in PostgreSQL

This article will show you how to set up user roles in PostgreSQL, and provide code examples. User roles are one of the best features of the PostgreSQL database system. This feature lets you group users and assign them permissions , making managing your database permissions and security much, much easier.

What are PostgreSQL’s User Roles?

In most database management systems, permission (such as being able to read or write to a database or table within a database) are assigned directly to user accounts in the database system. While this works, it’s not really convenient if you are dealing with a lot of users and a lot of databases.

User roles in PostgreSQL lets you assign permissions to  roles  rather than directly to users, and then assign those roles to users. This abstraction makes it easier to manage your databases, and improves security as it is less likely that you will accidentally grant the wrong permissions to the wrong user.

The best example of this is being able to update the permission for a role, with those permissions then being applied to all users in that role, rather than having to update (potentially tens or hundreds) of user accounts individually.

Users can have many roles, and the permissions from each will be applicable, as well as any permissions granted to users individually. As PostgreSQL’s permissions are restricted by default, and you are only granting permissions to  allow  something, there are no conflicts.

How to Create User Roles in PostgreSQL

The  CREATE_ROLE  statement is used to create new user roles in PostgreSQL:

Note the use of  WITH LOGIN  – this grants the  LOGIN  permission to the role from the outset, otherwise, users with that role would not be able to log in unless they had been granted that permission individually.

Granting Additional Permissions to a Role

The  GRANT  statement is use to grant permissions to a role once it has been created:

Above, the role  my_role  is granted permission to  CONNECT  to the database  my_database  and  SELECT  data from it.

A list of available PostgreSQL database permissions can be found here .

Assigning Roles to Users

The GRANT command is also used to assign roles to users:

Removing Roles from Users

The  REVOKE  statement is used to remove a user from a role:

  • How to List All Users, Permissions, and Roles in PostgreSQL
  • How to Use the userdel Command to Remove Users in Linux
  • How to List Users and Groups in Linux, With Examples
  • How to List/Show Users in MySQL/MariaDB

Photo of author

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Privacy Overview

Chapter 22. Database Roles

Table of Contents

PostgreSQL manages database access permissions using the concept of roles . A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

The concept of roles subsumes the concepts of “ users ” and “ groups ” . In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.

This chapter describes how to create and manage roles. More information about the effects of role privileges on various database objects can be found in Section 5.7 .

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

PostgreSQL / Authentication and authorization

Managing roles and role attributes in postgresql, introduction, what are roles, role attributes, what is a superuser role, checking existing role attributes, creating roles, changing existing roles, deleting roles, logging in using psql, changing to a different role during a session.

PostgreSQL uses various mechanisms to implement authentication , authorization , and object ownership within database clusters. Core among these is the concept of roles.

PostgreSQL roles are a combination of the ideas of users and groups into a single, flexible entity. They are the persona that user's adopt within the database system, are the entity by which the authentication system accepts or denies connections, and the subject of privilege management rules of all scopes.

This guide will cover what roles are and how to manage them within a PostgreSQL database cluster. More specifically, this guide will cover role management as it relates to role attributes. For a more broad overview of how roles fit into the larger picture, take a look at the introduction to authentication and authorization guide . To learn how to change role privileges on specific database objects, check out our guide on role grants .

In PostgreSQL, a role is a grouping of a specific set of capabilities, permissions, and "owned" entities. Instead of having distinct concepts of "users" and "groups", PostgreSQL uses roles to represent both of these ideas. A role can correspond to an individual person in the real world, or it can operate as a group with certain access that other roles can become members of.

Roles are the anchor point within PostgreSQL that determine who authentication and authorization policies apply to. Any policy that does not apply universally requires a notion of identity to define who to restrict and who to allow. In PostgreSQL, this identity is represented by roles.

PostgreSQL's authentication system has a number of different components, each of which are tied to roles. In order to be used for the initial connection to the database cluster, roles must first have the LOGIN attribute set. The authentication rules themselves are defined in the host-based configuration file called pg_hba.conf . Each rule defines methods of authentication that may be scoped to the individual role. For roles that are configured for password authentication must have a password attribute set so the system can validate the supplied user password.

In terms of authorization, roles are defined at the database cluster level, which in PostgreSQL, means that they are shared between databases. Since roles span databases, the authorization system controls the level of access each role has to each database entity. Because roles can represent groups of people, there is a great deal of flexibility in how access can be configured.

Roles are also essential to the concept of object ownership within PostgreSQL. Each database and table, for instance, have exactly one role configured as the owner. Other than superusers , the owner role is the only role that can modify or delete the actual object.

In summary, roles are at the core of most practical database operations. Their flexibility allows them to act both as user identifiers and user classes. Every action within the database cluster is checked against the role's privileges and the success of each connection to the database cluster is determined by the role one authenticates to. It is important to get a good handle on role management because of its importance within so many core operations.

Role attributes are flags on the role itself that determine some of the core privileges it has on the database cluster level. These can be set when the role is initially created, or changed at any time by any role with the appropriate attributes ( SUPERUSER or CREATEROLE in this case).

Attributes that can be applied to a role include:

  • LOGIN : Allows users to initially connect to the database cluster using this role. The CREATE USER command automatically adds this attribute, while CREATE ROLE command does not.
  • SUPERUSER : Allows the role to bypass all permission checks except the right to log in. Only other SUPERUSER roles can create roles with this attribute.
  • CREATEDB : Allows the role to create new databases.
  • CREATEROLE : Allows the role to create, alter, and delete other roles. This attribute also allows the role to assign or alter role membership. An exception is that a role with the CREATEROLE attribute cannot alter SUPERUSER roles without also having the SUPERUSER attribute.
  • REPLICATION : Allows the role to initiate streaming replication. Roles with this attribute must also have the LOGIN attribute.
  • PASSWORD : Assigns a password to the role that will be used with password or md5 authentication mechanisms. This attribute takes a password in quotations as an argument directly after the attribute keyword.
  • INHERIT : Determines whether the role inherits the privileges of roles it is a member of. Without the INHERIT , members must use SET ROLE to change into the other role in order to access those exclusive privileges. This attribute is set for new roles by default.

You can find out more about role attributes by checking out PostgreSQL's documentation on role attributes and the CREATE ROLE command .

As mentioned briefly above, a special privilege called superuser allows unrestricted administrative access to the database cluster. This is similar to the root account in Linux and Unix-like operating systems, but at the database level.

There must always be at least one role with superuser privileges in each database cluster. The initial superuser account is created during the installation process. The name of the initial superuser account can vary depending on the installation process, but most often, this account is called postgres .

It is not recommended to do your day-to-day work using an account with superuser privileges, both because of its potential for destructive actions and also to minimize the chance of compromising an account with broad access. Instead, most of the time, users should use accounts dedicated to the specific functions or data objects they are working with, only using the superuser accounts when more powerful access is required.

Now that you have a broad idea of what role attributes are and what types of privileges they allow, you should learn how to find the attributes applied to roles throughout PostgreSQL. This section will show you some commands to help you find the attributes set on roles in general and on your own current role specifically.

Listing all database roles and their attributes

There are a few different ways to check the attributes applied to roles throughout the system.

If you are using the psql command line client, you can take advantage of some helpful meta-commands which allow you to get role attribute information without a query.

The \du meta-command shows all roles and their attributes:

In this case, the postgres role is the default role with superuser privileges configured for this database cluster.

The equivalent SQL to list roles (discoverable by passing the -E or --echo-hidden flag when starting psql ) is:

A similar query that provides role attribute information (without the role membership component) is below. We use the psql meta-command \x on to output the results vertically for better readability here:

If you are only interested in seeing which roles have the superuser attribute, you can ask for a list explicitly:

Alternatively, you can list all users and their superuser status for a more complete picture:

The same information can be retrieved using PostgreSQL's "role" paradigm instead of its (sometimes ambiguous) "user" overlay with this slightly longer query instead:

Listing your own attributes

If you want to find the attributes of the role you are currently using, you can easily filter the output.

When using psql meta-commands, you can use the USER variable, which will be substituted with the current connected role. psql uses the colon ( : ) to interpolate variables:

To get a listing showing the values of all possible role attributes, you can use a query comparing the role name to the value returned by the CURRENT_ROLE PostgreSQL function . Again, we're using vertical output for readability:

To just check whether your current role has superuser privileges, you can type:

Check whether you have role management privileges

To make, alter, or delete roles, you must either have superuser or CREATEROLE privileges.

To check which roles within the system have role management privileges, type:

If you just want to know whether your current role has role management privileges, you can instead use:

Once you've verified that you have role management privileges, you can begin making, modifying, or removing roles within PostgreSQL.

One option to set role attributes is to declare them at the time that you create the role. This allows you to set the initial conditions for the role, but you can still modify them afterwards if you want to change the role's level of access. You can find more information about the CREATE ROLE command that we'll be using to familiarize yourself with the basic syntax.

One way of creating a role is from the command line. PostgreSQL includes a createuser command that will create a role within the database cluster with LOGIN privileges.

The general syntax is:

For example, to create a role named admin with superuser privileges while prompting for a password, you could type:

You would then be able to log in using the admin account according to the authentication methods outlined in the pg_hba.conf file .

To create roles using SQL , the general syntax looks like this:

Attributes can be defined by specifying them after the role name using WITH :

For example, to create a role named user1 that can login with the password secretpassword , you could type:

To instead create a role with superuser privileges (you must also be a superuser to successfully execute this command) that cannot login (user must use SET ROLE to change to this role), you could type:

To modify the attributes of existing roles, you can use the ALTER ROLE command instead. As with role creation, your current role must also have either superuser or CREATEROLE privileges. Users who do not have those privileges can only use the ALTER ROLE command to change their own password.

Altering roles allows you to change the attributes assigned to a role after creation. The same attributes mentioned in the role creation section can be used with the ALTER ROLE syntax. One difference is that each attribute type can be negated by adding the NO prefix. For example, to allow a role to login to the database cluster, you can give it the LOGIN attribute. To remove that ability, you'd alter the role by specifying NOLOGIN .

The ALTER ROLE command only changes the attributes are those explicitly mentioned. In other words, the ALTER ROLE command specifies changes to attributes, not a full set of new attributes.

To allow the user2 role to login to the database cluster, you can type:

Keep in mind that while this enables the ability to login, the allowed authentication methods are still controlled by the pg_hba.conf file.

If you want user2 to be able to login, create roles, and create databases instead, you can specify those three attributes, separated by spaces:

To revoke superuser status from a role (you can only execute this command using another superuser role), type:

To change the password for a role, you can type the following (all roles should be able to execute this command on their own role, regardless of CREATEROLE or superuser privileges):

Although the above command works, if possible, it is a better idea to use the psql meta-command to change passwords. The psql command automatically prompts for a password and encrypts it before sending it to the server. This helps avoid leaking sensitive data in logs.

You can change a role's password with psql by typing the following

You can also use the ALTER ROLE command to rename a role:

Keep in mind that you cannot rename your current session role.

Deleting an existing role follows a similar pattern to the previous commands. Again, you must have CREATEROLE or superuser privileges to execute these commands.

One complicating factor is that roles cannot be removed if they are still referenced by objects within the database. This means that you must delete or transfer ownership of any objects that the role owns. Afterwards, you must also revoke any additional privileges the role has on database objects.

A detailed explanation of how to appropriately reassign and drop privileges is provided by Erwin Brandstetter on the Database Administrators Stack Exchange site. This same process is used below.

First, you can reassign all of the role's owned objects using the REASSIGNED OWNED command. For instance, if you're preparing to delete the user2 role, you can assign its objects to the postgres role by typing:

Now the objects are owned by postgres , we can use the DROP OWNED command to revoke all of the other privileges we've been granted on objects. This command also deletes any objects we own, but since we have just transferred them to the postgres role, the user2 role no longer has any owned objects. Because of this, the command will only revoke any of the role's additional privileges:

Without the DROP OWNED shortcut above, you would have to execute REVOKE ALL PRIVILEGES on every individual object or object type that the role has privileges on.

Once you have revoked all associated privileges, you can remove the role by typing:

Once you have a new role configured, and have configured authentication details using the pg_hba.conf file , you can login to the database cluster using your new role. The psql command line client provides an easy way to do this.

By default, psql assumes you want to connect using a role that matches your operating system username. So if you are logged into your computer as john , psql will assume that you want to try to connect to the database using a role that's also called john .

To override this behavior, you can pass the -U or --username= option. For example, if you want to login to a role called kerry , you can type:

The success of the psql command will depend on the existence of the kerry role, the accessibility of the server you are trying to connect to, and the authentication rules defined on the server.

Sometimes, you may want to temporarily adopt the privileges and identity of another role you have access to. For instance, this is necessary if you want to gain the privileges of a role you are a member of if your current role does not have the INHERIT attribute.

To understand how this works, you have to know the terminology PostgreSQL uses to categorize active roles:

  • Session role : A session role is the role you logged in with during your initial connection to the PostgreSQL database cluster. It sets your initial privileges and determines your access to the system. This role must have the LOGIN attribute.
  • Current role : By contrast, the current role is the role that you are currently acting as. The privileges associated with the current role, whether set directly or inherited from other roles, determine the actions you are allowed to perform and the objects you have access to.

You can view your session and current role values by typing:

While the only way to change your session role is to start a new connection using a different role, you can change your current role using the SET ROLE command . The SET ROLE command is used to temporarily act as a different role. The command also optionally takes the following modifiers:

  • SESSION : The default setting. This causes the SET ROLE command to affect the entire database session.
  • LOCAL : This modifier will make the command change the role only for current transaction.

To change the current role to the user2 role (for the rest of the session), type:

If you check your session and current role values, you will see that the current role value has changed:

All of your actions will now use the user2 role as their context.

To change back to the session role you had been previously using, you can type:

An alternative that achieves the same result is:

PostgreSQL's system of roles, role attributes, grants, and authentication create a flexible system that allows administrators to effectively manage permissions and database access. This guide described what exactly roles are and how they encompass a broad range of use cases. It also covered how to create, modify, and delete roles and manage the role attributes that determine their global capabilities. Understanding how to manage these identities is necessary in order to secure your databases and provide usable access to your legitimate users.

To change a role's password in PostgreSQL, you can user the ALTER statement with PASSWORD . The syntax would look like this:

Alternatively, you can also use the psql meta-command like so:

To find the attributes of the current role , you can use psql meta-commands with the USER variable which will be substitued with the current connected role.

The basic syntax and result would look like:

To check whether a role can grant user privileges or create roles , you can use use the following SQL statement:

To check if your user has these privileges you can use the following statement:

REPLICATION is a role attribute in PostgreSQL that allows the role to initiate streaming replication .

Roles with this attribute must also have the LOGIN attribute.

You can create roles in PostgreSQL by using the CREATE ROLE command.

The basic syntax looks like:

And with attributes:

Justin Ellingwood

Justin Ellingwood

Prisma's data guide.

A growing library of articles focused on making databases more approachable.

HeatWare

Mastering PostgreSQL User Management: Users, Roles, Privileges, and Real-world Use Case

Photo of author

May 28, 2023

postgresql user management, postgres user management

Understanding PostgreSQL user management is essential for maintaining the security and proper function of your databases. This comprehensive guide aims to take you through the essentials of users, roles, and privileges in PostgreSQL, coupled with practical examples and a real-world production use case. Whether you’re a PostgreSQL beginner or a database professional, mastering these skills is a valuable asset.

Table of Contents

Understanding PostgreSQL Users and Roles

What is a postgresql user & role.

In PostgreSQL, a user is an entity that can interact with the database. Users can own database objects like tables, execute queries, and determine the operations others can perform on those objects.

A role is an encompassing term used for users, groups, and more. A role can own database objects, assign privileges on those objects to other roles, and group multiple roles into a single unit.

Roles come with various attributes that determine their capabilities, such as LOGIN, PASSWORD, SUPERUSER, CREATEDB, CREATEROLE, and INHERIT. For example, LOGIN allows the role to connect to the database, while CREATEDB lets the role create new databases.

Creating, Updating, and Deleting Users in PostgreSQL

Knowing how to manage users is vital in PostgreSQL administration. Let’s delve into the fundamental commands used to create, update, and delete users.

Creating Users

To create a new user, we use the CREATE ROLE SQL command along with attributes like LOGIN and PASSWORD. Here’s the syntax:

For example, to create a new user named “demo” with the password “demopassword”, the command would be:

If you are looking to create a user with full administrative access to PostgreSQL, create a superuser .

Updating Users

You can modify various role attributes using the ALTER ROLE command. To change a user’s password:

For example, to change the password of the “demo” user to “newdemopassword”:

To grant a user the ability to create databases:

For example, to grant the “demo” user this privilege:

Deleting Users

Deleting users in PostgreSQL is achieved using the DROP ROLE or DROP USER command. Here’s the basic syntax:

For example, to delete the “demo” user:

Remember, you can only delete a user if they don’t own any objects in the current database and aren’t a member of any existing roles.

Understanding Privileges in PostgreSQL

Privileges in PostgreSQL determine who can perform operations such as SELECT, INSERT, UPDATE, DELETE, and others on database objects.

Granting and Revoking Privileges

To grant privileges to a user, use the GRANT command. Here’s how to grant all privileges on a database to a user:

For example, to grant all privileges on a database named “testdb” to the “demo” user:

To revoke these privileges, use the REVOKE command:

For example, to revoke

all privileges on “testdb” from “demo”:

PostgreSQL User Management in a Production Environment

Let’s discuss a real-world example of how PostgreSQL user management might work in a production environment.

Suppose you’re managing a company’s database that has two teams: the development team and the finance team. Both teams need access to the database, but they require different levels of access.

Step 1: Role Creation

First, we create users (roles) for each team member:

Step 2: Group Roles

Next, we group these users into two group roles, “devteam” and “finteam”, using the CREATE ROLE command without the LOGIN attribute:

Step 3: Assign Users to Groups

We then assign each user to their respective group using the GRANT command:

Step 4: Granting Privileges

Finally, we grant appropriate privileges to each group. The devteam needs more access, so they get all privileges on the database, while the finteam only needs to execute SELECT queries:

This way, we maintain a clear separation of privileges while managing users effectively.

Mastering user management in PostgreSQL involves understanding the concepts of users, roles, and privileges, along with how to execute basic commands to create, update, and delete users. With this knowledge and the provided real-world production use case, you’re now equipped to handle PostgreSQL user management in various scenarios, ensuring the security and efficient operation of your databases.

Leave a Comment

Save my name, email, and website in this browser for the next time I comment.

Support us & keep this site free of annoying ads . Shop Amazon.com or Donate with Paypal

Home » PostgreSQL Administration » PostgreSQL ALTER ROLE Statement

PostgreSQL ALTER ROLE Statement

Summary : in this tutorial, you will learn how to use the PostgreSQL ALTER ROLE statement to modify the attributes of a role, rename a role, and change a role’s session default for a configuration variable.

Using the PostgreSQL ALTER ROLE to modify attributes of roles

To change attributes of a role , you use the following form of ALTER ROLE statement.

Here’s the basic syntax of the

The option can be:

  • SUPERUSER | NOSUPERUSER – determine if the role is a superuser or not.
  • CREATEDB | NOCREATEDB – allow the role to create new databases.
  • CREATEROLE | NOCREATEROLE – allow the role to create or change roles.
  • INHERIT | NOINHERIT – determine if the role inherits the privileges of roles of which it is a member.
  • LOGIN | NOLOGIN – allow the role to log in.
  • REPLICATION | NOREPLICATION – determine if the role is a replication role.
  • BYPASSRLS | NOBYPASSRLS – determine if the role is to bypass the row-level security (RLS) policy.
  • CONNECTION LIMIT limit – specify the number of concurrent connections a role can make, -1 means unlimited.
  • PASSWORD 'password' | PASSWORD NULL – change the role’s password.
  • VALID UNTIL 'timestamp' – set the date and time after which the role’s password is no longer valid.

The following rules are applied:

  • Superusers can change any of those attributes for any role.
  • Roles that have the CREATEROLE attribute can change any of these attributes for only non-superusers and no-replication roles.
  • Ordinal roles can only change their passwords.

First, log in to PostgreSQL server using the postgres role.

Second, create a new role called calf using the CREATE ROLE statement:

The calf role can log in with a password.

Because postgres is a superuser, it can change the role calf to be a superuser:

View the role calf :

The following statement sets the password of the role calf to expire until the end of 2050 :

Use the \du command to see the effect:

Using the PostgreSQL ALTER ROLE to rename roles

To change the name of a role, you use the following form of the ALTER ROLE statement:

In this syntax, you specify the name of the role after the ALTER ROLE keywords and the new name of the role after the TO keyword.

A superuser can rename any role. A role that has the CREATEROLE privilege can rename no-superuser roles.

If you use a role to log in to the PostgreSQL database server and rename it in the current session, you will get an error:

In this case, you need to connect to the PostgreSQL database server using a different role to rename that role.

You execute the following statement from the postgres ‘ session to rename the role calf to elephant :

Changing a role’s session default for a configuration variable

The following ALTER ROLE statement changes the role’s session default for a configuration variable:

In this syntax:

  • First, specify the name of the role that you want to modify the role’s session default, or use the CURRENT_USER , or SESSION_USER . You use the ALL option to change the settings for all roles.
  • Second, specify a database name after the IN DATABASE keyword to change only for sessions in the named database. In case you omit the IN DATABASE clause, the change will be applied to all databases.
  • Third, specify the configuration parameter and the new value in the SET clause.

Superusers can change the session defaults of any role. Roles with the CREATEROLE attribute can set the defaults for non-superuser roles. Ordinary roles can only set defaults for themselves. Only superusers can change a setting for all roles in all databases.

The following example uses the ALTER ROLE to give the role elephant a non-default, database-specific setting of the client_min_messages parameter:

  • Use the ALTER ROLE role_name option to modify the attributes of a role.
  • Use the ALTER ROLE role_name RENAME TO new_role statement to rename a role.
  • Use the ALTER ROLE role_name SET param=value statement to change a role’s session default for a configuration variable.

Understanding PostgreSQL roles and permissions

When you create an RDS for PostgreSQL DB instance using the AWS Management Console, an administrator account is created at the same time. By default, its name is postgres , as shown in the following screenshot:


        The default login identity for Credentials in the Create database page is postgres.

You can choose another name rather than accept the default ( postgres ). If you do, the name you choose must start with a letter and be between 1 and 16 alphanumeric characters. For simplicity's sake, we refer to this main user account by its default value ( postgres ) throughout this guide.

If you use the create-db-instance AWS CLI rather than the AWS Management Console, you create the name by passing it with the master-username parameter in the command. For more information, see Creating an Amazon RDS DB instance .

Whether you use the AWS Management Console, the AWS CLI, or the Amazon RDS API, and whether you use the default postgres name or choose a different name, this first database user account is a member of the rds_superuser group and has rds_superuser privileges.

Understanding the rds_superuser role

Controlling user access to the postgresql database, delegating and controlling user password management, using scram for postgresql password encryption.

In PostgreSQL, a role can define a user, a group, or a set of specific permissions granted to a group or user for various objects in the database. PostgreSQL commands to CREATE USER and CREATE GROUP have been replaced by the more general, CREATE ROLE with specific properties to distinguish database users. A database user can be thought of as a role with the LOGIN privilege.

The CREATE USER and CREATE GROUP commands can still be used. For more information, see Database Roles in the PostgreSQL documentation.

The postgres user is the most highly privileged database user on your RDS for PostgreSQL DB instance . It has the characteristics defined by the following CREATE ROLE statement.

The properties NOSUPERUSER , NOREPLICATION , INHERIT , and VALID UNTIL 'infinity' are the default options for CREATE ROLE, unless otherwise specified.

By default, postgres has privileges granted to the rds_superuser role, and permissions to create roles and databases. The rds_superuser role allows the postgres user to do the following:

Add extensions that are available for use with Amazon RDS. For more information, see Working with PostgreSQL features supported by Amazon RDS for PostgreSQL

Create roles for users and grant privileges to users. For more information, see CREATE ROLE and GRANT in the PostgreSQL documentation.

Create databases. For more information, see CREATE DATABASE in the PostgreSQL documentation.

Grant rds_superuser privileges to user roles that don't have these privileges, and revoke privileges as needed. We recommend that you grant this role only to those users who perform superuser tasks. In other words, you can grant this role to database administrators (DBAs) or system administrators.

Grant (and revoke) the rds_replication role to database users that don't have the rds_superuser role.

Grant (and revoke) the rds_password role to database users that don't have the rds_superuser role.

Obtain status information about all database connections by using the pg_stat_activity view. When needed, rds_superuser can stop any connections by using pg_terminate_backend or pg_cancel_backend .

In the CREATE ROLE postgres... statement, you can see that the postgres user role specifically disallows PostgreSQL superuser permissions. RDS for PostgreSQL is a managed service, so you can't access the host OS, and you can't connect using the PostgreSQL superuser account. Many of the tasks that require superuser access on a stand-alone PostgreSQL are managed automatically by Amazon RDS .

For more information about granting privileges, see GRANT in the PostgreSQL documentation.

The rds_superuser role is one of several predefined roles in an RDS for PostgreSQL DB instance.

In PostgreSQL 13 and earlier releases, predefined roles are known as default roles.

In the following list, you find some of the other predefined roles that are created automatically for a new RDS for PostgreSQL DB instance. Predefined roles and their privileges can't be changed. You can't drop, rename, or modify privileges for these predefined roles. Attempting to do so results in an error.

rds_password – A role that can change passwords and set up password constraints for database users. The rds_superuser role is granted with this role by default, and can grant the role to database users. For more information, see Controlling user access to the PostgreSQL database .

For RDS for PostgreSQL versions older than 14, rds_password role can change passwords and set up password constraints for database users and users with rds_superuser role. From RDS for PostgreSQL version 14 and later, rds_password role can change passwords and set up password constraints only for database users. Only users with rds_superuser role can perform these actions on other users with rds_superuser role.

rdsadmin – A role that's created to handle many of the management tasks that the administrator with superuser privileges would perform on a standalone PostgreSQL database. This role is used internally by RDS for PostgreSQL for many management tasks.

rdstopmgr – A role that's used internally by Amazon RDS to support Multi-AZ deployments.

To see all predefined roles, you can connect to your RDS for PostgreSQL DB instance and use the psql \du metacommand. The output looks as follows:

In the output, you can see that rds_superuser isn't a database user role (it can't login), but it has the privileges of many other roles. You can also see that database user postgres is a member of the rds_superuser role. As mentioned previously, postgres is the default value in the Amazon RDS console's Create database page. If you chose another name, that name is shown in the list of roles instead.

New databases in PostgreSQL are always created with a default set of privileges in the database's public schema that allow all database users and roles to create objects. These privileges allow database users to connect to the database, for example, and create temporary tables while connected.

To better control user access to the databases instances that you create on your RDS for PostgreSQL DB instance , we recommend that you revoke these default public privileges. After doing so, you then grant specific privileges for database users on a more granular basis, as shown in the following procedure.

To set up roles and privileges for a new database instance

Suppose you're setting up a database on a newly created RDS for PostgreSQL DB instance for use by several researchers, all of whom need read-write access to the database.

Use psql (or pgAdmin) to connect to your RDS for PostgreSQL DB instance:

When prompted, enter your password. The psql client connects and displays the default administrative connection database, postgres=> , as the prompt.

To prevent database users from creating objects in the public schema, do the following:

Next, you create a new database instance:

Revoke all privileges from the PUBLIC schema on this new database.

Create a role for database users.

Give database users that have this role the ability to connect to the database.

Grant all users with the lab_tech role all privileges on this database.

Create database users, as follows:

Grant these two users the privileges associated with the lab_tech role:

At this point, lab_user1 and lab_user2 can connect to the lab_db database. This example doesn't follow best practices for enterprise usage, which might include creating multiple database instances, different schemas, and granting limited permissions. For more complete information and additional scenarios, see Managing PostgreSQL Users and Roles .

For more information about privileges in PostgreSQL databases, see the GRANT command in the PostgreSQL documentation.

As a DBA, you might want to delegate the management of user passwords. Or, you might want to prevent database users from changing their passwords or reconfiguring password constraints, such as password lifetime. To ensure that only the database users that you choose can change password settings, you can turn on the restricted password management feature. When you activate this feature, only those database users that have been granted the rds_password role can manage passwords.

To use restricted password management, your RDS for PostgreSQL DB instance must be running PostgreSQL 10.6 or higher.

By default, this feature is off , as shown in the following:

To turn on this feature, you use a custom parameter group and change the setting for rds.restrict_password_commands to 1. Be sure to reboot your RDS for PostgreSQL DB instance so that the setting takes effect.

With this feature active, rds_password privileges are needed for the following SQL commands:

Renaming a role ( ALTER ROLE myrole RENAME TO newname ) is also restricted if the password uses the MD5 hashing algorithm.

With this feature active, attempting any of these SQL commands without the rds_password role permissions generates the following error:

We recommend that you grant the rds_password to only a few roles that you use solely for password management. If you grant rds_password privileges to database users that don't have rds_superuser privileges, you need to also grant them the CREATEROLE attribute.

Make sure that you verify password requirements such as expiration and needed complexity on the client side. If you use your own client-side utility for password related changes, the utility needs to be a member of rds_password and have CREATE ROLE privileges.

The Salted Challenge Response Authentication Mechanism (SCRAM) is an alternative to PostgreSQL's default message digest (MD5) algorithm for encrypting passwords. The SCRAM authentication mechanism is considered more secure than MD5. To learn more about these two different approaches to securing passwords, see Password Authentication in the PostgreSQL documentation.

We recommend that you use SCRAM rather than MD5 as the password encryption scheme for your RDS for PostgreSQL DB instance. It's a cryptographic challenge-response mechanism that uses the scram-sha-256 algorithm for password authentication and encryption.

You might need to update libraries for your client applications to support SCRAM. For example, JDBC versions before 42.2.0 don't support SCRAM. For more information, see PostgreSQL JDBC Driver in the PostgreSQL JDBC Driver documentation. For a list of other PostgreSQL drivers and SCRAM support, see List of drivers in the PostgreSQL documentation.

RDS for PostgreSQL version 13.1 and higher support scram-sha-256. These versions also let you configure your DB instance to require SCRAM, as discussed in the following procedures.

Setting up RDS for PostgreSQL DB instance to require SCRAM

you can require the RDS for PostgreSQL DB instance to accept only passwords that use the scram-sha-256 algorithm.

For existing RDS Proxies with PostgreSQL databases, if you modify the database authentication to use SCRAM only, the proxy becomes unavailable for up to 60 seconds. To avoid the issue, do one of the following:

Ensure that the database allows both SCRAM and MD5 authentication.

To use only SCRAM authentication, create a new proxy, migrate your application traffic to the new proxy, then delete the proxy previously associated with the database.

Before making changes to your system, be sure you understand the complete process, as follows:

Get information about all roles and password encryption for all database users.

Double-check the parameter settings for your RDS for PostgreSQL DB instance for the parameters that control password encryption.

If your RDS for PostgreSQL DB instance uses a default parameter group, you need to create a custom DB parameter group and apply it to your RDS for PostgreSQL DB instance so that you can modify parameters when needed. If your RDS for PostgreSQL DB instance uses a custom parameter group, you can modify the necessary parameters later in the process, as needed.

Change the password_encryption parameter to scram-sha-256 .

Notify all database users that they need to update their passwords. Do the same for your postgres account. The new passwords are encrypted and stored using the scram-sha-256 algorithm.

Verify that all passwords are encrypted using as the type of encryption.

If all passwords use scram-sha-256, you can change the rds.accepted_password_auth_method parameter from md5+scram to scram-sha-256 .

After you change rds.accepted_password_auth_method to scram-sha-256 alone, any users (roles) with md5 –encrypted passwords can't connect.

Getting ready to require SCRAM for your RDS for PostgreSQL DB instance

Before making any changes to your RDS for PostgreSQL DB instance, check all existing database user accounts. Also, check the type of encryption used for passwords. You can do these tasks by using the rds_tools extension. This extension is supported on RDS for PostgreSQL 13.1 and higher releases.

To get a list of database users (roles) and password encryption methods

Use psql to connect to your RDS for PostgreSQL DB instance , as shown in the following.

Install the rds_tools extension.

Get a listing of roles and encryption.

You see output similar to the following.

Creating a custom DB parameter group

If your RDS for PostgreSQL DB instance already uses a custom parameter group, you don't need to create a new one.

For an overview of parameter groups for Amazon RDS, see Working with parameters on your RDS for PostgreSQL DB instance .

The password encryption type used for passwords is set in one parameter, password_encryption . The encryption that the RDS for PostgreSQL DB instance allows is set in another parameter, rds.accepted_password_auth_method . Changing either of these from the default values requires that you create a custom DB parameter group and apply it to your instance.

You can also use the AWS Management Console or the RDS API to create a custom DB parameter group . For more information, see

You can now associate the custom parameter group with your DB instance.

To create a custom DB parameter group

Use the create-db-parameter-group CLI command to create the custom DB parameter group. This example uses postgres13 as the source for this custom parameter group.

For Linux, macOS, or Unix:

For Windows:

Use the modify-db-instance CLI command to apply this custom parameter group to your RDS for PostgreSQL DB cluster.

To resynchronize your RDS for PostgreSQL DB instance with your custom DB parameter group, you need to reboot the primary and all other instances of the cluster. To minimize impact to your users, schedule this to occur during your regular maintenance window.

Configuring password encryption to use SCRAM

The password encryption mechanism used by an RDS for PostgreSQL DB instance is set in the DB parameter group in the password_encryption parameter. Allowed values are unset, md5 , or scram-sha-256 . The default value depends on the RDS for PostgreSQL version, as follows:

RDS for PostgreSQL 14 and above – Default is scram-sha-256

RDS for PostgreSQL 13 – Default is md5

With a custom DB parameter group attached to your RDS for PostgreSQL DB instance, you can modify values for the password encryption parameter.


          Following, the RDS console shows the default values for the
              password_encryption parameters for RDS for PostgreSQL.

To change password encryption setting to scram-sha-256

Change the value of password encryption to scram-sha-256, as shown following. The change can be applied immediately because the parameter is dynamic, so a restart isn't required for the change to take effect.

Migrating passwords for user roles to SCRAM

You can migrate passwords for user roles to SCRAM as described following.

To migrate database user (role) passwords from MD5 to SCRAM

Log in as the administrator user (default user name, postgres ) as shown following.

Check the setting of the password_encryption parameter on your RDS for PostgreSQL DB instance by using the following command.

Change the value of this parameter to scram-sha-256. This is a dynamic parameter, so you don't need to reboot the instance after making this change. Check the value again to make sure that it's now set to scram-sha-256 , as follows.

Notify all database users to change their passwords. Be sure to also change your own password for account postgres (the database user with rds_superuser privileges).

Repeat the process for all databases on your RDS for PostgreSQL DB instance.

Changing parameter to require SCRAM

This is the final step in the process. After you make the change in the following procedure, any user accounts (roles) that still use md5 encryption for passwords can't log in to the RDS for PostgreSQL DB instance.

The rds.accepted_password_auth_method specifies the encryption method that the RDS for PostgreSQL DB instance accepts for a user password during the login process. The default value is md5+scram , meaning that either method is accepted. In the following image, you can find the default setting for this parameter.


        The RDS console showing the default and allowed values for the rds.accepted_password_auth_method parameters.

The allowed values for this parameter are md5+scram or scram alone. Changing this parameter value to scram makes this a requirement.

To change the parameter value to require SCRAM authentication for passwords

Verify that all database user passwords for all databases on your RDS for PostgreSQL DB instance use scram-sha-256 for password encryption. To do so, query rds_tools for the role (user) and encryption type, as follows.

Repeat the query across all DB instances in your RDS for PostgreSQL DB instance.

If all passwords use scram-sha-256, you can proceed.

Change the value of the accepted password authentication to scram-sha-256, as follows.

Warning

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Thanks for letting us know we're doing a good job!

If you've got a moment, please tell us what we did right so we can do more of it.

Thanks for letting us know this page needs work. We're sorry we let you down.

If you've got a moment, please tell us how we can make the documentation better.

  • SQL Server training
  • Write for us!

Daniel Calbimonte

PostgreSQL tutorial to create a user

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

  • PgAdmin installed. In this example, the PgAdmin 4 version 6.12 was used.
  • 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.

PgAdmin create login

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

PostgreSQL tutorial to create a user - General tab

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)

Definition tab

Also, in the Privilege s 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 superuse r 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.

PostgreSQL tutorial to create a user -Privileges tab to create login

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.

Membership tab for the PostgreSQL tutorial

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

PostgreSQL tutorial to create a user -Parameter tab to create a login or group 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.

Security tab

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:

PostgreSQL tutorial to create a user -Default system roles in Postgres

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.

PostgreSQL tutorial to create a user -Example to create a role

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.

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.

  • Recent Posts

Daniel Calbimonte

  • 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

Related posts:

  • Migrating SQL Server graph databases to ArangoDB
  • MySQL group_concat() function overview
  • Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps
  • How to Index Foreign Key Columns in SQL Server
  • How to overcome parameter sniffing problems in ad-hoc queries

assign role to user postgresql

19 February 2024

Printer friendly version

Database Feature Toggles

In software development the concept of feature toggles are used to selectively turn on and off features. They are, for example, used to restrict some newly introduced features to a select group to see how these features work. While this concept has been long used for user-facing application code, it is also a practice that is useful for database code.

In databases, feature toggles are used for several loosely related purposes. One common use is to separate, or decouple, rollout of database features from deployment of new versions of the application code. This allows developers to release software faster and with less risk.

Feature toggles involve wrapping features in ways that allow you to toggle them on and off for different groups of users. They are designed to give developers a way to manage their feature rollouts and ‘dark launch’ without having to synchronize with the application deployment schedule, and then to quickly remove access to new features that have problems.

In databases, feature toggles are sometimes confused with access controls, which are traditionally used in databases to allow access only to those parts of the database that are necessary for the role of a particular user. A toggle or switch must be able to work easily with procedural SQL, normally by indicating whether the current user is a member of the test group whereas access control will affect the result SQL Queries.

For database work, database roles are, in many RDBMSs, used for controlling access. In their traditional role of data security they ease the work of admin because they can be given membership to other database roles, fixed or user-defined; and inherit their access rights. A large number of users can thereby have their access rights controlled by a single DCL command. Because membership of any user can be queried easily, returning true or false, it is a simple way of implementing feature toggles as well. This is because they are built into the relational database system, designed for inheritance of access privileges, and are both enforced and managed without any extra work.

Database requirements for toggling

It is probably useful at this point to list the problems that database feature toggles solve, and the real-life uses to which they are put.

Allowing partial feature rollout – ‘canary releases’

Sometimes, you will need to control the rollout of database changes for a new feature, so that it can be introduced first to specific groups of ‘beta’ users. The feature rollout is restricted to a selected group so that if there are flaws to the new feature it affects only a few people who are good at giving feedback and aren’t ‘spooked’ by what we tend to call ‘wet paint’ features that might need more work.

If the new feature is severely wrong, you need to be able to switch it out completely so that the users simply revert to the current system. This switch must be simple and well-tested to ensure that reversion is complete

A/B testing

Sometimes, it is difficult to know in advance which is the best approach to the design of a feature. You will need to implement it in alternative forms, for two or more different groups of users, to compare their performance, usability, or other metrics, before making a permanent decision.

Parallel development

Sometimes, different development teams or individuals need to work both independently and concurrently on database features, without conflicts. The difficulty with this is in meeting a common development and deployment schedule because it is difficult to predict when either new feature will be complete and pass all its functional and acceptance tests. They cannot commit to a shared release date so will want a way of toggling these features on or off independently.

Hotfixes and emergency changes

Developers want to be able to do hotfixes or emergency changes to the database without a full release cycle. Instead, the change can be toggled in or out.

Decoupling deployments

Feature toggles can decouple the deployment of database changes from the release of new application versions if the interface between database and application isn’t changed. This allows for more frequent and independent updates to the database schema.

Conditional feature activation for a database

Sometimes, developers are forced to maintain several variants of a database purely because of international differences in commercial procedures, legal requirements, or financial obligations, even when most of the database features are common to all.

Using a single version of a database schema, you need to install several copies, activating database-level features based on context without changes in the schema. The correct settings are put in place on deployment for each installed database.

Concurrent handling of different ‘roles’ for the various types of users

Sometimes it is necessary to host several different types of users on the same database, where individuals or groups (roles) have different tasks, culture, access requirements or geographic locations. When this becomes more complex than access control, it must be possible to switch ‘toggles’ in the database to select the correct version of any procedures and views that need to vary between roles.

Practical techniques

The following sections describe a few typical techniques that are used by many developers to achieve feature toggling.

Application Contexts and Session Contexts

Within applications, it is usual to employ toggles and switches, usually via special application tables that are manipulated by a privileged user. This essentially switches features on or off, but unless you can somehow distinguish the identity of individual users or application logons, it can be complicated to do any toggling of features for individual users or groups of users, or to switch groups of users between different variants of a feature.

The first problem is in identifying the user. If the current session is identified by the user login, then it is a simple matter, and easy to determine whether the user should access the feature.

For applications that share connections, such as websites, Session, or application, contexts are used to identify each session. These are valuable when the application does not make connection to the database via individual user connections, but instead assigns sessions to which the user identity is attached. Basically, the application is responsible for identifying the user. Many relational database systems support Application contexts, or Session contexts, because they can provide fine-grained access control by providing key/value attributes that relate to the current session. As well as holding temporary values, the values in these columns can be to switch features on and off, or to determine whether the session can use a feature. However, if the application user exists as a user or role, then its membership is easily ascertained and easily administered.

Using Database Roles

For most of the leading relational databases, such a device, the database role , already exists within the SQL standard and it can be used to toggle features for groups of users. Relational databases have faced the problem of having users with different organizational roles for a long time. After all, HR records contain highly personal information, and it is standard practice to allow users access only to the information they require for their role within the organisation. Commercial databases have always had the wherewithal to control access to database objects according to the user’s role. Role-based access control (RBAC) also makes access controls easier to administer because users inherit the access rights from the roles of which they are members. To allow a more versatile, and tidier approach, schemas can be used alongside RBAC in the major RDBMSs.

Role-based Access Control (RBAC)

All established techniques for feature toggling rely on a database administrator assigning individual users to roles. You could, for example, become a ‘guinea-pig’ for a new feature by being assigned to, or being a member of, a particular role. The SQL role feature is even more powerful than this because you can assign a role to a role so that, for example, with one line of DCL (Data Control Language) SQL, an administrator can allow all members of the HR role to become testers for your new feature.

Using roles

Database user roles are conventionally used for RBAC to group together a set of privileges or permissions. Instead of assigning permissions to individual users, permissions are granted to roles, and users are assigned to one or more roles. Roles can be granted to users or to roles to provide specific privileges, and roles can be revoked to remove those privileges.

For a feature that needs to be switched, all it needs to do is to check that the user (or application role) has membership of a role with a certain name. If it has, only then will the feature work. The feature will execute different procedural code according to whether the user is a member of that role, either directly or indirectly. To make this happen, the logic must be built into the feature

Using roles and schemas together

Schema-based access control is available on the RDBMSs that are designed for enterprises, such as PostgreSQL and SQL Server. With the introduction of schemas, it becomes easy to apply different logic for each role without putting any extra logic in the feature; we simply assign roles to default to specific schemas. If the feature toggle is based within the application, then the application can, instead, explicitly specify the correct schema for that group of users. Within each schema there is a copy of the ‘interface’ functions, views and procedures.

This way, the features are used when they are used as a default schema. This can be done by the user accessing objects without a schema prefix, so the user will, by default, use the version of each function that is within their default schema. It may also be done by having the application append the schema when calls are made to the database manager.

This means that functions and views that implement the feature can present the same interface to the application, but each version of these objects can provide a different functionality. Different roles see a different ‘variant’ of the database, but all roles share the base tables of the database.

Schema-based access control is designed to ensure that users, by their membership of roles, have access only to the information that they need for their group role, within the organization. For example, only HR users will have access to salary information, and only customer-facing staff will have access to customer information.

I explain Schema-based access control in depth here in Schema-Based Access Control for SQL Server Databases

Determining whether a user is a ‘Guinea Pig’

In our practical examples, we will assume that each user of the system is defined within the system as a database ‘user’, because of the advantages to auditing that this confers. This is unlikely to be true of a website database or a microservice, but here there are several group credentials that can each represent a group of users. This application user is then assigned one or more roles.

Whatever type of role-based access system you use, you are likely to find that the first information you need in procedural code is whether the current user of the connection is allowed to use the feature. For example, let’s imagine you are live testing a new ‘Payroll’ system. Initially, it will be accessible only to members of the ‘Guineapig’ role, who will evaluate it. Membership of ‘Guineapig’ must include only existing members of the HR role. To preserve the current sanity levels of the DBA, it must be possible to revert everyone back to the current Payroll system very quickly, using just one line of DCL code that drops HR from membership of the ‘Guineapigs’ role.

The simplest type of ‘feature switch’ merely determines whether a user is, either directly or by inheritance, a member of a particular role. The interface with the application(s) can then determine how to handle the request. In our payroll example, any user who is a member of the ‘Guineapigs’ role will be allowed to use it. To switch the feature off for HR, you start by dropping membership of the ‘HR’ role from the ‘Guineapigs’ role. You’d then have to ensure that you haven’t given membership member of the ‘Guineapigs’ role by inheritance. Because roles can be nested, it pays to ensure with a system query that that they haven’t an indirect membership, otherwise it isn’t entirely simple to determine this.

In the SQL standard, there is a clear distinction between users and roles, because users do not automatically pass on their privileges to their members, whereas roles do. The better RDBMSs all follow this rule.

In SQL Server, there is a difference between a user and the roles assigned to it. It is easy to determine if a user is a member of a particular group role. This code will tell you if the current user is assigned to a ‘Guineapigs’ role (1 if so, else 0)

Oracle, like SQL Server, supports roles as a means of managing access and permissions, but with the addition of predefined user roles that can inherit privileges from other one. However it is sufficient for our purposes to use an ordinary role. The following code will tell you if the current user is assigned to a ‘Guineapigs’ role (1 if so, else 0)

In PostgreSQL, there is no real difference between a login role and a group role . To allow users to become members of roles, your SQL roles in PostgreSQL must have the INHERIT attribute, which is the default. With the right privileges, you, in your user role, can create group roles and assign them to, or revoke them from, other user roles.

Conversely, to prevent users from becoming members of users, it is best to specify NOINHERIT for your SQL users.

Roles were implemented as a GSoC 2013 project. In MariaDB, you can create roles, grant privileges to roles, and assign users to roles. Here you can use roles to switch features on and off easily. The only difference seems to be that a role granted directly to a user must be ‘Set’ before inheriting its privileges, unless it is a default role, so it would seem that feature toggles would be easiest to implement if every user had a default role that would determine their privileges.

Implementation issues

A database build or migration is concerned with DDL (Data-definition Language) SQL operations, but neither DML (Data Manipulation Language) nor (Data Control Language). Both DML, usually the loading of the default development data set, and the definition of the access controls are done after the build because these will depend on the database environment. Access controls are entirely different for a production system and the data will, hopefully, be entirely different too. In both cases, I find it best to apply any DML or DCL operations after a migration run completes.

For example, in the Flyway tool, but there are plenty of valid approaches but you’d probably want to have two separate SQL callback scripts, one that ensured that the roles were all defined and is idempotent (makes no assumptions about the state or database version), The second would define the role memberships that determine the state of the toggles and the users. This would be configurable for each separate environment.. It is wise to remember that a DDL database version won’t match either the DDL or DCL state. In both cases, the relationship could be ‘many-to-many’.

If your RDBMS supports schema-based access, this offers a neat way of feature-toggling, but this will mean some re-engineering work to an existing database and to the applications using it. However, if you are having to wrestle with the complications of variants in the way a database works for different groups of users, then it is worth the effort. Otherwise, you can adopt a more relaxed approach where you apply the logic within views or procedures, based on the membership of user roles.

Database roles are part of the SQL Standard, though the way they should be implemented isn’t precisely defined. Nevertheless, they are an excellent way of simplifying the assignments of access rights to individual users but are useful for controlling access of individual users to variants in the functionality. It is simpler to do more complex feature toggling in SQL Server or PostgreSQL because of their use of schemas but database-level can be done by every RDBMS that implements roles, even where users are identified only at the application level.

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Rate this article

assign role to user postgresql

Phil Factor

  • The Phrenetic Phoughts of Phil Factor
  • Phil writing about MongoDB
  • Phil writing for Redgate Product Learning
  • Phil on Twitter

Follow Phil Factor via

View all articles by Phil Factor

Load comments

Related articles

assign role to user postgresql

Metrics that matter for IT organizations on an agile journey

  • Software Delivery

You are using an outdated browser. Please upgrade your browser to improve your experience.

Backing up with pgBackRest

pgBackrest, an open source backup and restore solution for PostgreSQL, is included with VMware Postgres. You can configure PostgreSQL and pgBackRest to set up backup and restore for your Postgres databases.

Install pgBackRest Perl prerequisites.

Create pgBackRest configuration directories and files.

Check that pgBackRest is properly installed.

You will see errors if any dependencies are missing.

See the pgBackRest User Guide for help configuring Postgres and pgBackRest, and for backing up and restoring your Postgres databases.

Using pgAudit for logging

VMware Postgres includes the PostgreSQL extension pgAudit, that allows users to monitor specific operations and objects. pgAudit enhances the native Postgres logging abilities with improved log formatting and filtering operations, that assists with regulation compliance.

Installing pgAudit

Use the following table as a reference of PostgreSQL to pgAudit version mapping:

Install pgAudit using the appropriate package name for your VMware Postgres. For example:

Add pgAudit to the shared_preload_libraries in the postgresql.conf configuration file. For example:

Restart the database to apply the change into Postgres.

Enable the pgAudit extension:

Verify the installation using a command similar to:

Session Audit logging

Session logging will log operations performend by a specific user. Use the parameter pgaudit.log to start auditing, depending on your requirements.

To view the default pgAudit parameters, log into your database, and use the system view pg_settings in a command similar to:

which returns an output similar to:

Use the configuration parameter pgaudit.log , and a comma-separated list, to specify the classes of statements that will be logged in session mode. The possible values are: READ, WRITE, FUNCTION, ROLE, DDL, MISC, MISC_SET, ALL and NONE. The default is none . For details on each of the options, see pgaudit.log in the official pgAudit documentation.

Set pgaudit.log in postgresql.conf to apply globally, use ALTER DATABASE <your-database> SET pgaudit.log = '<your-values>' to monitor per-database, and use ALTER ROLE <your-database> SET pgaudit.log = '<your-values>' to monitor per-user.

Inspect the Postgres log files to confirm logging works as expected.

To audit all the reads, and writes, run:

To create a separate log entry for each relation reference in a statement, use:

Object Audit Logging

Object logging will audit all actions performed on a specific object, using the parameter pgaudit.role . Object mode supports SELECT, INSERT, UPDATE, and DELETE statements only.

Use object logging when you wish restrict logging to a subset of objects, and avoid monitoring all the session level information. In order to leverage specific object auditing you need to create an auditor role and grant that role on the objects you wish to audit.

To log every social security number read by anyone with the role auditor_role assigned, while avoiding logging everything with a session-level audit log, use:

For further details and examples, see Object Audit Logging in the pgAudit documentation.

Configuring psqlODBC

You configure psqlODBC by placing settings into a configuration file, odbc.ini , and then using an ODBC driver manager such as UnixODBC or iODBC to load the configuration file. See 7.3. Configuration Files in the PostgreSQL documentation for information about the ODBC driver configuration settings.

See also psqlODBC - PostgreSQL ODBC driver for additional HOWTOs and release-specific information associated with the driver software.

Configuring pgjdbc

In order to use the pgjdbc driver from a Java application, you must include the driver software in your CLASSPATH as described in Setting up the Class Path .

See also the full documentation at The PostgreSQL JDBC Interface for information about initializing and using the JDBC driver in your Java applications.

IMAGES

  1. PostgreSQL Roles

    assign role to user postgresql

  2. Create a PostgreSQL User / Role

    assign role to user postgresql

  3. PostgresQL Create Role By Practical Examples

    assign role to user postgresql

  4. How to Use Roles and Manage Permissions in PostgreSQL • CloudSigma

    assign role to user postgresql

  5. How to create user in PostgreSQL

    assign role to user postgresql

  6. How to Create User in PostgreSQL (Postgres) Using PgAdmin

    assign role to user postgresql

VIDEO

  1. Create a RBAC role and assign to user and Categorie

  2. How to assign user roles via role management in TeamViewer (Classic)

  3. Assign Roles to a User Group

  4. SQL Sesi #5

  5. Introduction to Multi-Version Concurrency Control and Vacuum

  6. GU UNIT 3

COMMENTS

  1. PostgreSQL: Documentation: 16: 22.3. Role Membership

    To set up a group role, first create the role: CREATE ROLE name ; Typically a role being used as a group would not have the LOGIN attribute, though you can set it if you wish. Once the group role exists, you can add and remove members using the GRANT and REVOKE commands: GRANT group_role TO role1, ... ; REVOKE group_role FROM role1, ... ;

  2. How to Set Up Users and Roles in PostgreSQL

    User roles in PostgreSQL lets you assign permissions to roles rather than directly to users, and then assign those roles to users. This abstraction makes it easier to manage your databases, and improves security as it is less likely that you will accidentally grant the wrong permissions to the wrong user.

  3. How To Use Roles and Manage Grant Permissions in PostgreSQL on a VPS

    First, make sure your server is running by using the systemctl start command: sudo systemctl start postgresql.service Then, you can switch to the postgres account by typing: sudo -i -u postgres You can now access the PostgreSQL prompt immediately by typing: psql To list the roles in your Postgres instance, type the following command: \ du Output

  4. Managing PostgreSQL users and roles

    Assign the applicable roles to these users to quickly grant them the same permissions as the role. For example, grant the readwrite role to app_user and grant the readonly role to reporting_user. At any time, you can remove the role from the user in order to revoke the permissions. The following diagram summarizes these recommendations:

  5. PostgreSQL: Documentation: 16: ALTER ROLE

    Description ALTER ROLE changes the attributes of a PostgreSQL role. The first variant of this command listed in the synopsis can change many of the role attributes that can be specified in CREATE ROLE. (All the possible attributes are covered, except that there are no options for adding or removing memberships; use GRANT and REVOKE for that.)

  6. PostgreSQL: Documentation: 16: Chapter 22. Database Roles

    Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

  7. PostgreSQL Role Membership

    To add a role to a group role, you use the following form of the GRANT statement: GRANT group_role to user_role; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql) For example, the following statement creates a new role alice and add the role alice to the group role sales:

  8. PostgreSQL CREATE ROLE Statement

    To create a new role in a PostgreSQL server, you use the CREATE ROLE statement. Here's the basic syntax of the CREATE ROLE statement: CREATE ROLE role_name; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql) When you create a role, it is valid in all databases within the database server (or cluster).

  9. How to Create Roles in PostgreSQL

    To create a new role in PostgreSQL, you can use the CREATE ROLE statement. This statement allows you to specify the name of the role, along with any additional parameters such as login, password, and role attributes.

  10. PostgreSQL Basics: Roles and Privileges

    Before we get started, lets establish a few terms: Roles: There is only one type of authentication principal in PostgreSQL, a ROLE, which exists at the cluster level.By convention, a ROLE that allows login is considered a user, while a role that is not allowed to login is a group.Please note, while the CREATE USER and CREATE GROUP commands still exist, they are simply aliases for CREATE ROLE.

  11. Managing roles & attributes with PostgreSQL

    Listing your own attributes. If you want to find the attributes of the role you are currently using, you can easily filter the output. When using psql meta-commands, you can use the USER variable, which will be substituted with the current connected role. psql uses the colon (:) to interpolate variables: \du :USER.

  12. sql

    8 Answers Sorted by: 457 All commands must be executed while connected to the right database cluster. Make sure of it. Roles are objects of the database cluster. All databases of the same cluster share the set of defined roles. Privileges are granted / revoked per database / schema / table etc.

  13. how to specify login role (user) in postgresql

    1 If you are logged into the same computer that Postgres is running on you can use the following psql login command, specifying the database (mydb) and username (myuser): psql -d mydb -U myuser If for some reason you are not prompted for a password when issuing these commands, you can use the -W option:

  14. Assigning privileges and roles to PostgreSQL users

    To assign roles to a cluster user, pass the list of required roles in the --grants parameter. This completely overwrites existing roles: if you want to extend or reduce the available list, first request the current roles with user information by running the yc managed-postgresql user get command. To assign roles, run the command:

  15. Mastering PostgreSQL User Management: Users, Roles, Privileges, and

    What is a PostgreSQL User & Role? In PostgreSQL, a user is an entity that can interact with the database. Users can own database objects like tables, execute queries, and determine the operations others can perform on those objects. A role is an encompassing term used for users, groups, and more. A role can own database objects, assign ...

  16. PostgreSQL ALTER ROLE Statement

    To change the name of a role, you use the following form of the ALTER ROLE statement: ALTER ROLE role_name TO new_name; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql) In this syntax, you specify the name of the role after the ALTER ROLE keywords and the new name of the role after the TO keyword. A superuser can rename any role.

  17. Understanding PostgreSQL roles and permissions

    Understanding the rds_superuser role. In PostgreSQL, a role can define a user, a group, or a set of specific permissions granted to a group or user for various objects in the database. PostgreSQL commands to CREATE USER and CREATE GROUP have been replaced by the more general, CREATE ROLE with specific properties to distinguish database users.

  18. postgresql

    5 Answers Sorted by: 19 Try something like: GRANT A TO B; It will grant all right of role A to B. For details read this chapter of the manual. Share Improve this answer

  19. PostgreSQL tutorial to create a user

    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 ...

  20. postgresql

    ie. Assign one set of roles to a user on one database, and another set of roles to the same user in a different database on the same server. I figure one way around this would be to create a new "training" role with full access, and then limit this role to just the training database using pg_hba.conf.

  21. What roles does a postgresql database make use of?

    I've created a backup of a database on one computer postgres@machine1$ pg_dump mydb > mydb.dump.sql and then, from a second computer, having created a mydb database, I restore it from the dump

  22. Database Feature Toggles

    PostgreSQL. In PostgreSQL, there is no real difference between a login role and a group role.To allow users to become members of roles, your SQL roles in PostgreSQL must have the INHERIT attribute, which is the default. With the right privileges, you, in your user role, can create group roles and assign them to, or revoke them from, other user roles.

  23. Configuring and Using VMware Postgres

    Set pgaudit.log in postgresql.conf to apply globally, use ALTER DATABASE <your-database> SET pgaudit.log = '<your-values>' to monitor per-database, and use ALTER ROLE <your-database> SET pgaudit.log = '<your-values>' to monitor per-user. Inspect the Postgres log files to confirm logging works as expected. Example: To audit all the reads, and ...