Having multiple users but don’t want them to use the same account?
In such a case you can use a role to group your users together.
And even better you can set default privileges for that role before creating anything important in your database to have your access privileges setup right from the get go!
In PostgreSQL terms the only difference between a role and an user is that the user comes with login permission from the start.

Setup

Lets start by creating a database, an admin_role and two admins.

postgres=# create database role_privileges;
CREATE DATABASE
postgres=# create role admin_role with nologin;
CREATE ROLE
postgres=# create user admin_1;
CREATE ROLE
postgres=# create user admin_2;
CREATE ROLE

Giving them the needed connection permissions and then adding them to the admin_role.

postgres=# grant connect on database role_privileges to admin_role ;
GRANT
postgres=# grant connect, create on database role_privileges to admin_role ;
GRANT
postgres=# grant admin_role to admin_1;
GRANT ROLE
postgres=# grant admin_role to admin_2;
GRANT ROLE

Lets have a quick look at the roles. As you can see the admins are part of the admin_role.

postgres=# \du
                                     List of roles
 Role name  |                         Attributes                         |  Member of
------------+------------------------------------------------------------+--------------
 admin_1    |                                                            | {admin_role}
 admin_2    |                                                            | {admin_role}
 admin_role | Cannot login                                               | {}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Now lets create a schema, schema_1, which can be accessed by the admin_role, as well as making it its owner.

postgres=# \c role_privileges
You are now connected to database "role_privileges" as user "postgres".
role_privileges=# create schema schema_1 authorization admin_role;
CREATE SCHEMA
role_privileges=# alter schema schema_1 owner to admin_role ;
ALTER SCHEMA

To not have to grant the privileges for every new table that is created we grant select, update and insert for all tables to the admin_role. This will cause any future tables, created by the admin_role, to have these privileges.

role_privileges=> alter default privileges in schema schema_1 grant select, update, insert on tables to admin_role ;
ALTER DEFAULT PRIVILEGES
role_privileges=> \ddp
                 Default access privileges
   Owner    |  Schema  | Type  |     Access privileges
------------+----------+-------+---------------------------
 admin_role | schema_1 | table | admin_role=arw/admin_role

Creating and reading tables

Alright, now lets create some tables as admin_1.

psql -U admin_1 -d role_privileges;
role_privileges=> create table schema_1.admin_1_random as select s, md5(random()::text) from generate_Series(1,2) s;
SELECT 2
role_privileges=> select * from schema_1.admin_1_random ;
 s |               md5
---+----------------------------------
 1 | 1c5b383c15bb1008e3ad4605656be9e5
 2 | c76cd774416c3c515d107236a6371f1c
(2 rows)

Nice, looking good so far. Lets see if we can read that as admin_2.

psql -U admin_2 -d role_privileges;
role_privileges=> select * from schema_1.admin_1_random ;
ERROR:  permission denied for table admin_1_random

Probably not what you were expecting. Lets have a closer look at the table permissions. Since we created the table as admin_1 it is owned by admin_1, not surprising and also how it should be. Let’s check what kind of privileges are active on the table.

role_privileges=> \dt schema_1.admin_1_random 
              List of relations
  Schema  |      Name      | Type  |  Owner
----------+----------------+-------+---------
 schema_1 | admin_1_random | table | admin_1
(1 row)

role_privileges=> \ddp
                 Default access privileges
   Owner    |  Schema  | Type  |     Access privileges
------------+----------+-------+---------------------------
 admin_role | schema_1 | table | admin_role=arw/admin_role
(1 row)

As we can see our default privileges have been applied. And as our admins are members of the admin_role you’d think they would be able to read each others tables freely.
That is not right, as the privileges have been granted to the admin_role and not directly to the admin users.

Correct way to use a group role

To have the desired privileges apply correctly the tables have to be created by the admin_role instead of on of the admin users.
So let’s create a new table as the admin_role and check if we can read that table as an admin user

role_privileges=> set role admin_role ;
SET
role_privileges=> create table schema_1.admin_role_random as select s, md5(random()::text) from generate_Series(1,2) s;
SELECT 2
role_privileges=> \q
psql -U admin_2 -d role_privileges;
role_privileges=> select * from schema_1.admin_role_random ;
s | md5
---+----------------------------------
1 | 4f9f6030e61c810d94e18db2bc167c6b
2 | 91b7c3d7f79aee9e5273adc387267e58
(2 rows)
role_privileges=> \q
psql -U admin_1 -d role_privileges;

role_privileges=> select * from schema_1.admin_role_random ;
s | md5
---+----------------------------------
1 | 4f9f6030e61c810d94e18db2bc167c6b
2 | 91b7c3d7f79aee9e5273adc387267e58
(2 rows)

Very nice, now that is the expected behavior. Both admins are able to read the newly created table.

The permissions also show the ownership of admin_role. Just to make it clear the attributes of the first table are displayed as well.

role_privileges=> \dt schema_1.admin_role_random
                 List of relations
  Schema  |       Name        | Type  |   Owner
----------+-------------------+-------+------------
 schema_1 | admin_role_random | table | admin_role
(1 row)

role_privileges=> \dt schema_1.admin_1_random
              List of relations
  Schema  |      Name      | Type  |  Owner
----------+----------------+-------+---------
 schema_1 | admin_1_random | table | admin_1
(1 row)

If you use a role to group users that will create tables these will have to created with the role itself.

If you are looking for an easy way to configure a role to be able to read everything in a database, you should check out pg_read_all_data defined role which is available from PostgreSQL 14+

Post Views: 2,050
Thumbnail [60x60]
by
Daniel Burgert