Why keycloak application need full privilege user to run the application

Getting advice
1

Hi All,
Can anyone tell, when we are configuring the keycloak application with database access, why we need full privilege for database user? Our company policy does not allow application database user to have full privilege. Only CRUD operations are allowed for application user. Is there any option we can avoid using full privilege for keycloak application DB user?

2

Keycloak often needs to create, modify, and drop database tables, indexes, and other schema objects as part of its operation. This is particularly relevant when upgrading Keycloak or when new features are added that require changes to the schema.

@prifulnath

1 Like
3

Thanks for your reply. So, we need to have a full permission user always for keycloak application? There is no way we can use a user with CRUD permissions, right?

1 Like
4

I recommend to give keycloak the privileges it needs but if there is no chance to give it, you can handle all the operation manually, meaning you will create the schema, update, upgrade it … and all that type pf DBA workload @prifulnath

5

Yeah db_owner is usually the issue. A workaround is often something like the following:

For Sql Server, these permissions generally work. db_datawriter
db_datareader
db_ddladmin…

There isn’t a direct one-to-one equivalent in PostgreSQL for SQL Server’s db_datawriter, db_datareader, and db_ddladmin roles. However, you can set up similar permissions in PostgreSQL using the following approach:

  1. Create a custom role for each permission set:
CREATE ROLE db_datawriter;
CREATE ROLE db_datareader;
CREATE ROLE db_ddladmin;
  1. Grant appropriate permissions to these roles:

For db_datawriter:

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

For db_datareader:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_datareader;

For db_ddladmin:

GRANT CREATE, ALTER, DROP ON ALL TABLES IN SCHEMA public TO db_ddladmin;
GRANT USAGE, CREATE ON SCHEMA public TO db_ddladmin;
  1. Assign these roles to users as needed:
GRANT db_datawriter TO username;
GRANT db_datareader TO username;
GRANT db_ddladmin TO username;

Remember that in PostgreSQL, you need to explicitly grant permissions on new objects to roles after creation. You can use default privileges to automate this process:

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO db_datareader;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT INSERT, UPDATE, DELETE ON TABLES TO db_datawriter;

This approach allows you to create a permission structure similar to SQL Server’s roles while adapting to PostgreSQL’s more flexible role-based access control system[1][3].

Citations:
[1] What is the equivalent of SQL Server’s db_datareader/db_datawriter … permissions - What is the equivalent of SQL Server's db_datareader/db_datawriter in PostgreSQL? - Database Administrators Stack Exchange
[2] Options for Granting Privileges in SQL Server, Oracle and PostgreSQL Options for Granting Privileges in SQL Server, Oracle and PostgreSQL
[3] Postgres permissions to create objects, comparable to SQL Server postgresql - Postgres permissions to create objects, comparable to SQL Server - Stack Overflow
[4] PostgreSQL vs SQL Server: 16 Critical Differences - Kinsta PostgreSQL vs SQL Server: 16 Critical Differences
[5] Postgre-SQL Flexible Server - pg_cron permissions and alternative Postgre-SQL Flexible Server - pg_cron permissions and alternative - Microsoft Q&A

1 Like
6

I suggest create an entire separate database for keycloak for many reasons.
This is one of them.
When you have this, you can give it the permissions it wants with no problems of leaks.

1 Like