Roles in PostgreSQL

There are no users, only roles!

See https://www.postgresql.org/docs/current/user-manag.html

PostgreSQL manages database access permissions using the concept of roles.

Roles englobes the concepts of “users” and “groups”.

Users or groups: 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.
Owners: 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.
Inheritance: It is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles.

Simply put:

A user is a role that can connect to the database. The role is defined as having LOGIN capability

Database roles

You create a new role with:

CREATE ROLE <name>;

To determine existing roles, examine the pg_roles table:

SELECT rolname FROM pg_roles;

And to find only the roles that can login i.e the users:

SELECT rolname FROM pg_roles WHERE rolcanlogin;

On my system this returns

 SELECT * FROM pg_roles WHERE rolcanlogin;
-[ RECORD 1 ]--+---------
rolname        | alexis
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  | [null]
rolbypassrls   | t
rolconfig      | [null]
oid            | 10
-[ RECORD 2 ]--+---------
rolname        | spock
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  | [null]
rolbypassrls   | f
rolconfig      | [null]
oid            | 82244

The role/user alexis can do anything. it is the superuser! Whole spock is not allowed to do much.

\du shows a more readable version of the table:

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 alexis    | Superuser, Create role, Create DB, Replication, Bypass RLS
 spock     |

When you create the server

In order to bootstrap the database system, a freshly initialized system always contains one predefined login-capable role.

This role is always a “superuser”, and it will have the same name as the operating system user that initialized the database cluster with initdb. This role is often named postgres.

Note: On macOS, it's designed to run under your user account rather than a dedicated system user. Homebrew installs are generally meant to be single-user focused, avoiding system-wide installations that require root privileges. Which explains why we don't see a postgres user or role in the table

In all cases (Mac, linux or windows), in order to create more roles you first have to connect as this initial role.

Creating a new user

Only roles that have the LOGIN attribute can be used as the initial role name for a database connection.

These 2 statements are equivalent.

CREATE ROLE <name> LOGIN;
CREATE USER <name>;

Default permissions

The statement create role <name is equivalent to

CREATE ROLE new_role WITH
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOINHERIT
    NOLOGIN
    NOREPLICATION
    CONNECTION LIMIT -1;

The role is not superuser, can't create databases, or roles, can't login and inherit or do replication. But it has no restrictions on the number of connections.

no restrictions on the number of connections ? but it cannot connect!?

Right but a user that could connect can be given, can be associated with this role. In that case the new_role will not limit the number of connections of the user.

In a mixture of roles it is always the most restrictive permission that takes precedence.

Other default permissions

These privileges are automatically granted (more in schemas in a mn, hold on!)

GRANT USAGE ON SCHEMA public TO PUBLIC;
GRANT CREATE ON SCHEMA public TO PUBLIC;  -- Note: many DBAs revoke this in production

and New roles can:

EXECUTE any function/procedure in public schema
Use any language marked as TRUSTED
Use any installed extension in public schema

But new roles CANNOT by default:

CREATE database
CREATE role
Access tables/views created by other users
Create objects in schemas other than public

Granting

To allow role to log in

ALTER ROLE new_role LOGIN;

To allow role to create databases

ALTER ROLE new_role CREATEDB;

To allow role to access specific database

GRANT CONNECT ON DATABASE your_database TO new_role;

To allow role to use a specific schema

GRANT USAGE ON SCHEMA schema_name TO new_role;

security

For a more secure setup

Revoke public schema creation from PUBLIC (common security practice)

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
``

Revoke public EXECUTE permission on functions (if needed)

```sql
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;

Grant specific permissions as needed

GRANT USAGE ON SCHEMA app_schema TO new_role;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO new_role;

Example of setting up a basic working role:

Always start by creating a role with minimal required privileges

CREATE ROLE app_user;
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app_schema TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app_schema TO app_user;

For future tables:

ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT USAGE, SELECT ON SEQUENCES TO app_user;

what roles can do or don't

super user has great powers

superuser status: A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser. To create a new database superuser, use CREATE ROLE name SUPERUSER. You must do this as a role that is already a superuser.

database creation: A role must be explicitly given permission to create databases (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE name CREATEDB.

role creation: same with creating roles

specify the password: A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. Specify a password upon role creation with CREATE ROLE name PASSWORD 'string'.

connection limit: Connection limit can specify how many concurrent connections a role can make. -1 (the default) means no limit. Specify connection limit upon role creation with CREATE ROLE name CONNECTION LIMIT 'integer'.

Example of INHERIT

CREATE ROLE joe LOGIN; CREATE ROLE admin; CREATE ROLE wheel; CREATE ROLE island; GRANT admin TO joe WITH INHERIT TRUE; GRANT wheel TO admin WITH INHERIT FALSE; GRANT island TO joe WITH INHERIT TRUE, SET FALSE;

Immediately after connecting as role joe, a database session will have use of privileges granted directly to joe plus any privileges granted to admin and island, because joe “inherits” those privileges.

However, privileges granted to wheel are not available, because even though joe is indirectly a member of wheel, the membership is via admin which was granted using WITH INHERIT FALSE.

Ownership

each object has an owner which has super powers on the object

Transfering ownership

-- Transfer ownership of specific tables ALTER TABLE trees OWNER TO trees_admin; ALTER TABLE maintenance_logs OWNER TO trees_admin;

predefined roles

https://www.postgresql.org/docs/current/predefined-roles.html

Predefined Roles

Role	Allowed Access
pg_read_all_data	Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.
pg_write_all_data	Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.

example

Create a basic user with login privilege

CREATE USER john WITH PASSWORD 'secure123';

Create a user with additional attributes

CREATE USER sarah WITH
    PASSWORD 'secure456'
    VALID UNTIL '2025-12-31'
    CONNECTION LIMIT 5;

Create a superuser (be careful with this in production!)

CREATE USER admin_user WITH
    SUPERUSER
    CREATEDB
    CREATEROLE
    LOGIN
    PASSWORD 'very_secure789';

Modify existing user attributes

ALTER USER john WITH CONNECTION LIMIT 2;
ALTER USER john VALID UNTIL '2024-12-31';

Set user-specific configurations

ALTER USER sarah SET search_path TO tree_inventory, public;
ALTER USER sarah SET timezone = 'Europe/Paris';

search_path & schemas

Schemas in PostgreSQL are like folders that help organize database objects (tables, views, functions, etc.).

Schemas are important to manage and secure access to important resources.

see https://neon.tech/postgresql/postgresql-administration/postgresql-schema

Default Schema

By default, objects go into the 'public' schema

CREATE TABLE trees (
    id SERIAL PRIMARY KEY,
    species VARCHAR(100),
    height NUMERIC(5,2)
);

Custom Schemas

Create schemas for different aspects of tree management

CREATE SCHEMA tree_maintenance;
CREATE SCHEMA tree_statistics;

Then create tables in specific schemas:

CREATE TABLE tree_maintenance.inspections (
    tree_id INTEGER REFERENCES public.trees(id),
    inspection_date DATE
);

Search Path:

The search path determines which schemas PostgreSQL looks in when you reference an object without specifying its schema
Default is typically: "$user", public

so your user can only see objects that are in

your own schema (can be empty be default if you have not created anything in it yet)
the public schema

A superuser can of course access all schemas.

Check current search path

SHOW search_path;

-- Modify search path
SET search_path TO tree_maintenance, public;

-- Now you can write queries without schema qualification
SELECT * FROM inspections; -- looks in tree_maintenance first, then public

Organization: Separate administrative data from scientific measurements
Access Control: Grant permissions at the schema level
Namespace isolation: Same table names in different schemas won't conflict

Existing schemas

There are a few shemas besides the public one in your PostgreSQL server:

SELECT schema_name
FROM information_schema.schemata;

returns:

   schema_name
--------------------
 information_schema
 pg_catalog
 pg_toast
 public

information_schema: Contains standardized views providing information about the database
- The tables in information_schema are actually views that query data from pg_catalog.
- ANSI/ISO standard - works across different SQL databases
- More user-friendly names
- Slower than pg_catalog (views on top of pg_catalog)
pg_catalog: Contains PostgreSQL-specific system tables and views
- PostgreSQL-specific
- More complete information
- Better performance
- Required for PostgreSQL-specific features
pg_toast: TOAST (The Oversized-Attribute Storage Technique) schema, handles storage of large field values. TOAST is PostgreSQL's strategy for handling large field values that won't fit into the standard page size (typically 8KB).
- Automatic large value handling
- Transparent to users
- Part of PostgreSQL's storage strategy for large values

Resources

https://neon.tech/postgresql/postgresql-administration/postgresql-schema

https://www.postgresql.org/docs/current/ddl-schemas.html

PostgreSQL Roles Management