PostgreSQL User Creation and Least Privilege Role Assignment

Introduction

In this article, I will guide you on how to create an account and apply role assignment following the Principle of Least Privilege, in order to grant only the exact permissions that the application needs.

Detail

To create a new user, you use

-- CREATE USER {username} WITH PASSWORD '{password}';
CREATE USER backend_user WITH PASSWORD 'backend_password';

-- CREATE ROLE {username} LOGIN PASSWORD '{password}';
CREATE ROLE backend_user LOGIN PASSWORD 'backend_password';

• Here, using CREATE USER is the old syntax, which supports LOGIN by default and it acts as an alias for CREATE ROLE
• In newer versions of Postgres, you should use CREATE ROLE because it is more flexible
  • If used with LOGIN, it creates a user
  • Without LOGIN, it creates a group role

When newly created, the user will by default have no permissions to

• SELECT data
• INSERT
• UPDATE
• DELETE
• CREATE TABLE
• CREATE FUNCTION
• DROP TABLE
• EXECUTE function
• USAGE on schema

Instead, they can only LOGIN and execute SQL statements that do not require special privileges, such as:

SELECT current_user;
SELECT version();
SELECT now();

To grant permission to the user

-- Statement 1
-- GRANT CONNECT ON DATABASE {database-name} TO {username};
GRANT CONNECT ON DATABASE postgres TO backend_user;

-- Statement 2
-- GRANT {clause} ON SCHEMA {schema-name} TO {username};
GRANT USAGE ON SCHEMA public TO backend_user;
GRANT CREATE ON SCHEMA public TO backend_user;

-- Statement 3
-- GRANT {clause} ON TABLE {table-name} TO {username};
GRANT SELECT ON TABLE account TO backend_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE account TO backend_user;

-- Statement 4
-- GRANT {clause} ON ALL TABLES IN SCHEMA {schema-name} TO {username};
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backend_user;

-- Statement 5
GRANT ALL ON TABLE account TO backend_user;

-- Statement 6
GRANT ALL ON ALL TABLES IN SCHEMA public TO backend_user;

-- Statement 7
-- GRANT EXECUTE ON FUNCTION {function} TO {username};
GRANT EXECUTE ON FUNCTION create_account(TEXT, TEXT) TO backend_user;

• Statement 1: Allows the user to connect to the database
• Statement 2: Grants permissions on the schema
  • USAGE: Allows access to the schema
  • CREATE: Allows creating tables, functions and views in the schema
• Statement 3: Grants permissions for a specific table
• Statement 4: Grants permissions for all tables in the schema
• Statement 5: Grants all privileges on a specific table
• Statement 6: Grants all privileges on all tables in the schema
• Statement 7: Grants permission to execute a function

To revoke permission from the user

-- Statement 1
-- REVOKE {clause} ON {table-name} FROM {username};
REVOKE INSERT ON account FROM backend_user;
REVOKE SELECT, INSERT, UPDATE, DELETE ON account FROM backend_user;

-- Statement 2
-- REVOKE ALL ON TABLE {table-name} FROM {username};
REVOKE ALL ON TABLE account FROM backend_user;

-- Statement 3
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM backend_user;

• Statement 1: Revokes permissions from a specific table
• Statement 2: Revokes all privileges from a specific table
• Statement 3: Revokes all privileges from all tables in the schema, note that this only applies to existing tables and does not affect tables created in the future

Next, to check and utilize the created role

-- Statement 1
SELECT session_user, current_user;

-- Statement 2
-- SET ROLE backend_user;
SET ROLE {username};

-- Statement 3
SET ROLE NONE;

• Statement 1: Retrieves session and current user information
• Statement 2: Switches the role
• Statement 3: Resets the role back to the original state

You can verify that when executing a query without permission, it will return an error

Happy coding!

See more articles here.