0%
June 7, 2024

Role and User Management in PostgreSQL

db-management

postgresql

sql

Contents

Create User and Drop User

CREATE USER testuser WITH PASSWORD 'testpassword123321';
DROP USER IF EXISTS testuser;

User = Role + Login Persmission

From AWS Documentation:

  • Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.

  • Therefore managing users is enough and we will ignore roles in users management.

  • Also from PostgreSQL documentation command that applies to a role can also be applied to a user as well.

Grant Permissions to a User

In the sequel let's assume we are working on:

  • a database called billie
  • a user called testuser
Connection

A permission to connect to a database:

GRANT CONNECT ON DATABASE billie TO testuser;
Enable or Disable to Grant Usage on Schema (Optional)
  • If a user is not a database maintainer, that user should not have this right and this part can be skipped.
  • Although a usage is granted, there is no right yet.
GRANT USAGE ON SCHEMA public TO testuser;
REVOKE USAGE ON SCHEMA public FROM testuser;
Grant or Revoke Usage (Create, Delete, Alter Table) on Schema
Create Table
GRANT USAGE, CREATE ON SCHEMA public TO testuser;

or the opposite

REVOKE USAGE, CREATE ON SCHEMA public FROM testuser;
Alter Table (No such thing)

From PostgreSQL Documentation:

  • Only the owner of a table can alter the table.
  • Each table can only have one owner (not including superuser).
  • In worst case, superuser can change the table ownership by 
    ALTER TABLE table_name OWNER TO new_owner_name;
    and let another developer take care of that table.
Delete Table (No such thing)

  • Only the owner of a table can drop the table.

  • Therefore in theory only two users can drop a table.

Grant SELECT, INSERT, UPDATE, DELETE Rights
For Existing Tables only

Note that the following command only applies to existing tables. The user has no privilege to new tables created after the execution.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO testuser;

We need to run the next command (in the next code block) ALTER DEFAULT ... in order to apply the changes to new tables as well.

Extends to Tables in the Future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testuser;

Note that we will be needing to run the opposite

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM testuser;

in order to delete this role.

Create a User with Limited Rights for Backend's CRUD Service

Creation
1CREATE USER testuser WITH PASSWORD 'aaaabbb';
2
3GRANT CONNECT ON DATABASE billie TO testuser;
4GRANT USAGE ON SCHEMA public TO testuser;
5GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO testuser;
6GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO testuser;
7
8ALTER DEFAULT PRIVILEGES IN SCHEMA public 
9  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testuser;
10--or
11  REVOKE SELECT ON TABLES FROM testuser

  • Line 4 is necessary, without that testuser can view the tables but cannot even select within the table.

  • Line 6 is necessary, otherwise testuser cannot insert record with auto-incremented counter as id, unless the only type of id we use is UUID.

  • Lines 8-11 are needed only when you want to adjust the right of testuser.

If you need to grant CREATE right of a schema to a person (e.g., table creation, usually a trusted person who performs well), add:

GRANT USAGE, CREATE ON SCHEMA public TO testuser;
Deletion
1ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE 
2SELECT, INSERT, UPDATE, DELETE ON TABLES FROM testuser;
3
4REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM testuser
5REVOKE ALL PRIVILEGES ON DATABASE billie FROM testuser;
6REVOKE USAGE ON SCHEMA public FROM testuser;
7DROP USER IF EXISTS testuser;
  • Lines 4-7 should be executed one by one. Running in batch may fail.

Useful SQL for Querying Users and Their Priviledges

View a list of Database Users
SELECT usename AS role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

View Priviledges Owned by a User
SELECT 
    grantee,
    table_schema AS schema,
    table_name,
    privilege_type AS privilege,
    grantor
FROM information_schema.table_privileges
WHERE grantee = 'testuser';

Now we can REVOKE the right one by one.