fix: postgresql user creation/deletion missing required privilege and cleanup steps #292
Description
When creating a PostgreSQL database and user on the affected versions listed above, the user is created, but does not receive the required privileges on the public schema, resulting in:
ERROR: permission denied for schema public
Additionally, when trying to delete the user, we got this:
ERROR: role "db_user" cannot be dropped because some objects depend on it
DETAIL: 2 objects in database db_name
This happens because OS does not automatically run the necessary SQL steps to grant proper schema privileges when creating the user and clean up and release ownership when deleting the user. As a result, the user must drop user and database manually.
Note
This issue doesn't affect PostgreSQL on versions 12-14.
This is only happening on 15-16.
Steps to reproduce
- Create a database and user:
os db create --db-type postgresql --db-name db_name
os db create-user --db-type postgresql --db-name db_name --username db_user --password 'db_pass' --privileges all- Attempt to write on the database:
psql -U db_user -d db_name -h 127.0.0.1 -c "CREATE TABLE public.test_permissions (id INT);"
ERROR: permission denied for schema public
LINE 1: CREATE TABLE public.test_permissions (id INT);- Attempt to remove the user:
os db delete-user --db-type postgresql --db-name db_name --db-username db_user2025-11-28T19:59:27Z ERR DeleteDatabaseUserError err="{\"stdErr\":\"ERROR: role \\\"db_user\\\" cannot be dropped because some objects depend on it\\nDETAIL: 4 objects in database db_name\\n\",\"exitCode\":1}"
{
"status": "infraError",
"body": "DeleteDatabaseUserInfraError"
}
Required manual workaround
After creating the user, the following SQL must be run manually:
psql -U postgres -d db_name <<EOF
GRANT USAGE ON SCHEMA public TO db_user;
GRANT CREATE ON SCHEMA public TO db_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO db_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO db_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO db_user;
EOFBefore deleting the user, the following must be run manually:
psql -U postgres -d db_name -c "DROP OWNED BY db_user;"Without these, the delete-user command cannot succeed.
Note
This issue doesn't affect PostgreSQL on versions 12-14.
This is only happening on 15-16.