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

 Copy
\du

Set up a test database

Let’s create a quick database to explore roles:

 Copy
CREATE DATABASE finance;
CREATE SCHEMA accounting;

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

 Copy
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:

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

Assume the simon identity for this session:

 Copy
SET ROLE simon;

Adding privileges to roles

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

 Copy
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:

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

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

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

Now lets try that simon identity again:

 Copy
SET ROLE simon;

Verify you can query the invoices table:

 Copy
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:

 Copy
SET ROLE postgres;

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

 Copy
REVOKE CONNECT ON DATABASE finance FROM simon;
REVOKE USAGE ON SCHEMA accounting FROM simon;
REVOKE SELECT, INSERT, UPDATE, DELETE ON accounting.invoices FROM simon;

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.

 Copy
SET ROLE postgres;

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

 Copy
GRANT accounting_ro TO simon;

Look at the list of roles:

 Copy
\du

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

Create a new customers table:

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

Assume the simon identity:

 Copy
SET ROLE simon;

Verify the invoices table can be queried:

 Copy
SELECT * FROM accounting.invoices;

Verify the customers table can be queried:

 Copy
SELECT * FROM accounting.customers;

 Copy
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:

 Copy
SET ROLE postgres;

 Copy
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

 Copy
ALTER DEFAULT PRIVILEGES IN SCHEMA accounting GRANT SELECT ON TABLES TO accounting_ro;

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: postgres, application, 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.

Conclusion

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.