Tutorial Instructions

Postgres Users and Roles

Let’s learn about creating and managing PostgreSQL database user roles.

Groups and users

A role can be either an individual database user, or a group of database users. A group could be a representation of a job description i.e. accountant, sales, marketing etc.

Database roles are global across a database cluster installation, they are not per individual database.

Show all roles

At any time, you can use psql to show you the roles in your database


Set up a test database

Let’s create a quick database to explore roles:


CREATE TABLE accounting.invoices( invoice_id serial PRIMARY KEY, invoice_date DATE, amount NUMERIC );

INSERT INTO accounting.invoices(invoice_date, amount) VALUES ('2024-03-15', 250.50); INSERT INTO accounting.invoices(invoice_date, amount) VALUES ('2024-01-20', 110.99); INSERT INTO accounting.invoices(invoice_date, amount) VALUES ('2024-03-29', 1000);

Creating a new user role

Let’s create a new role for a person named simon. Postgres is case sensitive, so using all lowercase will make basic sql and psql interactions easier. Create a role for this user with a password:

CREATE ROLE simon WITH PASSWORD '21654641seeswf!2@' LOGIN;

Assume the simon identity for this session:

SET ROLE simon;

Adding privileges to roles

With our new login, now let’s query the invoices table:

SELECT * FROM accounting.invoices;
ERROR:  permission denied for schema accounting
LINE 1: SELECT FROM accounting.invoices;

This error is expected, the role simon only have the permission to login. To fix that, assume the postgres identity:

SET ROLE postgres;

Add the necessary privileges to query the table invoices: Note that adding the GRANT SELECT privilege will grant ‘read’ permissions to this user.

GRANT CONNECT ON DATABASE finance to simon; GRANT USAGE ON SCHEMA accounting TO simon; GRANT SELECT ON accounting.invoices TO simon;

We can also add write privileges to insert, update, or delete as needed. Note that these can be added separately as needed.

GRANT INSERT, UPDATE, DELETE accounting.invoices TO simon;

Now lets try that simon identity again:

SET ROLE simon;

Verify you can query the invoices table:

SELECT * FROM accounting.invoices;

The role simon is now able to query the data from the table accounting.invoices.

Removing role privileges

To remove a privilege use the REVOKE and FROM keywords.

Assume the postgres identity:

SET ROLE postgres;

Similar to the grant commands used earlier, replace GRANT with REVOKE and TO with FROM


Creating group roles

Imagine you have a lot of employees; creating individual permissions could become tedious quite fast. Instead of manually assigning grants to every user, it's recommended to:

  • Create a "group" role
  • Grant the necessary privileges for users to perform their tasks to that role
  • Grant the newly created role to the user role

Postgres used to have a system of groups and roles, but that was phased out in favor of roles within roles.

Let’s try making a group. Create a read only role named accounting_ro for the accounting department.

SET ROLE postgres;

CREATE ROLE accounting_ro NOLOGIN; GRANT CONNECT ON DATABASE finance TO accounting_ro; GRANT USAGE ON SCHEMA accounting TO accounting_ro; GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO accounting_ro;

Notice the role creation is using NOLOGIN. This ensure no one can connect using that role, look at this role more like a role definition for a group.

Now grant role membership accounting_ro to simon.

GRANT accounting_ro TO simon;

Look at the list of roles:


A new role accounting_ro has been created and simon is now a member of that role.

Create a new customers table:

CREATE TABLE accounting.customers( customer_id serial PRIMARY KEY, name TEXT, address TEXT );

Assume the simon identity:

SET ROLE simon;

Verify the invoices table can be queried:

SELECT * FROM accounting.invoices;

Verify the customers table can be queried:

SELECT * FROM accounting.customers;
ERROR: permission denied for table customers

This error message is expected. At the time we granted SELECT ON ALL TABLES to the role accounting_ro, the customers table did not exist. To fix this, we can add GRANT permissions for the accounting.customers table.

Assume the postgres identity:

SET ROLE postgres;
GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO accounting_ro;

Granting access to all new tables in the schema

To grant access to new tables in the future automatically, the default privileges in the schema needs to be created.

Alter the default privileges so any new tables also have select privileges


Roles for your applications and other tools

Just like you gave simon access to some of the accounting tables, you’ll also need to have roles for each service or application that connects to your database. You might need roles for:

  • application
  • analytics

Roles and logins in connection strings

When you’re creating roles inside the database, you may also need to use these roles when making connections to the database from your other applications or services.

The connection string as a database URL contains the following parameters:

  • protocol: postgres://
  • username: postgresapplication, user , etc
  • password:
  • hostname:cluster hostname
  • port: defaults to 5432 unless you’ve altered this
  • database name: usually postgres unless you’ve created a new database

Here’s a visual example of a connection string.

Principal of least privilege

When giving out roles and privileges in Postgres, keep in mind that for the safety of your data and security of your business, you should give out role privileges based on the least amount of privilege the role should have. Never assume more or add roles in addition to the roles core duty. Roles can always be edited.


Roles are an important part of any database. As you saw during this tutorial, Postgres roles are flexible and powerful. You can:

  • Leverage role membership to help with role maintenance
  • Tailor the roles for your needs and control who has access to the data

Always keep in mind the best practices when creating roles. Follow the least amount of privileges approach as it will ensure tight and secure access to your data.

See our tutorial on row level security for additional layers of role and table security.

Loading terminal...

Loading terminal...